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 might be a good spot to use an SQL-language function. Here's a quick one that should work for this situation:
CREATE OR REPLACE FUNCTION PolygonalIntersection(a geometry, b geometry)
RETURNS geometry AS $$
SELECT ST_Collect(geom)
FROM
(SELECT (ST_Dump(ST_Intersection(a, b))).geom
UNION ALL
-- union in an empty polygon so we get an
-- empty geometry instead of NULL if there
-- is are no polygons in the intersection
SELECT ST_GeomFromText('POLYGON EMPTY')) SQ
WHERE ST_GeometryType(geom) = 'ST_Polygon';
$$ LANGUAGE SQL;
This will retain the polygonal components of an intersection, but throw away everything else. It always returns a MultiPolygon, even if you have one or no components.
WITH
square as (SELECT ST_GeomFromText('POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))') AS geom),
biggersquare as (SELECT ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))') AS geom),
adjacentsquare as (SELECT ST_GeomFromText('POLYGON ((0 0, 1 0, 1 -1, -1 -1, 0 0))') AS geom)
SELECT ST_AsText(PolygonalIntersection(square.geom, biggersquare.geom))
FROM square, biggersquare;
--"MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)))"
SELECT ST_AsText(PolygonalIntersection(square.geom, adjacentsquare.geom))
FROM square, adjacentsquare;
--"MULTIPOLYGON(EMPTY)"
Best Answer
Here is what we can do,
GeometryCollection
with one polygon using theST_Polygonize
aggregate. This is our test data.GeometryCollection
withST_GeometryN(geom,1)
. If your input is already a polygon, you'll pick up from here.ST_Boundary
. The boundary is aLINESTRING
.LINESTRING
into points usingST_PointN
, and using those points as arguments toST_MakeLine
It looks like this,
Now we have to decompose the boundary into 2-point line strings. First we move the aggregate into an inner-select
Then we recompose simple lines,
That returns..