Sorry, try one of these:
update table2 set area=mytable.name
from (select table1.name, table3.code
from table3, table1
where st_within(table3.geom,table1.geom)) AS mytable
where mytable.code=table2.code;
or
update table2 set area=table1.name
from table3, table1
where st_within(table3.geom, table1.geom)
and table3.code=table2.code;
-Steve
On 1/6/2013 2:26 AM, Ulises F-Troche wrote:
Thanks for the suggestion, Steve, but when I ran your suggested
statement, it fail with...
ERROR: columnn mytable.code do not exists
LINE 6: where mytable.code=table2.code;
^
********** Error **********
ERROR: no existe la columna mytable.code
SQL state: 42703
Character: 191
------------------------------------------------------------------------
*From:* Stephen Woodbridge <[email protected]>
*To:* [email protected]
*Sent:* Thursday, January 3, 2013 2:39 PM
*Subject:* Re: [postgis-users] Update from spatial query problem
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/> <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] <mailto:[email protected]>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
