在mysql中使用经度和纬度查找两点之间的距离 [英] Find distance between two points using latitude and longitude in 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是由于浮点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.
还有一种更好的方法，通过公式 Thaddeus Vincenty .它使用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.
这篇关于在mysql中使用经度和纬度查找两点之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
