ST_Explode
Signature
tableName[*, explod_id] ST_Explode('tableName');
tableName[*, explod_id] ST_Explode('query');
tableName[*, explod_id] ST_Explode('tableName', 'fieldName');
tableName[*, explod_id] ST_Explode('query', 'fieldName');
Description
Explodes the GEOMETRYCOLLECTIONs in the fieldName column of table tableName, or in a query, into multiple Geometries.
If no field name is specified, the first Geometry column is used.
The select query must be enclosed in parenthesis ().
Examples
CREATE TABLE test_point AS SELECT
'MULTIPOINT((1 1), (2 2))'::Geometry as THE_GEOM;
With the tableName
SELECT * FROM ST_Explode('test_point');
With a query
SELECT * FROM ST_Explode('(SELECT * FROM test_point WHERE ST_Dimension(THE_GEOM)=0)');
-- Answer:
-- | THE_GEOM | EXPLOD_ID |
-- | ------------|-----------|
-- | POINT(1 1) | 1 |
-- | POINT(2 2) | 2 |
With tableName and fieldName
CREATE TABLE test_point AS SELECT
'MULTIPOINT((1 1), (2 2))'::Geometry the_geomA,
'MULTIPOINT((3 3), (2 6))'::Geometry the_geomB;
SELECT * FROM ST_Explode('test_point', 'the_geomB');
-- Answer:
-- | THE_GEOMA | THE_GEOMB | EXPLOD_ID |
-- |---------------------------|-------------|-----------|
-- | MULTIPOINT((1 1), (2 2)) | POINT(3 3) | 1 |
-- | MULTIPOINT((1 1), (2 2)) | POINT(2 6) | 2 |