Mysql 8.0 中存储 gis 数据的正确姿势

2021-08-01 阅读: 评论: 约 1979 字预计阅读 4 分钟

请为列指定 SRID

如果你想要在使用一些 spatial function 的时候获得尽可能精确的值，请务必为您的数据库中的 geometry 列指定 SRID，如 st_area st_distance 等。否则您将获得直角坐标上的距离，而不是真实的值，另外在 mysql 老版本中，不指定 SRID（默认为 0），可以使用某些计算函数，但是在 mysql 中，某些函数必须指定 SRID，也要求被计算的列必须指定 SRID。

什么是 SRID？

SRID 指定的是 spatial 数据的坐标系，地理数据中一个最重要的指标就是坐标系，没有坐标系，就不存在经纬度，不存在投影，不存在地心，不存在真实距离，一切将会变为平面坐标，与真实地理信息天差地别。

SRID 坐标系定义了什么？简单来说，这里不展开，坐标系定义了，大地水准面 -> 椭球体 -> 大地基准面，他们分别是海平面、地球形状和球心、球心偏移等，在每个尺度和地区，这些值都在发生变化，所以会定义不同的坐标系，以尽量消除误差。

最常用的 SRID 是什么？

在服务端，通常存储数据都是使用 4326 的 SRID，在前端，地图渲染使用 3857 坐标系（代表莫卡托投影），而处理数据，使用经过定制的 4326 坐标系（如 GeoJSON ，使用经纬度反过来的 4326 坐标系）

平常我们见到的 84 坐标系、高德坐标系、百度坐标系、国测、火星等乱糟糟的都是基于 4326 坐标系的定义的，国内这些坐标系只是做了数值的转换，没有改变坐标系定义的参数。

定义 SRID 为 4326 需要注意什么？

通常，在 mysql 8.0 中，不给列定义坐标系，就不可以给列增加空间索引，所以当你知道需要给列定义 SRID 之后，你以为事情已经结束了，其实问题还刚开始，真正的复杂度还在后面，不急，先来看怎么定义 SRID

1

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);

Ok，万事大吉，然后你开始向数据库写入数据：

1

insert into geom (g) values (st_geomfromtext('POINT (120.11, 80.12)',4326))

大错特错，虽然你还没意识到哪里错了，但是我们需要回到坐标系的问题了（这句 sql 是不会执行成功的）

坐标轴顺序

SRID 除了定义上述提到的大地基准，球体，水准面等，他还会定义坐标轴的单位和顺序，例如是度，还是公里，是 lng/lat 还是 lat/lng。

不巧了，4326 规范中定义的轴顺序是 lat/lng，也就是维度/经度，而你发现你日常生活中拿到或者处理的数据，大部分都是经纬度的顺序，甚至很多前端的库，例如处理 wkt 的库，处理 geojson 的库，都假定了坐标是 lng/lat。。最后出来的结果也都是 lng/lat，而实际上规范里的定义，坐标系中定义的是什么，就是什么顺序，而数据库通常都严格实现了规范的定义。

所以，你插入刚才的表的数据，必须是纬度经度的顺序，这样才能插入成功，也才能建立索引。

1

insert into geom (g) values (st_geomfromtext('POINT (80.12, 120.11)',4326))

事实上，并不是所有系统都是按照规范定义的实现的，除了部分框架或者库是因为作者没搞清楚坐标系的概念（如 npmjs.org/wkt ，wkt 是不带坐标系的，所以转换 wkt 必须指定坐标系，而作者以为 wkt 的顺序都是经纬度。。）

特殊情况

这里简单列举： 1.GeoJSON 的官方规范中规定，所有 GeoJSON 中出现的坐标轴顺序都是经纬度，但是其定义完全遵循 4326 坐标系

2.GeoServer 中内置的 4326 坐标系，其坐标轴是强制的经纬度顺序，与官方定义不同，主要是因为 Geoserver 是偏末端的系统，通常在前端或者末端，都会尝试统一成经纬度的顺序。

3.OGC 为了兼容这些定义，定义了一个新的坐标系，他完全等于 4326，唯一的不同是坐标轴反过来了，现在大部分系统都没有实现这个标准：urn:ogc:def:crs:OGC::CRS84 ，例如 mysql 8 就没有这个坐标系的实现。

结束了吗？

有没有觉得，前端的数据坐标系都是经纬度，而到了数据库一侧，就要变成纬经度，感觉非常麻烦？有没有办法可以直接用经纬度的方法操作数据库？还真有，多看看 mysql 8.0 的官方文档就知道了。

1

insert into geom (g) values (st_geomfromtext('POINT (120.11 80.12)',4326,'axis-order=long-lat')))

加一个 axis-order 的参数，就可以让你插入的语句可以传入反过来的坐标轴了

1

SELECT ST_AsText(ST_SwapXY(g)) from geom;

这样，读取的时候也可以自动反转，而底层存储，实际上还是纬经度。

嗯？这么简单？

不，实际上真正搞清楚这些事情的人还是比较稀少的，而且这里面历史遗留问题比较严重，所以造成了比较混乱的局面，所以当你使用一些 mysql client 操作刚才定义的数据库的时候，你会发现一些很奇怪的现象，以 Navicat 为例，当你用 Navicat 打开这张表的时候，你会发现 Navicat 自动把相关的列转成了经纬度的顺序（我不知道为什么 Navicat 要这么做），存储的实际数据和展示的数据不符，这还没什么，当你想编辑一个列的时候，你发现你编辑之后无法保存，报的错就是因为轴顺序错误，也就是虽然它展示的是经纬度，但是你编辑的时候，要自己把数据翻转一次才能编辑，非常麻烦，所以基本上，只能用 sql 对数据库进行操作。

甜点

Mysql 中指定了 SRID 之后，计算会精确很多，但是大家通常在前端或者 python、java 等程序中，会引用一些代码包，里面可以算 polygon 的面积，point 的距离等，也没见他们要指定 SRID，那他们是怎么算的，实际上，大部分库为了简化计算，都是使用标准圆球体作为参考做计算的，所以他们的精度不如 4326，而且差异可能还挺大，但是这样计算比较简单，例如 turf.js 中计算距离的函数：

联系我

我是芋头，一名工作 10 多年的前端开发，目前我主要专注于 GIS 领域，包括分析、计算、可视化等，欢迎勾搭。

目录