How can I get a polygon of everything between two polygons in PostGIS?

Question

I am trying to find all the neighborhoods located between two (or more) neighborhoods. The definition of between is obviously a bit fuzzy but for our purpose, I'd like to extract all neighborhoods that overlap with the shape connecting two disjoint shapes.

For this, I am trying to build a polygon merging two polygons and including everything in between the two. I am not very familiar with the terminology, so I hope the following picture helps understand better what I am trying to do.

I am using PostgreSQL with PostGIS extension.

Source polygons

Desired output polygon

How could I write a PostGIS query to get this? I tried the following but without success:

SELECT ST_MakePolygon(ST_ExteriorRing(ST_Union(shape)))
FROM (
    SELECT *, 1 as group_id
    FROM subquery
) a
GROUP BY group_id

where subquery is just a table with the selected neighborhoods. shape is a polygon.

Thanks a lot for your help!

Answer 1

As mentioned in comments, a ST_ConvexHull is by far the easiest solution to generate your desired Polygon:

SELECT ST_ConvexHull(ST_Collect(shape)) AS geom
FROM   subquery
;

ST_Collect is a lot more performant compared to ST_Union, and ST_ConvexHull will happily work with MULTI* geometries and GEOEMTRYCOLLECTIONs.

Answer 2

The convex hull idea worked pretty well but has the disadvantage of including parts of neighborhoods on the other sides of the source neighborhoods.

The following query builds on the convex hulls idea but fix the areas around the original neighborhoods.

SELECT ST_Buffer(ST_MakePolygon(ST_ExteriorRing(ST_Buffer(ST_Collect(ST_Difference(ST_ConvexHull(ST_Union(shape)), ST_Collect(st_convexhull(shape))), ST_Collect(shape))::geography, 1)::geometry))::geography, -1)
FROM (
    SELECT *, 1 as group_id
    FROM subquery
) a
GROUP BY group_id

Decomposing this query:

• Get the convex hull of both shapes together ST_ConvexHull(ST_Collect(shape)
• Remove the individual convex hulls of the original neighborhoods ST_Difference(x, ST_Collect(st_convexhull(shape))))
• Add the original shape of the source neighborhoods to the previous shape ST_Collect(prev, ST_Collect(shape))
• Add expand the area by 1 meter to make sure the original neighborhoods and the ones from the full convex hulls are touching `ST_Buffer(prev::geography, 1)
• Get the exterior ring and make a polygon out of it ST_MakePolygon(ST_ExteriorRing(prev::geometry))
• Shrink the resulting shape back by 1 meter ST_Buffer(prev::geography, -1)

The resulting selection

Answer 3

@nbeuchat,

I found your idea interesting, at least from my point of view,

But function ST_Buffer() distorts the boundaries and so you don't mind my suggestion next,

run your script without ST_Buffer and see the result :-)...

SELECT (ST_MakePolygon(ST_ExteriorRing(ST_Union(
ST_Difference(ST_ConvexHull(ST_Union(geom)), ST_Collect(ST_Convexhull(geom))), ST_Collect(geom))))) AS geom FROM <name_table>;