ST_Geometry function

The ST_Geometry function constructs a geometry from a given representation.

ST_Geometry constructs a geometry from one of the following inputs:

A well-known text representation
A well-known binary representation
An ESRI shape representation
A representation in the geography markup language (GML)

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 well-known text representation, the well-known binary representation, the ESRI shape representation, or the GML representation is null, then null is returned.

Syntax

Parameter

wkt

A value of type CLOB(2G) that contains the well-known text representation of the resulting geometry.

wkb

A value of type BLOB(2G) that contains the well-known binary representation of the resulting geometry.

shape

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

gml

A value of type 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 0 (zero) is used implicitly.

If srs_id does not identify a spatial reference system listed in the catalog view DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS, then an error is returned (SQLSTATE 38SU1).

Return type

db2gse.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 or line from Geographic Markup Language (GML) line 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. ST_LineFromText can create only a line from WKT line representation. ST_WKTToSql can construct any type, but only from WKT representation.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse 

CREATE TABLE sample_geometries(id INTEGER, geometry ST_GEOMETRY)

INSERT INTO sample_geometries(id, geometry)
VALUES
    (7001, ST_Geometry('point(1 2)', 1) ),
    (7002, ST_Geometry('linestring(33 2, 34 3, 35 6)', 1) ),
    (7003, ST_Geometry('polygon((3 3, 4 6, 5 3, 3 3))', 1)),
    (7004, ST_Geometry('<gml:Point srsName=";EPSG:4269";><gml:coord>
	        <gml:X>50</gml:X><gml:Y>60</gml:Y></gml:coord>
           </gml:Point>', 1))
 
SELECT id, cast(geometry..ST_AsText AS varchar(120)) AS geometry
FROM   sample_geometries

Results:


ID          GEOMETRY
----------- ------------------------------------------------------------       
7001        POINT ( 1.00000000 2.00000000) 
                                         
7002        LINESTRING ( 33.00000000 2.00000000, 34.00000000 3.00000000, 
  35.00000000 6.00000000)

7003        POLYGON (( 3.00000000 3.00000000, 5.00000000 3.00000000, 
  4.00000000 6.00000000, 3.00000000 3.00000000))

7004        POINT ( 50.00000000 60.00000000)