Creating polygons from gaps in PostGIS?

Question

Using PostGIS, I want to fill a gap between other polygons.

Image to explain from where to extract the polygon:

Can you help with some directions what functions to use?

Answer 1

Use ST_Union to aggregate the polygons into one polygon and then ST_InteriorRingN to get the border of the gap and ST_BuildArea to get polygon of the gap. If there is more than one such gap use generate_series and ST_NumInteriorRings. Example:

WITH polygons(geom) AS
(VALUES (ST_Buffer(ST_Point(0, 0), 1.1,3)),
        (ST_Buffer(ST_Point(0, 2), 1.1,3)),
        (ST_Buffer(ST_Point(2, 2), 1.1,3)),
        (ST_Buffer(ST_Point(2, 0), 1.1,3)),
        (ST_Buffer(ST_Point(4, 1), 1.3,3))
),
bigpoly AS
(SELECT ST_UNION(geom)geom 
 FROM polygons)
SELECT ST_BuildArea(ST_InteriorRingN(geom,i)) 
FROM bigpoly
CROSS JOIN generate_series(1,(SELECT ST_NumInteriorRings(geom) FROM bigpoly)) as i;

Blue-start polygons Purple-final polygons

Answer 2

Jakub's answer does the job. In a similar approach, you could use ST_DumpRings to dump the interior rings, which have path != 0. Perhaps it can be a little bit faster for bigger tables.

WITH polygons(geom) AS
(VALUES (ST_Buffer(ST_Point(0, 0), 1.1,3)),
    (ST_Buffer(ST_Point(0, 2), 1.1,3)),
    (ST_Buffer(ST_Point(2, 2), 1.1,3)),
    (ST_Buffer(ST_Point(2, 0), 1.1,3)),
    (ST_Buffer(ST_Point(4, 1), 1.3,3))
),
bigpoly AS
(SELECT ST_Union(geom) geom FROM polygons),
rings AS
(SELECT (ST_DumpRings(geom)).* FROM bigpoly)
SELECT geom FROM rings WHERE path[1] != 0;