1

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

Source shapes

Desired output polygon

Merged shapes

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!

5

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.

| improve this answer | |
  • Note that, for high performance 'in between' searches on large tables, you may want to go different routes. A similar approach would be to search for bbox intersection with lines connecting pairs of centroids. Personally, I'd go for bbox intersection of your neighborhoods with (dynamic) pairs of bboxes of those neighborhoods you want to search in between. – geozelot Mar 4 at 11:36
  • 1
    @BERA +1 for your cluster approach; if pairing by spatial relation is required, that would be my choice! – geozelot Mar 4 at 11:39
  • 1
    That worked great as a starting point! The ST_Collect worked well too. The problem is that this query includes areas on the edges of the original neighborhoods which are not desired. I found a more complex query below which does the trick, might be a bit too complex though – nbeuchat Mar 4 at 11:53
  • @nbeuchat as I said, this will generate your 'desired polygon'; if I have the time I will add a query that I think will perform better for your actual desired outcome... – geozelot Mar 4 at 12:01
  • 1
    @nbeuchat btw. you could simply shrink the convex hull by a tiny negative buffer to exclude neighborhoods on the polygon edges if you keep trying to get them by intersection (note that there are also other spatial relations that may help narrowing down your search!) – geozelot Mar 4 at 12:37
1

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

Merged neighborhoods

| improve this answer | |
0

Not exactly what you want, but Close enough?

select st_convexhull(st_collect(geom)) as geom
from(
select geom, st_clusterdbscan(geom, eps:=25000, minpoints:=2) over () AS cid --polygons within 25000 map units are grouped together
from (
select * from ak_riks
where "kommunnamn" IN ('Askersund','Karlstad','Katrineholm','Karlskoga','Ludvika')) sub) cluster
where cid is not null
group by cid

enter image description here

| improve this answer | |
-1

@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>;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.