H2GIS

Fork me on GitHub

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;

-- ST_Explode using the 'tableName'
SELECT * FROM ST_Explode('test_point');

-- or
-- ST_Explode using 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     |

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    |
See also