ST_Dump
Name
ST_Dump — Returns a set of
geometry_dump
rows for the components of a geometry.
Synopsis
geometry_dump[]
ST_Dump
(
geometry
g1
)
;
Description
A set-returning function (SRF) that extracts the components of a geometry.
It returns a set of
geometry_dump
rows,
each containing a geometry (
geom
field)
and an array of integers (
path
field).
For an atomic geometry type
(POINT,LINESTRING,POLYGON) a single record is returned with
an empty
path
array and the input geometry as
geom
.
For a collection or multi-geometry a record is returned for each
of the collection components, and the
path
denotes the
position of the component inside the collection.
ST_Dump is useful for expanding geometries. It is the inverse of a ST_Collect / GROUP BY, in that it creates new rows. For example it can be use to expand MULTIPOLYGONS into POLYGONS.
Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
Availability: PostGIS 1.0.0RC1. Requires PostgreSQL 7.3 or higher.
|
|
|
Prior to 1.3.4, this function crashes if used with geometries that contain CURVES. This is fixed in 1.3.4+ |
This method supports Circular Strings and Curves.
This function supports Polyhedral surfaces.
This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
This function supports 3d and will not drop the z-index.
Examples
Dump the components of geometries stored in a table.
SELECT sometable.field1,
(ST_Dump(sometable.geom)).geom AS geom
FROM sometable;
Break a CompoundCurve into its constituent LineStrings and CircularStrings.
SELECT a.geom, ST_HasArc(a.geom)
FROM ( SELECT (ST_Dump(p_geom)).geom AS geom
FROM (SELECT 'COMPOUNDCURVE(CIRCULARSTRING(0 0,1 1,1 0),(1 0,0 1))'::geometry AS p_geom) AS b
) AS a;
st_asewkt | st_hasarc -----------------------------+---------- CIRCULARSTRING(0 0,1 1,1 0) | t LINESTRING(1 0,0 1) | f (2 rows)
This example breaks a PolyhedralSurface into its faces.
SELECT (a.p_geom).path[1] As path,(a.p_geom).geom As geom_ewkt
FROM (SELECT ST_Dump('POLYHEDRALSURFACE(
((0 0 0,0 0 1,0 1 1,0 1 0,0 0 0)),
((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0)),((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0)),((1 1 0,1 1 1,1 0 1,1 0 0,1 1 0)),
((0 1 0,0 1 1,1 1 1,1 1 0,0 1 0)),((0 0 1,1 0 1,1 1 1,0 1 1,0 0 1))
)' ) AS p_geom ) AS a;
path | geom_ewkt
------+------------------------------------------
1 | POLYGON((0 0 0,0 0 1,0 1 1,0 1 0,0 0 0))
2 | POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))
3 | POLYGON((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0))
4 | POLYGON((1 1 0,1 1 1,1 0 1,1 0 0,1 1 0))
5 | POLYGON((0 1 0,0 1 1,1 1 1,1 1 0,0 1 0))
6 | POLYGON((0 0 1,1 0 1,1 1 1,0 1 1,0 0 1))
This example uses a TIN geometry.
SELECT (g.gdump).path,(g.gdump).geom as wkt
FROM
(SELECT
ST_Dump('TIN (((
0 0 0,
0 0 1,
0 1 0,
0 0 0
)),((
0 0 0,
0 1 0,
1 1 0,
0 0 0
))
)' ) AS gdump
) AS g;
path | wkt
------+-------------------------------------
{1} | TRIANGLE((0 0 0,0 0 1,0 1 0,0 0 0))
{2} | TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0))