I have a table containing tornado index values by zip code. A small number of
the zip codes do not have values. I would like to update the tornado_index
values for those records by simply using the value for the nearest zip_code (by
comparing distance to the centroid) in the same table. The UPDATE statement
that I use executes, but it appears to return the same record from the
sub-select each time. Doesn't the sub-select get executed for each record, as
it depends on a value (the_geom) from the outer table? Is there a better way
to do this?
-- Using postgres 8.3.6 and postgis 1.3
-- working.zip_tornado_index --
id int
zip_code text
tornado_index int
the_geom geometry (SRID=4326)
UPDATE working.zip_tornado_index
SET tornado_index = (
SELECT b.tornado_index
FROM working.zip_tornado_index b
WHERE b.tornado_index IS NOT NULL
AND ST_DWithin(the_geom,b.the_geom,0.1)
ORDER BY ST_Distance(the_geom,b.the_geom) ASC NULLS LAST
LIMIT 1
)
WHERE tornado_index IS NULL
;
Thanks,
Pete_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
