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

2021-11-20 00:00:00 latitude-longitude mysql

我有下表

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

现在我想获得两点之间的距离.假设一个用户有一个城市 3，一个用户有一个城市 7.我的场景是一个用户有一个城市，纬度和经度正在搜索其他用户与他的城市的距离.例如，拥有城市 3 的用户正在搜索.他想得到任何其他城市的用户的距离说是7.我搜索并找到了以下查询

Now I want to get distance between two points. Say a user is having a city 3 and a user is having a city 7. My scenario is one user having a city and latitue and longtitude is searching other users distance from his city. For example user having city 3 is searching. He wants to get distance of user of any other city say it is 7. I have searched and found following query

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)

据我所知，此查询查找从一个点到所有其他点的距离.现在我想获得从一点到另一点的距离.

As for as I know this query finds distance from one point to all other points. Now I want to get distance from one point to other point.

任何指导方针将不胜感激.

Any guide line will be much appreciated.

推荐答案

我认为您的问题是说您拥有要计算距离的两个城市的 city 值.

I think your question says you have the city values for the two cities between which you wish to compute the distance.

此查询将为您完成这项工作，生成以公里为单位的距离.它使用球面余弦定律公式.

This query will do the job for you, yielding the distance in km. It uses the spherical cosine law formula.

请注意，您将表格与其自身连接起来，以便您可以检索两个坐标对进行计算.

Notice that you join the table to itself so you can retrieve two coordinate pairs for the computation.

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 是每度纬度的公里数，基于旧的拿破仑对米的定义，即从赤道到极点距离的千分之一.该定义对于定位器工作来说已经足够接近了.

Notice that the constant 111.1111 is the number of kilometres per degree of latitude, based on the old Napoleonic definition of the metre as one ten-thousandth of the distance from the equator to the pole. That definition is close enough for location-finder work.

如果您想要法定英里而不是公里，请改用 69.0.

If you want statute miles instead of kilometres, use 69.0 instead.

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

如果您正在寻找附近的点，您可能会想使用这样的子句:

If you're looking for nearby points you may be tempted to use a clause something like this:

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

那是(正如我们在美国马萨诸塞州波士顿附近所说)非常缓慢.

That is (as we say near Boston MA USA) wicked slow.

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

In that case you need to use a bounding box computation. See this writeup about how to do that. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

该公式包含一个 LEAST() 函数.为什么?因为如果 ACOS() 函数的参数甚至略大于 1 就会抛出错误. 当所讨论的两点非常接近时，带有 COS() 和 SIN() 计算有时会产生略大于 1 的值，这是由于 浮动-point epsilon(不准确).LEAST(1.0,dirty-great-expression) 调用解决了这个问题.

The formula contains a LEAST() function. Why? Because the ACOS() function throws an error if its argument is even slightly greater than 1. When the two points in question are very close together, the expression with the COS() and SIN() computations can sometimes yield a value slightly greater than 1 due to floating-point epsilon (inaccuracy). The LEAST(1.0, dirty-great-expression) call copes with that problem.

有一个更好的方法，公式 撒迪厄斯·文森蒂.它使用 ATAN2() 而不是 ACOS() 所以它不太容易受到 epsilon 问题的影响.

There's a better way, a formula by Thaddeus Vincenty. It uses ATAN2() rather than ACOS() so it's less susceptible to epsilon problems.

相关文章