Pete, I'm not very good at these, but I don't think you have specified your columns enough in the subselect. There isn't any relationship between this query
> 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 and the insert. Does this return the same value every time? By this I mean, you have the_geom in your subselect, but no reference to a table outside the subselect. If you run this query, > UPDATE working.zip_tornado_index z > SET tornado_index = ( > SELECT b.tornado_index > FROM working.zip_tornado_index b > WHERE b.tornado_index IS NOT NULL > AND ST_DWithin(z.the_geom,b.the_geom,0.1) > ORDER BY ST_Distance(z.the_geom,b.the_geom) ASC NULLS LAST > LIMIT 1 > ) > WHERE tornado_index IS NULL; is the result any different? Other's can probably help more than I. cheers Ben On 14/05/2011, at 5:13 AM, Pete Yunker wrote: > 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
