MySQL空间数据存储及函数

不久前开发了一个地图相关的后端项目，需要提供一些点线面相关的存储、查询、分析相关的操作，于是对MySQL空间函数进行充分调研并应用在项目中;MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构)，还有有细分类型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等，我们了解了空间函数，在涉及到经纬度存储，路线存储方面的业务就能够使用此类型进行存储，使用相关空间函数进行分析业务实现，以下所有数据库操作基于MySQL5.7.20。

一、数据类型

1.什么是MySQL空间数据

MySQL提供了数据类型geometry用来存储坐标信息，geometry类型支持以下三种数据存储

2.什么是geojson

GeoJSON是一种对各种地理数据结构进行编码的格式。GeoJSON对象可以表示几何、特征或者特征集合。GeoJSON支持下面几何类型：点、线、面、多点、多线、多面和几何集合。GeoJSON里的特征包含一个几何对象和其他属性，特征集合表示一系列特征。一个完整的GeoJSON数据结构总是一个（JSON术语里的）对象。在GeoJSON里，对象由名/值对--也称作成员的集合组成。对每个成员来说，名字总是字符串。成员的值要么是字符串、数字、对象、数组，要么是下面文本常量中的一个："true","false"和"null"。数组是由值是上面所说的元素组成

除了简单的点、线、面，为了满足复杂的地理环境及地图业务，还会有多点(MultiPoint),多线(MultiLineString),多面(MultiPolygon),几何集合(GeometryCollection)等，熟悉json就可以快速的熟悉并应用geojson

3.格式化空间数据类型（geometry相互转换geojson）

数据库存储的空间数据通过可视化工具展示的明文结构为上面示例中所见，结构并不易于客户端解析，所以MySQL提供了几个空间函数用来解析及格式化空间数据，geojson是gis空间数据展示的标准格式，前端地图框架及后端空间分析相关框架都会支持geojson格式

示例：

准备示例数据

函数应用示例

1.查询绿藤气象监测点信息将geometry处理成geojson格式

执行sql:

select id,point_name,ST_ASGEOJSON(point_geom) as geojson from meteorological_point where id = 1

2.新增一个点位信息，客户端提交的点位geometry字符串需要使用ST_GEOMFROMTEXT函数处理才能插入，否则会报错

客户端提交点位信息

{    "point_name":"新帅集团监测点",    "geotext":"POINT(117.420671499 40.194914201)"}}

insert into meteorological_point(point_name, point_geom) values("新帅集团监测点", "POINT(117.420671499 40.194914201)")

insert into meteorological_point(point_name, point_geom) values("新帅集团监测点", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

客户端提交点位信息

{    "point_name":"民爆公司监测点",    "geojson":"{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}"}}

insert into meteorological_point(point_name, point_geom) values("民爆公司监测点", ST_GeomFromGeoJSON("{\"type\": \"Point\", \"coordinates\": [117.410671499, 40.1549142015]}"))

mysql geometry数据存储需要对geometry文本或geojson进行函数处理后才能进行存储，否则会报错，查询时候使用格式化函数转成geojson方便服务端传输和客户端框架解析

二、空间分析

在上一部分介绍了空间函数存储，查询格式化处理相关的操作，了解空间数据结构及geojson，这一部分介绍空间数据处理函数的应用

1、根据点位及半径，生成缓冲区

在地图功能中，缓冲区是非常常见的功能，一来可以查看点线面一定范围类的覆盖区域，二来在一些分析场景中，已知一个位子坐标信息及缓冲半径，生成缓冲区作为查询条件进行地理搜索

SELECT ST_ASGEOJSON(ST_BUFFER(ST_GeomFromGeoJSON('${geojsonStr}'),${radius}))

调用方传来一个geojson字符串及半径（米），使用ST_GeomFromGeoJSON将geojson字符串处理成数据库中的geometry，再使用ST_BUFFER(geometry, 半径)s生成缓冲区空间数据，函数返回的格式也是geometry,所以在外面包一层ST_ASGEOJSON函数将返回结果处理成geojson,便于客户端读取及渲染

示例：

有一个点位的geojson字符串为 "{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}",缓冲半径50米（注意：ST_BUFFER()的参数地理信息及返回值均使用墨卡托坐标系，如非墨卡托坐标系的geojson,需使用工具类进行转换处理）

public class MercatorUtils {    /**     * 点位geojson转墨卡托     *     * @param point     * @return     */    public static JSONObject point2Mercator(JSONObject point) {        JSONArray xy = point.getJSONArray(COORDINATES);        JSONArray mercator = lngLat2Mercator(xy.getDouble(0), xy.getDouble(1));        point.put(COORDINATES, mercator);        return point;    }    /**     * 经纬度转墨卡托     */    public static JSONArray lngLat2Mercator(double lng, double lat) {        double x = lng * 20037508.342789 / 180;        double y = Math.log(Math.tan((90 + lat) * M_PI / 360)) / (M_PI / 180);        y = y * 20037508.34789 / 180;        JSONArray xy = new JSONArray();        xy.add(x);        xy.add(y);        return xy;    }        /**     * 墨卡托坐标系数据转普通坐标系     */    public static JSONObject mercatorPolygon2Lnglat(JSONObject polygon) {        JSONArray coordinates = polygon.getJSONArray(COORDINATES);        JSONArray xy = coordinates.getJSONArray(0);        JSONArray ms = new JSONArray();        for (int i = 0; i < xy.size(); i++) {            JSONArray p = xy.getJSONArray(i);            JSONArray m = mercator2lngLat(p.getDouble(0), p.getDouble(1));            ms.add(m);        }        JSONArray newCoordinates = new JSONArray();        newCoordinates.add(ms);        polygon.put(COORDINATES, newCoordinates);        return polygon;    }}

客户端提交点位geojson及半径
使用墨卡托工具类将点位geojson转换成墨卡托坐标系的geojson
调用sql进行缓冲区生成
返回值使用墨卡托工具类转换成mercatorPolygon2Lnglat返回给调用方

小结：

上面介绍如何使用mysql st_buffer函数生成缓冲区，实际操作起来经过我在研发中的应用是可行的，实际开发中还可以使用一些工具包来实现缓冲区生成，如geotools...

三、判断点位所在城市

判断用户点位所在城市-客户端提交用户的定位信息，判断用户所在城市(使用ST_INTERSECTS()判断两个几何是否相交即可，返回0或1)

SELECT ST_INTERSECTS(ST_GeomFromGeoJSON('${geoJsonStrA}'), ST_GeomFromGeoJSON('${geoJsonStrB}'))

使用格式化函数将geojson处理成函数支持的geomtry格式，使用ST_INTERSECTS进行判断即可

四、常用的空间函数

总结：

MySQL为空间数据的存储及分析提供了丰富的数据类型及函数，我们学习此类函数能够帮助我们更好的处理地理信息，使用前需要对坐标系、geojson相关知识进行了解，避免踩坑，如果有相关问题也可以在评论区交流，如有误区请指正。