[postgis-users] Update from spatial query problem

[Stephen Woodbridge]

On 1/3/2013 2:27 PM, Ulises F-Troche wrote:
> Hi group,
>
> I'm new to PostGIS and so far I'm really liking it.  I'm trying to
> update a table based on a spatial query but I have not been able to do
> it successfully.  I have 3 tables as follow:
>
> table1 (areas):
> - id (polygon code)
> - name (polygon name)
> - geom (polygon)
>
> table2 (non spatial table for persons)
> - code (person id)
> - name (person name)
> - lastname (person last name)
> - area (area name as appears in table1)
>
> table3 (person location)
> - code (person id)
> - geom (point)
>
> I need to update table2.area (which is null) based on the location of
> each point in table3. The following query provides the location
> information required...
>
> select table3.*,table1.name
> from table3,table1
> where st_within(table3.geom,table1.geom);
>
> but when try to create the update statement it is updating all records
> with the same value (area name), which is the same of the first record
> returned by the selection query...
>
> update table2
> set area=mytable.name
> from (select table1.name <http://table1.name/>
> from
> table3,table1
> where st_within(table3.geom,table1.geom)) AS mytable;

Your update has no qualifier that says "What record int table2 should 
get updated" so all records are getting updated.

This might give you better results:

update table2 set area=mytable.name
from (select table1.name
         from table3, table1
         where st_within(table3.geom,table1.geom)) AS mytable
where mytable.code=table2.code;

-Steve


> Any help to figure this out is appreciated.
>
> Thanks,
>
> Ulises
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>