Hi,
I think you can use st_difference bu you have to be careful because it will
return as many rows as l2 geometries intersect l1 ones. Union them should be a
solution but I doubt it is the more performance way :
With l2_union as (
Select l1.id, l1.name, st_union(l2.geom) geom
From l1, l2
Where st_intersects(l1.geom, l2.geom)
Group by l1.id, l1.name
)
Select l1.id, l1.name, st_area(st_difference(l1.geom, l2_union.geom))
>From l1, l2_union
Where l1.id = l2_union.id
Hugues.
De : [email protected]
[mailto:[email protected]] De la part de Cedric Duprez
Envoyé : mercredi 22 mai 2013 08:26
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Area of non-intersecting parts of layers
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 : [email protected]
[mailto:[email protected]] 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 from layer2 l2 where st_contains(l1.geom, l2.geom)
)
group by 1;
Nicolas
On 21 May 2013 22:11, Hugues François <[email protected]> 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 : [email protected]
[mailto:[email protected]] 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
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
