Mysql 同时比较两列(经纬度)获得最接近的数字结果 [英] Mysql get closest numeric result comparing two columns (lat and long) at same time

问题描述

我有一个带有经度和纬度的城市，以及一个包含经度和纬度的城市名称数据库.由于可能有多个城市具有相同的名称，因此我想匹配地理上最接近的城市.

I have a city with longitude and latitude, and a database of city names, also with longitude and latitude. Since there can be several cities with the same name, I want to match the one that is geographically closest.

举个例子，我有纽约，纬度为 40.7262，经度为 -73.9796.我想找到离NY最近的Bangor城市，db里有几个:

To give an example, I have New York with a lat of 40.7262 and a long of -73.9796. I want to find the closest city of Bangor to NY, and there are several in the db:

Bangor  PA  40.86555560 -75.20694440
Bangor  NY  44.81222220 -74.39777780
Bangor  ME  44.80111110 -68.77833330

我可以通过此查询获得最近的纬度:

I can get the closest latitude with this query:

Select * from cities 
where city='bangor'  
order by abs(Latitude - 40.7262) limit 1;

我可以通过这个查询获得最近的经度:

and I can get the closest longitude with this query:

Select * from cities 
where city='bangor' 
order by abs(Longitude - -73.9796) limit 1;

但这并不能让我确定最近的城市，因为纽约州班戈在一个案例中获胜，而班戈宾夕法尼亚州又在另一个案例中获胜.考虑到纬度和经度，我如何编写查询以找到最近的城市?

but that does NOT get me the definitive closest city because Bangor NY wins in one case and Bangor PA wins another. How can I write my query to find the closest city taking into account BOTH lat and long?

推荐答案

我们可以使用 Haversine 公式 确定地图上两点之间的距离，给定它们的纬度和经度.您可以通过以下链接获得更多详细信息:http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

We can use Haversine formula to determine the distance between two points on a map, given their latitudes and longitudes. You can get more details at this link: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

我们使用参考链接中描述的公式确定距 Bangor 城市的公里距离，然后根据计算出的距离ORDER BY.LIMIT 1 允许我们考虑最近的城市.

We determine the distance in km from the Bangor city, using the formula described in the referred link, and then ORDER BY on the calculated distance. LIMIT 1 allows us to consider the closest city.

SELECT *, 
       111.045 *
       DEGREES(ACOS(COS(RADIANS(40.7262))
         * COS(RADIANS(Latitude))
         * COS(RADIANS(-73.9796 - Longitude))
         + SIN(RADIANS(40.7262))
         * SIN(RADIANS(Latitude)))) AS distance_in_km
FROM cities 
WHERE city='bangor'  
ORDER BY distance_in_km LIMIT 1;

这篇关于Mysql 同时比较两列(经纬度)获得最接近的数字结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！