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

[Cedric Duprez]

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<http://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<mailto: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> [mailto: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<mailto: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/6a43bd13/attachment.html>