<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Thanks, Steve...</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal;">it worked great! In my statement the last line was missing.</div><div><br></div> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"> <div dir="ltr"> <font size="2" face="Arial"> <hr size="1"> <b><span style="font-weight:bold;">From:</span></b> Stephen Woodbridge <woodbri@swoodbridge.com><br> <b><span style="font-weight:
bold;">To:</span></b> postgis-users@lists.osgeo.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Sunday, January 6, 2013 8:56 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Update from spatial query problem<br> </font> </div> <br>
Sorry, try one of these:<br><br><br>update table2 set area=mytable.name<br> from (select <a target="_blank" href="http://table1.name/">table1.name</a>, table3.code<br> from table3, table1<br> where st_within(table3.geom,table1.geom)) AS mytable<br> where mytable.code=table2.code;<br><br>or<br><br>update table2 set area=table1.name<br> from table3, table1<br> where st_within(table3.geom, table1.geom)<br> and table3.code=table2.code;<br><br>-Steve<br><br>On 1/6/2013 2:26 AM, Ulises F-Troche wrote:<br>> Thanks for the suggestion, Steve, but when I ran your suggested<br>> statement, it fail with...<br>><br>> ERROR: columnn mytable.code do not exists<br>> LINE 6: where mytable.code=table2.code;<br>> ^<br>><br>> ********** Error **********<br>><br>>
ERROR: no existe la columna mytable.code<br>> SQL state: 42703<br>> Character: 191<br>><br>><br>> ------------------------------------------------------------------------<br>> *From:* Stephen Woodbridge <<a ymailto="mailto:woodbri@swoodbridge.com" href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>><br>> *To:* <a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>> *Sent:* Thursday, January 3, 2013 2:39 PM<br>> *Subject:* Re: [postgis-users] Update from spatial query problem<br>><br>> On 1/3/2013 2:27 PM, Ulises F-Troche wrote:<br>> > Hi group,<br>> ><br>> > I'm new to PostGIS and so far I'm really liking it. I'm trying to<br>> > update a table based on a spatial query but I have not been able to do<br>> > it successfully. I have 3 tables as
follow:<br>> ><br>> > table1 (areas):<br>> > - id (polygon code)<br>> > - name (polygon name)<br>> > - geom (polygon)<br>> ><br>> > table2 (non spatial table for persons)<br>> > - code (person id)<br>> > - name (person name)<br>> > - lastname (person last name)<br>> > - area (area name as appears in table1)<br>> ><br>> > table3 (person location)<br>> > - code (person id)<br>> > - geom (point)<br>> ><br>> > I need to update table2.area (which is null) based on the location of<br>> > each point in table3. The following query provides the location<br>> > information required...<br>> ><br>> > select table3.*,table1.name<br>> > from table3,table1<br>> > where
st_within(table3.geom,table1.geom);<br>> ><br>> > but when try to create the update statement it is updating all records<br>> > with the same value (area name), which is the same of the first record<br>> > returned by the selection query...<br>> ><br>> > update table2<br>> > set area=mytable.name<br>> > from (select table1.name <http://table1.name/> <<a href="http://table1.name/" target="_blank">http://table1.name/</a>><br>> > from<br>> > table3,table1<br>> > where st_within(table3.geom,table1.geom)) AS mytable;<br>><br>> Your update has no qualifier that says "What record int table2 should<br>> get updated" so all records are getting updated.<br>><br>> This might give you better results:<br>><br>> update table2 set area=mytable.name<br>> from (select table1.name<br>>
from table3, table1<br>> where st_within(table3.geom,table1.geom)) AS mytable<br>> where mytable.code=table2.code;<br>><br>> -Steve<br>><br>><br>> > Any help to figure this out is appreciated.<br>> ><br>> > Thanks,<br>> ><br>> > Ulises<br>> ><br>> ><br>> ><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > <a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <mailto:<a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br>> ><br>><br>>
_______________________________________________<br>> postgis-users mailing list<br>> <a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <mailto:<a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>><br>><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>><br><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br><br> </div> </div> </div></body></html>