[postgis-users] Update column to nearest neighbor's value using subselect

[Pete Yunker]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110513/9c333623/attachment.html>