<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" rel="noreferrer nofollow noopener" href="http://blog.cleverelephant.ca/2019/07/postgis-overlays.html">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" rel="noreferrer nofollow noopener" href="https://eloiribeiro.eu/s/bkoWYyNZJMT8bpW">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 class="protonmail_signature_block " style="font-family: Arial, sans-serif; font-size: 14px;">
    
            <div class="protonmail_signature_block-proton protonmail_signature_block-empty">
        
            </div>
</div>