ST_Geometry function

The ST_Geometry function constructs a geometry from a specified representation.

ST_Geometry constructs a geometry from one of the following inputs:

A WKT format
A well-known binary 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 is in.

The dynamic type of the resulting geometry is one of the instantiable subtypes of ST_Geometry.

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.

wkb

A value of type VARBINARY or BLOB(2G) that contains the WKB format of the resulting geometry.

shape

A value of type VARBINARY or BLOB(2G) that represents the ESRI shape format of the resulting geometry.

geojson

A value of type VARCHAR or CLOB(2G) that represents the resulting geometry using GeoJSON.

gml

A value of type VARCHAR or CLOB(2G) that represents the resulting geometry using the Geography Markup Language (GML).

srs_id

A value of type INTEGER that identifies the spatial reference system for the resulting geometry.

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_Geometry

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_Geometry function can be used to create and insert a point from a well-known text (WKT) point representation, a line from a Geography Markup Language (GML) line representation, or a polygon from a GeoJSON representation.

The ST_Geometry function is the most flexible of the spatial type constructor functions because it can create any spatial type from various geometry representations. For example, ST_LineFromText can create a line from WKT line or GeoJSON line representation. ST_WKTToSql can construct any type, but only from WKT format. The spatial reference system used for these geometries is the default spatial reference system 4326.


CREATE TABLE sample_geometries(id INTEGER, geometry ST_GEOMETRY)

INSERT INTO sample_geometries(id, geometry)
VALUES
    (7001, ST_Geometry('point(1 2)')),
    (7002, ST_Geometry('linestring(33 2, 34 3, 35 6)')),
    (7003, ST_Geometry('polygon((3 3, 4 6, 5 3, 3 3))')),
    (7004, ST_Geometry('<gml:Point srsName="EPSG:4326"><gml:coord>
	        <gml:X>50</gml:X><gml:Y>60</gml:Y></gml:coord>
           </gml:Point>')),
    (7005, ST_Geometry('{ "type": "Polygon", "coordinates": 
                        [[[10.0, 11.2], [10.5, 11.9], [10.8, 12.0], [10.0, 11.2]]] }'))
 
SELECT id, cast(ST_AsText(geometry) AS varchar(120)) AS geometry
FROM   sample_geometries

Results:


ID    GEOMETRY
----- ---------------------------------------------------------------------------       
7001  POINT (1.000000 2.000000)  
                                           
7002  LINESTRING (33.000000 2.000000, 34.000000 3.000000, 35.000000 6.000000)

7003  POLYGON ((3.000000 3.000000, 5.000000 3.000000, 4.000000 6.000000, 
        3.000000 3.000000))
        
7004  POINT (50.000000 60.000000)

7005  POLYGON ((10.000000000 11.200000000, 10.800000000 12.000000000, 
        10.500000000 11.900000000, 10.000000000 11.200000000))