地理位置SQL查询找不到确切的位置(Geolocation SQL query not finding exact location)

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

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

我将这些城市作为我的数据库的摘录：

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

但当我在Saint-Rémi市周围运行查询时，请查询以下内容......

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来修改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);

I have been testing my geolocation query for some time now and I haven't found any issues with it until now.

I am trying to search for all cities within a given radius, often times I'm searching for cities surrounding a city using that city's coords, but recently I tried searching around a city and found that the city itself was not returned.

I have these cities as an excerpt in my database:

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

But when I run my query around the city of Saint-Rémi, with the following query...

I get these results:

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

The town of Saint-Rémi is missing from the search.

So I tried a modified query hoping to get a better result:

But I get the same result...

However, if I modify Saint-Rémi's coords slighly by changing the last digit of the lat or long by 1, both queries will return Saint-Rémi. Also, if I center the query on any of the other cities above, the searched city is returned in the results.

Can anyone shed some light on what may be causing my queries above to not display the searched city of Saint-Rémi? I have added a sample of the table (with extra fields removed) below.

I'm using MySQL 5.0.45, thanks in advance.

最满意答案

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

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

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

In your first query, I believe you've inverted the longitudes in the subtraction. The Spherical Law of Cosines is:

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

If lat1 is substituted with tblcity.latitude, long1 must be substituted with tblcity.longitude. I think you've accidentally substituted long2 in your query. Does this one work better?

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

I haven't looked into your second query yet, but hopefully that helps.

更多推荐