MySQL中的空间索引技术深度解析：利用Spatial Key优化地理空间数据查询

Eucalyptus2024-08-06

简介

MySQL中的空间索引是一种针对空间数据类型的特殊索引,主要用于优化地理空间数据的查询性能。这类索引主要应用于存储几何对象如点、线、面、多边形等数据类型的列上,比如POINT, LINESTRING, POLYGON, 和 GEOMETRY等。空间索引利用R-Tree或其变种等数据结构来组织空间数据,使得数据库能高效地执行包含空间运算的SQL语句,如查找某个区域内的所有点、计算两个几何对象之间的距离、判断一个对象是否包含在另一个对象中等空间查询操作。通过使用空间索引,MySQL能够大幅度减少在大数据集上进行复杂空间分析和位置相关查询所需的时间,特别适用于地理信息系统 GIS 、位置服务、地图应用等领域。

空间数据类型

GEOMETRY 抽象基类

虽然我们不直接创建GEOMETRY类型的列作为空间索引,但它作为所有空间数据类型的基类,理解其概念是基础。比如,在设计数据库时,可以声明一个表的某一列为GEOMETRY类型,但实际上会具体到POINT、LINESTRING等子类型。

POINT

POINT:用于存储点的坐标。

1	POINT(15 20)

LINESTRING

用于存储线段,可以由一个或多个点组成。

1	LINESTRING(0 0, 10 10, 20 25, 50 60)

POLYGON

用于存储多边形,即闭合的线段,带一个外环, 一个内环。

1	POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

MULTIPOINT

用于存储多个点的集合。

1	MULTIPOINT(0 0, 20 20, 60 60)

MULTILINESTRING

用于存储多条线段的集合,多个LineString值。

1	MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

MULTIPOLYGON

用于存储多个多边形的集合,多个 Polygon值。

1	MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

GEOMETRYCOLLECTION

用于存储多种类型的空间对象的集合,比如由两个组成 Point值和一个 LineString

1	GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

总结

官方文档:

MySQL :: MySQL 8.0 Reference Manual :: 13.4.3 Supported Spatial Data Formats

函数

`ST_GeomFromText()`

ST_GeomFromText() 是一个在许多数据库系统中用于处理地理空间数据的功能函数,尤其是在PostGIS PostgreSQL的扩展中广泛使用。这个函数的主要目的是将一个表示几何对象的Well-Known Text (WKT)格式的字符串转换为数据库内部的二进制几何类型,以便于进行空间分析和查询。

基本语法:

1	ST_GeomFromText(text WKT[, integer srid])

`ST_X`

这个函数用于从一个点几何对象中提取经度坐标 x坐标 ,即点在水平方向的位置。在地理坐标系统中,这通常指的是经度值。

基本语法:

1	ST_X(point)

`ST_Y`

相对地,这个函数用于从同一个点几何对象中提取纬度坐标 y坐标 ,即点在垂直方向的位置。在地理坐标系统中,这对应于纬度值。

基本语法:

1	ST_Y(point)

ST_AsText

将点转换为Well-Known Text (WKT)格式的字符串表示,这个函数在上面提到的几种数据类型中是通用的

基本语法:

1	ST_AsText(point)

空间函数

ST_Contains: 判断一个几何对象是否包含另一个几何对象。
ST_Distance: 计算两个几何对象之间的距离。
ST_Buffer: 根据给定的距离,在几何对象周围创建一个缓冲区。
ST_Intersection: 计算两个几何对象的交集。
ST_Union: 计算多个几何对象的并集。

总结

上面介绍的函数是常用的,下面的这些函数针对不同的数据类型用到的比较少

ST_StartPoint(line):返回线段的起始点。
ST_EndPoint(line):返回线段的终点。
ST_NumPoints(line):返回线段中的点数。
ST_PointN(line, n):返回线段中的第n个点。
ST_Area(poly):计算多边形的面积。
ST_Centroid(poly):计算多边形的质心。
ST_Perimeter(poly):计算多边形的周长。
ST_NumGeometries(multipoint):返回多点集合中的点数。
ST_GeometryN(multipoint, n):返回多点集合中的第n个几何对象。
类似于MULTIPOINT,使用ST_NumGeometries()、ST_GeometryN()等函数来访问和操作其中的线段。
同样使用ST_NumGeometries()、ST_GeometryN()等函数来操作多边形集合中的每个多边形。
ST_NumGeometries(collection):返回集合中的几何对象数量。
ST_GeometryN(collection, n):返回集合中的第n个几何对象。

坐标系

WGS 84坐标系 EPSG:4326 默认/常用

**经度 Longitude **:从本初子午线通过英国格林尼治的经线向东或向西测量的角度,范围从-180°到 180°。
**纬度 Latitude **:从赤道测量到南北极点的角度,范围从-90° 南极到 90° 北极。

官方文档:

MySQL :: MySQL 8.0 Reference Manual :: 13.4.5 Spatial Reference System Support

Web Mercator Projection (EPSG:3857)

这是网络地图服务中广泛使用的一种投影方式,如Google Maps、OpenStreetMap等。它基于墨卡托投影,适用于快速显示和可视化,但高纬度地区会有较大形变。

China Geodetic Coordinate System 2000 (CGCS2000) / EPSG:4490

中国自有的大地坐标系统,类似于WGS 84,但有自己定义的参考框架,适用于中国地区的高精度测量和制图。

UTM (Universal Transverse Mercator) 系统

UTM是一种全球分带的墨卡托投影坐标系统,将地球分为60个6度带,每个带都有自己的坐标系,常用于军事、航空、地形图等领域,便于局部地区的高精度测量和制图。EPSG代码根据具体的带号和北/南半球有所不同。

State Plane Coordinate System (SPCS)

美国的一种坐标参考系统,分为多个区域,旨在提供美国境内更精确的测量和制图,包括两种投影类型:横轴墨卡托和兰伯特锥形。每个州或区域有特定的EPSG代码。

British National Grid (BNG) / EPSG:27700

英国使用的一种坐标系,基于Transverse Mercator投影,适用于英国及周边岛屿的测量和地图制作。

European Terrestrial Reference System 1989 (ETRS89) / EPSG:4258

为欧洲设计的一种地心坐标参考系统,与WGS 84非常接近但在欧洲大陆内部提供了更高的长期稳定性,适用于欧洲的地理信息系统和测量工作。

Pulkovo 1942 / EPSG:4284

旧苏联及一些东欧国家曾经使用的坐标系,基于普尔科沃天文台的地理位置。

空间索引

创建一个有空间列的表

CREATE TABLE places (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
location geometry NOT NULL SRID 4326
PRIMARY KEY ( id )
) ENGINE InnoDB AUTO_INCREMENT 8 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;

添加空间索引

1	alter table places add spatial spatial_index(location);

看到这里可能有人会问了,为什么不在创建表的时候就创建号空间索引,要在后面单独添加那,这是因为空间索引不能和其他的索引在创建表的同时创建,因为使用的数据结构不一样

给表中插入不同子数据类型的数据

INSERT INTO places (name, location) VALUES ('Place A', ST_GeomFromText('POINT(1 1) ', 4326));
INSERT INTO places (name, location) VALUES ('Place B', ST_GeomFromText('LINESTRING(1 1,2 2,3 3))', 4326));
INSERT INTO places (name, location) VALUES ('Place C', ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5)) ', 4326));
INSERT INTO places (name, location) VALUES ('Place D', ST_GeomFromText('MULTIPOINT((0 0),(20 20),(60 60)) ', 4326));
INSERT INTO places (name, location) VALUES ('Place E', ST_GeomFromText('MULTILINESTRING((10 10,20 20),(15 15,30 15)) , 4326));
INSERT INTO places (name, location) VALUES ('Place F', ST_GeomFromText('GEOMETRYCOLLECTION(POINT(10 10),POINT(30 30),LINESTRING(15 15,20 20))', 4326));
INSERT INTO places (name, location) VALUES ('Beijing', ST_GeomFromText('POINT(39.9042 116.4074) ', 4326));