[postgis-users] Attributes update

[Andy Anderson]

I think what Lucas wants is actually the mirror statement:

UPDATE parcels SET landuse = land_use.type
FROM landuse WHERE  
ST_Within(centroid(parcels.the_geom),land_use.the_geom);

-- Andy

On May 30, 2008, at 7:54 AM, Obe, Regina wrote:

> Markus,
>
> Rule of thumb for relation operations - I think you always read it  A
> function_name B
>
> So A within B -> Within(A,B)
>
> I think that holds true for all the spatial relation functions in
> PostGIS.
>
> Lucas - Markus gave you the correlated sub query approach.  You can  
> also
> do it without a subquery.  Which one is faster depends on how big your
> data sets are I think.  The non-correlated I think generally works
> better (especially for large data sets) (make sure to have indexes on
> both geom fields).
>
> I think the correlated sub query always forces a nested loop join.
>
>
> Below is a non-sub query way of doing it.  For the new versions of
> PostGIS you can also replace the && Within with just ST_Within.
>
> UPDATE land_use SET type = parcels.landuse
> FROM parcels
> WHERE
> ST_Within(centroid(parcels.the_geom),land_use.the_geom);
>
> Hope that helps,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Markus Schaber
> Sent: Friday, May 30, 2008 7:26 AM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] Attributes update
>
> Hi, Lucas,
>
> "Lucas Mueller" <lucas.mueller at gmail.com> wrote:
>
>
>> I have two polygon layers (let's say Parcels and Land_use). I would
> like to
>> assign the attribute Land_use.type to the Parcels.landuse column by a
> query
>> that assigns a certain land use type to a parcel if the centroid of
> the
>> parcel is within a certain land use polygon. I hope to be clear
> enough...
>> Any idea on how to proceed?
>
> Try something like:
>
> update land_use set type=(select parcels.landuse FROM parcels where
> land_use.the_geom && parcels.the_geom AND
> within(centroid(parcels.the_geom),land_use.the_geom));
>
> Btw, I tend to mix up the argument order of within and contains, so
> please double-check.
>
>
> Regards,
> Markus
>
> -- 
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in Europe! www.ffii.org
> www.nosoftwarepatents.org
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users