<div dir="ltr">Hello,<div><br></div><div>You could use st_polygonize on the union of the polygons' boundaries to reconstruct all closed linestrings as polygons.</div><div>A spatial join on these polygons could be used to retrieve original polygons information</div><div><br></div><div style="background-color:rgb(43,43,43);color:rgb(169,183,198)"><pre style="font-family:Consolas,monospace"><span style="color:rgb(204,120,50)">select </span>(<span style="color:rgb(255,198,109);font-style:italic">st_dump</span>(<span style="color:rgb(255,198,109);font-style:italic">st_polygonize</span>(geom))).geom<br><span style="color:rgb(204,120,50)">from </span>(<br> <span style="color:rgb(204,120,50)">select </span><span style="color:rgb(255,198,109);font-style:italic">st_union</span>(<span style="color:rgb(255,198,109);font-style:italic">st_boundary</span>(<span style="color:rgb(152,118,170)">geom</span>)) <span style="color:rgb(204,120,50)">as </span>geom <br> <span style="color:rgb(204,120,50)">from </span>complex_circles</pre><pre style="font-family:Consolas,monospace">) <span style="color:rgb(204,120,50)">as </span>t<span style="color:rgb(204,120,50)">;</span></pre></div><div><br></div><div><img src="cid:ii_lly0yd7e3" alt="Screenshot 2023-08-30 at 19.41.31.png" width="406" height="352" style="margin-right: 0px;"><img src="cid:ii_lly0zhbr5" alt="Screenshot 2023-08-30 at 19.42.47.png" width="385" height="352" style="margin-right: 0px;"><br></div><div><br></div><div>Nicolas<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 30 Aug 2023 at 18:45, Eloi Ribeiro <<a href="mailto:mail@eloiribeiro.eu">mail@eloiribeiro.eu</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 style="font-family:Arial,sans-serif;font-size:14px"><span>Hi all,</span><div><br></div><div><span>Did anyone successfully executed the following procedure <a rel="noreferrer nofollow noopener" href="http://blog.cleverelephant.ca/2019/07/postgis-overlays.html" target="_blank">http://blog.cleverelephant.ca/2019/07/postgis-overlays.html</a> with complex geometries (polygons with holes)?</span></div><div><br></div><div><span>Below the SQL code I am using (read failing). I still get duplicated (overlapped) polygons and some others are just missing in the final layer.</span></div><div><span>Any help would be very much appreciated.</span></div><div><br></div><div><span>Sample data that I'm using: <a rel="noreferrer nofollow noopener" href="https://eloiribeiro.eu/s/bkoWYyNZJMT8bpW" target="_blank">https://eloiribeiro.eu/s/bkoWYyNZJMT8bpW</a></span></div><div><br></div><div><span>CREATE SEQUENCE boundaries_gid_seq;</span></div><div><br></div><div><span>CREATE TABLE boundaries AS</span></div><div><span> SELECT nextval('boundaries_gid_seq') AS gid,</span></div><div><span> gid AS gid_old,</span></div><div><span> (ST_DumpRings(geom)).path AS path,</span></div><div><span> ((ST_DumpRings(geom)).geom) AS geom</span></div><div><span> FROM complex_circles; -- layer with complex polygons, with holes</span></div><div><br></div><div><span>ALTER TABLE boundaries ADD PRIMARY KEY(gid);</span></div><div><span>CREATE INDEX boundaries_geom_idx ON boundaries USING gist(geom);</span></div><div><br></div><div><span>CREATE SEQUENCE polys_seq;</span></div><div><br></div><div><span>CREATE TABLE polys AS</span></div><div><span> SELECT nextval('polys_seq') AS gid,</span></div><div><span> ((ST_Dump(ST_Polygonize(geom))).geom) AS geom</span></div><div><span> FROM boundaries;</span></div><div><br></div><div><span>ALTER TABLE polys ADD PRIMARY KEY(gid);</span></div><div><span>CREATE INDEX polys_geom_idx ON polys USING gist(geom);</span></div><div><br></div><div><span>ALTER TABLE polys ADD COLUMN count INTEGER DEFAULT 0;</span></div><div><span>ALTER TABLE polys ADD COLUMN codes text;</span></div><div><br></div><div><span>UPDATE polys</span></div><div><span> SET count = p.count,</span></div><div><span> codes = p.array_agg</span></div><div><span> FROM (</span></div><div><span> SELECT p.gid, count(*), array_agg(code)</span></div><div><span> FROM polys p</span></div><div><span> JOIN complex_circles c</span></div><div><span> ON ST_Contains(c.geom, ST_PointOnSurface(p.geom))</span></div><div><span> GROUP BY p.gid</span></div><div><span> ) AS p</span></div><div><span> WHERE p.gid = polys.gid;</span></div><div><br></div><div><span>DELETE FROM polys WHERE count = 0;</span></div><div><br></div><div><span>Software version:</span></div><div><span>SELECT version();</span></div><div><span>"PostgreSQL 12.16 ..."</span></div><div><span>SELECT postgis_full_version();</span></div><div><span>"POSTGIS=""3.1.9 95a641b"" [EXTENSION] PGSQL=""120"" GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" LIBXML=""2.9.1"" LIBJSON=""0.11"""</span></div><div><br></div><div><br></div><span>Eloi</span><div><br></div></div><div style="font-family:Arial,sans-serif;font-size:14px">
<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>