[postgis-users] Complex Overlays

[Nicolas Ribot]

Hello,

You could use st_polygonize on the union of the polygons' boundaries to
reconstruct all closed linestrings as polygons.
A spatial join on these polygons could be used to retrieve original
polygons information

select (st_dump(st_polygonize(geom))).geom
from (
    select st_union(st_boundary(geom)) as geom
    from complex_circles

) as t;


[image: Screenshot 2023-08-30 at 19.41.31.png][image: Screenshot 2023-08-30
at 19.42.47.png]

Nicolas

On Wed, 30 Aug 2023 at 18:45, Eloi Ribeiro <mail at eloiribeiro.eu> wrote:

> Hi all,
>
> Did anyone successfully executed the following procedure
> http://blog.cleverelephant.ca/2019/07/postgis-overlays.html with complex
> geometries (polygons with holes)?
>
> 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.
> Any help would be very much appreciated.
>
> Sample data that I'm using: https://eloiribeiro.eu/s/bkoWYyNZJMT8bpW
>
> CREATE SEQUENCE boundaries_gid_seq;
>
> CREATE TABLE boundaries AS
>   SELECT nextval('boundaries_gid_seq') AS gid,
>         gid AS gid_old,
>         (ST_DumpRings(geom)).path AS path,
>         ((ST_DumpRings(geom)).geom) AS geom
>   FROM complex_circles; -- layer with complex polygons, with holes
>
> ALTER TABLE boundaries ADD PRIMARY KEY(gid);
> CREATE INDEX boundaries_geom_idx ON boundaries USING gist(geom);
>
> CREATE SEQUENCE polys_seq;
>
> CREATE TABLE polys AS
>   SELECT nextval('polys_seq') AS gid,
>          ((ST_Dump(ST_Polygonize(geom))).geom) AS geom
>   FROM boundaries;
>
> ALTER TABLE polys ADD PRIMARY KEY(gid);
> CREATE INDEX polys_geom_idx ON polys USING gist(geom);
>
> ALTER TABLE polys ADD COLUMN count INTEGER DEFAULT 0;
> ALTER TABLE polys ADD COLUMN codes text;
>
> UPDATE polys
>   SET count  = p.count,
>       codes  = p.array_agg
>   FROM (
>     SELECT p.gid, count(*), array_agg(code)
>     FROM polys p
>     JOIN complex_circles c
>     ON ST_Contains(c.geom, ST_PointOnSurface(p.geom))
>     GROUP BY p.gid
>   ) AS p
>   WHERE p.gid = polys.gid;
>
> DELETE FROM polys WHERE count = 0;
>
> Software version:
> SELECT version();
> "PostgreSQL 12.16 ..."
> SELECT postgis_full_version();
> "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"""
>
>
> Eloi
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230830/d4e5f5b2/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot 2023-08-30 at 19.41.31.png
Type: image/png
Size: 147750 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230830/d4e5f5b2/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot 2023-08-30 at 19.42.47.png
Type: image/png
Size: 143880 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230830/d4e5f5b2/attachment-0001.png>