编程宝库 - 技术改变世界

编程宝库

Mysql知识

MySQL空间数据存储及函数

前言：

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

一、数据类型

1.什么是MySQL空间数据

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

还有多点MULTIPOINT（多点）、MULTILINESTRING（多线）、MULTIPOLYGON（多面）、GEOMETRYCOLLECTION（集合，可放入点线面）等类型

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)")

报错 1416 - Cannot get geometry object from data you send to the GEOMETRY field

正确插入sql:

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

3.新增点位，客户端提交点位格式为geojson格式，需要使用ST_GeomFromGeoJSON函数处理后进行插入

客户端提交点位信息

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

插入SQL

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}))

SQL解读

调用方传来一个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就可以作为上面缓冲区的sql生成缓冲区空间数据了，生成的缓冲区数据也是墨卡托坐标系，需使用mercatorPolygon2Lnglat进行处理后返回给客户端，调用流程如下:

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

小结：

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

三、判断点位所在城市

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

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

SQL解读：

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

四、常用的空间函数

总结：

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

关于MySQL空间数据存储及函数的文章就介绍至此，更多相关MySQL空间数据存储及函数内容请搜索编程宝库以前的文章，希望大家多多支持编程宝库！

下一节：MySQL分库分表详情

MySQL教程

一、业务场景介绍假设目前有一个电商系统使用的是MySQL，要设计大数据量存储、高并发、高性能可扩展的方案，数据库中有用户表。用户会非常多，并且要实现高扩展性，你会怎么去设计？ OK咱们先看传统的分库分表方 ...

MySQL基础快速入门知识总结(附思维导图)

MySQL分库分表详情