[postgis-users] update an st_intersects

[ibrahim saricicek]

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/a584dd68/attachment.html>