[postgis-users] update an st_intersects

[Rafael Soto]

I have a good idea for you!

Execute a select before you UPDATE to catch all POI has more than one
province. You know?


Around this..

SELECT poi.id, count(poi.id)

FROM province,poi

WHERE ST_Intersects(poi.the_geom, province.the_geom)

GROUP BY poi.id

HAVING count(poi.id) > 1



On Wed, Jan 27, 2010 at 6:23 AM, ibrahim saricicek <
ibrahimsaricicek at gmail.com> wrote:

> 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.idrandomly.
>>
>> 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
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Rafael Soto
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100127/2e3f8435/attachment.html>