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)
Taras
  • 32,823
  • 4
  • 66
  • 137
rm167
  • 469
  • 2
  • 8

2 Answers2

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

BERA
  • 72,339
  • 13
  • 72
  • 161
0

You can use ST_ClusterIntersecting or ST_ClusterIntersectingWin to do this.

dr_jts
  • 5,038
  • 18
  • 15
  • 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 https://postgis.net/docs/ – robert tuw Mar 19 '24 at 18:11