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

[Ben Madin]

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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users