[postgis-users] Complex Overlays

[Eloi Ribeiro]

Thanks Nicolas! Perfect!

Just for future reference, SQL code after fix:

DROPSEQUENCEIFEXISTS myschema.polys_seq;
DROPTABLEIFEXISTS myschema.polys;
CREATESEQUENCEmyschema.polys_seq;

CREATETABLEmyschema.polys AS
SELECT nextval('myschema.polys_seq') AS gid,
(ST_Dump(ST_Polygonize(geom))).geom
FROM
(
SELECT ST_Union(ST_Boundary(geom)) AS geom
FROM myschema.complex_circles
) AS t;

ALTERTABLE myschema.polys ADDPRIMARY KEY(gid);
CREATEINDEXpolys_geom_idxON myschema.polys USING gist(geom);
GRANT ALL ONTABLE myschema.polys TO myschema;
GRANTSELECTONTABLE myschema.polys TO myschema_r;
ALTERTABLE myschema.polys ADD COLUMN count INTEGERDEFAULT0;

UPDATE myschema.polys
SET count = p.count
FROM (
SELECT p.gid, count(*)
FROM myschema.polys p
JOIN myschema.complex_circles c
ON ST_Contains(c.geom, ST_PointOnSurface(p.geom))
GROUP BY p.gid
) AS p
WHERE p.gid = polys.gid;

DELETEFROM myschema.polys WHERE count =0;DELETEFROM myschema.polys WHERE ST_Area(geom)/ST_Perimeter(geom) <0.1;
Cheers!

Eloi

------- Original Message -------
On Wednesday, August 30th, 2023 at 19:43, Nicolas Ribot <nicky666 at gmail.com> wrote:

> 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
>
> ;
>
> [Screenshot 2023-08-30 at 19.41.31.png][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/20230831/866889ce/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/20230831/866889ce/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/20230831/866889ce/attachment-0001.png>