地理位置 SQL 查询未找到确切位置 [英] Geolocation SQL query not finding exact location

问题描述

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

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  

但是当我在 Saint-Rémi 市运行查询时，使用以下查询...

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

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  

搜索中缺少 Saint-Rémi 镇.

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

所以我尝试了一个修改后的查询，希望得到更好的结果:

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

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 

但是我得到了同样的结果......

But I get the same result...

但是，如果我通过将 lat 或 long 的最后一位数字更改 1 来稍微修改 Saint-Rémi 的坐标，则两个查询都将返回 Saint-Rémi.此外，如果我将查询集中在上面的任何其他城市上，则搜索到的城市将在结果中返回.

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.

我正在使用 MySQL 5.0.45，在此先感谢.

I'm using MySQL 5.0.45, thanks in advance.

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

推荐答案

在您的第一个查询中，我相信您已经在减法中反转了经度.余弦球面定律是:

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

如果 lat1 替换为 tblcity.latitude，long1 必须替换为 tblcity.longitude.我认为您在查询中不小心替换了 long2 .这个更好用吗?

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.

这篇关于地理位置 SQL 查询未找到确切位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！