地理位置SQL查询未找到精确位置

Question

地理位置SQL查询未找到精确位置

4

我已经测试了我的地理定位查询一段时间了，直到现在都没有发现任何问题。

我正在尝试搜索给定半径内的所有城市，通常情况下，我使用该城市的坐标来搜索周围的城市，但最近我尝试在一个城市周围搜索，发现这个城市本身没有被返回。

我在数据库中有这些城市的摘录：

city            latitude    longitude  
Saint-Mathieu   45.316708   -73.516253  
Saint-Édouard   45.233374   -73.516254  
Saint-Michel    45.233374   -73.566256  
Saint-Rémi      45.266708   -73.616257

然而，当我使用以下查询在圣雷米市周围运行我的查询时...

SELECT tblcity.city, tblcity.latitude, tblcity.longitude, 
truncate((degrees(acos( sin(radians(tblcity.latitude)) 
* sin(radians(45.266708)) 
+ cos(radians(tblcity.latitude)) 
* cos(radians(45.266708)) 
* cos(radians(tblcity.longitude - -73.616257) ) ) ) 
* 69.09*1.6),1) as distance 
FROM tblcity HAVING distance < 10 ORDER BY distance desc

我得到了以下结果：

city            latitude    longitude     distance  
Saint-Mathieu   45.316708   -73.516253    9.5  
Saint-Édouard   45.233374   -73.516254    8.6  
Saint-Michel    45.233374   -73.566256    5.3

搜索结果中缺少圣雷米镇。

因此，我尝试了修改后的查询，希望能得到更好的结果：

SELECT tblcity.city, tblcity.latitude, tblcity.longitude, 
truncate(( 6371 * acos( cos( radians( 45.266708 ) ) 
* cos( radians( tblcity.latitude ) ) 
* cos( radians( tblcity.longitude ) 
- radians( -73.616257 ) ) 
+ sin( radians( 45.266708 ) ) 
* sin( radians( tblcity.latitude ) ) ) ),1) AS distance 
FROM tblcity HAVING distance < 10 ORDER BY distance desc

但是我得到的结果却相同……

然而，如果我稍微修改圣雷米的坐标，只需将纬度或经度的最后一位数字更改1，两个查询都将返回圣雷米。此外，如果我将查询中心放在上述任何其他城市之一，搜索的城市也将出现在结果中。

有人可以解释一下为什么我的上述查询不能显示搜索的圣雷米城吗？下面是表格的示例（去除了额外的字段）。

我正在使用MySQL 5.0.45，提前感谢您的帮助。

CREATE TABLE `tblcity` ( 
`IDCity` int(1) NOT NULL auto_increment, 
`City` varchar(155) NOT NULL default '', 
`Latitude` decimal(9,6) NOT NULL default '0.000000', 
`Longitude` decimal(9,6) NOT NULL default '0.000000', 
PRIMARY KEY (`IDCity`) 
) ENGINE=MyISAM AUTO_INCREMENT=52743 DEFAULT CHARSET=latin1 AUTO_INCREMENT=52743; 

INSERT INTO `tblcity` (`city`, `latitude`, `longitude`) VALUES 
('Saint-Mathieu', 45.316708, -73.516253), 
('Saint-Édouard', 45.233374, -73.516254), 
('Saint-Michel', 45.233374, -73.566256), 
('Saint-Rémi', 45.266708, -73.616257);

- Iridium52

2个回答

2

您正在使用“余弦定理球面公式”，这种公式在小距离（如零！）时容易出现舍入误差 - 请参见this discussion。输入acos（）的长表达式评估为略大于1.0，超出了界限。

以下是使用Python进行计算的问题说明：

>>> from math import sin, cos, acos, radians
>>> lat = radians(45.266708)
>>> long_expression = sin(lat) * sin(lat) + cos(lat) * cos(lat) * cos(0.0)
>>> repr(long_expression)
'1.0000000000000002'
>>> acos(long_expression)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: math domain error
>>>

看起来MySQL在抛出异常的地方代替了NULL。我对MySQL了解不多，但你应该能够通过类似于ifnull(acos(long_expression), 0.0)或coalesce(acos(long_expression), 0.0)的方式克服这个问题。

另外，你可以使用haversine公式，这将把舍入问题从你的门前转移到地球的另一侧。

更新：我已经在Python中使用该公式计算了一个点与相同点之间的应为零的距离，针对美国邮政编码文件中37582个唯一的（纬度、经度）二元组进行了测试。

其中：

- 31591个（84.1%）产生了零距离； - 4244个（11.3%）产生了9.5厘米的距离。 - 831个（2.2%）产生了13.4厘米的距离。 - 916个（2.4%）产生了“cos”值为1.0000000000000002，如果未检测到并避免，将在acos()中导致异常。

似乎明确测试lat1 = lat2和lon1 = lon2，并在这种情况下避免使用公式（只使用零）可能是一个好主意 - 这将给出一致的答案并避免困惑。

- John Machin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mike Pelley · Accepted Answer

在您的第一个查询中，我认为您在减法中颠倒了经度。球面余弦定理如下：

d = acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(long2−long1))*R

如果将lat1替换为tblcity.latitude，则必须将long1替换为tblcity.longitude。我认为您在查询中意外地替换了long2。这个是否更好？

SELECT tblcity.city, tblcity.latitude, tblcity.longitude, 
truncate((degrees(acos( sin(radians(tblcity.latitude)) 
* sin(radians(45.266708)) 
+ cos(radians(tblcity.latitude)) 
* cos(radians(45.266708)) 
* cos(radians(-73.616257 - tblcity.longitude) ) ) ) 
* 69.09*1.6),1) as distance 
FROM tblcity HAVING distance < 10 ORDER BY distance desc

我还没有研究你的第二个查询，但希望这有所帮助。