<div dir="ltr">Hi,<div><br></div><div style>I'm too wondering what should be the final result.</div><div style><br></div><div style>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:</div>
<div style><br></div><div>SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha</div><div>FROM layer1 l1</div><div>INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)</div><div>
GROUP BY 1, 2</div><div><br></div><div>UNION</div><div><br></div><div>select l1.name1, '' as name2, sum(st_area(l1.geom)) / 10000</div><div>from layer1 l1 </div><div>where not exists (</div><div><span class="" style="white-space:pre"> </span>select <a href="http://l2.id">l2.id</a> from layer2 l2 where st_contains(l1.geom, l2.geom)</div>
<div><span class="" style="white-space:pre"> </span>)</div><div style>group by 1;</div><div style><br></div><div style>Nicolas </div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 21 May 2013 22:11, Hugues François <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="FR" link="blue" vlink="purple"><div><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Hello,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">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)) ?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Hugues.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<div><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">De :</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>De la part de</b> Cedric Duprez<br>
<b>Envoyé :</b> mardi 21 mai 2013 18:55<br><b>À :</b> PostGIS Users Discussion<br><b>Objet :</b> [postgis-users] Area of non-intersecting parts of layers<u></u><u></u></span></p></div></div><div><div class="h5"><p class="MsoNormal">
<u></u> <u></u></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Hi all,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:10.0pt;font-family:"Arial","sans-serif"">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:10.0pt;font-family:"Arial","sans-serif"">When I try to find area of intersections between the 2 layers, no problem with the following query:<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB">SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB">FROM layer1 l1<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB">INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB">GROUP BY 1, 2<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB">ORDER BY 1, 2;<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-GB">OK.<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB">But I also try to get the area of my layer1 (grouped by name1) that does not contain elements of my layer2.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-GB">I tried with LEFT JOIN, with ST_CONTAINS, without success.<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB">Does anyone have an idea on how to write this query?<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-GB">Thanks in advance,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-GB">Cedric Duprez<u></u><u></u></span></p></div></div></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>