MYSQL无法选择两个点（纬度、经度）之间距离为0的记录。

Question

我有以下的MySQL查询：

SELECT de.geoId,(6371 * ACOS( COS( RADIANS(zde.latitude) )* 
    COS(RADIANS( de.latitude ) ) * COS( RADIANS( de.longitude ) - RADIANS(zde.longitude) ) + 
    SIN( RADIANS(zde.latitude) ) * SIN( RADIANS( de.latitude ) ) ) )  AS distance 
FROM tbl_zipde AS zde 
    LEFT JOIN  tbl_country_de AS de 
             ON (de.admin1_code=zde.admin_code1)  
                 Where zde.id=8 and  de.geoId=24 having distance<1

这个查询应该从 tbl_zipde 表中返回一条记录，其纬度和经度值与 id=8 的记录的相同，但由于这两点之间计算出的距离为0，mysql 将0识别为距离的 NULL 值，因此它不会返回任何记录，但是如果我删除 "having distance<1"，那么它将返回来自 tbl_country_de 表的正确记录（id=24），但表列距离的值为 NULL。

我应该如何编写 mysql 查询，以便 mysql 返回两点之间距离为 0 的记录（纬度和经度值）？

为什么 mysql 返回 NULL 而不是 '0' ？

我已将纬度和经度的数据类型从 decimal(10,7) 更改为 float(10,7)，现在可以正常工作了。 不知道这是否是真正的问题？

- sorin

---

null表示数学错误（例如除以零），纬度/经度字段中的空值，或者连接没有返回任何记录，强制数据库填充缺失字段的空值，这些空值会向上传递到数学计算中。 - Marc B

@MARC B 好的，我明白了。但是仅仅通过将数据类型从十进制数更改为浮点数就解决了问题。这是什么原因？ - sorin

没有想法。尝试使用分解的部分重新运行原始查询，例如radians（de.latitude）作为radlat，并查看其中是否出现null。请记住，null是“具有传染性”的。如果您在任何类型的数学运算中包含null值，则整个操作的结果将变为null。 - Marc B

Answer 1

这个问题已经存在一段时间了，但是还没有得到正确的答案。

DECIMAL不是地理位置数据的合适数据类型。

该问题中的公式被称为球面余弦定理公式。如果您仔细观察这个公式，就会注意到当您处理非常接近的点时，它需要取一个非常接近于1的数的反余弦函数(ACOS())。这是一种数值不稳定的操作。在这个背景下，不稳定意味着如果输入有小误差，则函数的输出可能会有很大的变化。

有一个更好的用于计算球面上距离的公式，叫做Vincenty公式。它的最后一个操作是ATAN2()：对于微小角度来说，这是一个更加数值稳定的操作。以下是它：

111.045 * DEGREES(ATAN2(SQRT(
    POW(COS(RADIANS(lat2))*SIN(RADIANS(lon2-lon1)),2) +
    POW(COS(RADIANS(lat1))*SIN(RADIANS(lat2)) -
         (SIN(RADIANS(lat1))*COS(RADIANS(lat2)) *
          COS(RADIANS(lon2-lon1))) ,2)),
  SIN(RADIANS(lat1))*SIN(RADIANS(lat2)) +
  COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1))))

这是一个MySQL存储过程: http://www.plumislandmedia.net/mysql/vicenty-great-circle-distance-formula/，可用于计算大圆距离。如果需要了解相关的数学知识，请参考http://en.wikipedia.org/wiki/Great-circle-distance。请注意，MySQL中的三角函数（余弦、正弦、反正切等）使用服务器计算机的浮点数运算子系统实现。如果您以DECIMAL格式提供数据，则会将数据转换为DOUBLE，进行计算，然后再次进行转换，这将导致精度丢失。对于商业GPS类型的纬度和经度数据，FLOAT数据类型（IEEE-488单精度浮点数）提供了足够的精度。问题中提到的NULL结果可能是由于输入ACOS的值略微大于1而导致的，这是由于在DOUBLE和DECIMAL之间进行转换时存在误差。改用FLOAT和Vincenty公式可以消除此类故障。

- O. Jones

Answer 2

它不应该返回null。您使用了左连接而不是内连接，因此您的表中可能有一些没有在tbl_country_de中找到匹配管理员代码的记录。

或者您的数据中是否有null值？

- FJT

---

我不仅使用此查询来查找具有相同纬度和经度的点，而且还用它来查找最接近另一个点的点，但它不会返回具有相同纬度和经度（距离=0）的点。 - sorin

将左连接改为内连接有帮助吗？ - FJT

MySQL将距离=0视为null值，这使我不知道如何选择。如果我写“having distance IS NULL”，那么它可以工作，但是如果距离是0.1呢？ - sorin

为什么MySQL返回距离时会返回NULL而不是'0'？ - sorin

你的数据中有空值吗？ - FJT

Answer 3

我知道这是一个非常老的帖子，但我刚在一些遗留代码中遇到了这个问题。我通过在距离计算周围使用coalesce函数来解决了这个问题。

select coalesce(*distance caclulation*, 0) as distance from ...

因此，从计算中得到的是0而不是null。我确定有人会告诉我这是一种hackish和可怕的方法，但对我们来说也不是关键操作，否则某个人早就会发现错误了。

- Andy

Answer 4

我已将纬度和经度的数据类型从decimal(10,7)更改为float(10,7)，并且它可以正常工作。 不知道那是否是真正的问题？

- sorin

---

真的吗？我想象相反的情况应该是正确的！（但我不是数学家） - Strawberry