Thanks Ben, that was the problem. On May 13, 2011, at 8:43 PM, Ben Madin wrote:
> 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
