[postgis-users] update an st_intersects

[Rafael Soto]

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.Suhr at 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-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at 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.soto at 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 <
> ibrahimsaricicek at 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.soto at 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 <
> ibrahimsaricicek at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> --
> Rafael Soto
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> --
> Rafael Soto
>
> Sent from Brasilia, DF, Brazil
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Rafael Soto
Sent from Brasilia, DF, Brazil
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100126/db15c0b6/attachment.html>