ST_GeomFromText function

The ST_GeomFromText function takes a well-known text (WKT) format or a GeoJSON format of a geometry and, optionally, a spatial reference system identifier as input parameters and returns the corresponding geometry.

ST_GeomFromText constructs a geometry from one of the following inputs:

A WKT format
GeoJSON format

The preferred version for this functionality is ST_Geometry.

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.

geojson

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

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

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.

In this example the ST_GeomFromText function is used to create and insert a point, a linestring, and a polygon from a well known text (WKT) point representation.

The following code inserts rows into the SAMPLE_POINTS table with IDs and geometries in spatial reference system 1 using WKT format.


CREATE TABLE sample_geometries(id INTEGER, geometry ST_GEOMETRY)

INSERT INTO sample_geometries(id, geometry)
VALUES
    (1251, ST_GeomFromText('point(1 2)', 1) ),
    (1252, ST_GeomFromText('linestring(33 2, 34 3, 35 6)', 1) ),
    (1253, ST_GeomFromText('polygon((3 3, 4 6, 5 3, 3 3))', 1))

The following SELECT statement will return the ID and GEOMETRIES from the SAMPLE_GEOMETRIES table.


SELECT id, cast(ST_AsText(geometry) AS varchar(105)) 
   AS geometry 
FROM   sample_geometries

Results:


ID    GEOMETRY
----- -----------------------------------------------------------------
 1251 POINT (1.000000 2.000000)                                             
 1252 LINESTRING (33.000000 2.000000, 34.000000 3.000000, 35.000000 6.000000)
 1253 POLYGON ((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.

In this example the ST_GeomFromText function is used to create and insert a point, a linestring and a polygon from a GeoJSON point representation using the same values as in Example 1. The following code inserts rows into the SAMPLE_POINTS table with IDs and geometries in the default spatial reference system 4326.


CREATE TABLE sample_geometries(id INTEGER, geometry ST_GEOMETRY)

INSERT INTO sample_geometries(id, geometry)
VALUES
    (1251, ST_GeomFromText('{ "type": "point", "coordinates": [1, 2] }') ),
    (1252, ST_GeomFromText('{ "type": "linestring", "coordinates": [[33, 2], [34, 3], [35, 6]] }') ),
    (1253, ST_GeomFromText('{ "type": "polygon", "coordinates": [[[3, 3], [4, 6], [5, 3], [3, 3]]] }') )

The following SELECT statement will return the ID and GEOMETRIES from the SAMPLE_GEOMETRIES table:


SELECT id, cast(ST_AsText(geometry) AS varchar(300)) 
   AS geometry 
FROM   sample_geometries

Results:


ID          GEOMETRY
----------- ---------------------------------------------------------------------------------------------------------
       1251 POINT (1.000000000 2.000000000)
       1252 LINESTRING (33.000000000 2.000000000, 34.000000000 3.000000000, 35.000000000 6.000000000)
       1253 POLYGON ((3.000000000 3.000000000, 5.000000000 3.000000000, 4.000000000 6.000000000, 3.000000000 3.000000000))