计算距离的MySQL三角运算结果为空

Question

我在计算lat液化天然气距离时遇到了一个奇怪的问题。如果我在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))))

给出结果。

有什么原因会导致这种情况吗？

Answer 1

一点都不奇怪，

这三个表达式中没有一个计算相同的东西。

的差异

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))))

sin(radians(30.50492700)))) SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) *

​

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))))

评价

• 第一表达式
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.496706))等于1
  4. sin(radians(30.50492800)) * sin(radians(30.50492800))等于0.257670419351355
  5. 因此，我们对0.861585503968495 * 0.861585503968495 * 1 + 0.257670419351355进行了评估，给出了1.000000000000000513393952565025
  6. acos(1.000000000000000513393952565025)返回null。

​

• 第二表达式
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.496706))等于1
  4. sin(radians(30.50492800)) * sin(radians(30.50492700))等于0.257670411718131
  5. 因此，我们计算了0.861585503968495 * 0.861585503968495 * 1 + 0.257670411718131，它等于0.999999992366776513393952565025。
  6. acos(0.999999992366776513393952565025) = 0.000123557464423953
  7. 3959 * 0.000123557464423953 = 0.489164001654431

​

• 第三表达式
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.49670))等于0.999999999999995
  4. sin(radians(30.50492800)) * sin(radians(30.50492800))等于0.257670419351355
  5. 因此，我们计算了0.861585503968495 * 0.861585503968495 * 0.999999999999995 + 0.257670419351355，它等于0.999999999999996801746049321797。
  6. acos(0.999999999999996801746049321797)相等8.16170211889098e-08
  7. 乘以3959，得到0.000323121786886894

​

为什么acos(1.0000000000000513393952565025)返回null ?

从文档，

返回X的弧余弦，即余弦为X的值。如果X不在-1到1的范围内，则返回NULL。

如您所见，1.000000000000000513393952565025高于1。

对于其他两个不给空的表达式，acos的给定参数为

• 0.999999992366776513393952565025
• 0.000323121786886894

它们都在[-1, 1]范围内，所以我们没有空。