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

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 （已知文本）。
WKB （已知二进制）采用十六进制格式（不带前导 0x）。
EWKT （扩展的已知文本）。
EWKB （扩展的已知二进制文件）采用十六进制格式（不带前导 0x）。
GeoJSON。

binary_expression

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

variant_expression

实参必须是 GeoJSON 格式的 OBJECT。

geography_expression

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

可选：

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

allow_invalid: 如果为 TRUE，即使输入形状无效且无法修复，指定函数也应返回 GEOGRAPHY 或 GEOMETRY 对象。有关详细信息，请参阅指定如何处理无效的地理空间形状。

返回¶

该函数返回 GEOMETRY 类型的值。

使用说明¶

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

示例¶

下面的示例演示如何使用 TO_GEOMETRY 函数将通过 WKT 表示的对象转换为 GEOMETRY 对象。该示例未指定 srid 实参，并且未在对象的输入表示形式中指定 SRID，因此 SRID 设置为 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)) |
+----+---------------------------------------+