灰帽子
0%

PostgreSQL - 经纬度计算距离ACOS函数ERROR: input is out of range

发表于 更新于 分类 阅读次数： Valine： 2.1k 4 分钟
记录在PostgreSQL中使用经纬度计算距离时碰到的ACOS函数参数超出范围(ERROR: input is out of range)的排查过程和解决方案。

0.问题背景

项目中需要计算两个点的距离，已记录对应的经纬度
计算公式如下:

1
SELECT ROUND((6371393.0*ACOS(SIN(点1纬度/180*PI())*SIN(点2纬度/180*PI())+COS(点1纬度/180*PI())*COS(点2纬度/180*PI())*COS((点1经度-点2经度)/180*PI())))::NUMERIC, 3);

1.问题复现

点1位置: 经度122.132181、纬度40.272723，点2位置与点1相同，正常结果距离应该是0，实际执行结果如下

1
SELECT ROUND((6371393.0*ACOS(SIN(40.272723/180*PI())*SIN(40.272723/180*PI())+COS(40.272723/180*PI())*COS(40.272723/180*PI())*COS((122.132181-122.132181)/180*PI())))::NUMERIC, 3);

报错: 

1
ERROR:  input is out of range

2.问题定位

反余弦ACOS函数的参数范围必须在[-1,1]范围内，当参数超出范围时就会报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 验证ACOS(1)的值
dev=# SELECT ACOS(1);
 acos
------
    0
(1 row)

-- 查询ACOS的入参, 输出为1
dev=# SELECT SIN(40.272723/180*PI())*SIN(40.272723/180*PI())+COS(40.272723/180*PI())*COS(40.272723/180*PI())*COS((122.132181-122.132181)/180*PI());
 ?column?
----------
        1
(1 row)

-- 比较输出值与1的大小， 输出值竟然比1大！
dev=# SELECT SIN(40.272723/180*PI())*SIN(40.272723/180*PI())+COS(40.272723/180*PI())*COS(40.272723/180*PI())*COS((122.132181-122.132181)/180*PI()) > 1;
 ?column?
----------
 t
(1 row)

3.原因描述

二进制无法精确表示浮点数计算后的值，当计算后的值理论上为1时，在计算机中存储的可能是1.0000000000000001或者0.999999999999999
在PG的低版本中(上面测试用的版本是10.20)无法直观的看出精度，只能通过比较的方式得到结论
在PG较高的版本中(如12版本)可以直观的看出结果, 存储的值是1.0000000000000002

1
2
3
4
5
dev=# SELECT SIN(40.272723/180*PI())*SIN(40.272723/180*PI())+COS(40.272723/180*PI())*COS(40.272723/180*PI())*COS((122.132181-122.132181)/180*PI());
      ?column?
--------------------
 1.0000000000000002
(1 row)

4.解决方案

PG有一对函数可计算最大值和最小值: GREATEST取最大值，LEAST取最小值

1
2
3
4
5
dev=# SELECT GREATEST(1,2,3,4), LEAST(1,2,3,4);
 greatest | least
----------+-------
        4 |     1
(1 row)

GREATES和 LEAST函数搭配使用可以限制ACOS函数的参数只能在[-1,1]之间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 当在[-1,1]范围内时取真实值
dev=# SELECT GREATEST(LEAST(0.99, 1), -1);
 greatest
----------
     0.99
(1 row)

-- 当超出[-1,1]范围内时取边界值
dev=# SELECT GREATEST(LEAST(1.01, 1), -1);
 greatest
----------
        1
(1 row)

-- 修改后可正常执行的SQL如下:
dev=# SELECT ACOS(GREATEST(LEAST(SIN(40.272723/180*PI())*SIN(40.272723/180*PI())+COS(40.272723/180*PI())*COS(40.272723/180*PI())*COS((122.132181-122.132181)/180*PI()), 1), -1));
 acos
------
    0
(1 row)

5.更健壮的根据经纬度计算距离的SQL语句

1
SELECT ROUND((6371393.0*ACOS(GREATEST(LEAST(SIN(点1纬度/180*PI())*SIN(点2纬度/180*PI())+COS(点1纬度/180*PI())*COS(点2纬度/180*PI())*COS((点1经度-点2经度)/180*PI()), 1), -1)))::NUMERIC, 3);