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 class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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 class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" 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 class="MsoNormal" style="text-indent: 35.4pt;">(</p>
<p class="MsoNormal" style="margin-left: 35.4pt; text-indent: 35.4pt;">SELECT
DISTINCT province.ogc_fid AS id</p>
<p class="MsoNormal" style="margin-left: 35.4pt; text-indent: 35.4pt;">FROM province</p>
<p class="MsoNormal" style="margin-left: 35.4pt; text-indent: 35.4pt;">WHERE ST_Intersects(poi.the_geom,
province.the_geom)</p>
<p class="MsoNormal" style="text-indent: 35.4pt;">) 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-size: 10pt; font-family: "Courier New"; color: rgb(31, 73, 125);"> </span></p>
<p class="MsoNormal"><span style="font-size: 10pt; font-family: "Courier New"; color: rgb(31, 73, 125);"> </span></p>
<p class="MsoNormal"><span style="font-size: 10pt; font-family: "Courier New"; color: rgb(31, 73, 125);">Gr </span></p>
<p class="MsoNormal"><span style="font-size: 10pt; font-family: "Courier New"; color: rgb(31, 73, 125);">Ralf</span></p>
<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </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 class="MsoNormal" style="margin-bottom: 12pt;">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 class="MsoNormal" style="margin-bottom: 12pt;"> </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 class="MsoNormal" style="margin-bottom: 12pt;"><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 class="MsoNormal" style="margin-bottom: 12pt;">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 -moz-use-text-color -moz-use-text-color rgb(204, 204, 204); border-width: medium medium medium 1pt; padding: 0cm 0cm 0cm 6pt; margin-left: 4.8pt; margin-right: 0cm;">
<div>
<div>
<p class="MsoNormal" style="margin-bottom: 12pt;"> </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 class="MsoNormal" style="margin-bottom: 12pt;">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 class="MsoNormal" style="margin-bottom: 12pt;">_______________________________________________<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 class="MsoNormal" style="margin-bottom: 12pt;"><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 class="MsoNormal" style="margin-bottom: 12pt;"><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 class="MsoNormal" style="margin-bottom: 12pt;">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>