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

Hi I have the following table

嗨，我有下表

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

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

现在我希望得到两点之间的距离。假设一个用户正在拥有一个城市3并且一个用户正在拥有一个城市7.我的场景是一个用户拥有一个城市，而latracy和longtitude正在搜索其他用户与他的城市的距离。例如，具有城市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)

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.

任何指南都将非常感谢。

5 个解决方案

#1

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.

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

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

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.

请注意，常数111.1111是每纬度纬度的公里数，基于旧的拿破仑定量仪表作为从赤道到极点的距离的万分之一。这个定义足够接近定位器工作。

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

如果您想要法定里程而不是公里，请使用69.0代替。

http://sqlfiddle.com/#!2/abcc8/4/0

http://sqlfiddle.com/#!2/abcc8/4/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.

那就是（正如我们在美国波士顿附近所说的那样）邪恶的。

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/

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

#2

Not sure how your distance calculation is going on but you need to do a self join your table and perform the calculation accordingly. Something like this probably

不确定您的距离计算是如何进行的，但您需要自行加入您的表并相应地执行计算。可能是这样的

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

#3

Here's a MySQL function that will take two latitude/longitude pairs, and give you the distance in degrees between the two points. It uses the Haversine formula to calculate the distance. Since the Earth is not a perfect sphere, there is some error near the poles and the equator.

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

• To convert to miles, multiply by 3961.
• 要转换为里程，请乘以3961。
• To convert to kilometers, multiply by 6373.
• 要转换为公里数，请乘以6373。
• To convert to meters, multiply by 6373000.
• 要转换为米，请乘以6373000。

• To convert to feet, multiply by (3961 * 5280) 20914080.

  要转换为英尺，请乘以（3961 * 5280）20914080。

  DELIMITER $$

  DELIMITER $$

  CREATE FUNCTION `haversine`(

  创造功能`hasrsine`（

      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;

  DELIMITER;

#4

Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM.

Heres是MySQL查询和功能，它用于获取两个经纬度之间的距离，距离将在KM中返回。

Mysql Query :-

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

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 ;

---

How to use in your PHP Code

如何在PHP代码中使用

SELECT getDistance(lat,lng,$lat,$lng) as distance 
FROM your_table.

#5

Here's a formula I converted from https://www.geodatasource.com/developers/javascript

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

It's a nice clean function that calculates the distance in KM

这是一个很好的清洁功能，可以计算KM的距离

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 ;

You'll also find the same function in different languages on the site;

您还可以在网站上找到不同语言的相同功能;