MySQL :: MySQL 8.4 参考手册 :: 13.4.3 支持的空间数据格式

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

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

打开官方英文原文 ↗

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

MySQL 8.4 参考手册 / 13 种数据类型 / 13.4 空间数据类型 / 13.4.3 支持的空间数据格式

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

13.4.3 支持的空间数据格式13.4.3 Supported Spatial Data Formats

在查询中用来表示几何对象的两种标准空间数据格式：Two standard spatial data formats are used to represent geometry objects in queries:

广为人知的文本 (WKT) 格式Well-Known Text (WKT) format
广为人知的二进制 (WKB) 格式Well-Known Binary (WKB) format

在内部，MySQL 以一种既不同于 WKT 也不同于 WKB 的格式存储几何值。（内部格式类似 WKB，但前面有 4 个字节用于表示 SRID。）Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. (Internal format is like WKB but with an initial 4 bytes to indicate the SRID.)

有可用的函数用于在不同数据格式之间进行转换；请参阅第 14.16.6 节，“几何格式转换函数”。There are functions available to convert between different data formats; see Section 14.16.6, “Geometry Format Conversion Functions”.

以下各节描述了 MySQL 使用的空间数据格式：The following sections describe the spatial data formats MySQL uses:

著名文本（WKT）格式Well-Known Text (WKT) Format

几何值的著名文本（WKT）表示旨在以 ASCII 形式交换几何数据。OpenGIS 规范提供了 Backus-Naur 文法，规定了书写 WKT 值的正式生成规则（参见第 13.4 节，“空间数据类型”）。The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form. The OpenGIS specification provides a Backus-Naur grammar that specifies the formal production rules for writing WKT values (see Section 13.4, “Spatial Data Types”).

几何对象的 WKT 表示示例：Examples of WKT representations of geometry objects:

一个 Point：A Point:
```
POINT(15 20)
```
点坐标是指定的，没有分隔的逗号。这与 SQL Point() 函数的语法不同，后者要求坐标之间有逗号。请注意使用适合给定空间操作上下文的语法。例如，以下语句都使用 ST_X() 从 Point 对象中提取 X 坐标。第一个直接使用 Point() 函数生成对象。第二个使用 WKT 表示并通过 ST_GeomFromText() 转换为 Point。The point coordinates are specified with no separating comma. This differs from the syntax for the SQL Point() function, which requires a comma between the coordinates. Take care to use the syntax appropriate to the context of a given spatial operation. For example, the following statements both use ST_X() to extract the X-coordinate from a Point object. The first produces the object directly using the Point() function. The second uses a WKT representation converted to a Point with ST_GeomFromText().
```
mysql> SELECT ST_X(Point(15, 20));
+---------------------+
| ST_X(POINT(15, 20)) |
+---------------------+
|                  15 |
+---------------------+

mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
|                                    15 |
+---------------------------------------+
```
一个包含四个点的 LineString：A LineString with four points:
```
LINESTRING(0 0, 10 10, 20 25, 50 60)
```
点坐标对由逗号分隔。The point coordinate pairs are separated by commas.
一个具有一个外环和一个内环的 Polygon：A Polygon with one exterior ring and one interior ring:
```
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
```
一个具有三个 Point 值的 MultiPoint：A MultiPoint with three Point values:
```
MULTIPOINT(0 0, 20 20, 60 60)
```
空间函数，例如 ST_MPointFromText() 和 ST_GeomFromText()，它们接受 MultiPoint 值的 WKT 格式表示，允许值中的单个点被括号包围。例如，以下两个函数调用都是有效的：Spatial functions such as ST_MPointFromText() and ST_GeomFromText() that accept WKT-format representations of MultiPoint values permit individual points within values to be surrounded by parentheses. For example, both of the following function calls are valid:
```
ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')
```
一个具有两个 LineString 值的 MultiLineString：A MultiLineString with two LineString values:
```
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
```
一个具有两个 Polygon 值的 MultiPolygon：A MultiPolygon with two Polygon values:
```
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
```
一个 GeometryCollection，包含两个 Point 值和一个 LineString：A GeometryCollection consisting of two Point values and one LineString:
```
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
```

知名二进制 (WKB) 格式Well-Known Binary (WKB) Format

几何值的知名二进制（WKB）表示用于以二进制流交换几何数据，这些二进制流以包含几何 WKB 信息的 BLOB 值表示。此格式由 OpenGIS 规范定义（见第 13.4 节，“空间数据类型”）。它也在 ISO SQL/MM 第 3 部分：空间标准中定义。The Well-Known Binary (WKB) representation of geometric values is used for exchanging geometry data as binary streams represented by BLOB values containing geometric WKB information. This format is defined by the OpenGIS specification (see Section 13.4, “Spatial Data Types”). It is also defined in the ISO SQL/MM Part 3: Spatial standard.

WKB 使用 1 字节无符号整数、4 字节无符号整数和 8 字节双精度数字（IEEE 754 格式）。一个字节是八位。WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is eight bits.

例如，一个对应于 POINT(1 -1) 的 WKB 值由这个长度为 21 字节的序列组成，每个字节用两个十六进制数字表示：For example, a WKB value that corresponds to POINT(1 -1) consists of this sequence of 21 bytes, each represented by two hexadecimal digits:

0101000000000000000000F03F000000000000F0BF

该序列由下表所示的组件组成。The sequence consists of the components shown in the following table.

表 13.2 WKB 组件示例Table 13.2 WKB Components Example

组件Component	大小Size	价值Value
字节顺序Byte order	1 字节1 byte	`01`
WKB 类型WKB type	4 字节4 bytes	`01000000`
X 坐标X coordinate	8 字节8 bytes	`000000000000F03F`
Y 坐标Y coordinate	8 字节8 bytes	`000000000000F0BF`

组件表示如下：Component representation is as follows:

字节顺序指示符为 1 或 0，用于表示小端或大端存储。小端和大端字节顺序也分别被称为网络表示（NDR）和外部表示（XDR）。The byte order indicator is either 1 or 0 to signify little-endian or big-endian storage. The little-endian and big-endian byte orders are also known as Network Data Representation (NDR) and External Data Representation (XDR), respectively.
WKB 类型是用于表示几何类型的代码。MySQL 使用 1 到 7 的值来表示 Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon 以及 GeometryCollection。The WKB type is a code that indicates the geometry type. MySQL uses values from 1 through 7 to indicate Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
Point 值具有 X 和 Y 坐标，每个坐标都以双精度值表示。A Point value has X and Y coordinates, each represented as a double-precision value.

更复杂几何形状的 WKB 值具有更复杂的数据结构，详见 OpenGIS 规范。WKB values for more complex geometry values have more complex data structures, as detailed in the OpenGIS specification.

内部几何存储格式Internal Geometry Storage Format

MySQL 使用 4 个字节来指示 SRID，然后跟随值的 WKB 表示形式来存储几何值。有关 WKB 格式的描述，请参见知名二进制 (WKB) 格式。MySQL stores geometry values using 4 bytes to indicate the SRID followed by the WKB representation of the value. For a description of WKB format, see Well-Known Binary (WKB) Format.

对于 WKB 部分，适用以下 MySQL 特定的考虑因素：For the WKB part, these MySQL-specific considerations apply:

字节序指示字节为 1，因为 MySQL 将几何数据存储为小端值。The byte-order indicator byte is 1 because MySQL stores geometries as little-endian values.
MySQL 支持的几何类型包括 Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon 和 GeometryCollection。不支持其他几何类型。MySQL supports geometry types of Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. Other geometry types are not supported.
只有 GeometryCollection 可以为空。这样的值会以 0 个元素存储。Only GeometryCollection can be empty. Such a value is stored with 0 elements.
多边形环可以顺时针和逆时针指定。MySQL 在读取数据时会自动翻转这些环。Polygon rings can be specified both clockwise and counterclockwise. MySQL flips the rings automatically when reading data.

笛卡尔坐标以空间参考系统的长度单位存储，X 值存储在 X 坐标中，Y 值存储在 Y 坐标中。轴方向由空间参考系统指定。Cartesian coordinates are stored in the length unit of the spatial reference system, with X values in the X coordinates and Y values in the Y coordinates. Axis directions are those specified by the spatial reference system.

地理坐标以空间参考系统的角度单位存储，经度存储在 X 坐标中，纬度存储在 Y 坐标中。轴方向和子午线由空间参考系统指定。Geographic coordinates are stored in the angle unit of the spatial reference system, with longitudes in the X coordinates and latitudes in the Y coordinates. Axis directions and the meridian are those specified by the spatial reference system.

LENGTH() 函数返回存储值所需的字节空间。例如：The LENGTH() function returns the space in bytes required for value storage. Example:

mysql> SET @g = ST_GeomFromText('POINT(1 -1)');
mysql> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
|         25 |
+------------+
mysql> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g)                                            |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+

值的长度为 25 字节，由这些组成部分构成（从十六进制值可以看到）：The value length is 25 bytes, made up of these components (as can be seen from the hexadecimal value):

4 字节用于整数 SRID（0）4 bytes for integer SRID (0)
1 字节用于整数字节序（1 = 小端序）1 byte for integer byte order (1 = little-endian)
4 字节用于整数类型信息（1 = Point）4 bytes for integer type information (1 = Point)
8 字节用于双精度 X 坐标 (1)8 bytes for double-precision X coordinate (1)
8 字节用于双精度 Y 坐标 (−1)8 bytes for double-precision Y coordinate (−1)

首页上一级下一节