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
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
