[postgis-users] Area of non-intersecting parts of layers

[Hugues François]

Hi,

 

I think you can use st_difference bu you have to be careful because it will return as many rows as l2 geometries intersect l1 ones. Union them should be a solution but I doubt it is the more performance way :

 

With l2_union as (

                Select l1.id, l1.name, st_union(l2.geom) geom

                From l1, l2

                Where st_intersects(l1.geom, l2.geom)

                Group by l1.id, l1.name

)

 

Select l1.id, l1.name, st_area(st_difference(l1.geom, l2_union.geom))

>From l1, l2_union

Where l1.id = l2_union.id

 

Hugues.

 

De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Cedric Duprez
Envoyé : mercredi 22 mai 2013 08:26
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Area of non-intersecting parts of layers

 

Thanks for the answers.

 

Sorry if I did not explain what I wish correctly.

I'm trying to get the area of the difference between l1 and l2 (which is l1 - intersect(l1, l2)).

 

Cedric

 

________________________________

De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Nicolas Ribot
Envoyé : mardi 21 mai 2013 22:23
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Area of non-intersecting parts of layers

 

Hi,

 

I'm too wondering what should be the final result.

 

To resolve the case where you also want the the areas of layers 1 geometries that do not contain any layer2 geom, you could use UNION to join the 2 queries:

 

SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha

FROM layer1 l1

INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)

GROUP BY 1, 2

 

UNION

 

select l1.name1, '' as name2, sum(st_area(l1.geom)) / 10000

from layer1 l1 

where not exists (

            select l2.id from layer2 l2 where st_contains(l1.geom, l2.geom)

            )

group by 1;

 

Nicolas 

 

On 21 May 2013 22:11, Hugues François <hugues.francois at irstea.fr> wrote:

Hello,

 

I'm not sure to understand what you are trying to do. Do you want to find the area of polygons from layer 1 which don't contain any polygon from layer 2 or the difference between l1 and l2  (l1 - intersection(l1,l2)) ?

 

Hugues.

 

 

De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Cedric Duprez
Envoyé : mardi 21 mai 2013 18:55
À : PostGIS Users Discussion
Objet : [postgis-users] Area of non-intersecting parts of layers

 

Hi all,

 

I have 2 layers composed of multipolygons in Postgis 2.0. Each layer contains polygons with a name that is sometime common to several polygons.

When I try to find area of intersections between the 2 layers, no problem with the following query:

SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha

FROM layer1 l1

INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)

GROUP BY 1, 2

ORDER BY 1, 2;

 

OK.

But I also try to get the area of my layer1 (grouped by name1) that does not contain elements of my layer2.

 

I tried with LEFT JOIN, with ST_CONTAINS, without success.

 

Does anyone have an idea on how to write this query?

 

Thanks in advance,

 

Cedric Duprez


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130522/9449b96e/attachment.html>