PostGIS – Deleting Small Holes in Polygons by Specifying Size

Question

I have a big MultiPolygon layer in which I want to delete holes smaller than 100m².

QGIS do it well and fast with the algorithm "native:deleteholes", but I need to use it as part of a SQL workflow with PostGIS. I found this:

SELECT gid, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
   SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
   FROM my_spatial_table
   ) s
GROUP BY gid;

But it does not allow to specify the max dimension of the holes to delete.

Is there a way?

Answer 1

This is one of those processes which are simple in an iterative language, but trickier in set-oriented SQL. But the ST_DumpX set-returning functions in PostGIS and the array handling in Postgres make it easier (but still fiddly to get working). The following solution works on MultiPolygons and also Polygons if they happen to be in the dataset.

WITH data(id, geom) AS (VALUES
   (1, 'MULTIPOLYGON (((100 100, 100 0, 0 0, 0 100, 100 100), (10 10, 10 70, 60 10, 10 10), (30 90, 90 90, 90 30, 30 90), (20 80, 10 80, 10 90, 20 80), (90 10, 80 10, 80 20, 90 10)), ((0 170, 100 170, 100 120, 0 120, 0 170), (10 130, 10 140, 20 130, 10 130)))'::geometry)
  ,(2, 'MULTIPOLYGON (((200 100, 300 100, 300 0, 200 0, 200 100), (210 10, 210 70, 260 10, 210 10), (280 80, 280 90, 290 80, 280 80)), ((200 160, 260 160, 260 120, 200 120, 200 160)))'::geometry)
  ,(3, 'POLYGON ((110 90, 190 90, 190 10, 110 10, 110 90), (120 20, 120 80, 180 20, 120 20), (170 70, 170 80, 180 70, 170 70))'::geometry)
)
SELECT id, ST_Collect( 
    ARRAY( SELECT ST_MakePolygon( 
              ST_ExteriorRing(geom),
              ARRAY( SELECT ST_ExteriorRing( rings.geom )
                      FROM ST_DumpRings(geom) AS rings
                      WHERE rings.path[1] > 0 AND ST_Area( rings.geom ) >= 100
            )
    )
    FROM ST_Dump(geom) AS poly ) 
  ) AS geom
FROM data;