MySQL :: MySQL 8.4 参考手册 :: 14.16.6 几何格式转换函数

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.16 空间分析函数 / 14.16.6 几何格式转换函数

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 88%

14.16.6 几何格式转换函数14.16.6 Geometry Format Conversion Functions

MySQL 支持本节中列出的函数，用于将几何值从内部几何格式转换为 WKT 或 WKB 格式，或用于交换 X 和 Y 坐标的顺序。MySQL supports the functions listed in this section for converting geometry values from internal geometry format to WKT or WKB format, or for swapping the order of X and Y coordinates.

还有一些函数可以将字符串从 WKT 或 WKB 格式转换为内部几何格式。请参见第 14.16.3 节，“从 WKT 值创建几何值的函数”，以及第 14.16.4 节，“从 WKB 值创建几何值的函数”。There are also functions to convert a string from WKT or WKB format to internal geometry format. See Section 14.16.3, “Functions That Create Geometry Values from WKT Values”, and Section 14.16.4, “Functions That Create Geometry Values from WKB Values”.

像ST_GeomFromText()这样接受 WKT 几何集合参数的函数，可以理解 OpenGIS 'GEOMETRYCOLLECTION EMPTY' 标准语法和 MySQL 'GEOMETRYCOLLECTION()' 非标准语法。生成空几何集合的另一种方法是调用GeometryCollection()且不传入参数。像ST_AsWKT()这样生成 WKT 值的函数会生成'GEOMETRYCOLLECTION EMPTY'标准语法：Functions such as ST_GeomFromText() that accept WKT geometry collection arguments understand both OpenGIS 'GEOMETRYCOLLECTION EMPTY' standard syntax and MySQL 'GEOMETRYCOLLECTION()' nonstandard syntax. Another way to produce an empty geometry collection is by calling GeometryCollection() with no arguments. Functions such as ST_AsWKT() that produce WKT values produce 'GEOMETRYCOLLECTION EMPTY' standard syntax:

mysql> SET @s1 = ST_GeomFromText('GEOMETRYCOLLECTION()');
mysql> SET @s2 = ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
mysql> SELECT ST_AsWKT(@s1), ST_AsWKT(@s2);
+--------------------------+--------------------------+
| ST_AsWKT(@s1)            | ST_AsWKT(@s2)            |
+--------------------------+--------------------------+
| GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY |
+--------------------------+--------------------------+
mysql> SELECT ST_AsWKT(GeomCollection());
+----------------------------+
| ST_AsWKT(GeomCollection()) |
+----------------------------+
| GEOMETRYCOLLECTION EMPTY   |
+----------------------------+

除非另有说明，本节中的函数对其几何参数的处理方式如下：Unless otherwise specified, functions in this section handle their geometry arguments as follows:

如果任何参数为NULL，则返回值为NULL。If any argument is NULL, the return value is NULL.
如果任何几何参数不是语法上格式正确的几何体，则会发生ER_GIS_INVALID_DATA 错误。If any geometry argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.
如果任何几何参数处于未定义的空间参考系统中，则输出轴的顺序按照几何体中出现的顺序，并发生ER_WARN_SRS_NOT_FOUND_AXIS_ORDER 警告。If any geometry argument is in an undefined spatial reference system, the axes are output in the order they appear in the geometry and an ER_WARN_SRS_NOT_FOUND_AXIS_ORDER warning occurs.
默认情况下，地理坐标（纬度，经度）按照几何参数的空间参考系统指定的顺序进行解释。可以提供一个可选的options参数来覆盖默认的轴顺序。options由用逗号分隔的key=value组成。唯一允许的key值是axis-order，其允许的值为lat-long、long-lat和srid-defined（默认）。By default, geographic coordinates (latitude, longitude) are interpreted as in the order specified by the spatial reference system of geometry arguments. An optional options argument may be given to override the default axis order. options consists of a list of comma-separated key=value. The only permitted key value is axis-order, with permitted values of lat-long, long-lat and srid-defined (the default).
如果options参数为NULL，返回值为NULL。如果options参数无效，将产生错误以说明原因。If the options argument is NULL, the return value is NULL. If the options argument is invalid, an error occurs to indicate why.
否则，返回值不为 NULL。Otherwise, the return value is non-NULL.

这些函数可用于格式转换或坐标交换：These functions are available for format conversions or coordinate swapping:

ST_AsBinary(g [, options]), ST_AsWKB(g [, options])

将内部几何格式的值转换为其 WKB 表示，并返回二进制结果。Converts a value in internal geometry format to its WKB representation and returns the binary result.

函数返回值具有地理坐标（纬度，经度），顺序由适用于几何参数的空间参考系统指定。可以提供一个可选的options参数以覆盖默认的坐标轴顺序。The function return value has geographic coordinates (latitude, longitude) in the order specified by the spatial reference system that applies to the geometry argument. An optional options argument may be given to override the default axis order.

ST_AsBinary() 和 ST_AsWKB() 按本节介绍中描述的方式处理它们的参数。ST_AsBinary() and ST_AsWKB() handle their arguments as described in the introduction to this section.

mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326);
mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)));
+-----------------------------------------+
| ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g))) |
+-----------------------------------------+
| LINESTRING(5 0,10 5,15 10)              |
+-----------------------------------------+
mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')));
+----------------------------------------------------------------+
| ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat'))) |
+----------------------------------------------------------------+
| LINESTRING(0 5,5 10,10 15)                                     |
+----------------------------------------------------------------+
mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long')));
+----------------------------------------------------------------+
| ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long'))) |
+----------------------------------------------------------------+
| LINESTRING(5 0,10 5,15 10)                                     |
+----------------------------------------------------------------+

ST_AsText(g [, options]), ST_AsWKT(g [, options])
将内部几何格式的值转换为其 WKT 表示，并返回字符串结果。Converts a value in internal geometry format to its WKT representation and returns the string result.
函数返回值具有地理坐标（纬度，经度），顺序由适用于几何参数的空间参考系统指定。可以提供一个可选的options参数以覆盖默认的坐标轴顺序。The function return value has geographic coordinates (latitude, longitude) in the order specified by the spatial reference system that applies to the geometry argument. An optional options argument may be given to override the default axis order.
ST_AsText() 和 ST_AsWKT() 按本节介绍处理其参数。ST_AsText() and ST_AsWKT() handle their arguments as described in the introduction to this section.
```
mysql> SET @g = 'LineString(1 1,2 2,3 3)';
mysql> SELECT ST_AsText(ST_GeomFromText(@g));
+--------------------------------+
| ST_AsText(ST_GeomFromText(@g)) |
+--------------------------------+
| LINESTRING(1 1,2 2,3 3)        |
+--------------------------------+
```
MultiPoint 值的输出包括每个点周围的括号。例如：Output for MultiPoint values includes parentheses around each point. For example:
```
mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
+---------------------------------+
| ST_AsText(ST_GeomFromText(@mp)) |
+---------------------------------+
| MULTIPOINT((1 1),(2 2),(3 3))   |
+---------------------------------+
```

ST_SwapXY(g)

接受内部几何格式的参数，交换几何中每个坐标对的 X 和 Y 值，并返回结果。Accepts an argument in internal geometry format, swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

ST_SwapXY() 按本节介绍中说明的方式处理其参数。ST_SwapXY() handles its arguments as described in the introduction to this section.

mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)');
mysql> SELECT ST_AsText(@g);
+----------------------------+
| ST_AsText(@g)              |
+----------------------------+
| LINESTRING(0 5,5 10,10 15) |
+----------------------------+
mysql> SELECT ST_AsText(ST_SwapXY(@g));
+----------------------------+
| ST_AsText(ST_SwapXY(@g))   |
+----------------------------+
| LINESTRING(5 0,10 5,15 10) |
+----------------------------+

首页上一级下一节