<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18876"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial>Ibrahim,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial>One of the problems is you probably have cases where the
poi fall on a boundary of a province and in those cases your original query
will return more than one record.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial>If you don't care which one, the easiest update statement to
write is</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=828194418-26012010>
<P class=MsoNormal>UPDATE poi </P>
<P class=MsoNormal><SPAN class=828194418-26012010>SET</SPAN> province_id
= <SPAN class=828194418-26012010>province</SPAN><SPAN
class=828194418-26012010>.ogc_fid</SPAN></P>
<P class=MsoNormal><SPAN class=828194418-26012010></SPAN>FROM<SPAN
class=828194418-26012010> province</SPAN></P>
<P class=MsoNormal><SPAN class=828194418-26012010></SPAN>WHERE
ST_Intersects(poi.the_geom, province.the_geom)</P>
<P style="TEXT-INDENT: 35.4pt" class=MsoNormal><FONT color=#0000ff size=2
face=Arial></FONT> </P>
<P style="TEXT-INDENT: 35.4pt" class=MsoNormal><SPAN
class=828194418-26012010><FONT color=#0000ff size=2
face=Arial>Leo</FONT></SPAN></P></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>ibrahim saricicek<BR><B>Sent:</B> Tuesday, January 26, 2010 10:32
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
update an st_intersects<BR></FONT><BR></DIV>
<DIV></DIV>Hi all again;<BR><BR>Sorry for mail traffic but i can't find a
solution.<BR><BR>Select distinct works wrong. The row is updated with <A
href="http://province.id">province.id</A> randomly.<BR><BR>Isn't there any
example of updating data with surrounding object details
???<BR><BR>Regards,<BR>IBO...<BR><BR>
<DIV class=gmail_quote>On Tue, Jan 26, 2010 at 5:14 PM, Rafael Soto <SPAN
dir=ltr><<A
href="mailto:rafael.soto@gmail.com">rafael.soto@gmail.com</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Hey Ralf, this snippet is not true because one poi does not
intersects more than one province!<BR>If you put DISTINCT you use a random
logic
<DIV>
<DIV></DIV>
<DIV class=h5><BR><BR>
<DIV class=gmail_quote>On Tue, Jan 26, 2010 at 12:29 PM, Suhr, Ralf <SPAN
dir=ltr><<A href="mailto:Ralf.Suhr@itc-halle.de"
target=_blank>Ralf.Suhr@itc-halle.de</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV lang=DE vlink="purple" link="blue">
<DIV>
<P class=MsoNormal> </P>
<P class=MsoNormal>UPDATE poi set province_id = <A href="http://sub.id"
target=_blank>sub.id</A></P>
<P class=MsoNormal>FROM</P>
<P style="TEXT-INDENT: 35.4pt" class=MsoNormal>(</P>
<P style="TEXT-INDENT: 35.4pt; MARGIN-LEFT: 35.4pt" class=MsoNormal>SELECT
DISTINCT province.ogc_fid AS id</P>
<P style="TEXT-INDENT: 35.4pt; MARGIN-LEFT: 35.4pt" class=MsoNormal>FROM
province</P>
<P style="TEXT-INDENT: 35.4pt; MARGIN-LEFT: 35.4pt" class=MsoNormal>WHERE
ST_Intersects(poi.the_geom, province.the_geom)</P>
<P style="TEXT-INDENT: 35.4pt" class=MsoNormal>) AS sub</P>
<P class=MsoNormal>WHERE province_id = <A href="http://sub.id"
target=_blank>sub.id</A></P>
<P class=MsoNormal><SPAN
style="FONT-FAMILY: 'Courier New'; COLOR: rgb(31,73,125); FONT-SIZE: 10pt"></SPAN> </P>
<P class=MsoNormal><SPAN
style="FONT-FAMILY: 'Courier New'; COLOR: rgb(31,73,125); FONT-SIZE: 10pt"></SPAN> </P>
<P class=MsoNormal><SPAN
style="FONT-FAMILY: 'Courier New'; COLOR: rgb(31,73,125); FONT-SIZE: 10pt">Gr
</SPAN></P>
<P class=MsoNormal><SPAN
style="FONT-FAMILY: 'Courier New'; COLOR: rgb(31,73,125); FONT-SIZE: 10pt">Ralf</SPAN></P>
<P class=MsoNormal><SPAN
style="COLOR: rgb(31,73,125); FONT-SIZE: 11pt"></SPAN> </P>
<DIV
style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; BORDER-TOP: rgb(181,196,223) 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<P class=MsoNormal><B><SPAN style="FONT-SIZE: 10pt">Von:</SPAN></B><SPAN
style="FONT-SIZE: 10pt"> <A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A> [mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A>] <B>Im
Auftrag von </B>ibrahim saricicek<BR><B>Gesendet:</B> Dienstag, 26. Januar
2010 15:11<BR><B>An:</B> PostGIS Users Discussion<BR><B>Betreff:</B> Re:
[postgis-users] update an st_intersects</SPAN></P></DIV>
<DIV>
<DIV></DIV>
<DIV>
<P class=MsoNormal> </P>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal>Hi all,<BR><BR>The same
error<BR><BR>ERROR: more than one row returned by a subquery used as an
expression<BR><BR>any comments?</P>
<DIV>
<P class=MsoNormal>On Tue, Jan 26, 2010 at 2:30 PM, Rafael Soto <<A
href="mailto:rafael.soto@gmail.com"
target=_blank>rafael.soto@gmail.com</A>> wrote:</P>
<P class=MsoNormal>Try it</P>
<DIV>
<P class=MsoNormal><BR><BR>update poi set province_id=(</P></DIV>
<P class=MsoNormal>select province.ogc_fid from province where
intersects(poi.the_geom, province.the_geom)=True group by
province.ogc_fid<BR>)</P>
<DIV>
<DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal> </P>
<DIV>
<P class=MsoNormal>On Tue, Jan 26, 2010 at 10:26 AM, ibrahim saricicek
<<A href="mailto:ibrahimsaricicek@gmail.com"
target=_blank>ibrahimsaricicek@gmail.com</A>> wrote:</P>
<P class=MsoNormal>Hi all,<BR><BR>Thanks, understand.<BR><BR>Ok, is there
another way to update poi's province.ogc_fid with the bounding province's
ogc_fid ???<BR>Namely i want to learn the province of
POI's...<BR><BR>Regards..</P>
<DIV>
<DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal><BR><BR></P>
<DIV>
<P class=MsoNormal>On Tue, Jan 26, 2010 at 2:20 PM, Rafael Soto <<A
href="mailto:rafael.soto@gmail.com"
target=_blank>rafael.soto@gmail.com</A>> wrote:</P>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal>Hello my friend..<BR><BR>Your
query is not wrong but your record true..<BR><BR>When you call a subselect
to complete your UPDATE operation, this subselect can be only one row in
result return.<BR>To correct this, you must be define one filter to decide
what the result you want to UPDATE.<BR>To see the mistake, execute a
subselect {{{ select<B> province.ogc_fid from province,poi where
intersects(poi.the_geom, province.the_geom)=True group by
province.ogc_fid</B> }} and check the number of rows in the
resultset.<BR><BR><BR><BR></P>
<DIV>
<DIV>
<DIV>
<P class=MsoNormal>On Tue, Jan 26, 2010 at 9:57 AM, ibrahim saricicek <<A
href="mailto:ibrahimsaricicek@gmail.com"
target=_blank>ibrahimsaricicek@gmail.com</A>> wrote:</P></DIV></DIV>
<BLOCKQUOTE
style="BORDER-BOTTOM: medium none; BORDER-LEFT: 1pt solid; PADDING-BOTTOM: 0cm; PADDING-LEFT: 6pt; PADDING-RIGHT: 0cm; MARGIN-LEFT: 4.8pt; BORDER-TOP: medium none; MARGIN-RIGHT: 0cm; BORDER-RIGHT: medium none; PADDING-TOP: 0cm">
<DIV>
<DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal> </P>
<DIV>
<P class=MsoNormal>Hi all,<BR><BR>I have two tables; provinces an points
of interests. I wanna update province_id column with st_intersects
function..</P>
<DIV>
<P class=MsoNormal><BR><BR>My sql;<BR><BR>update poi set
province_id=(</P></DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal>select province.ogc_fid
from province,poi where intersects(poi.the_geom, province.the_geom)=True
group by province.ogc_fid<BR>)</P></DIV>
<P class=MsoNormal>The message is <BR><BR>ERROR: more than one row
returned by a subquery used as an expression<BR><BR>What should I change?
</P></DIV></DIV>
<P style="MARGIN-BOTTOM: 12pt"
class=MsoNormal>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A></P></BLOCKQUOTE></DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal><BR><BR clear=all><BR>--
<BR><SPAN style="COLOR: rgb(136,136,136)">Rafael
Soto<BR><BR></SPAN><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A></P></DIV>
<P class=MsoNormal> </P></DIV></DIV>
<P style="MARGIN-BOTTOM: 12pt"
class=MsoNormal><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A></P></DIV>
<P class=MsoNormal><BR><BR clear=all><BR>-- <BR>Rafael Soto</P></DIV></DIV>
<P style="MARGIN-BOTTOM: 12pt" class=MsoNormal>Sent from Brasilia, DF,
Brazil<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A></P></DIV>
<P
class=MsoNormal> </P></DIV></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR><BR
clear=all><BR>-- <BR>Rafael Soto<BR>Sent from Brasilia, DF,
Brazil<BR></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR></BODY></HTML>