mysql解析json字符串数组(mysql json数组查询)
前言
不久前开发了一个地图相关的后端项目,需要提供一些点线面相关的存储、查询、分析相关的操作,于是对MySQL空间函数进行充分调研并应用在项目中;MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构),还有有细分类型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等,我们了解了空间函数,在涉及到经纬度存储,路线存储方面的业务就能够使用此类型进行存储,使用相关空间函数进行分析业务实现,以下所有数据库操作基于MySQL5.7.20。
数据类型
1.什么是MySQL空间数据
MySQL提供了数据类型geometry用来存储坐标信息,geometry类型支持以下三种数据存储
在地图功能中,缓冲区是非常常见的功能,一来可以查看点线面一定范围类的覆盖区域,二来在一些分析场景中,已知一个位子坐标信息及缓冲半径,生成缓冲区作为查询条件进行地理搜索
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 { /** * 点位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…
判断点位所在城市
2.判断用户点位所在城市-客户端提交用户的定位信息,判断用户所在城市(使用ST_INTERSECTS()判断两个几何是否相交即可,返回0或1)
SELECT ST_INTERSECTS(ST_GeomFromGeoJSON('${geoJsonStrA}'), ST_GeomFromGeoJSON('${geoJsonStrB}'))复制代码
SQL解读
使用格式化函数将geojson处理成函数支持的geomtry格式,使用ST_INTERSECTS进行判断即可
常用的空间函数
总结
MySQL为空间数据的存储及分析提供了丰富的数据类型及函数,我们学习此类函数能够帮助我们更好的处理地理信息,使用前需要对坐标系、geojson相关知识进行了解,避免踩坑,如果有相关问题也可以在评论区交流,如有误区请指正。
原创文章,作者:admin,如若转载,请注明出处:https://www.qq65hfghe5.com/tg/45675.html