我在计算纬度液化天然气距离时遇到了一个奇怪的问题。如果我在 mysql 中输入此查询
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))
它会导致空值。但是,进入
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians(30.50492700))))
或
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.49670)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))
给出一个结果。
任何原因会导致这种情况?
也不奇怪,
这 3 个表达式中没有一个计算相同的东西。
差异
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) *
cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) *
sin(radians(30.50492800))))
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) *
cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) *
sin(radians(
30.50492700
))))
SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(
-90.49670
)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))
评估
- 第一个表达式
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(-90.49670600) - radians(-90.496706))
等于 1 -
sin(radians(30.50492800)) * sin(radians(30.50492800))
等于0.257670419351355
- 所以我们评估
0.861585503968495 * 0.861585503968495 * 1 + 0.257670419351355
,这给了1.000000000000000513393952565025
-
acos(1.000000000000000513393952565025)
返回空值。
-
- 第二个表达式
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(-90.49670600) - radians(-90.496706))
等于 1 -
sin(radians(30.50492800)) * sin(radians(30.50492700))
等于0.257670411718131
- 所以我们评估
0.861585503968495 * 0.861585503968495 * 1 + 0.257670411718131
等于0.999999992366776513393952565025
-
acos(0.999999992366776513393952565025)
=0.000123557464423953
-
3959 * 0.000123557464423953
=0.489164001654431
-
- 第三种表达方式
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(30.50492800))
等于0.861585503968495
-
cos(radians(-90.49670600) - radians(-90.49670))
等于0.999999999999995
-
sin(radians(30.50492800)) * sin(radians(30.50492800))
等于0.257670419351355
- 因此,我们评估
0.861585503968495 * 0.861585503968495 * 0.999999999999995 + 0.257670419351355
等于0.999999999999996801746049321797
-
acos(0.999999999999996801746049321797)
等于8.16170211889098e-08
乘以 3959
,我们得到0.000323121786886894
-
为什么 acos(1.000000000000000513393952565025) 返回空值?
从文档中,
返回 X 的反角余弦,即余弦为 X 的值。 如果 X 不在 -1 到 1 范围内,则返回 NULL。
如您所见,1.000000000000000513393952565025
高于 1。
对于不给出 null 的另外两个表达式,要acos
的给定参数为
- 0.999999992366776513393952565025
- 0.000323121786886894
它们都在[-1, 1]
范围内,因此我们没有得到空值。