[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] polygons inside polygon
From:       Nicolas Ribot <nicolas.ribot () gmail ! com>
Date:       2021-11-30 18:16:47
Message-ID: CAGAwT=0TdmJrRvXpM4P+92SxshPf++8Ak4ceZTryQ_Ppp0gFQA () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


Hi,

What about a simple left join on the table searching for polygons contained
inside other polygon ?
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).
You can then create the tables you want based on the outer_pg values

select distinct p1.id, p2.id is null as outer_pg
from polygon p1
     left join polygon p2 on p1.id <> p2.id and st_within(p1.geom, p2.geom)
order by p1.id;

id	outer_pg
1	true
2	true
3	true
4	true
5	false
6	false
7	false
8	true
9	true
10	true
11	false


Nicolas

[Attachment #5 (text/html)]

<div dir="ltr"><div><div>Hi,</div><div><br></div><div>What about a simple left join \
on the table searching for polygons contained inside other polygon \
?</div><div>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).</div><div>You can then create the tables you want based on the outer_pg \
values<br></div><font size="2"><span \
style="color:rgb(204,120,50)"></span></font><br><div><font size="2"><span \
style="color:rgb(204,120,50)"></span></font><div><pre \
style="background-color:rgb(43,43,43);color:rgb(169,183,198);font-family:&quot;Consolas&quot;,monospace"><font \
size="2"><span style="color:rgb(204,120,50)"><span \
style="color:rgb(204,120,50)">select distinct </span>p1.<span \
style="color:rgb(152,118,170)">id</span><span style="color:rgb(204,120,50)">, \
</span>p2.<span style="color:rgb(152,118,170)">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>polygon p1<br>     <span \
style="color:rgb(204,120,50)">left join </span>polygon p2 <span \
style="color:rgb(204,120,50)">on </span>p1.<span style="color:rgb(152,118,170)">id \
</span>&lt;&gt; p2.<span style="color:rgb(152,118,170)">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(152,118,170)">geom</span><span style="color:rgb(204,120,50)">, \
</span>p2.<span style="color:rgb(152,118,170)">geom</span>)<br><span \
style="color:rgb(204,120,50)">order by </span>p1.<span \
style="color:rgb(152,118,170)">id</span><span \
style="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><br></span></span></font></pre></div></div></div><div>Nicolas</div></div>




_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]