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

[Nicolas Ribot]

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/20130521/9c12a259/attachment.html>