[postgis-users] Update field from expression

[Poynter, David]

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 at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Birgit Laggner
Sent: Friday, January 28, 2011 6:19 AM
To: postgis-users at 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 at 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 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