Proper way to use ST_Dump

[carlsunderman]

I am trying to use ST_Dump with a geometry column and can't find any docs or examples. I created a sample multipart polygon and just want to explode it. it only has an ID column and a geom column. Are there any docs anywhere on this?

[Maxxen]

Hi! We generally try to follow the PostGIS behavior, so in this case their docs might be more detailed.

However, DuckDB can't generally return multiple rows from a single function, so in this case we return a list of { path: INT[], geom: GEOMETRY } structs instead. So If you want to "explode" a multipart geometry into multiple rows you need to combine ST_Dump with DuckDB's special UNNEST function. E.g.

SELECT ST_DUMP('MULTIPOINT(0 0, 1 1, 2 2)'::GEOMETRY);

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                            st_dump(CAST('MULTIPOINT(0 0, 1 1, 2 2)' AS GEOMETRY))                            │
│                                   struct(geom geometry, path integer[])[]                                    │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{'geom': POINT (0 0), 'path': [1]}, {'geom': POINT (1 1), 'path': [2]}, {'geom': POINT (2 2), 'path': [3]}] │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

# With UNNEST

SELECT UNNEST(ST_DUMP('MULTIPOINT(0 0, 1 1, 2 2)'::GEOMETRY));
┌────────────────────────────────────────────────────────────────┐
│ unnest(st_dump(CAST('MULTIPOINT(0 0, 1 1, 2 2)' AS GEOMETRY))) │
│             struct(geom geometry, path integer[])              │
├────────────────────────────────────────────────────────────────┤
│ {'geom': POINT (0 0), 'path': [1]}                             │
│ {'geom': POINT (1 1), 'path': [2]}                             │
│ {'geom': POINT (2 2), 'path': [3]}                             │
└────────────────────────────────────────────────────────────────┘

# You can also pass 'recursive := true` to unnest the structs too:

SELECT UNNEST(ST_DUMP('MULTIPOINT(0 0, 1 1, 2 2)'::GEOMETRY), recursive := true);
┌─────────────┬─────────┐
│    geom     │  path   │
│  geometry   │ int32[] │
├─────────────┼─────────┤
│ POINT (0 0) │ [1]     │
│ POINT (1 1) │ [2]     │
│ POINT (2 2) │ [3]     │
└─────────────┴─────────┘