# Creating polygons from gaps in PostGIS?

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?

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

• This is one of those answers that is so genius, I wish I could upvote it twice. Commented Dec 8, 2016 at 19:08
• @JohnBarça Wow, you really made my day. I used to compete with you long ago. Thanks. Commented Dec 8, 2016 at 19:45
• I upvote for you @JohnBarça, now someone has to vote for me. Commented Jun 2, 2017 at 13:47

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;