<div dir="ltr"><div>Hi Paul,</div><div><br></div><div>I often use a CTE to update values for a table:</div><div><br></div><div><pre style="background-color:rgb(43,43,43);color:rgb(169,183,198);font-family:"Consolas",monospace"><font size="2"><span style="color:rgb(204,120,50)">with </span>tmp <span style="color:rgb(204,120,50)">as </span>(<br> <span style="color:rgb(204,120,50)">select distinct </span>p1.<span style="color:rgb(255,198,109)">id</span><span style="color:rgb(204,120,50)">, </span>p2.<span style="color:rgb(255,198,109)">id </span><span style="color:rgb(204,120,50)">is null as </span>outer_pg<br> <span style="color:rgb(204,120,50)">from </span>org p1<br> <span style="color:rgb(204,120,50)">left join </span>org p2 <span style="color:rgb(204,120,50)">on </span>p1.<span style="color:rgb(255,198,109)">id </span><> p2.<span style="color:rgb(255,198,109)">id </span><span style="color:rgb(204,120,50)">and </span><span style="color:rgb(255,198,109);font-style:italic">st_within</span>(p1.<span style="color:rgb(255,198,109)">geom</span><span style="color:rgb(204,120,50)">, </span>p2.<span style="color:rgb(255,198,109)">geom</span>)<br>) <span style="color:rgb(204,120,50)">update </span>org o <span style="color:rgb(204,120,50)">set </span><span style="color:rgb(255,198,109)">outer_pg </span>= t.outer_pg<br><span style="color:rgb(204,120,50)">from </span>tmp t <br><span style="color:rgb(204,120,50)">where </span>o.<span style="color:rgb(255,198,109)">id </span>= <a href="http://t.id">t.id</a><span style="color:rgb(204,120,50)">;</span></font></pre></div><div><br></div><div>Nicolas<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 1 Dec 2021 at 11:10, <<a href="mailto:paul.malm@lfv.se">paul.malm@lfv.se</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="SV">
<div class="gmail-m_4106588397645987078WordSection1">
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">Thanks Nicolas,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">I’ve added a boolean column (outer_pg) in my original layer (org).<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">Then I tried to populate that column, but it I don’t have the skills…
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">I tried to use your selection:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">SELECT DISTINCT p1.fid, p2.fid IS null AS "outer_pg"<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">FROM org p1<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"> LEFT JOIN org p2 ON p1.fid <> p2.fid AND st_within(p1.the_geom, p2.the_geom)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">order by p1.fid;<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">My try to populate outer_pg:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">UPDATE org
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">SET "outer_pg" = (SELECT outer_p FROM (<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">SELECT DISTINCT p1.fid, p2.fid IS null AS outer_p<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">from org p1<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"> LEFT JOIN org p2 ON p1.fid <> p2.fid AND st_within(p1.the_geom, p2.the_geom) WHERE p1.fid = org.fid) AS tmp)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">But that is not correct, do you have any suggestions?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">Kind regards,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">Paul<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)" lang="EN-US">
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:11pt;font-family:"Calibri",sans-serif">Från:</span></b><span style="font-size:11pt;font-family:"Calibri",sans-serif"> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>>
<b>För </b>Nicolas Ribot<br>
<b>Skickat:</b> den 30 november 2021 19:17<br>
<b>Till:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Ämne:</b> Re: [postgis-users] polygons inside polygon<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p><strong><span style="background:yellow none repeat scroll 0% 0%">Klicka bara på länkar och öppna bilagor om du litar på avsändaren och vet att innehållet är säkert.</span></strong><u></u><u></u></p>
<div>
<div>
<div>
<p class="MsoNormal">Hi,<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">What about a simple left join on the table searching for polygons contained inside other polygon ?<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Polygons that are contained inside other polygons will have false for the outer_pg column and true if they are not contained (ie are outer polygons).<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">You can then create the tables you want based on the outer_pg values<u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<pre style="margin-bottom:12pt;background:rgb(43,43,43) none repeat scroll 0% 0%"><span style="font-family:Consolas;color:rgb(204,120,50)">select distinct p1.</span><span style="font-family:Consolas;color:rgb(152,118,170)">id</span><span style="font-family:Consolas;color:rgb(204,120,50)">, p2.</span><span style="font-family:Consolas;color:rgb(152,118,170)">id </span><span style="font-family:Consolas;color:rgb(204,120,50)">is null as outer_pg<br>from polygon p1<br> left join polygon p2 on p1.</span><span style="font-family:Consolas;color:rgb(152,118,170)">id </span><span style="font-family:Consolas;color:rgb(204,120,50)"><> p2.</span><span style="font-family:Consolas;color:rgb(152,118,170)">id </span><span style="font-family:Consolas;color:rgb(204,120,50)">and </span><i><span style="font-family:Consolas;color:rgb(255,198,109)">st_within</span></i><span style="font-family:Consolas;color:rgb(204,120,50)">(p1.</span><span style="font-family:Consolas;color:rgb(152,118,170)">geom</span><span style="font-family:Consolas;color:rgb(204,120,50)">, p2.</span><span style="font-family:Consolas;color:rgb(152,118,170)">geom</span><span style="font-family:Consolas;color:rgb(204,120,50)">)<br>order by p1.</span><span style="font-family:Consolas;color:rgb(152,118,170)">id</span><span style="font-family:Consolas;color:rgb(204,120,50)">;<br><br>id outer_pg<br>1 true<br>2 true<br>3 true<br>4 true<br>5 false<br>6 false<br>7 false<br>8 true<br>9 true<br>10 true<br>11 false<br><br></span><span style="font-family:Consolas;color:rgb(169,183,198)"><u></u><u></u></span></pre>
</div>
</div>
</div>
<div>
<p class="MsoNormal">Nicolas<u></u><u></u></p>
</div>
</div>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>