Le portail francophone de la géomatique

WITH p as (SELECT row_number as a,unnest((st_dumprings((st_dump (resgeom)).geom)).path) as b,((st_dumprings((st_dump (resgeom)).geom)).geom) as c
FROM testgeom3 ),  -- On transforme les multipolygones à trous en n polygones sans trous si path = 0 exterior ring si >0 trous
p1 as (SELECT a,b,st_exteriorring(c) as e,st_area(c) as d FROM p   order by a,b), -- on transforme les polygones ext en polyline
p2 as (SELECT a, st_accum(e) as sg FROM p1 WHERE b<>0 AND d >=1000 GROUP BY a), les trous de plus de 1000 m²
p3 as (SELECT a, e FROM p1 WHERE b=0) les enveloppes ext (shells)
SELECT p2.a,CASE WHEN not sg is null THEN st_makepolygon(e,sg) ELSE st_makepolygon(e) END 
into testpoly1 FROM p3 LEFT JOIN p2 ON p2.a=p3.a ORDER BY a -- On reconstitue les polygones soit avec les trous qui restent soit sans.