ST_DUMP()

All functions > GEOSPATIAL > ST_DUMP()

Explodes a Multi* or GeometryCollection into an array of its component geometries.

Signatures

Dump

Returns: Array of sub-geometries

ST_DUMP(geo: T) → GEOMETRY[] or GEOGRAPHY[]

With:

• T : Custom types: GEOM_POINT | GEOM_LINESTRING | GEOM_POLYGON | GEOM_MULTIPOINT | GEOM_MULTILINESTRING | GEOM_MULTIPOLYGON | GEOM_MULTIANY | GEOG_POINT | GEOG_LINESTRING | GEOG_POLYGON | GEOG_MULTIPOINT | GEOG_MULTILINESTRING | GEOG_MULTIPOLYGON | GEOG_MULTIANY

Signature notes:

• Multi* or GeometryCollection: returns one element per sub-geometry
• Singular geometry (Point, LineString, Polygon): returns a 1-element array
• Empty geometry: returns an empty array []
• Use UNNEST to expand the result into rows
• On DuckDB: uses ST_Dump which returns structs; the geom field is extracted via list_transform
• On BigQuery and Trino: emulated with ST_GEOMETRYN over a generated index sequence
• Returns NULL if the input is NULL

Examples

SELECT
    f1 := ARRAY_LENGTH(ST_DUMP(ST_GEOMFROMTEXT('MULTIPOINT ((0 0), (1 1), (2 2))'))), -- Explode a MultiPoint into an array of Points
    f2 := ARRAY_LENGTH(ST_DUMP(ST_GEOMPOINT(5.0, 5.0))) -- Singular geometry returns a 1-element array
;