[postgis-users] update an st_intersects

[Suhr, Ralf]

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

 

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