使用Haversine公式计算用户距离时PostgreSQL报输入超出范围错误

问题原因 #

PostgreSQL的acos()函数要求输入值必须在[-1, 1]范围内，但由于浮点计算的精度误差，你的Haversine公式计算出的参数可能会出现极小的超出范围值（比如1.0000000001或-1.0000000001），触发"Input is out of range"错误。

解决方案 #

1. 限制acos参数的取值范围

用PostgreSQL的least()和greatest()函数把计算结果钳制在[-1, 1]之间，修改你的Haversine公式：

String HAVERSINE_FORMULA = "(6371 * acos(least(greatest(cos(radians(?2)) * cos(radians(uwc.latitude)) *" +
    " cos(radians(uwc.longitude) - radians(?1)) + sin(radians(?2)) * sin(radians(uwc.latitude)), -1.0), 1.0)))";

2. 避免重复计算公式（优化性能）

原查询中WHERE和ORDER BY都重复计算了Haversine公式，不仅低效，还可能重复触发精度问题。可以用子查询先计算距离，再过滤排序：

@Query("SELECT uwc FROM (" +
        "SELECT uwc, " + HAVERSINE_FORMULA + " AS distance " +
        "FROM UserWithCoordinate uwc) AS sub " +
        "WHERE sub.distance < ?3 " +
        "ORDER BY sub.distance DESC")
List<UserWithCoordinate> findByCoordinates(double longitude, double latitude, double distance, Pageable pageable);

3. 校验经纬度数据合法性

检查你的UserWithCoordinate表中，latitude是否在[-90, 90]之间，longitude是否在[-180, 180]之间。非法的经纬度值也会导致三角函数计算出异常值。

验证修改 #

修改后重新测试查询，精度误差被钳制后，acos()函数就不会再抛出越界错误，同时查询性能也会因为避免重复计算而提升。

内容的提问来源于stack exchange，提问作者TheWieand