MySQL空间函数计算线半径为300米之内面的经纬度集合

项目场景

MySQL空间函数运用：ST_Asgeojson、ST_Buffer、ST_GeomFromText

项目场景：某一天，突然来了一个需求，需要计算线集合半径为300米之内面的经纬度集合，前端用地图api嫌麻烦，所以要后端算好，前端直接用。我直呼：好（zhen）家（gou）伙（lan）！

---

函数介绍

MySQL版本：8.0.25

ST_Asgeojson

返回 JSON 格式的表示几何的字符串。

ST_GeomFromText

用于将几何数据从可读的文本类型转换成内部存储的二进制类型。

ST_Buffer

获取几何对象和距离，然后返回与源对象的距离小于或等于以输入距离为单位测量半径的所有点。

st_buffer(geometry, 半径)

geometry为点时【例：st_buffer(ST_GeomFromText("POINT(1 2)"), 300)】，点+半径生成圆；
geometry为线时【例：st_buffer(ST_GeomFromText("linestring(1 2,3 4)"), 300)】，线+半径生成圆角矩形；
geometry为面时【例：st_buffer(ST_GeomFromText("POLYGON(1 2,3 4,5 6)"), 300)】，面+半径生成更大的面。

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

代码实现

刚开始做这个需求的时候，找到了对应的函数，写起来飞快
三下五除二，就写好了
具体实现如下：

<select id="getPolygon" resultType="com.*.*.**Response">
        select
        ST_Asgeojson(ST_Buffer(ST_GeomFromText(
            <foreach collection="dto.line" item="point" open="'linestring(" close=")'" separator=", ">
                    ${point.x} ${point.y}
            </foreach>
        ), ${dto.radius})) as geometry from dual
    </select>

OK，打完收工

---

自测时，发现了不对劲，返回的经纬度集合中，居然出现了负数！！！不可思议呀！
随即问前端
我：你入参传线的经纬度是什么坐标系？
前端：84坐标系
我：嗯？！（灵光一闪）
以前做过GIS的我，知道地图有84坐标系、火星坐标系、百度坐标系等等
当时我就在想MySQL使用的什么坐标系？！

经过了解，原来ST_Buffer函数的入参和返参都使用的是墨卡托坐标系

ST_Buffer
注意：ST_BUFFER()的参数地理信息及返回值均使用墨卡托坐标系，如非墨卡托坐标系的geojson,需使用工具类进行转换处理

于是乎：业务逻辑修改成，先将传入的84坐标系的点集合 转换成 墨卡托坐标系的点集合，交由数据库进行计算，再将计算的结果转换成84坐标系的点集合，传给前端。

	@Resource
    private DeviceWarnDAO deviceWarnDAO;

	public MyResponse getPolygon(MyDTO dto){
        if (dto.getLine().size() == 0) {
            throw new BusinessException("经纬度集合不为空");
        }
        if (Objects.isNull(dto.getRadius())) {
            throw new BusinessException("半径不为空");
        }
        List<MyQO> list = dto.getLine();
        list.forEach(v -> {
            JSONArray jsonArray = MercatorUtils.lngLat2Mercator(v.getX(), v.getY());
            v.setX(jsonArray.getDouble(0));
            v.setY(jsonArray.getDouble(1));
        });
        MyResponse myResponse = deviceWarnDAO.getPolygon(dto);
        JSONObject jsonObject = JSON.parseObject(myResponse.getGeometry());
        JSONObject newJsonObject = MercatorUtils.mercatorPolygon2Lnglat(jsonObject);
        myResponse.setGeometry(newJsonObject.toJSONString());
        return myResponse;
	}

附上工具类

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import static java.lang.Math.PI;

/**
 * 墨卡托工具类
 * @author admin
 */
public class MercatorUtils {

    private final static String COORDINATES = "coordinates";
    /**
     * 点位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) * PI / 360)) / (PI / 180);
        y = y * 20037508.34789 / 180;
        JSONArray xy = new JSONArray();
        xy.add(x);
        xy.add(y);
        return xy;
    }

    public static void main(String[] args) {
        JSONArray a = lngLat2Mercator(117.348938,31.805369);
    }

    /**
     * 墨卡托坐标系数据转普通坐标系
     */
    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;
    }

    public static JSONArray mercator2lngLat(double mercatorX, double mercatorY) {
        double x = mercatorX / 20037508.342789 * 180;
        double y = mercatorY / 20037508.34789 * 180;
        y = 180 / PI * (2 * Math.atan(Math.exp(y * PI / 180)) - PI / 2);
        JSONArray newCoordinates = new JSONArray();
        newCoordinates.add(x);
        newCoordinates.add(y);
        return newCoordinates;
    }
}

成功！！

---

最后

该需求比较急，着急做需求就没有尝试使用代码计算，另外Java代码也是可以实现的；
为了深入学习MySQL的空间函数，为此记录一下