st_union(geom[]) does not union single-geometrycollection arrays

Dian Fay di at nmfay.com
Sat Feb 24 15:16:22 PST 2024


Postgres 16.1
postgis_version: 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

I've produced some geometrycollection arrays from
st_clusterintersecting. Now I want to combine each cluster, to end up
with as many simple geometries as there were originally clusters. This
turns out to work well as long as I have multiple clusters. When the
array contains only one geometrycollection -- in other words, _all_ the
original geometries overlapped and should be combined -- it doesn't.
Here's a distilled example showing a successful cluster simplification
with two clusters:

```
select
  st_asewkt(
    st_union(
      array[
        st_geomfromtext(
          'GEOMETRYCOLLECTION(
            POLYGON((0 0,10 0,25 25,0 10,0 0)),
            POLYGON((20 20,30 20,30 30,20 30,20 20))
          )'
        ),
        st_geomfromtext(
          'GEOMETRYCOLLECTION(
            POLYGON((100 100, 110 100, 110 110, 100 100))
          )'
        )
      ]
    )
  );
```

The polygons in the first collection overlap and are merged:

```
MULTIPOLYGON((
  (10 0,0 0,0 10,20 22,20 30,30 30,30 20,22 20,10 0)),
  ((110 100,100 100,110 110,110 100))
)
```

However, remove the second geometrycollection:

```
select
  st_asewkt(
    st_union(
      array[
        st_geomfromtext(
          'GEOMETRYCOLLECTION(
            POLYGON((0 0,10 0,25 25,0 10,0 0)),
            POLYGON((20 20,30 20,30 30,20 30,20 20))
          )'
        )
      ]
    )
  );
```

And st_union doesn't touch it:

```
GEOMETRYCOLLECTION(
  POLYGON((0 0,10 0,25 25,0 10,0 0)),
  POLYGON((20 20,30 20,30 30,20 30,20 20))
)
```

Since this does pull it out of the array, I can get the result I'm after
by nesting `st_union(st_union(array[....]))`. But it's counterintuitive,
at least to me, that `pgis_union_geometry_array` skips these. Should the
"one geom, good geom" case really apply to geometrycollections or is it
meant more for simple geometries?


More information about the postgis-devel mailing list