mysql解析json字符串数组（mysql json数组查询）

2022-02-16 19:30 浏览:2

前言

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

数据类型

1.什么是MySQL空间数据

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

数据结构	示例	说明
POINT(点)	POINT(113.3 40.08)	用于存储点位信息，包含经纬度信息
LINESTRING(线)	LineString(84.070 33.801,99.52 30.292)	用来存储路线信息
POLYGON(面)	POLYGON((84.070 33.801, 84.100 33.801,84.070 33.801))	用来存储面数据

还有多点MULTIPOINT（多点）、MULTILINESTRING（多线）、MULTIPOLYGON（多面）、GEOMETRYCOLLECTION（集合，可放入点线面）等类型，就不一一赘述了

2.什么是geojson

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

几何类型	格式
点	{ “type”: “Point”, “coordinates”: [经度, 纬度] }
线	{ “type”: “LineString”, “coordinates”: [ [ 经度, 纬度 ], [ 经度, 纬度 ] ] }
面	{“type”:”Polygon”,”coordinates”:[[[经度,纬度],[经度,纬度],[经度,纬度]]]}

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

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

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

操作	函数
geojson -> geometry	ST_GeomFromGeoJSON
geometry -> geojson	ST_ASGEOJSON
geometry字符串 -> geometry	ST_GEOMFROMTEXT

示例

准备示例数据

id(bigint)	point_name(varchar)	point_geom(geometry)
1	绿藤气象监测点	POINT(116.410671499 40.1849142015)
2	长藤资本监测点	POINT(113.3 40.08)

函数应用示例

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

执行sql:

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

查询结果:

id	point_name	geojson
1	绿藤气象监测点	{“type”: “Point”, “coordinates”: [116.410671499, 40.1849142015]}

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，这一部分介绍空间数据处理函数的应用

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

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

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

SQL解读

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

示例

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

public class MercatorUtils {
    
    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…

判断点位所在城市

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

SELECt ST_INTERSECTS(ST_GeomFromGeoJSON('${geoJsonStrA}'), ST_GeomFromGeoJSON('${geoJsonStrB}'))
复制代码

SQL解读

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

常用的空间函数

名称	描述
ST_INTERSECTS()	判断两个几何是否相交
ST_DISTANCE()	两个几何的距离
ST_CONTAIONS()	几何是否包含
ST_ISVALID()	几何是否有效

总结

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

留言