Categories:

Geospatial Functions, Conversion Functions

TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

See also:

TRY_TO_GEOMETRY , ST_GEOMETRYFROMWKB , ST_GEOMETRYFROMWKT

Syntax

Use one of the following:

TO_GEOMETRY( <varchar_expression> [ , <srid> ] [ , <allow_invalid> ] )

TO_GEOMETRY( <binary_expression> [ , <srid> ] [ , <allow_invalid> ] )

TO_GEOMETRY( <variant_expression> [ , <srid> ] [ , <allow_invalid> ] )
Copy

Arguments

Required:

varchar_expression

The argument must be a string expression that represents a valid geometric object in one of the following formats:

  • WKT (well-known text).

  • WKB (well-known binary) in hexadecimal format (without a leading 0x).

  • EWKT (extended well-known text).

  • EWKB (extended well-known binary) in hexadecimal format (without a leading 0x).

  • GeoJSON.

binary_expression

The argument must be a binary expression in WKB or EWKB format.

variant_expression

The argument must be an OBJECT in GeoJSON format.

Optional:

srid

The integer value of the SRID to use.

allow_invalid

If TRUE, specifies that the function should return a GEOGRAPHY or GEOMETRY object, even when the input shape is invalid and cannot be repaired. For details, refer to Specifying How Invalid Geospatial Shapes Are Handled.

Returns

The function returns a value of type GEOMETRY.

Usage Notes

  • Issues an error if the input cannot be parsed as one of the supported formats (WKT, WKB, EWKT, EWKB, GeoJSON).

  • For GeoJSON, WKT, and WKB input, if the srid argument is not specified, the resulting GEOMETRY object has the SRID set to 0.

  • To construct a GEOMETRY object from WKT or EWKT input, you can also use ST_GEOMETRYFROMWKT.

  • To construct a GEOMETRY object from WKB or EWKB input, you can also use ST_GEOMETRYFROMWKB.

Examples

The following example shows how to use the TO_GEOMETRY function to convert an object represented in WKT to a GEOMETRY object. The example does not specify the srid argument, and the SRID is not specified in the input representation of the object, so the SRID is set to 0.

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT TO_GEOMETRY('POINT(1820.12 890.56)');
Copy
+--------------------------------------+
| TO_GEOMETRY('POINT(1820.12 890.56)') |
|--------------------------------------|
| SRID=0;POINT(1820.12 890.56)         |
+--------------------------------------+

The following example converts an object represented in EWKT to a GEOMETRY object. The input EKWT specifies the SRID to use:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT TO_GEOMETRY('SRID=4326;POINT(1820.12 890.56)');
Copy
+------------------------------------------------+
| TO_GEOMETRY('SRID=4326;POINT(1820.12 890.56)') |
|------------------------------------------------|
| SRID=4326;POINT(1820.12 890.56)                |
+------------------------------------------------+

The following example demonstrates how to specify the SRID as the srid input argument:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT TO_GEOMETRY('POINT(1820.12 890.56)', 4326);
Copy
+--------------------------------------------+
| TO_GEOMETRY('POINT(1820.12 890.56)', 4326) |
|--------------------------------------------|
| SRID=4326;POINT(1820.12 890.56)            |
+--------------------------------------------+

The following example returns the GEOMETRY object for a geospatial object with a Z coordinate described in EWKT format:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT TO_GEOMETRY('SRID=32633;POINTZ(389866.35 5819003.03 30)');
Copy
+-----------------------------------------------------------+
| TO_GEOMETRY('SRID=32633;POINTZ(389866.35 5819003.03 30)') |
|-----------------------------------------------------------|
| SRID=32633;POINTZ(389866.35 5819003.03 30)                |
+-----------------------------------------------------------+