[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Update field from expression
From:       "Poynter, David" <POYNTER () adeq ! state ! ar ! us>
Date:       2011-01-28 18:24:26
Message-ID: C7CEDA45A6C7024DBCC401646937DC470B7B33A3B1 () dpex03
[Download RAW message or body]

I believe it was Ture who asked the rhetorical question, and it was a good one.  The \
update query as I did it did not work correctly, the updated field ended up a single \
value.  The query as Birgit did it worked as expected and was much faster to boot.

Thanks to all for your help,
david

-----Original Message-----
From: postgis-users-bounces@postgis.refractions.net \
                [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of \
                Birgit Laggner
Sent: Friday, January 28, 2011 6:19 AM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Update field from expression

  Hi Nicolas,

you're right - I've made this mistake very often myself...

If there can be only one polygon for each point where st_within turns
true for (which I assume), then this form of the query may solve that
problem:

UPDATE permit_sites p_s SET huc = giddy.h_gid FROM (SELECT p.gid p_gid, h.gid h_gid \
FROM permit_sites p inner join huc8 h on p.the_geom @ h.the_geom  WHERE \
ST_Within(p.the_geom,h.the_geom)) AS giddy WHERE p_s.gid=giddy.p_gid;


I hope, I made no mistake...

Regards,

Birgit.


On 28.01.2011 10:55, Ture Pålsson wrote:
> 2011/1/28 Nicolas Ribot<nicolas.ribot@gmail.com>:
> 
> > UPDATE permit_sites SET huc = giddy.gid FROM (SELECT h.gid FROM huc8 h,
> > permit_sites p WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy;
> Let me ask a rhetorical question: Which row from the inner SELECT gets
> connected to which row of the table being updated?
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]