ST_GeomCollection function
Use the ST_GeomCollection function to construct a geometry collection.
ST_GeomCollection constructs a geometry collection from
one of the following inputs:
- Well-known text (WKT) format
- Well-known binary (WKB) format
- ESRI shape format
- GeoJSON format
- Geography Markup Language (GML) format
An optional spatial reference system identifier can be specified to identify the spatial reference system that the resulting geometry collection is in.
If the WKT, WKB, ESRI shape, or GML format is null, then null is returned.
If the WKT, WKB, ESRI shape, GeoJSON, or GML format is null, then null is returned.
For details about the supported formats, see Supported data formats.
Syntax
Parameters
- wkt
- A value of type VARCHAR or CLOB(2G) that contains the WKT format of the resulting geometry collection.
- wkb
- A value of type VARBINARY or BLOB(2G) that contains the WKB format of the resulting geometry collection.
- shape
- A value of type VARBINARY or BLOB(2G) that represents the ESRI shape format of the resulting geometry collection.
- geojson
- A value of type VARCHAR or CLOB(2G) that represents the resulting geometry collection using GeoJSON.
- gml
- A value of type VARCHAR or CLOB(2G) that represents the resulting geometry collection using the Geography Markup Language (GML).
- srs_id
- A value of type INTEGER that identifies the spatial reference
system for the resulting geometry collection.
If the srs_id parameter is omitted, the spatial reference system with the numeric identifier 4326 is used implicitly.
If srs_id does not identify a spatial reference system listed in the catalog view SYSGEO.ST_SPATIAL_REFERENCE_SYSTEMS, then an error is returned (SQLSTATE 38SU1).
Return type
ST_GeomCollection
Notes
An ST_GeomCollection geometry can only contain a single type of geometry.
If the srs_id parameter is omitted, cast wkt and gml explicitly to the CLOB data type. Otherwise, the function call might be resolved to the function used to cast values from the reference type REF(ST_GeomCollection) to the ST_GeomCollection type.
Example
The following examples shows how to ensure that a function call to ST_GeomCollection resolves to the correct function. Also, the lines that show results are reformatted for readability. The spacing in your results can vary according to your online display.
The following code illustrates how the ST_GeomCollection function
can be used to create and insert a multipoint, multiline, and multipolygon from well-known text
(WKT) format and a multipoint from Geography Markup Language (GML) into an ST_GeomCollection
column.The following code illustrates how the ST_GeomCollection
function can be used to create and insert a multipoint, multiline, and multipolygon from well-known
text (WKT) format, a multipoint from Geography Markup Language (GML), and a geometry collection
consisting of a line and a point in GeoJSON format into an ST_GeomCollection column. The spatial
reference system used for these geometries is the default spatial reference system
4326.
CREATE TABLE sample_geomcollections(id INTEGER,
geometry ST_GEOMCOLLECTION)
INSERT INTO sample_geomcollections(id, geometry)
VALUES
(4001, ST_GeomCollection('multipoint(1 2, 4 3, 5 6)', 1)),
(4002, ST_GeomCollection('multilinestring(
(33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12),
(39 3, 37 4, 36 7))', 1)),
(4003, ST_GeomCollection('multipolygon(((3 3, 4 6, 5 3, 3 3),
(8 24, 9 25, 1 28, 8 24),
(13 33, 7 36, 1 40, 10 43, 13 33)))', 1)),
(4004, ST_GeomCollection('<gml:MultiPoint srsName="EPSG:4269"
><gml:PointMember><gml:Point>
<gml:coord><gml:X>10</gml:X>
<gml:Y>20</gml:Y></gml: coord></gml:Point>
</gml:PointMember><gml:PointMember>
<gml:Point><gml:coord><gml:X>30</gml:X>
<gml:Y>40</gml:Y></gml:coord></gml:Point>
</gml:PointMember></gml:MultiPoint>', 1))
SELECT id, cast(ST_AsText(geometry) AS varchar(350)) AS geomcollection
FROM sample_geomcollections
CREATE TABLE sample_geomcollections(id INTEGER,
geometry ST_GEOMCOLLECTION)
INSERT INTO sample_geomcollections(id, geometry)
VALUES
(4001, ST_GeomCollection('multipoint(1 2, 4 3, 5 6)') ),
(4002, ST_GeomCollection('multilinestring(
(33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12),
(39 3, 37 4, 36 7))') ),
(4003, ST_GeomCollection('multipolygon(((3 3, 4 6, 5 3, 3 3),
(8 24, 9 25, 1 28, 8 24),
(13 33, 7 36, 1 40, 10 43, 13 33)))')),
(4004, ST_GeomCollection('<gml:MultiPoint srsName="EPSG:4326"
><gml:PointMember><gml:Point>
<gml:coord><gml:X>10</gml:X>
<gml:Y>20</gml:Y></gml: coord></gml:Point>
</gml:PointMember><gml:PointMember>
<gml:Point><gml:coord><gml:X>30</gml:X>
<gml:Y>40</gml:Y></gml:coord></gml:Point>
</gml:PointMember></gml:MultiPoint>')),
(4005, ST_GeomCollection('{ "type": "multipolygon", "coordinates":
[ [[[10.0, 11.2], [10.5, 11.9], [10.8, 12.0], [10.0, 11.2]]],
[[[13, 33], [7, 36], [1, 40], [10, 43], [13, 33]]]
] }'))
SELECT id, cast(ST_AsText(geometry) AS varchar(500)) AS geomcollection
FROM sample_geomcollections
Results:
ID GEOMCOLLECTION
----------- -----------------------------------------------------------------
4001 MULTIPOINT (1.000000 2.000000, 4.000000 3.000000, 5.000000 6.000000)
4002 MULTILINESTRING ((33.000000 2.000000, 34.000000 3.000000,
35.000000 6.000000),(28.000000 4.000000, 29.000000 5.000000,
31.000000 8.000000, 43.000000 12.000000),(39.000000 3.000000,
37.000000 4.000000, 36.000000 7.000000))
4003 MULTIPOLYGON (((13.000000 33.000000, 10.000000 43.000000,
1.000000 40.000000, 7.000000 36.000000, 13.000000 33.000000)),
((8.000000 24.000000, 9.000000 25.000000, 1.000000 28.000000,
8.000000 24.000000)),((3.000000 3.000000, 5.000000 3.000000,
4.000000 6.000000, 3.000000 3.000000)))
4004 MULTIPOINT (10.000000 20.000000, 30.000000 40.000000)
ID GEOMCOLLECTION
----------- -----------------------------------------------------------------
4001 MULTIPOINT (1.000000 2.000000, 4.000000 3.000000, 5.000000 6.000000)
4002 MULTILINESTRING ((33.000000 2.000000, 34.000000 3.000000,
35.000000 6.000000),(28.000000 4.000000, 29.000000 5.000000,
31.000000 8.000000, 43.000000 12.000000),(39.000000 3.000000,
37.000000 4.000000, 36.000000 7.000000))
4003 MULTIPOLYGON (((13.000000 33.000000, 10.000000 43.000000,
1.000000 40.000000, 7.000000 36.000000, 13.000000 33.000000)),
((8.000000 24.000000, 9.000000 25.000000, 1.000000 28.000000,
8.000000 24.000000)),((3.000000 3.000000, 5.000000 3.000000,
4.000000 6.000000, 3.000000 3.000000)))
4004 MULTIPOINT (10.000000 20.000000, 30.000000 40.000000)
4005 MULTIPOLYGON (((13.000000000 33.000000000, 10.000000000 43.000000000,
1.000000000 40.000000000, 7.000000000 36.000000000, 13.000000000 33.000000000)),
((10.000000000 11.200000000, 10.800000000 12.000000000, 10.500000000 11.900000000,
10.000000000 11.200000000)))