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

Cedric Duprez Cedric.Duprez at ign.fr
Wed May 22 06:29:17 PDT 2013

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
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,

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

More information about the postgis-users mailing list