ST_GeomCollFromTxt function
The ST_GeomCollFromTxt function takes a well-known text (WKT) format or a GeoJSON format of a geometry collection and, optionally, a spatial reference system identifier as input parameters and returns the corresponding geometry collection.
ST_GeomCollFromTxt constructs a geometry from one of the following inputs:
- A WKT format
- GeoJSON format
The recommended function for achieving the same result is ST_GeomCollection. It is recommended because of its flexibility: ST_GeomCollection takes additional forms of input as well as the well-known binary format.
If the specified WKT format is null, then null is returned.
If the specified WKT or GeoJSON 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.
- geojson
- A value of type VARCHAR or CLOB(2G) that represents the resulting geometry collection using GeoJSON.
- 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
Example
In the following example, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display.
The following code illustrates how the ST_GeomCollFromTxt function can be used to create and
insert a multipoint, multiline, and multipolygon from a well-known text (WKT) format into a
GeomCollection column.
CREATE TABLE sample_geomcollections(id INTEGER, geometry ST_GEOMCOLLECTION)
INSERT INTO sample_geomcollections(id, geometry)
VALUES
(4011, ST_GeomCollFromTxt('multipoint(1 2, 4 3, 5 6)', 1) ),
(4012, ST_GeomCollFromTxt('multilinestring(
(33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12),
(39 3, 37 4, 36 7))', 1) ),
(4013, ST_GeomCollFromTxt('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))
SELECT id, cast(ST_AsText(geometry) AS varchar(340))
AS geomcollection
FROM sample_geomcollections
Results:
ID GEOMCOLLECTION
------ --------------------------------------------------------------------
4011 MULTIPOINT (1.000000 2.000000, 4.000000 3.000000, 5.000000 6.000000)
4012 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))
4013 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)))
Examples
- Example 1:
-
In the following example, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display.
The following code illustrates how the ST_GeomCollFromTxt function can be used to create and insert a multipoint, multiline, and multipolygon from a well-known text (WKT) format into a GeomCollection column.CREATE TABLE sample_geomcollections(id INTEGER, geometry ST_GEOMCOLLECTION) INSERT INTO sample_geomcollections(id, geometry) VALUES (4011, ST_GeomCollFromTxt('multipoint(1 2, 4 3, 5 6)', 1) ), (4012, ST_GeomCollFromTxt('multilinestring( (33 2, 34 3, 35 6), (28 4, 29 5, 31 8, 43 12), (39 3, 37 4, 36 7))', 1) ), (4013, ST_GeomCollFromTxt('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)) SELECT id, cast(ST_AsText(geometry) AS varchar(340)) AS geomcollection FROM sample_geomcollectionsResults:ID GEOMCOLLECTION ------ -------------------------------------------------------------------- 4011 MULTIPOINT (1.000000 2.000000, 4.000000 3.000000, 5.000000 6.000000) 4012 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)) 4013 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)))
- Example 2:
-
In the following example, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display. The following code illustrates how the ST_GeomCollFromTxt function can be used to create and insert a multipoint, multiline, and multipolygon from a GeoJSON format into a GeomCollection column using the same values as in Example 1 and using the default spatial reference system 4326.
CREATE TABLE sample_geomcollections(id INTEGER, geometry ST_GEOMCOLLECTION) INSERT INTO sample_geomcollections(id, geometry) VALUES (4011, ST_GeomCollFromTxt('{ "type": "multipoint", "coordinates": [[1, 2], [4, 3], [5, 6]] }') ), (4012, ST_GeomCollFromTxt('{ "type": "multilinestring", "coordinates": [ [[33, 2], [34, 3], [35, 6]], [[28, 4], [29, 5], [31, 8], [43 12]], [[39, 3], [37, 4], [36, 7]] ] }') ), (4013, ST_GeomCollFromTxt('{ "type": "multipolygon", "coordinates": [ [[[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]]] ] }') ) SELECT id, cast(ST_AsText(geometry) AS varchar(600)) AS geomcollection FROM sample_geomcollectionsResults:ID GEOMCOLLECTION ------ -------------------------------------------------------------------- 4011 MULTIPOINT (1.000000000 2.000000000, 4.000000000 3.000000000, 5.000000000 6.000000000) 4012 MULTILINESTRING ((33.000000000 2.000000000, 34.000000000 3.000000000, 35.000000000 6.000000000), (28.000000000 4.000000000, 29.000000000 5.000000000, 31.000000000 8.000000000, 43.000000000 12.000000000),(39.000000000 3.000000000, 37.000000000 4.000000000, 36.000000000 7.000000000)) 4013 MULTIPOLYGON (((13.000000000 33.000000000, 10.000000000 43.000000000, 1.000000000 40.000000000, 7.000000000 36.000000000, 13.000000000 33.000000000)),((8.000000000 24.000000000, 9.000000000 25.000000000, 1.000000000 28.000000000, 8.000000000 24.000000000)), ((3.000000000 3.000000000, 5.000000000 3.000000000, 4.000000000 6.000000000, 3.000000000 3.000000000)))