[postgis-users] update an st_intersects

[Paragon Corporation]

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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.soto at 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.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


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100126/0133c98f/attachment.html>