Alias

Dump

Description

Splits Multi-geometries and GeometryCollections into a set of single geometries.

The type of geometries to extract can be indicated as an integer. Possible values are 0 for Points, 1 for LineStrings, 2 for Polygons.

The reverse function of ST_Dump is ST_Collect.

	GeometryCollections are supported.

	JASPA ST_Dump function returns just an array of geometries. PostGIS ST_Dump function creates an array of geometries with an array of integers (path).

Coordinate Dimensions

2D	3D	M

Spatial Standards Support

Examples

PostgreSQL

ST_Dump(geomA)

SELECT st_dump (st_geomfromtext('MULTIPOINT (1 2, 3 4, 5 6)'));
                                    st_dump
--------------------------------------------------------------------------------
 \001\001\000\000\000\000\000\000\000\000\000\360?\000\000\000\000\000\000\000@
 \001\001\000\000\000\000\000\000\000\000\000\010@\000\000\000\000\000\000\020@
 \001\001\000\000\000\000\000\000\000\000\000\024@\000\000\000\000\000\000\030@
(3 rows)

SELECT ST_astext(st_dump (st_geomfromtext('MULTIPOINT (1 2, 3 4, 5 6)')));
  st_astext
-------------
 POINT (1 2)
 POINT (3 4)
 POINT (5 6)
(3 rows)

--4D GeometryCollection
SELECT ST_astext(st_dump (st_geomfromtext('GEOMETRYCOLLECTION (POINT (2 2 1 8), POINT (2 5 1 9), 
POLYGON ((3 4 4 5, 3.2 6 8 9, 2.8 7.4 7 2, 5.1 7.1 5 8, 4.4 4.5 4 8, 3 4 4 5)))')));

                                   st_astext
--------------------------------------------------------------------------------
 POINT (2 2 1 8)
 POINT (2 5 1 9)
 POLYGON ((3 4 4 5, 3.2 6 8 9, 2.8 7.4 7 2, 5.1 7.1 5 8, 4.4 4.5 4 8, 3 4 4 5))
(3 rows)

ST_Dump(geomA,dim)

--Creates a set from points
SELECT ST_astext(st_dump (
st_geomfromtext('GEOMETRYCOLLECTION (POINT (2 2), POINT(5 3), POINT (6 5),POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4)))')
,0));

--Result
    st_astext
-------------
 POINT (2 2)
 POINT (5 3)
 POINT (6 5)
(3 rows)

--Creates a set from polygons
SELECT ST_astext(st_dump (
st_geomfromtext('GEOMETRYCOLLECTION (POINT (2 2), POINT(5 3), POINT (6 5),POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4)))')
,2));

--Result
                       st_astext
--------------------------------------------------------
 POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4))
(1 row)

H2

ST_Dump(geomA)

SELECT * from st_dump ('select st_geomfromewkt(''MULTIPOINT (1 2, 3 4, 5 6)'')');

JASPA.ST_GEOMFROMEWKT('MULTIPOINT (1 2, 3 4, 5 6)')  
0101000000000000000000f03f0000000000000040
010100000000000000000008400000000000001040
010100000000000000000014400000000000001840


SELECT ST_AsText(geom) from st_dump ('select st_geomfromewkt(''MULTIPOINT (1 2, 3 4, 5 6)'') as geom');

ST_ASTEXT(GEOM)  
POINT (1 2)
POINT (3 4)
POINT (5 6)

--4D GeometryCollection
SELECT ST_AsText(geom) from st_dump ('select st_geomfromewkt(''GEOMETRYCOLLECTION (POINT (2 2 1 8), POINT (2 5 1 9), 
POLYGON ((3 4 4 5, 3.2 6 8 9, 2.8 7.4 7 2, 5.1 7.1 5 8, 4.4 4.5 4 8, 3 4 4 5)))'') as geom');

ST_ASTEXT(GEOM)  
POINT (2 2 1 8)
POINT (2 5 1 9)
POLYGON ((3 4 4 5, 3.2 6 8 9, 2.8 7.4 7 2, 5.1 7.1 5 8, 4.4 4.5 4 8, 3 4 4 5))

ST_Dump(geomA,dim)

--Creates a set from points
SELECT ST_AsText(geom) from st_dump ('select st_geomfromewkt(''GEOMETRYCOLLECTION (POINT (2 2), POINT(5 3), POINT (6 5), 
 POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4)))'') as geom',0);

--Result
JASPA.ST_ASTEXT(GEOM)  
POINT (2 2)
POINT (5 3)
POINT (6 5)

--Creates a set from polygons
SELECT ST_AsText(geom) from st_dump ('select st_geomfromewkt(''GEOMETRYCOLLECTION (POINT (2 2), POINT(5 3), POINT (6 5), 
POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4)))'')',2);

--Result
JASPA.ST_ASTEXT(GEOM)  
POLYGON ((3 4, 3.2 6, 2.8 7.4, 5.1 7.1, 4.4 4.5, 3 4))

Related functions

ST_Collect, ST_Dimension, ST_DumpPoints,ST_DumpRings