MySQL 数据类型：空间数据类型 - 版本历史

2023年3月31日 (五) 15:06 Eijux

2023-03-31T15:06:21Z

←上一版本		2023年3月31日 (五) 23:06的版本
第1行：		第1行：
	[[category:~~MySQL~~]]		[[category:MySQL文档]]

	== 关于空间数据类型 ==		== 关于空间数据类型 ==

Eijux：/* 使用空间索引 */

2021-03-26T17:00:39Z

使用空间索引

←上一版本		2021年3月27日 (六) 01:00的版本
第545行：		第545行：

	== 使用空间索引 ==		== 使用空间索引 ==
			优化程序调查在使用 WHERE 子句中使用诸如“'''MBRContains()'''”或“'''MBRWithin()'''”之类的功能的查询的搜索中是否涉及可用的空间索引。


			以下查询查找给定矩形中的所有对象：
			<syntaxhighlight lang="xml">
			mysql> SET @poly =
			-> 'Polygon((30000 15000,
			31000 15000,
			31000 16000,
			30000 16000,
			30000 15000))';
			mysql> SELECT fid,ST_AsText(g) FROM geom WHERE
			-> MBRContains(ST_GeomFromText(@poly),g);
			+-----+---------------------------------------------------------------+
			\| fid \| ST_AsText(g) \|
			+-----+---------------------------------------------------------------+
			\| 21 \| LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... \|
			\| 22 \| LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... \|
			\| 23 \| LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... \|
			\| 24 \| LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... \|
			\| 25 \| LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... \|
			\| 26 \| LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... \|
			\| 249 \| LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... \|
			\| 1 \| LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... \|
			\| 2 \| LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... \|
			\| 3 \| LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... \|
			\| 4 \| LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... \|
			\| 5 \| LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... \|
			\| 6 \| LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... \|
			\| 7 \| LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... \|
			\| 10 \| LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... \|
			\| 11 \| LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... \|
			\| 13 \| LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... \|
			\| 154 \| LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... \|
			\| 155 \| LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... \|
			\| 157 \| LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... \|
			+-----+---------------------------------------------------------------+
			20 rows in set (0.00 sec)
			</syntaxhighlight>
			使用'''EXPLAIN'''检查此查询的执行方式：
			<syntaxhighlight lang="xml">
			mysql> SET @poly =
			-> 'Polygon((30000 15000,
			31000 15000,
			31000 16000,
			30000 16000,
			30000 15000))';
			mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM geom WHERE
			-> MBRContains(ST_GeomFromText(@poly),g)\G
			************************* 1. row *************************
			id: 1
			select_type: SIMPLE
			table: geom
			type: range
			possible_keys: g
			key: g
			key_len: 32
			ref: NULL
			rows: 50
			Extra: Using where
			1 row in set (0.00 sec)
			</syntaxhighlight>
			检查没有空间索引会发生什么：
			<syntaxhighlight lang="xml">
			mysql> SET @poly =
			-> 'Polygon((30000 15000,
			31000 15000,
			31000 16000,
			30000 16000,
			30000 15000))';
			mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM g IGNORE INDEX (g) WHERE
			-> MBRContains(ST_GeomFromText(@poly),g)\G
			************************* 1. row *************************
			id: 1
			select_type: SIMPLE
			table: geom
			type: ALL
			possible_keys: NULL
			key: NULL
			key_len: NULL
			ref: NULL
			rows: 32376
			Extra: Using where
			1 row in set (0.00 sec)
			</syntaxhighlight>


			在不使用空间索引的情况下执行“SELECT”语句会产生相同的结果，但是会使执行时间从 0.00 秒增加到 0.46 秒：
			<syntaxhighlight lang="xml">
			mysql> SET @poly =
			-> 'Polygon((30000 15000,
			31000 15000,
			31000 16000,
			30000 16000,
			30000 15000))';
			mysql> SELECT fid,ST_AsText(g) FROM geom IGNORE INDEX (g) WHERE
			-> MBRContains(ST_GeomFromText(@poly),g);
			+-----+---------------------------------------------------------------+
			\| fid \| ST_AsText(g) \|
			+-----+---------------------------------------------------------------+
			\| 1 \| LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... \|
			\| 2 \| LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... \|
			\| 3 \| LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... \|
			\| 4 \| LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... \|
			\| 5 \| LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... \|
			\| 6 \| LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... \|
			\| 7 \| LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... \|
			\| 10 \| LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... \|
			\| 11 \| LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... \|
			\| 13 \| LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... \|
			\| 21 \| LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... \|
			\| 22 \| LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... \|
			\| 23 \| LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... \|
			\| 24 \| LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... \|
			\| 25 \| LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... \|
			\| 26 \| LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... \|
			\| 154 \| LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... \|
			\| 155 \| LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... \|
			\| 157 \| LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... \|
			\| 249 \| LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... \|
			+-----+---------------------------------------------------------------+
			20 rows in set (0.46 sec)
			</syntaxhighlight>

Eijux：/* 创建空间索引 */

2021-03-26T16:55:29Z

创建空间索引

←上一版本		2021年3月27日 (六) 00:55的版本
第487行：		第487行：

	== 创建空间索引 ==		== 创建空间索引 ==
			对于 InnoDB 和 MyISAM 数据表，MySQL 可以使用类似于'''创建常规索引的语法创建空间索引'''，但是'''使用SPATIAL关键字'''。'''空间索引中的列必须声明为NOT NULL'''。
			（“SPATIAL”：空间的）


			示例，创建空间索引：
			# 使用“CREATE TABLE”：
			#: <syntaxhighlight lang="xml">
			CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
			</syntaxhighlight>
			# 使用“ALTER TABLE”：
			#: <syntaxhighlight lang="xml">
			CREATE TABLE geom (g GEOMETRY NOT NULL);
			ALTER TABLE geom ADD SPATIAL INDEX(g);
			</syntaxhighlight>
			# 使用“CREATE INDEX”：
			#: <syntaxhighlight lang="xml">
			CREATE TABLE geom (g GEOMETRY NOT NULL);
			CREATE SPATIAL INDEX g ON geom (g);
			</syntaxhighlight>
			* SPATIAL INDEX 创建 R 树索引。对于支持空间列的非空间索引的存储引擎，该引擎将创建 B 树索引。空间值的 B 树索引可用于精确值查找，但不适用于范围扫描。


			要删除空间索引，请使用“ALTER TABLE”或“DROP INDEX”：
			<syntaxhighlight lang="xml">
			With ALTER TABLE:
			ALTER TABLE geom DROP INDEX g;
			With DROP INDEX:
			DROP INDEX g ON geom;
			</syntaxhighlight>


			示例：假设表 geom 包含 32,000 多个几何，这些几何存储在 GEOMETRY 类型的 g 列中。该表还具有“AUTO_INCREMENT”列“fid”，用于存储对象 ID 值。
			<syntaxhighlight lang="xml">
			mysql> DESCRIBE geom;
			+-------+----------+------+-----+---------+----------------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+-------+----------+------+-----+---------+----------------+
			\| fid \| int(11) \| \| PRI \| NULL \| auto_increment \|
			\| g \| geometry \| \| \| \| \|
			+-------+----------+------+-----+---------+----------------+
			2 rows in set (0.00 sec)

			mysql> SELECT COUNT(*) FROM geom;
			+----------+
			\| count(*) \|
			+----------+
			\| 32376 \|
			+----------+
			1 row in set (0.00 sec)
			</syntaxhighlight>
			要在列 g 上添加空间索引，请使用以下语句：
			<syntaxhighlight lang="xml">
			mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
			Query OK, 32376 rows affected (4.05 sec)
			Records: 32376 Duplicates: 0 Warnings: 0
			</syntaxhighlight>

	== 使用空间索引 ==		== 使用空间索引 ==

Eijux：/* 优化空间分析 */

2021-03-26T16:48:02Z

优化空间分析

←上一版本		2021年3月27日 (六) 00:48的版本
第474行：		第474行：
	</syntaxhighlight>		</syntaxhighlight>

	== 优化空间分析 ==		== 优化空间分析【？？？】==
			对于 MyISAM 和 InnoDB 的表，可以使用'''SPATIAL 索引'''优化包含空间数据的列中的搜索操作。最典型的操作是：
			# 点查询，搜索包含给定点的所有对象
			# 区域查询搜索与给定区域重叠的所有对象


			MySQL 对空间列上的 SPATIAL 索引使用“具有二次分裂的 R 树”【？？？】。使用几何的最小边界矩形('''MBR''')构建SPATIAL索引。对于大多数几何图形，MBR 是围绕几何图形的最小矩形。对于水平或垂直线串，MBR 是退化为线串的矩形。对于一个点，MBR 是退化为该点的矩形。

			* 也可以在空间列上创建普通索引。在非SPATIAL索引中，必须为除 POINT 列之外的任何空间列声明前缀。

			* MyISAM 和 InnoDB 支持 SPATIAL 和非SPATIAL 索引。

	== 创建空间索引 ==		== 创建空间索引 ==

	== 使用空间索引 ==		== 使用空间索引 ==

Eijux：/* 提取空间数据 */

2021-03-26T16:44:34Z

提取空间数据

←上一版本		2021年3月27日 (六) 00:44的版本
第457行：		第457行：

	== 提取空间数据 ==		== 提取空间数据 ==
			可以以内部格式获取存储在 table 中的几何值。您也可以将它们转换为 WKT 或 WKB 格式。
			# 以内部格式获取空间数据：
			#* （使用内部格式获取几何值在 '''table 到 table 的传输'''中非常有用）
			#: <syntaxhighlight lang="xml">
			CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;
			</syntaxhighlight>
			# 以 WKT 格式获取空间数据：
			#* '''ST_AsText()'''函数将几何图形从内部格式转换为 WKT 字符串。
			#: <syntaxhighlight lang="xml">
			SELECT ST_AsText(g) FROM geom;
			</syntaxhighlight>
			# 以 WKB 格式获取空间数据：
			#* '''ST_AsBinary()'''函数将几何图形从内部格式转换为包含 WKB 值的BLOB。
			#: <syntaxhighlight lang="xml">
			SELECT ST_AsBinary(g) FROM geom;
			</syntaxhighlight>

	== 优化空间分析 ==		== 优化空间分析 ==

Eijux：/* 填充空间列 */

2021-03-26T16:42:05Z

填充空间列

←上一版本		2021年3月27日 (六) 00:42的版本
第395行：		第395行：

	== 填充空间列 ==		== 填充空间列 ==
			创建空间列之后，可以使用空间数据填充它们。

			* 值应以内部几何格式存储，但是可以将其从“已知文本”(WKT)或“已知二进制”(WKB)格式转换为该格式。


			示例：通过将 WKT 值转换为内部几何格式将几何值插入 table 中：【使用“'''ST_GeomFromText()'''”创建几何值】
			# 直接在 INSERT 语句中执行转换：
			#: <syntaxhighlight lang="xml">
			INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

			SET @g = 'POINT(1 1)';
			INSERT INTO geom VALUES (ST_GeomFromText(@g));
			</syntaxhighlight>
			# 在INSERT之前执行转换：
			#: <syntaxhighlight lang="xml">
			SET @g = ST_GeomFromText('POINT(1 1)');
			INSERT INTO geom VALUES (@g);
			</syntaxhighlight>
			# 以下示例将更复杂的几何插入 table 中：
			#: <syntaxhighlight lang="xml">
			SET @g = 'LINESTRING(0 0,1 1,2 2)';
			INSERT INTO geom VALUES (ST_GeomFromText(@g));

			SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
			INSERT INTO geom VALUES (ST_GeomFromText(@g));

			SET @g = 'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
			INSERT INTO geom VALUES (ST_GeomFromText(@g));
			</syntaxhighlight>


			前面的示例使用“ST_GeomFromText()”创建几何值。您还可以使用特定于类型的功能：【如：“'''ST_PointFromText()'''”、“'''ST_LineStringFromText()'''”、“'''ST_PolygonFromText()'''”、“'''ST_GeomCollFromText()'''”等】
			<syntaxhighlight lang="xml">
			SET @g = 'POINT(1 1)';
			INSERT INTO geom VALUES (ST_PointFromText(@g));

			SET @g = 'LINESTRING(0 0,1 1,2 2)';
			INSERT INTO geom VALUES (ST_LineStringFromText(@g));

			SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
			INSERT INTO geom VALUES (ST_PolygonFromText(@g));

			SET @g =
			'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
			INSERT INTO geom VALUES (ST_GeomCollFromText(@g));
			</syntaxhighlight>


			想要使用几何值的 WKB 表示形式的 Client 端应用程序负责将查询中正确形成的 WKB 发送到服务器。有几种方法可以满足此要求。例如：【使用“'''ST_GeomFromWKB()'''”】
			# 使用十六进制 Literals 语法插入“POINT(1 1)”值：
			#: <syntaxhighlight lang="xml">
			INSERT INTO geom VALUES
			(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));
			</syntaxhighlight>
			# ODBC 应用程序可以发送 WKB 表示形式，并使用'''BLOB'''类型的参数将其绑定到占位符：
			#: <syntaxhighlight lang="xml">
			INSERT INTO geom VALUES (ST_GeomFromWKB(?))
			</syntaxhighlight>
			#* 其他编程接口可能支持类似的占位符机制。
			#* 在 C 程序中，可以使用“mysql_real_escape_string_quote()”转义二进制值，并将结果包含在发送到服务器的查询字符串中。【？？？】

	== 提取空间数据 ==		== 提取空间数据 ==

Eijux：/* 创建空间列 */

2021-03-26T16:33:29Z

创建空间列

←上一版本		2021年3月27日 (六) 00:33的版本
第381行：		第381行：

	== 创建空间列 ==		== 创建空间列 ==
			MySQL 提供了一种用于为几何类型创建空间列的标准方法，例如，使用“CREATE TABLE”或“ALTER TABLE”。
			* MyISAM，InnoDB，NDB 和 ARCHIVE 的 table 支持空间列。

			# 使用'''CREATE TABLE'''语句创建一个带有空间列的 table：
			#: <syntaxhighlight lang="xml">
			CREATE TABLE geom (g GEOMETRY);
			</syntaxhighlight>
			# 使用'''ALTER TABLE'''语句向现有 table 中添加或删除空间列：
			#: <syntaxhighlight lang="xml">
			ALTER TABLE geom ADD pt POINT;
			ALTER TABLE geom DROP pt;
			</syntaxhighlight>

	== 填充空间列 ==		== 填充空间列 ==

Eijux：/* 几何格式正确性和有效性 */

2021-03-26T16:30:56Z

几何格式正确性和有效性

←上一版本		2021年3月27日 (六) 00:30的版本
第363行：		第363行：

	== 几何格式正确性和有效性 ==		== 几何格式正确性和有效性 ==
			对于几何值，MySQL 区分语法结构正确和几何有效的概念。

			语法结构正确：
			# 线串至少有两点
			# 多边形至少有一个环
			# 多边形环是闭合的(第一个点和最后一个点相同)
			# 多边形环至少具有 4 个点(最小多边形是第一个点和最后一个点相同的三角形)
			# 集合不为空(GeometryCollection除外)

			几何有效：
			# 多边形不是自相交的
			# 多边形内圈位于外圈内
			# 多边形不具有重叠的多边形

			* 如果几何结构的语法不正确，则空间功能将失败。解析 WKT 或 WKB 值的空间导入函数会因尝试创建语法上格式不正确的几何而产生错误。还检查语法格式正确性，以尝试将几何存储到 table 中。
			* 允许插入，选择和更新几何无效的几何，但是它们在语法上必须格式正确。由于计算量大，'''MySQL 不会明确检查几何有效性'''。空间计算可能会检测到无效几何的某些情况并引发错误，但它们也可能会返回未定义的结果而不会检测到无效性。需要几何有效几何形状的应用程序应使用'''“ST_IsValid()”'''功能对其进行检查。

	== 创建空间列 ==		== 创建空间列 ==

Eijux：/* 支持的空间数据格式 */

2021-03-26T16:13:27Z

支持的空间数据格式

显示更改

2021年3月26日 (五) 15:47 Eijux

2021-03-26T15:47:46Z

显示更改

MySQL 数据类型：空间数据类型 - 版本历史

2023年3月31日 (五) 15:06 Eijux

Eijux：​/* 使用空间索引 */

Eijux：​/* 创建空间索引 */

Eijux：​/* 优化空间分析 */

Eijux：​/* 提取空间数据 */

Eijux：​/* 填充空间列 */

Eijux：​/* 创建空间列 */

Eijux：​/* 几何格式正确性和有效性 */

Eijux：​/* 支持的空间数据格式 */