[postgis-users] Update from spatial query problem

[Ulises F-Troche]

Thanks, Steve...

it worked great! In my statement the last line was missing.


________________________________
 From: Stephen Woodbridge <woodbri at swoodbridge.com>
To: postgis-users at lists.osgeo.org 
Sent: Sunday, January 6, 2013 8:56 AM
Subject: Re: [postgis-users] Update from spatial query problem
 
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 <woodbri at swoodbridge.com>
> *To:* postgis-users at lists.osgeo.org
> *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
>  > postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>  > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>  >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130106/3696f066/attachment.html>