ST_Dump

What is ST_Dump?

ST_Dump is a set-returning PostGIS function that decomposes a multi-geometry or collection into its component simple geometries, yielding one row per part. Each row is a geometry_dump record containing the part's path (an integer array of its position within nested collections) and the geometry itself.

ST_Dump(geometry g) → setof geometry_dump(path integer[], geom geometry)

For a single (non-multi) geometry, ST_Dump returns one row with path = '{}'. For a MULTIPOLYGON it returns one row per polygon, and for a GEOMETRYCOLLECTION it recurses into each element.

When would you use ST_Dump?

Use ST_Dump to explode multi-geometries into rows for per-part analysis — computing area of each polygon, counting parts, or joining individual components:

1SELECT region_id, (d).path[1] AS part_idx,
2       ST_Area((d).geom) AS part_area
3FROM (
4  SELECT region_id, ST_Dump(geom) AS d FROM regions
5) t;

It is the canonical PostGIS equivalent of "explode" in other GIS tools. Use ST_DumpPoints, ST_DumpSegments, or ST_DumpRings for vertex-, segment-, or ring-level decomposition.

FAQs

What is the geometry_dump type?

A composite PostgreSQL type with two fields: path integer[] (location within nested structures) and geom geometry (the part itself). Access with (d).path and (d).geom after expanding the row.

How deep does ST_Dump recurse?

Fully — a GEOMETRYCOLLECTION of MULTIPOLYGONs is flattened into individual polygons, with the path array tracking the full nesting position.

What happens when I ST_Dump a single polygon?

One row, with path = '{}' and geom equal to the input polygon. This lets you write generic queries that work uniformly on both simple and multi geometries.

How do I preserve part ordering?

The path array gives you the original position. For stable ordering across queries, use ORDER BY path after unnesting.