在MySQL中使用纬度和经度查找两点之间的距离

Question

嗨，我有以下表格：

 --------------------------------------------
 |  id  |  city  |  Latitude  |  Longitude  |
 --------------------------------------------
 |  1   |   3    |   34.44444 |   84.3434   |
 --------------------------------------------
 |  2   |   4    | 42.4666667 | 1.4666667   |
 --------------------------------------------
 |  3   |   5    |  32.534167 | 66.078056   |
 --------------------------------------------
 |  4   |   6    |  36.948889 | 66.328611   |
 --------------------------------------------
 |  5   |   7    |  35.088056 | 69.046389   |
 --------------------------------------------
 |  6   |   8    |  36.083056 |   69.0525   |
 --------------------------------------------
 |  7   |   9    |  31.015833 | 61.860278   |
 --------------------------------------------

现在我想要获取两个点之间的距离。比如用户A位于城市3，用户B位于城市7。我的场景是：一个用户拥有一个城市和纬度经度，并且正在搜索与他所在城市距离的其他用户。例如，拥有城市3的用户正在搜索。他想要获得任何其他城市的用户的距离，例如城市7。我已经搜索并找到以下查询：

SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)

据我所知，这个查询会找到一个点到所有其他点的距离。现在，我想要得到一个点到另一个点的距离。

非常感谢任何指导。

- Enthusiast

---

所以对表进行自连接以获取两组坐标，然后在这两个点上运行计算即可。 - Marc B

你有没有考虑将这个从SQL查询中取出，使用Google Maps API来完成呢？我知道这并没有回答你的问题。但是你可能会发现这项工作已经为你完成了。 - durbnpoisn

@MarcB 没有明白你的意思，亲。 - Enthusiast

@durbnpoisn 不，我不想要那个解决方案。这是我的要求。 - Enthusiast

@Enthusiast - 没关系。我说过我知道那不是答案，只是提供了一个替代方案。 - durbnpoisn

Answer 1

我认为你的问题表明你已经拥有了两个城市的city值，想要计算它们之间的距离。

这个查询可以完成任务，并返回以公里为单位的距离。它使用了球面余弦定理公式。

请注意，你将表格与自身连接，以便检索出两个坐标对用于计算。

SELECT a.city AS from_city, b.city AS to_city, 
   111.111 *
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
         * COS(RADIANS(b.Latitude))
         * COS(RADIANS(a.Longitude - b.Longitude))
         + SIN(RADIANS(a.Latitude))
         * SIN(RADIANS(b.Latitude))))) AS distance_in_km
  FROM city AS a
  JOIN city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

请注意，常数111.1111是每纬度公里数，基于旧版拿破仑定义的米为赤道到极点距离的万分之一。这个定义对于定位工作已足够精确。 如果您想使用英里而不是公里，请使用69.0。

http://sqlfiddle.com/#!9/21e06/412/0

如果您想寻找附近的地点，您可能会尝试使用类似这样的从句：

   HAVING distance_in_km < 10.0    /* slow ! */
    ORDER BY distance_in_km DESC

这就像我们在美国马萨诸塞州波士顿附近所说的那样，非常慢。

在这种情况下，您需要使用边界框计算。请参阅有关如何执行此操作的说明。http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

公式包含一个LEAST()函数。为什么？因为如果ACOS()函数的参数稍微大于1，它会抛出错误。当两个相关点非常接近时，由于浮点数epsilon（不精确），带有COS()和SIN()计算的表达式有时可能产生略大于1的值。 LEAST（1.0，dirty-great-expression）调用处理该问题。

有一种更好的方式，是由Thaddeus Vincenty提出的formula。它使用ATAN2()而不是ACOS()，因此对epsilon问题更不敏感。

---

2022年编辑（由Alexio Vay完成）：截至今日，现代解决方案应该是以下简短代码：

   select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)) 

请查看Naresh Kumar的答案。

- O. Jones

---

你在这里写的和在sqlfiddle上写的不一样。虽然两者都能工作，但是sqlfiddle的结果更准确。 - Mubasshir Pawle

使用这段代码查询了2个坐标之间最近的位置。运行良好！ - El Dude

当我在mysql中使用这个公式时，对于非常接近的距离，我得到了null值。我的解决方法是添加一个"LEAST"函数来防止越界的数字。公式如下：DEGREES(ACOS(LEAST(COS(RADIANS(lat)) * COS(RADIANS(google_lat)) * COS(RADIANS(lng - google_lng)) + SIN(RADIANS(lat)) * SIN(RADIANS(google_lat)),1.0)))，它表示距离的单位为公里。 - Robert

谢谢您的回答。您能解释一下为什么要使用最小函数并插入1.0吗？ - haneulkim

makewhite，好问题！请查看@Robert的评论和我的最近编辑。 - O. Jones

Answer 2

你可以使用ST_Distance_Sphere() MySQL内置函数，支持MySQL 5.7及以上版本。它能更有效地计算以米为单位的距离。

select ST_Distance_Sphere(point(lng, lat), point(lng,lat))

i.e.

    select ST_Distance_Sphere(
        point(-87.6770458, 41.9631174),
        point(-73.9898293, 40.7628267)
    ) 

参考自使用MySQL计算距离

- Naresh Kumar

---

这个答案需要更加更新，符合现代的标准。这是一种现代化的解决方案。 - AlexioVay

请注意，结果以米为单位给出，因此您可能需要进行转换。 - arrmani88

此答案仅提供大致距离，而非准确距离。@O. Jones的方法使用余弦定理可得到与Google地图距离计算器完全相同的精确距离。 - Vinith

在我的测试中，ST_Distance_Sphere 方法比使用长 COS 项的已接受答案慢3-5倍。使用 ST_Distance_Sphere 方法计算我16480个邮政编码与我的位置之间的距离需要160毫秒，而使用 cos 项仅需要63毫秒。 - heiwil

这里的结果相似，但比较慢，慢了1.73倍。在GCP上，使用1个vCPU和614.4 MB RAM的MySQL 8.0计算~100万条记录，ST_Distance_Sphere花费了2.04秒。而使用被接受的答案只需要1.18秒。我会选择使用ST_Distance_Sphere来提高可读性，如果需要优化的话，以后再使用被接受的答案。（附注：我的测试不包括网络时间，只包括计算时间。下载1百万行数据的网络时间会更长。） - IcyIcicle

Answer 3

这是一个用于获取两个纬度和经度之间距离的MySQL查询和函数，并且距离以公里为单位返回。

MySQL查询：

SELECT (6371 * acos( 
                cos( radians(lat2) ) 
              * cos( radians( lat1 ) ) 
              * cos( radians( lng1 ) - radians(lng2) ) 
              + sin( radians(lat2) ) 
              * sin( radians( lat1 ) )
        ) ) as distance 
FROM your_table;

MySQL函数：

DELIMITER $$
CREATE FUNCTION `getDistance`(`lat1` VARCHAR(200), `lng1` VARCHAR(200), `lat2` VARCHAR(200), `lng2` VARCHAR(200)) RETURNS varchar(10) CHARSET utf8
begin
declare distance varchar(10);

set distance = (select (6371 * acos( 
                                     cos( radians(lat2) ) 
                                   * cos( radians( lat1 ) ) 
                                   * cos( radians( lng1 ) - radians(lng2) )       
                                   + sin( radians(lat2) ) 
                                   * sin( radians( lat1 ) )
                ) ) as distance); 

if(distance is null)
then
 return '';
else 
return distance;
end if;
end$$
DELIMITER ;

---

如何在您的 PHP 代码中使用

SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance 
FROM your_table.

- Umesh Jee

---

近十年后，这个函数给出的结果与谷歌地图距离测量的结果完全相同。谢谢！ - fernandojmartin

Answer 4

这是一个MySQL函数，它将获取两个纬度/经度对，并给出这两个点之间的距离（以角度计算）。它使用Haversine公式计算距离。由于地球不是完美的球体，在极点和赤道附近存在一些误差。

• 要转换为英里，请乘以3961。
• 要转换为公里，请乘以6373。
• 要转换为米，请乘以6373000。
• 要转换为英尺，请乘以(3961 * 5280) 20914080。

DELIMITER $$

CREATE FUNCTION \`haversine\`(

        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS float
    NO SQL
    DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude. To get miles, multiply by 3961, and km by 6373'

BEGIN

    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));

END;

DELIMITER;

- Sloan Thrasher

---

请注意，值3961是您向北或向南移动一弧度时在地球表面行驶的距离。 - O. Jones

我认为应该是6371而不是6373。 - Buffalo

如果要转换为英里/公里等，请不要使用DEGREES函数调用，因为转换系数是以弧度为单位的，因此在乘以转换系数之前不需要转换为度数。 - Andy

感谢您提供有关转换的信息。 - RahulAN

Answer 5

不确定您的距离计算是如何进行的，但您需要使用self join表格并相应地执行计算。大概就像这样：

select t1.id as userfrom, 
t2.id as userto, 
( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( t1.Latitude ) ) * 
cos( radians( t1.Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * 
sin( radians( t2.Latitude ) ) ) ) AS `distance` 
from table1 t1 
inner join table1 t2 on t2.city > t1.city

- Rahul

Answer 6

重要提示！任何使用或复制这些计算的人，请确保在将计算传递给 acos() 函数时使用 least(1.0, (...))。 acos() 函数不会接受大于 1 的值，当比较完全相同的纬度/经度值时，有时计算结果会变成类似于 1.000002 的数字。这将产生一个 NULL 距离而不是 0，并且根据查询结构可能无法返回您要查找的结果！

以下是正确的：

select round( 
  ( 3959 * acos( least(1.0,  
    cos( radians(28.4597) ) 
    * cos( radians(lat) ) 
    * cos( radians(lng) - radians(77.0282) ) 
    + sin( radians(28.4597) ) 
    * sin( radians(lat) 
  ) ) ) 
), 1) as distance
from locations having distance <= 60 order by distance

这是错误的:

select round( 
  ( 3959 * acos( 
    cos( radians(28.4597) ) 
    * cos( radians(lat) ) 
    * cos( radians(lng) - radians(77.0282) ) 
    + sin( radians(28.4597) ) 
    * sin( radians(lat) 
  ) ) 
), 1) as distance
from locations having distance <= 60 order by distance

最高评分的答案也谈到了这一点，但我想确保这非常清楚，因为我刚刚发现我的查询中存在一个长期存在的错误。

- Scott

Answer 7

这是我从https://www.geodatasource.com/developers/javascript转换的公式。

它是一个干净整洁的函数，用于计算距离（单位为千米）。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `FN_GET_DISTANCE`(
lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE
) RETURNS double
BEGIN
    DECLARE radlat1 DOUBLE;
    DECLARE radlat2 DOUBLE;
    DECLARE theta DOUBLE;
    DECLARE radtheta DOUBLE;
    DECLARE dist DOUBLE;
    SET radlat1 = PI() * lat1 / 180;
    SET radlat2 = PI() * lat2 / 180;
    SET theta = lng1 - lng2;
    SET radtheta = PI() * theta / 180;
    SET dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);
    SET dist = acos(dist);
    SET dist = dist * 180 / PI();
    SET dist = dist * 60 * 1.1515;
    SET dist = dist * 1.609344;
RETURN dist;
END$$
DELIMITER ;

您还可以在该网站上找到不同语言版本的相同功能；

- user2288580

Answer 8

也许对某些人有帮助，我成功地通过FN_GET_DISTANCE函数实现了我的任务：

SELECT SUM (t.distance) as Distance FROM
(SELECT (CASE WHEN (FN_GET_DISTANCE (Latitude, Longitude, @OLDLatitude, @OLDLongitude)) BETWEEN 0.01 AND 2 THEN
FN_GET_DISTANCE (Latitude, Longitude, @OLDLatitude, @OLDLongitude) ELSE 0 END) AS distance,
IF (@OLDLatitude IS NOT NULL, @OLDLatitude: = Latitude, 0),
IF (@OLDLongitude IS NOT NULL, @OLDLongitude: = Longitude, 0)
FROM `data`, (SELECT @OLDLatitude: = 0) var0, (SELECT @OLDLongitude: = 0) var1
WHERE ID_Dev = 1
AND DateTime BETWEEN '2021-05-23 08:00:00' AND '2021-05-23 20:00:00'
ORDER BY ID DESC) t;

- Юрий Гусак