3

I have a polygon geometry with 100+ polygons originally converted from a raster table - see the below image

enter image description here

Zoomed view of one of the polygons

enter image description here

I would like to unify the touching polygons (i.e. sharing a common vertex or a part of the perimeter). I tried with st_dump(st_union(...)) but it only unifies polygons which share a part of the perimeter and not a vertex. See below

enter image description here

I think I can do this by using st_union and st_touches. But I just don't know how to write the correct SQL with just one geometry as st_tocuhes requires two geometries. My below attempt creates one large multi-polygon with all the polygons which is not what I want

SELECT st_union(a.geom) AS geom
FROM orginal_geom a, original_geom b
WHERE st_touches(a.geom, b.geom)

2 Answers 2

5

I can recommend this way, it is very fast.

Each group of polygons is assigned the same id if they are 0 distance apart, and number at least 2, using ST_ClusterDBSCAN.

Single polygons will get NULL as cluster id.

with clusters as (
select st_clusterdbscan(geom, 0, 2) over() cluster_id, geom
from test.minigrid)

select st_union(geom) as geom
from clusters where cluster_id is not null
group by cluster_id

union

select geom from clusters where cluster_id is null

1.1 s. to dissolve 36 k squares:

enter image description here

1
  • 2
    ST_ClusterDBSCAN is exactly what I wanted. Many thanks.
    – rm167
    Aug 1, 2023 at 13:59
0

You can use ST_ClusterIntersecting or ST_ClusterIntersectingWin to do this.

1
  • ST_ClusterIntersectingWin(geom) returns an error 42883 function ST_ClusterIntersectingWin(geometry) does not exist, other function that do throw errors are: ST_ClusterWithinWin(), ST_CoverageInvalidEdges() ... these errors occur even though I use the example code from postgis.net/docs
    – robert tuw
    Mar 19 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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