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
