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

[Cedric Duprez]

That's it! I could reduce the query to a single spatial join using ST_INTERSECTS, with common table expressions:

WITH t1 AS (
            SELECT name1, SUM(ST_Area(geom)) AS surface
            FROM layer1
            GROUP BY name1
)
, t2 AS (
            SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) AS surface
            FROM layer1 l1
            INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)
            GROUP BY 1, 2
)
, t3 AS (
            SELECT l1.name1, SUM(t2.surface) AS surface
            FROM layer1 l1
            INNER JOIN t2 ON l1.name = t2.name
            GROUP BY 1, 2
)
SELECT t1.name1, t1.surface - t3.surface AS gap
FROM t1
INNER JOIN t3 ON t1.name1 = t3.name1
ORDER BY 1, 2;

This is performing because I just keep 1 spatial inner join, using ST_INTERSECTS.

Thanks a lot for your help.

Kind regards,

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