Ibrahim, 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. If you don't care which one, the easiest update statement to write is UPDATE poi
SET province_id = province.ogc_fid FROM province WHERE ST_Intersects(poi.the_geom, province.the_geom) Leo _____ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of ibrahim saricicek Sent: Tuesday, January 26, 2010 10:32 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] update an st_intersects Hi all again; Sorry for mail traffic but i can't find a solution. Select distinct works wrong. The row is updated with province.id randomly. Isn't there any example of updating data with surrounding object details ??? Regards, IBO... On Tue, Jan 26, 2010 at 5:14 PM, Rafael Soto <rafael.s...@gmail.com> wrote: Hey Ralf, this snippet is not true because one poi does not intersects more than one province! If you put DISTINCT you use a random logic On Tue, Jan 26, 2010 at 12:29 PM, Suhr, Ralf <ralf.s...@itc-halle.de> wrote: UPDATE poi set province_id = sub.id FROM ( SELECT DISTINCT province.ogc_fid AS id FROM province WHERE ST_Intersects(poi.the_geom, province.the_geom) ) AS sub WHERE province_id = sub.id Gr Ralf Von: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] Im Auftrag von ibrahim saricicek Gesendet: Dienstag, 26. Januar 2010 15:11 An: PostGIS Users Discussion Betreff: Re: [postgis-users] update an st_intersects Hi all, The same error ERROR: more than one row returned by a subquery used as an expression any comments? On Tue, Jan 26, 2010 at 2:30 PM, Rafael Soto <rafael.s...@gmail.com> wrote: Try it update poi set province_id=( select province.ogc_fid from province where intersects(poi.the_geom, province.the_geom)=True group by province.ogc_fid ) On Tue, Jan 26, 2010 at 10:26 AM, ibrahim saricicek <ibrahimsarici...@gmail.com> wrote: Hi all, Thanks, understand. Ok, is there another way to update poi's province.ogc_fid with the bounding province's ogc_fid ??? Namely i want to learn the province of POI's... Regards.. On Tue, Jan 26, 2010 at 2:20 PM, Rafael Soto <rafael.s...@gmail.com> wrote: Hello my friend.. Your query is not wrong but your record true.. When you call a subselect to complete your UPDATE operation, this subselect can be only one row in result return. To correct this, you must be define one filter to decide what the result you want to UPDATE. To see the mistake, execute a subselect {{{ select province.ogc_fid from province,poi where intersects(poi.the_geom, province.the_geom)=True group by province.ogc_fid }} and check the number of rows in the resultset. On Tue, Jan 26, 2010 at 9:57 AM, ibrahim saricicek <ibrahimsarici...@gmail.com> wrote: Hi all, I have two tables; provinces an points of interests. I wanna update province_id column with st_intersects function.. My sql; update poi set province_id=( select province.ogc_fid from province,poi where intersects(poi.the_geom, province.the_geom)=True group by province.ogc_fid ) The message is ERROR: more than one row returned by a subquery used as an expression What should I change? _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Rafael Soto _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Rafael Soto Sent from Brasilia, DF, Brazil _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Rafael Soto Sent from Brasilia, DF, Brazil _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
