<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=FR link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi all,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>When I try to find area of intersections between the
2 layers, no problem with the following query:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>SELECT l1.name1, l2.name2,
SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>FROM layer1 l1<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>GROUP BY 1, 2<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>ORDER BY 1, 2;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>OK.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>But I also try to get the area of my layer1 (grouped
by name1) that does not contain elements of my layer2.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>I tried with LEFT JOIN, with ST_CONTAINS, without
success.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>Does anyone have an idea on how to write this query?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>Thanks in advance,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-GB
style='font-size:12.0pt'>Cedric Duprez<o:p></o:p></span></font></p>
</div>
</body>
</html>