MySQL 在mysql中使用纬度和经度查找两点之间的距离
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24370975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find distance between two points using latitude and longitude in mysql
提问by Enthusiast
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。我的场景是一个用户有一个城市，纬度和经度正在搜索其他用户与他的城市的距离。例如，拥有城市 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.
任何指导方针将不胜感激。
回答by O. Jones
I think your question says you have the cityvalues for the two cities between which you wish to compute the distance.
我认为您的问题是说您拥有city要计算距离的两个城市的值。
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
Notice that the constant 111.1111is 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.0instead.
如果您想要法定英里而不是公里，请69.0改用。
http://sqlfiddle.com/#!9/21e06/412/0
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.
那是（正如我们在美国马萨诸塞州波士顿附近所说的）非常缓慢。
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/
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.
该公式包含一个LEAST()函数。为什么？因为如果ACOS()函数的参数甚至略大于 1，该函数就会抛出错误。 当所讨论的两个点非常接近时，由于浮点 epsilon（不准确），带有COS()和SIN()计算的表达式有时会产生略大于 1 ）。该呼叫与问题对应。LEAST(1.0, dirty-great-expression)
There's a better way, a formulaby Thaddeus Vincenty. It uses ATAN2()rather than ACOS()so it's less susceptible to epsilon problems.
有一个更好的方法，一个由Thaddeus Vincenty 提出的公式。它使用而不是因此它不太容易受到 epsilon 问题的影响。ATAN2()ACOS()
回答by Umesh Jee
Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM.
这是用于获取两个纬度和经度之间距离的 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(lat1,lng1,$lat2,$lng2) as distance
FROM your_table.
回答by Sloan Thrasher
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.
- To convert to kilometers, multiply by 6373.
- To convert to meters, multiply by 6373000.
- To convert to feet, multiply by (3961 * 5280) 20914080.
- 要转换为英里，请乘以 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;
回答by Naresh Kumar
You can use the ST_Distance_SphereMySql build-in function. It computes the distance in meters more efficiently.
您可以使用ST_Distance_SphereMySql 内置函数。它以米为单位更有效地计算距离。
Supported since MySql 5.7 version and above.
自 MySql 5.7 及以上版本支持。
select ST_Distance_Sphere(
point(-87.6770458, 41.9631174),
point(-73.9898293, 40.7628267))
Referred from Calculating distance using MySQL
回答by Rahul
Not sure how your distance calculation is going on but you need to do a self joinyour table and perform the calculation accordingly. Something like this probably
不确定您的距离计算是如何进行的，但您需要做一个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
回答by user2288580
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;
您还可以在网站上找到不同语言的相同功能；
回答by Scott
IMPORTANT! Anyone using or copying these calculations MAKE SURE to use least(1.0, (...))when passing the calculation to the acos()function. The acos()function will NOT take a value above 1 and I have found when comparing lat/lng values that are identical there are times when the calculations come out to something like 1.000002. This will produce a distance of NULLinstead of 0and may not return results you're looking for depending on how your query is structured!
重要的！任何使用或复制这些计算的人请确保least(1.0, (...))在将计算传递给acos()函数时使用。该acos()函数不会采用大于 1 的值，我发现在比较相同的 lat/lng 值时，有时计算结果类似于1.000002. 这将产生一个距离NULL而不是，0并且可能不会返回您正在寻找的结果，具体取决于您的查询结构！
This is CORRECT:
这是对的：
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
This is WRONG:
这是错误的：
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
The highest rated answer also talks about this, but I wanted to make sure this was very clear since I just found a long standing bug in my query.
评分最高的答案也谈到了这一点，但我想确保这一点非常清楚，因为我刚刚在查询中发现了一个长期存在的错误。
