[postgis-users] update an st_intersects

[ibrahim saricicek]

Hi,

Thanks to all, I'll use this query or seperate the data to find the
problemlematic one..

Regards,
IBO...

On Tue, Jan 26, 2010 at 8:47 PM, Paragon Corporation <lr at pcorp.us> wrote:

>  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
>>
>>
>
> _______________________________________________
> 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/20100127/2248bec2/attachment.html>