大哥们，别再用最大最小经纬度进行Between来做围栏查询啦

需求背景

通过一组闭环的经纬度查询数据库中在此围栏内的数据。

原始做法

原来的做法是先计算出经纬度的最大值和最小值，然后用 BETWEEN 语句进行范围查询。具体实现代码如下：

/**
 * 构建最大经度、最大纬度、最小经度、最小纬度
 *
 * @param points
 * @return
 */
private SignalGridQueryVo buildMaxAndMinPoint(List<VerticesPoint> points) {
    BigDecimal maxLatitude = points.stream()
            .map(VerticesPoint::getLatitude)
            .max(BigDecimal::compareTo)
            .orElse(null);

    BigDecimal minLatitude = points.stream()
            .map(VerticesPoint::getLatitude)
            .min(BigDecimal::compareTo)
            .orElse(null);

    BigDecimal maxLongitude = points.stream()
            .map(VerticesPoint::getLongitude)
            .max(BigDecimal::compareTo)
            .orElse(null);

    BigDecimal minLongitude = points.stream()
            .map(VerticesPoint::getLongitude)
            .min(BigDecimal::compareTo)
            .orElse(null);

    return SignalGridQueryVo.builder()
            .maxLatitude(maxLatitude)
            .maxLongitude(maxLongitude)
            .minLatitude(minLatitude)
            .minLongitude(minLongitude)
            .build();
}

基于此，通过数据库查询以下数据：

SELECT * FROM table WHERE lon BETWEEN #{minLon} AND #{maxLon} AND lat BETWEEN #{minLat} AND #{maxLat};

虽然查询结果似乎正确，但在地图上展示时，某些经纬度点却漂移到了围栏外部。问题原因可能如下：

问题分析

1. 经度范围问题（跨越国际日期变更线）
   经度的范围是 -180 到 +180 度。当围栏跨越国际日期变更线（经度180°）时，简单的最大经度和最小经度查询会导致错误。例如，经度最大值可能是180°，最小值可能是-179°，但这两个值实际表示的是跨越经线的范围。

2. 纬度网格的物理距离差异
   由于纬度的物理距离在地球不同纬度有所不同，简单的矩形范围查询可能不完全符合实际地理范围，特别是在高纬度地区，纬度差异较小的情况下，物理距离较大。

解决方案

为了解决以上问题，我们可以使用数据库中的空间索引，并基于点与多边形关系来查询数据，从而避免因经纬度范围计算带来的误差。

数据库表设计与空间索引

首先，在数据库中添加一个 POINT 类型的字段来存储经纬度信息，并创建空间索引以提高查询效率：

CREATE TABLE new_table ( id INT AUTO_INCREMENT PRIMARY KEY, pos_location POINT NOT NULL COMMENT '经纬度', -- 其他字段... SPATIAL KEY `idx_pos_location` (`pos_location`) );

查询语句

使用空间查询，基于多边形包含关系查询是否在围栏内。查询的 SQL 语句如下：

select * from table WHERE st_contains( ST_GeomFromText(#{queryVo.polygon, typeHandler=com.xxx.config.PolygonTypeHandler}), point(pos_longitude, pos_latitude) )

自定义类型转换器

为了支持上述查询，首先需要创建一个自定义的类型转换器 PolygonTypeHandler，将经纬度点列表转换为 POLYGON 格式：

@Slf4j
@MappedTypes(List.class)
public class PolygonTypeHandler extends BaseTypeHandler<List<VerticesPoint>> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<VerticesPoint> parameter, JdbcType jdbcType) throws SQLException {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("POLYGON((");
        for (VerticesPoint point : parameter) {
            stringBuffer.append(point.getLongitude().toString())
                        .append(" ")
                        .append(point.getLatitude().toString())
                        .append(",");
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);
        stringBuffer.append("))");
        ps.setString(i, stringBuffer.toString());
    }

    @Override
    public List<VerticesPoint> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return new ArrayList<>();
    }

    @Override
    public List<VerticesPoint> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return new ArrayList<>();
    }

    @Override
    public List<VerticesPoint> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return new ArrayList<>();
    }
}

VerticesPoint 类

VerticesPoint 类用于表示经纬度信息，代码如下：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class VerticesPoint {

    @GeoField
    private BigDecimal longitude;
    @GeoField
    private BigDecimal latitude;

    @Override
    public String toString() {
        return "POINT(" + longitude.toString() + " " + latitude.toString() + ")";
    }

    public VerticesPoint deepCopy() {
        return new VerticesPoint(longitude, latitude);
    }
}

插入数据

为了将经纬度信息存储到数据库中，需要使用自定义类型解析器 GeoPointTypeHandler：

@Slf4j
@MappedTypes(VerticesPoint.class)
public class GeoPointTypeHandler extends BaseTypeHandler<VerticesPoint> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, VerticesPoint parameter, JdbcType jdbcType) throws SQLException {
        log.info("执行了setNonNullParameter");
        ps.setString(i, parameter.toString());
    }

    @Override
    public VerticesPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        log.info("执行了getNullableResult");
        String wkt = rs.getString(columnName);
        if (wkt == null) {
            return null;
        }
        String[] parts = wkt.replace("POINT(", "").replace(")", "").split(" ");
        BigDecimal longitude = new BigDecimal(parts[0]);
        BigDecimal latitude = new BigDecimal(parts[1]);
        return new VerticesPoint(longitude, latitude);
    }

    @Override
    public VerticesPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        log.info("执行了getNullableResult");
        String wkt = rs.getString(columnIndex);
        if (wkt == null) {
            return null;
        }
        String[] parts = wkt.replace("POINT(", "").replace(")", "").split(" ");
        BigDecimal longitude = new BigDecimal(parts[0]);
        BigDecimal latitude = new BigDecimal(parts[1]);
        return new VerticesPoint(longitude, latitude);
    }

    @Override
    public VerticesPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        log.info("执行了getNullableResult");
        String wkt = cs.getString(columnIndex);
        if (wkt == null) {
            return null;
        }
        String[] parts = wkt.replace("POINT(", "").replace(")", "").split(" ");
        BigDecimal longitude = new BigDecimal(parts[0]);
        BigDecimal latitude = new BigDecimal(parts[1]);
        return new VerticesPoint(longitude, latitude);
    }
}

数据插入 SQL

插入数据时，使用自定义类型处理器来处理经纬度字段：

INSERT INTO table ..., pos_location VALUES (ST_GeomFromText(#{}, #{}, ... #{entity.posLocation, typeHandler=com.xxx.config.GeoPointTypeHandler}));

总结

通过引入空间索引、空间查询以及自定义类型处理器，我们实现了更加精确且高效的围栏查询，解决了经纬度漂移问题。此方法避免了简单矩形范围查询带来的误差，特别适用于复杂的地理查询，同时保持了业务代码的简洁与优雅。