地理位置SQL查询找不到确切位置

问题描述：

我一直在测试我的地理位置查询一段时间，直到现在我还没有发现任何问题。地理位置SQL查询找不到确切位置

我想搜索给定半径范围内的所有城市，我经常使用该城市的坐标搜索城市周围的城市，但最近我尝试搜索城市周围，发现城市本身并未返回。

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

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

但我通过改变得到同样的结果...

但是，如果我修改圣 - 雷米的COORDS性能稍微lat或long的最后一位数字加1，这两个查询都会返回Saint-Rémi。另外，如果我将查询集中在上述任何其他城市中，搜索到的城市将返回结果中。

任何人都可以阐明什么可能会导致我的查询上面，以不显示搜索到的Saint-Rémi城市？我在下面添加了一个表格样本（删除了额外的字段）。

我使用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);

答

在你的第一个查询中，我相信你已经在减法中颠倒了经度。余弦的球形法是：

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

我还没有看过你的第二个查询，但希望这有助于。

感谢迈克，我试过了你所建议的查询，我想出了同样的结果，圣雷米镇仍然被省略。我不会声称我知道查询背后的数学，我不得不谷歌它才能创建它，但上面的等式确实有助于我更好地理解查询背后的数学。谢谢！ – Iridium52 2010-04-23 00:54:22

我之前很懒，没有真正测试过。但听到它失败了，我很惊讶，所以现在我尝试了它。无可否认，你通过提供SQL输入来创建表格变得很容易。无论如何，它的工作！不知道现在该说些什么......; o） – 2010-04-23 01:13:03

嗯。即使经度相反，您的原始查询也适用于我，这是有道理的，因为减法将变为零。现在我不知道为什么它不适合你。你能用“HAVING city ='Saint-Rémi'替换”HAVING distance 2010-04-23 01:19:48

答

您正在使用“球形余弦定律”公式，该公式在小距离处（如零！）容易舍入误差 - 请参阅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)的方法来克服此问题。

或者，您可以使用半正定公式，它将舍入问题从您的门阶转移到地球的另一侧。

更新：我测试过在Python中使用该公式计算点和相同点之间应该为零的距离，对于a中的每个37582个唯一（lat，lon）2元组file of US zip codes。

其中：

31591（84.1％）产生的零距离
4244（11.3％）产生9.5厘米的距离。
831（2.2％）产生了13.4厘米的距离。
916（2.4％）产生了一个1.0000000000000002的“cos”值，如果没有被检测到和避免，会导致acos（）中发生异常。

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

地理位置SQL查询找不到确切位置

相关推荐