Hi,<br><br>Thanks to all, I'll use this query or seperate the data to find the problemlematic one..<br><br>Regards,<br>IBO...<br><br><div class="gmail_quote">On Tue, Jan 26, 2010 at 8:47 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Ibrahim,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">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><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">If you don't care which one, the easiest update statement to
write is</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span>
<p class="MsoNormal">UPDATE poi </p>
<p class="MsoNormal"><span>SET</span> province_id
= <span>province</span><span>.ogc_fid</span></p><div class="im">
<p class="MsoNormal"><span></span>FROM<span> province</span></p>
<p class="MsoNormal"><span></span>WHERE
ST_Intersects(poi.the_geom, province.the_geom)</p>
<p style="text-indent: 35.4pt;" class="MsoNormal"><font color="#0000ff" face="Arial" size="2"></font> </p>
</div><p style="text-indent: 35.4pt;" class="MsoNormal"><span><font color="#0000ff" face="Arial" size="2">Leo</font></span></p></span></div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
<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>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></div><div class="h5">
<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" target="_blank">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" target="_blank">rafael.soto@gmail.com</a>></span>
wrote:<br>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); 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><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: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">
<div vlink="purple" link="blue" lang="DE">
<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-style: solid none none; border-color: rgb(181, 196, 223) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0cm 0cm;">
<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-style: none none none solid; border-color: -moz-use-text-color; border-width: medium medium medium 1pt; padding: 0cm 0cm 0cm 6pt; margin-left: 4.8pt; margin-right: 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" 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></div></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>