<div style="font-family: Arial, sans-serif; font-size: 14px;">Thanks Nicolas! Perfect!</div><div style="font-family: Arial, sans-serif; font-size: 14px;"><br></div><div style="font-family: Arial, sans-serif; font-size: 14px;">Just for future reference, SQL code after fix:</div><div style="font-family: Arial, sans-serif; font-size: 14px;"><br></div><div style="font-family: Arial, sans-serif; font-size: 14px;"><div style="font-family: "Droid Sans Mono", "monospace", monospace; font-weight: normal; line-height: 19px; white-space: pre; color: rgb(204, 204, 204); background-color: rgb(31, 31, 31);"><span><span style="color: rgb(86, 156, 214);">DROP</span><span> </span><span style="color: rgb(86, 156, 214);">SEQUENCE</span><span> </span><span style="color: rgb(86, 156, 214);">IF</span><span> </span><span style="color: rgb(86, 156, 214);">EXISTS</span><span> myschema.polys_seq;</span></span><div><span style="color: rgb(86, 156, 214);">DROP</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> </span><span style="color: rgb(86, 156, 214);">IF</span><span> </span><span style="color: rgb(86, 156, 214);">EXISTS</span><span> myschema.polys;</span></div><div><span style="color: rgb(86, 156, 214);">CREATE</span><span> </span><span style="color: rgb(86, 156, 214);">SEQUENCE</span><span> </span><span style="color: rgb(220, 220, 170);">myschema</span><span>.polys_seq;</span></div><br><div><span style="color: rgb(86, 156, 214);">CREATE</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> </span><span style="color: rgb(220, 220, 170);">myschema</span><span>.polys </span><span style="color: rgb(86, 156, 214);">AS</span><span> </span></div><div><span style="color: rgb(86, 156, 214);">SELECT</span><span> nextval(</span><span style="color: rgb(206, 145, 120);">'myschema.polys_seq'</span><span>) </span><span style="color: rgb(86, 156, 214);">AS</span><span> gid,</span></div><div><span> (ST_Dump(ST_Polygonize(geom))).geom</span></div><div><span style="color: rgb(86, 156, 214);">FROM</span><span> </span></div><div><span> (</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">SELECT</span><span> ST_Union(ST_Boundary(geom)) </span><span style="color: rgb(86, 156, 214);">AS</span><span> geom </span></div><div><span> </span><span style="color: rgb(86, 156, 214);">FROM</span><span> myschema.complex_circles</span></div><div><span> ) </span><span style="color: rgb(86, 156, 214);">AS</span><span> t;</span></div><br><div><span style="color: rgb(86, 156, 214);">ALTER</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">ADD</span><span> </span><span style="color: rgb(86, 156, 214);">PRIMARY KEY</span><span>(gid);</span></div><div><span style="color: rgb(86, 156, 214);">CREATE</span><span> </span><span style="color: rgb(86, 156, 214);">INDEX</span><span> </span><span style="color: rgb(220, 220, 170);">polys_geom_idx</span><span> </span><span style="color: rgb(86, 156, 214);">ON</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">USING</span><span> gist(geom);</span></div><div><span style="color: rgb(86, 156, 214);">GRANT</span><span> ALL </span><span style="color: rgb(86, 156, 214);">ON</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">TO</span><span> myschema;</span></div><div><span style="color: rgb(86, 156, 214);">GRANT</span><span> </span><span style="color: rgb(86, 156, 214);">SELECT</span><span> </span><span style="color: rgb(86, 156, 214);">ON</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">TO</span><span> myschema_r;</span></div><div><span style="color: rgb(86, 156, 214);">ALTER</span><span> </span><span style="color: rgb(86, 156, 214);">TABLE</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">ADD</span><span> COLUMN count </span><span style="color: rgb(86, 156, 214);">INTEGER</span><span> </span><span style="color: rgb(86, 156, 214);">DEFAULT</span><span> </span><span style="color: rgb(181, 206, 168);">0</span><span>;</span></div><br><div><span style="color: rgb(86, 156, 214);">UPDATE</span><span> myschema.polys</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">SET</span><span> count </span><span style="color: rgb(212, 212, 212);">=</span><span> p.count</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">FROM</span><span> (</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">SELECT</span><span> p.gid, </span><span style="color: rgb(220, 220, 170);">count</span><span>(</span><span style="color: rgb(212, 212, 212);">*</span><span>)</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">FROM</span><span> myschema.polys p </span></div><div><span> </span><span style="color: rgb(86, 156, 214);">JOIN</span><span> myschema.complex_circles c</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">ON</span><span> ST_Contains(c.geom, ST_PointOnSurface(p.geom))</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">GROUP BY</span><span> p.gid</span></div><div><span> ) </span><span style="color: rgb(86, 156, 214);">AS</span><span> p</span></div><div><span> </span><span style="color: rgb(86, 156, 214);">WHERE</span><span> p.gid </span><span style="color: rgb(212, 212, 212);">=</span><span> polys.gid;</span></div><br><div><span style="color: rgb(86, 156, 214);">DELETE</span><span> </span><span style="color: rgb(86, 156, 214);">FROM</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">WHERE</span><span> count </span><span style="color: rgb(212, 212, 212);">=</span><span> </span><span style="color: rgb(181, 206, 168);">0</span><span>;</span></div><span><span style="color: rgb(86, 156, 214);">DELETE</span><span> </span><span style="color: rgb(86, 156, 214);">FROM</span><span> myschema.polys </span><span style="color: rgb(86, 156, 214);">WHERE</span><span> ST_Area(geom)</span><span style="color: rgb(212, 212, 212);">/</span><span>ST_Perimeter(geom) </span><span style="color: rgb(212, 212, 212);"><</span><span> </span><span style="color: rgb(181, 206, 168);">0</span><span>.</span><span style="color: rgb(181, 206, 168);">1</span><span>;</span></span></div><br></div><div style="font-family: Arial, sans-serif; font-size: 14px;">Cheers!<br></div><div style="font-family: Arial, sans-serif; font-size: 14px;"><br></div>
<div class="protonmail_signature_block" style="font-family: Arial, sans-serif; font-size: 14px;">
<div class="protonmail_signature_block-user">
<div><div>Eloi<br></div></div><div><br></div>
</div>
<div class="protonmail_signature_block-proton protonmail_signature_block-empty">
</div>
</div>
<div style="font-family: Arial, sans-serif; font-size: 14px;"><br></div><div class="protonmail_quote">
------- Original Message -------<br>
On Wednesday, August 30th, 2023 at 19:43, Nicolas Ribot <nicky666@gmail.com> wrote:<br><br>
<blockquote class="protonmail_quote" type="cite">
<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 style="margin-right: 0px;" height="352" width="406" alt="Screenshot 2023-08-30 at 19.41.31.png" class="proton-embedded" src="cid:ii_lly0yd7e3"><img style="margin-right: 0px;" height="352" width="385" alt="Screenshot 2023-08-30 at 19.42.47.png" class="proton-embedded" src="cid:ii_lly0zhbr5"><br></div><div><br></div><div>Nicolas<br></div></div><br><div class="gmail_quote"><div class="gmail_attr" dir="ltr">On Wed, 30 Aug 2023 at 18:45, Eloi Ribeiro <<a href="mailto:mail@eloiribeiro.eu" rel="noreferrer nofollow noopener" target="_blank">mail@eloiribeiro.eu</a>> wrote:<br></div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><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 target="_blank" href="http://blog.cleverelephant.ca/2019/07/postgis-overlays.html" rel="noreferrer nofollow noopener">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 target="_blank" href="https://eloiribeiro.eu/s/bkoWYyNZJMT8bpW" rel="noreferrer nofollow noopener">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 target="_blank" href="mailto:postgis-users@lists.osgeo.org" rel="noreferrer nofollow noopener">postgis-users@lists.osgeo.org</a><br>
<a target="_blank" rel="noreferrer nofollow noopener" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>
</blockquote><br>
</div>