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

[Pete Yunker]

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