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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
