类别：: 地理空间函数、转换函数

TO_GEOMETRY¶

解析输入，并返回 GEOMETRY 类型的值。

另请参阅：: TRY_TO_GEOMETRY、 ST_GEOMETRYFROMWKB、 ST_GEOMETRYFROMWKT

语法¶

使用下列之一：

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

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

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

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

Copy

实参¶

必填：

varchar_expression

实参必须是字符串表达式，它使用以下格式之一表示有效的几何对象：

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

实参必须是 WKB 或 EWKB 格式的二进制表达式。

variant_expression

实参必须是 GeoJSON 格式的 OBJECT。

geography_expression

实参必须是 GEOGRAPHY 类型的表达式。

可选：

srid: 要使用的 SRID 的整数值。

allow_invalid: If TRUE, specifies that the function returns a GEOGRAPHY or GEOMETRY object, even when the input shape isn't valid and can't be repaired. For more information, see 指定如何处理无效的地理空间形状.

返回¶

该函数返回 GEOMETRY 类型的值。

使用说明¶

如果无法将输入解析为支持的格式之一（WKT、WKB、EWKT、EWKB、GeoJSON），则会引发错误。
对于 GeoJSON、WKT 和 WKB 输入，如果未指定 srid 实参，则生成的 GEOMETRY 对象将 SRID 设置为 0。
要从 WKT 或 EWKT 输入来构造 GEOMETRY 对象，还可以使用 ST_GEOMETRYFROMWKT。
要从 WKB 或 EWKB 输入来构造 GEOMETRY 对象，还可以使用 ST_GEOMETRYFROMWKB。

示例¶

The following example shows how to use the TO_GEOMETRY function to convert an object represented in WKT to a GEOMETRY object. The example doesn't specify the srid argument, and the SRID isn't 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)         |
+--------------------------------------+

以下示例将通过 EWKT 表示的对象转换为 GEOMETRY 对象。输入 EKWT 指定要使用的 SRID ：

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)                |
+------------------------------------------------+

下面的示例演示如何将 SRID 指定为 srid 输入实参：

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)            |
+--------------------------------------------+

以下示例为具有 Z 坐标（以 EWKT 格式描述）的地理空间对象返回 GEOMETRY 对象：

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)                |
+-----------------------------------------------------------+

有关将 GEOGRAPHY 对象转换为 GEOMETRY 对象的示例，请参阅在 GEOGRAPHY 和 GEOMETRY 之间转换。

The next examples use the TO_GEOMETRY function in queries on data in a table.

Create a temporary table and insert rows with GEOMETRY values:

CREATE OR REPLACE TEMP TABLE demo_to_geometry AS
SELECT
  1                                                     AS id,
  'POINT(10 20)'                                        AS wkt_col,         -- VARCHAR (WKT)
  'SRID=32633;POINT(500000.0 4649776.22)'               AS ewkt_col,        -- VARCHAR (EWKT)
  ST_ASWKB(TO_GEOMETRY('LINESTRING(0 0, 1 1)'))         AS wkb_bin_col,     -- BINARY (WKB)
  PARSE_JSON('{"type":"Point","coordinates":[10,20]}')  AS geojson_col,     -- VARIANT (GeoJSON)
  TO_GEOGRAPHY('POINT(-122.35 37.55)')                  AS geog_col,        -- GEOGRAPHY
  'POLYGON((0 0,2 2,2 0,0 2,0 0))'                      AS invalid_wkt_col, -- invalid shape
  0                                                     AS srid0,           -- SRID columns to show positional args
  3857                                                  AS srid_col,
  TRUE                                                  AS allow_true,      -- allow_invalid flags from columns
  FALSE                                                 AS allow_false
UNION ALL
SELECT
  2,
  'LINESTRING(0 0, 10 10)',
  'SRID=32633;POINT(389866.35 5819003.03)',
  ST_ASWKB(TO_GEOMETRY('POINT(2 3)')),
  PARSE_JSON('{"type":"LineString","coordinates":[[0,0],[1,1]]}'),
  TO_GEOGRAPHY('LINESTRING(-124.2 42,-120.01 41.99)'),
  'POLYGON((0 0,1 1,1 0,0 1,0 0))',
  0,
  3857,
  TRUE,
  FALSE;

Copy

This table has columns with data types that the TO_GEOMETRY function accepts as inputs in the following formats:

VARCHAR (WKT/WKB and hex/EWKT/EWKB/GeoJSON)
BINARY (WKB/EWKB)
VARIANT (GeoJSON object)
GEOGRAPHY

Optional srid and allow_invalid values can follow any of these formats. The ST_ASWKB、ST_ASBINARY function generates valid WKB BINARY values.

The following example converts VARCHAR values in the wkt_col column to GEOMETRY values by using the default SRID of 0:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(wkt_col) AS g
  FROM demo_to_geometry;

Copy

+----+------------------------------+
| ID | G                            |
|----+------------------------------|
|  1 | SRID=0;POINT(10 20)          |
|  2 | SRID=0;LINESTRING(0 0,10 10) |
+----+------------------------------+

The following example converts VARCHAR values in the wkt_col column to GEOMETRY values by using the SRID value in the srid_col column:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(wkt_col, srid_col) AS g
  FROM demo_to_geometry;

Copy

+----+----------------------------------+
| ID | G                                |
|----+----------------------------------|
|  1 | SRID=3857;POINT(10 20)           |
|  2 | SRID=3857;LINESTRING(0 0,10 10)  |
+----+----------------------------------+

The following example converts VARCHAR values in the ewkt_col column to GEOMETRY values, with the SRID value embedded in the ewkt_col column value:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(ewkt_col) AS g
  FROM demo_to_geometry;

Copy

+----+--------------------------------------------+
| ID | G                                          |
|----+--------------------------------------------|
|  1 | SRID=32633;POINT(500000 4649776.22)        |
|  2 | SRID=32633;POINT(389866.35 5819003.03)     |
+----+--------------------------------------------+

The following example converts BINARY values in the wkb_bin_col column to GEOMETRY values:

ALTER SESSION SET BINARY_OUTPUT_FORMAT='HEX';

SELECT id, TO_GEOMETRY(wkb_bin_col) AS g
  FROM demo_to_geometry;

Copy

+----+----------------------------+
| ID | G                          |
|----+----------------------------|
|  1 | SRID=0;LINESTRING(0 0,1 1) |
|  2 | SRID=0;POINT(2 3)          |
+----+----------------------------+

The following example converts VARIANT values in the geojson_col column to GEOMETRY values by using the SRID value in the srid_col column:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(geojson_col, srid_col) AS g
  FROM demo_to_geometry;

Copy

+----+--------------------------------+
| ID | G                              |
|----+--------------------------------|
|  1 | SRID=3857;POINT(10 20)         |
|  2 | SRID=3857;LINESTRING(0 0,1 1)  |
+----+--------------------------------+

The following example converts GEOGRAPHY values in the geog_col column to GEOMETRY values by using the SRID value in the srid_col column:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(geog_col, srid_col) AS g
  FROM demo_to_geometry;

Copy

+----+-----------------------------------------------+
| ID | G                                             |
|----+-----------------------------------------------|
|  1 | SRID=4326;POINT(-122.35 37.55)                |
|  2 | SRID=4326;LINESTRING(-124.2 42,-120.01 41.99) |
+----+-----------------------------------------------+

The following example converts VARCHAR values in the invalid_wkt_col column to GEOMETRY values by using the SRID value in the srid0 column (0) and the allow_invalid value in the allow_true column:

ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT='EWKT';

SELECT id, TO_GEOMETRY(invalid_wkt_col, srid0, allow_true) AS g
  FROM demo_to_geometry;

Copy

The output includes shapes that aren't valid:

+----+---------------------------------------+
| ID | G                                     |
|----+---------------------------------------|
|  1 | SRID=0;POLYGON((0 0,2 2,2 0,0 2,0 0)) |
|  2 | SRID=0;POLYGON((0 0,1 1,1 0,0 1,0 0)) |
+----+---------------------------------------+