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<http://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]<mailto:[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]> [mailto:[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]<mailto:[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
