使用MySQL进行地理坐标计算

拼搏现实的明天。 2022-12-16 06:07 691阅读 0赞

随着移动设备的不断普及，APP程序已经成为各类型应用系统的关键流量入口，今年9月份发布的中国互联网络发展状况报告显示，我国手机网民规模已经达到9.32亿，占比99.2%。移动端程序设计已经是所有产品经理绕不开的话题。

相比以往PC端程序，移动APP交互方式除传统用户输入外，还增加了额外的维度信息：地理坐标。例如，驾车时地图实时导航到达目的地的最佳行进路线；点外卖时系统自动根据用户位置搜索附近商家并进行推荐；办公打卡考勤时识别员工是否在执勤点附近。对于开发人员，未来或多或少会接触到地理空间计算场景，在这里简单介绍下如何使用MySQL进行地理空间计算。

## 一、GIS介绍 ##

在计算机领域，对地理信息的处理离不开采集、管理、模拟、分析、展示等一系列步骤，将其转换为计算机能够识别并保存的数据，而实现这一套功能的系统称之为GIS（地理信息系统，Geographic Information System）。

由于GIS系统的专业性，使用环境相对封闭，一直没有形成统一的规范。直到1996年，美国成立了开放地理信息联合会OGC，并提出了开放式地理数据互操作规范OpenGIS。

OpenGIS主要解决了以下几个问题：

1.  互通性：可以在不同GIS系统间互通数据，相互交换信息。
2.  兼容性：数据和硬件弱耦合，让用户可以自由选择服务支持方以及系统架构，如分布式平台和数据库管理软件等。
3.  可扩展性：提供对未来新地理数据类型和软件的扩展支持。

## 二、MySQL GIS功能 ##

目前GIS有非常多成熟产品，如Arcgis公司提供全套商业解决方案；开源GIS服务器GeoServer和MapServer；空间数据库PostgreSQL、PostGIS等。

MySQL自4.1版本就引入了对OpenGIS的支持，不过此时只有ISAM存储引擎能够使用GIS特性，支持的功能也相对有限。直到5.7版本开始，InnoDB引擎也加入了对地理数据的支持。对使用MySQL作为关系型数据库的应用来说，此时用最小的成本就可以实现对空间数据的处理能力。

MySQL对GIS的支持包含三个部分： 空间数据类型、空间索引、空间函数。

### 2.1 空间数据类型 ###

OpenGIS定义的几何模型中，MySQL目前（至8.0版本）只支持其中的8种，分别为

1.  GEOMETRY：几何对象的父类。可以为POINT、LINESTRING或POLYGON之一。
2.  POINT：点。包含(X, Y)坐标信息。
3.  LINESTRING：折线。由一系列点对象组成的线段，点之间使用直线连接。如果首尾坐标相连则形成环。
4.  POLYGON：平面。由一系列坐标构成的封闭多边形平面，内部可以挖空型成带孔的平面。
5.  MULTIPOINT：点集合。
6.  MULTILINESTRING：折线集合。
7.  MULTIPOLYGON：平面集合。
8.  GEOMETRYCOLLECTION：几何对象几何。

除去重复的部分，MySQL支持的基础空间对象主要为以下三类：  
![三类基础图形对象][20201018165936675.png_pic_center]可以给上述数据对象设置坐标系属性SRID，不过需要注意的是在mysql 5.7版本里所有空间函数只支持直角坐标系计算，此时SRID设置无效。另外5.7中X、Y坐标长度无限制，可以超过180度和90度。在8.0版本中SRID功能才正式启用。

GIS系统中，几何图形数据通常以WKT（文本格式）或WKB（二级制格式）的格式进行存储，不过为了存储图形的坐标系信息，MySQL在WKB格式的基础上额外添加了4字节用来存储SRID，作为内部存储空间数据的结构。

以下是各类图形的WKT表示法：

-- 点
    POINT(15 20)
    
    -- 折线
    LINESTRING(0 0, 10 10, 20 25, 50 60)
    
    -- 平面
    POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
    
    -- 点集合
    MULTIPOINT(0 0, 20 20, 60 60)
    
    -- 折线集合
    MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
    
    -- 平面集合
    MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
    
    -- 几何图形几何，支持混合不同几何类型
    GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

2.1.1 DDL操作

MySQL将空间数据当字段类型进行管理，因此可以像普通字段一样在已有表中追加字段或创建新表，如下：

-- 创建带几何图形的表
    CREATE TABLE geom (g GEOMETRY);
    
    -- 已有数据表中新增或删除空间数据类型字段
    ALTER TABLE geom ADD pt POINT;
    ALTER TABLE geom DROP pt;

2.1.2 DML操作

为能够正确管理数据库中的空间数据，在插入或修改记录时，需要使用MySQL能够识别的格式，即上面说到的WKT或WKB格式。

*  WKT格式

-- 插入数据
    INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));
    INSERT INTO geom VALUES (ST_PointFromText('POINT(1 1)'));
    INSERT INTO geom VALUES (ST_LineStringFromText('LINESTRING(0 0,1 1,2 2)'));
    INSERT INTO geom VALUES (ST_PolygonFromText('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_GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'));

*  WKB格式

INSERT INTO geom VALUES (ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

由于MySQL使用类似WKB的二进制方式存储空间数据，因此select时通常需要加入转换方法以识别内容。

-- WKT格式显示
    SELECT ST_AsText(g) FROM geom;
    -- 查询结果：POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))

另外如果需要导出二进制数据给MySQL以外的空间数据库使用，导出时也需要加上转换方法。

-- 转为标准WKB格式
    SELECT ST_AsBinary(g) FROM geom;

### 2.3 空间索引 ###

对于传统数据类型，MySQL会创建B+树索引进行快速检索。不过对空间数据，检索依据不再是单纯的比较大小，更多的是几何图形间的位置关系，例如两线段是否相交、点是否在平面内、平面A是否完全包含图形B等等。对此，MySQL新引入了基于R树的空间索引的数据结构。

R树是B树结构在二维空间的扩展，它将所有空间数据对象通过最小包围矩形进行表示（MBR），如下：  
![MBR示例][MBR]对于点图形，MBR退化为一个点；水平或垂直线中，MBR退化为直线。

检索时，R树索引仅判断数据库中哪些记录的最小包围矩形与查询条件对应的最小包围矩形存在重叠，即返回。

如下，折线A与折线B的MBR存在重叠，当以折线A作为条件查询相较图形时，将得到折线B。平面C因MBR不与折线A重叠，因此不会命中。  
![查询示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ3MDIyNzc2_size_16_color_FFFFFF_t_70_pic_center]  
创建空间索引的方式和普通索引一样，只需要指定SPATIAL关键字，不过要求目标字段必须为NOT NULL类型。

-- 建表时指定索引
    CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
    
    -- 在g列上增加空间索引
    ALTER TABLE geom ADD SPATIAL INDEX(g);
    
    -- 删除索引
    ALTER TABLE geom DROP INDEX g;

## 2.4 空间函数 ##

上面提到MySQL使用R树作为空间索引结构，但需要注意的是，如下场景R树索引同样会视为命中，因为R树索引只判断MBR是否重叠，不考虑MBR内部元素是否真正相交。  
![特殊例子][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ3MDIyNzc2_size_16_color_FFFFFF_t_70_pic_center 1]  
R树索引的这种特性，保证了它在处理海量空间数据时有非常快的效率，不过存在的问题是查询结果不保证准确。对此，MySQL提供了两套API方法，分别对应于精确查询、以及仅MBR重叠的快速查询两类场景。

2.4.1 精确查询方法

精确匹配的方法命名统一以ST\_开头，查询时依照对象形状精确匹配，方法列表如下：

<table> 
 <thead> 
  <tr> 
   <th>方法名</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ST_Contains</td> 
   <td>判断一个图形是否完全包含另一个图形</td> 
  </tr> 
  <tr> 
   <td>ST_Crosses</td> 
   <td>判断两个图形是否存在交叉，但相互不完全包含</td> 
  </tr> 
  <tr> 
   <td>ST_Disjoint</td> 
   <td>判断两个图形是否不相交</td> 
  </tr> 
  <tr> 
   <td>ST_Distance</td> 
   <td>计算两个图形间的最短距离</td> 
  </tr> 
  <tr> 
   <td>ST_Equals</td> 
   <td>判断两个图形是否完全相同</td> 
  </tr> 
  <tr> 
   <td>ST_Intersects</td> 
   <td>判断两个图形是否相交</td> 
  </tr> 
  <tr> 
   <td>ST_Overlaps</td> 
   <td>判断两个图形是否存在重叠</td> 
  </tr> 
  <tr> 
   <td>ST_Touches</td> 
   <td>判断两个图形是否仅边界相交</td> 
  </tr> 
  <tr> 
   <td>ST_Within</td> 
   <td>判断一个图形是否完全在另一个图形内部</td> 
  </tr> 
 </tbody> 
</table>

ST\_Crosses与ST\_Intersects在概念上较为接近，但不完全一样，其准确定义为：如果交集形成了一个维度比两个源几何的最大维度小一的几何，并且交集位于两个源几何内部，则 ST\_Crosses成立。  
如下ST\_Crosses为False，但ST\_Intersects为True。  
![Cross与Intersect的区别][Cross_Intersect]  
2.4.2 MBR匹配方法

快速查询的方法命名统一以MBR开头，功能与ST方法类似。

<table> 
 <thead> 
  <tr> 
   <th>方法名</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MBRContains</td> 
   <td>判断一个图形MBR是否完全包含另一个图形MBR</td> 
  </tr> 
  <tr> 
   <td>MBRCoveredBy</td> 
   <td>判断判断一个图形的MBR是否被另一个图形MBR所覆盖</td> 
  </tr> 
  <tr> 
   <td>MBRCovers</td> 
   <td>判断一个图形的MBR是否覆盖另一个图形的MBR</td> 
  </tr> 
  <tr> 
   <td>MBRDisjoint</td> 
   <td>计算两个图形的MBR是否不相交</td> 
  </tr> 
  <tr> 
   <td>MBREqual</td> 
   <td>判断两个图形的MBR是否相同</td> 
  </tr> 
  <tr> 
   <td>MBRIntersects</td> 
   <td>判断两个图形MBR是否相交</td> 
  </tr> 
  <tr> 
   <td>MBROverlaps</td> 
   <td>判断两个图形MBR是否存在内部重叠，但相互不完全覆盖</td> 
  </tr> 
  <tr> 
   <td>MBRTouches</td> 
   <td>判断两个图形MBR是否仅边界相交</td> 
  </tr> 
  <tr> 
   <td>MBRWithin</td> 
   <td>判断一个图形MBR是否完全在另一个图形MBR内部</td> 
  </tr> 
 </tbody> 
</table>

2.4.3 空间数据查找示例

以下是空间函数的使用示例：

-- 查找所有MBR包含目标点的图形
    SELECT fid,ST_AsText(g) FROM geom WHERE MBRContains(ST_GeomFromText('POINT(1 1)'),g);
    
    -- 计算两点距离
    SELECT ST_Distance(ST_GeomFromText('Point(1,1)'), ST_GeomFromText('Point(2,2)'));

### 2.5 小结 ###

以上是MySQL中GIS功能的一些概要介绍。MySQL提供了GIS数据格式存储以及相应的数据计算方法，不过实际使用时还是要根据具体项目情况设计存储方案。

例如，对于多边形模型，可以以MULTIPOLYGON的方式进行存储，这样ST方法能够精确计算得到结果，但如果图形区域非常复杂，且数据量大，ST函数性能会有较大影响；也可以将原始的模型拆成逐条线段，以LINESTRING的方式分别存储，检索时通过MBR方法定位相交部分线段，再通过几何上的射线法来计算图形关系，从而提升性能。

需要指出的是，MySQL虽然提供了一定的空间数据处理能力，不过与专业GIS软件相比仍然存在一定差距。

1.  跨日线处理问题。在经纬度坐标体系中，经度坐标范围为\[180,-180\]，但当一条水平直线从 经度170 向东延伸至 经度-170时，会导致MySQL无法正确识别图形，只能通过人工进行坐标系换算处理。
2.  劣弧原则。同样是上面的场景，当用户输入\[170, 0\], \[-170, 0\]的经纬度坐标时，此时用户所想的是从东经170起，横跨换日线到达西经170位置，而在MySQL中会认为该线段从东经170向西，经过本初子午线到达西经170，得到完全相反的结果。
3.  不支持曲线格式。MySQL目前只支持线性插值，若要存储曲线只能通过拟近的方式来实现，也无法体现大圆线/恒向线等连接方式。

以上问题在于MySQL提供的只是基于直角坐标系的空间计算，非真正意义上的经纬度换算。如果程序计算维度在我国经纬度范围内，上述问题并不会出现；但对于航空、气象计算等国际数据处理场景，则需要考虑选用更为专业的空间数据库。

### 参考资料： ###

[第46次《中国互联网络发展状况统计报告》][46]  
[GIS基础知识介绍][GIS]  
[OpenGIS中文文档][OpenGIS]  
[MySQL 5.7用户手册][MySQL 5.7]  
[R树空间索引][R]

作者：阮伟聪

[20201018165936675.png_pic_center]: /images/20221123/00fd19f927da46cba4db6cd7a68434df.png
[MBR]: /images/20221123/959e799c92f246c9b8e3d9a492c8788e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ3MDIyNzc2_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/981d4d7a838b45f8b50695fab6485d07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ3MDIyNzc2_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/f76e272a63a849de80c64c0ca44eb94e.png
[Cross_Intersect]: /images/20221123/81817ff90ab84476ba2920937597ac7e.png
[46]: http://www.gov.cn/xinwen/2020-09/29/5548176/files/1c6b4a2ae06c4ffc8bccb49da353495e.pdf
[GIS]: https://www.cnblogs.com/zhurong/p/9514921.html
[OpenGIS]: https://www.osgeo.cn/doc_opengis/index.html#
[MySQL 5.7]: https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html
[R]: https://blog.csdn.net/houzuoxin/article/details/16113895