php MySQL 大圆距离（Haversine 公式）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/574691/
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
MySQL Great Circle Distance (Haversine formula)
提问by Nick Woodhams
I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:
我有一个可用的 PHP 脚本，它获取经度和纬度值，然后将它们输入到 MySQL 查询中。我想让它单独成为 MySQL。这是我当前的 PHP 代码：
if ($distance != "Any" && $customer_zip != "") { //get the great circle distance
//get the origin zip code info
$zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
$result = mysql_query($zip_sql);
$row = mysql_fetch_array($result);
$origin_lat = $row['lat'];
$origin_lon = $row['lon'];
//get the range
$lat_range = $distance/69.172;
$lon_range = abs($distance/(cos($details[0]) * 69.172));
$min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
$max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
$min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
$max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
$sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
}
Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.
有谁知道如何使这完全是 MySQL？我浏览了一些互联网，但大多数关于它的文献都令人困惑。
回答by Pavel Chuchuva
From Google Code FAQ - Creating a Store Locator with PHP, MySQL & Google Maps:
来自Google 代码常见问题 - 使用 PHP、MySQL 和 Google 地图创建商店定位器：
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
这是将查找距离 37, -122 坐标 25 英里半径内最近的 20 个位置的 SQL 语句。它根据该行的纬度/经度和目标纬度/经度计算距离，然后仅询问距离值小于25的行，按距离对整个查询进行排序，并将其限制为20个结果。要按公里而不是英里搜索，请将 3959 替换为 6371。
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
回答by Jacco
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
with latitude and longitude in radian.
以弧度为单位的纬度和经度。
so
所以
SELECT
acos(
cos(radians( $latitude0 ))
* cos(radians( $latitude1 ))
* cos(radians( $longitude0 ) - radians( $longitude1 ))
+ sin(radians( $latitude0 ))
* sin(radians( $latitude1 ))
) AS greatCircleDistance
FROM yourTable;
is your SQL query
是您的 SQL 查询
to get your results in Km or miles, multiply the result with the mean radius of Earth (3959miles,6371Km or 3440nautical miles)
要获得以公里或英里为单位的结果，请将结果乘以地球的平均半径（3959英里、6371公里或3440海里）
The thing you are calculating in your example is a bounding box. If you put your coordinate data in a spatial enabled MySQL column, you can use MySQL's build in functionalityto query the data.
您在示例中计算的是一个边界框。如果将坐标数据放在启用空间的 MySQL 列中，则可以使用MySQL 的内置功能来查询数据。
SELECT
id
FROM spatialEnabledTable
WHERE
MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))
回答by silvio
If you add helper fields to the coordinates table, you can improve response time of the query.
如果向坐标表中添加辅助字段，则可以提高查询的响应时间。
Like this:
像这样：
CREATE TABLE `Coordinates` (
`id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object',
`type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type',
`sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians',
`cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians',
`cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians',
`lat` FLOAT NOT NULL COMMENT 'latitude in degrees',
`lon` FLOAT NOT NULL COMMENT 'longitude in degrees',
INDEX `lat_lon_idx` (`lat`, `lon`)
)
If you're using TokuDB, you'll get even better performance if you add clustering indexes on either of the predicates, for example, like this:
如果您使用 TokuDB，如果您在任一谓词上添加聚簇索引，您将获得更好的性能，例如，如下所示：
alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);
You'll need the basic lat and lon in degrees as well as sin(lat) in radians, cos(lat)*cos(lon) in radians and cos(lat)*sin(lon) in radians for each point. Then you create a mysql function, smth like this:
对于每个点，您需要基本的纬度和经度以及弧度的 sin(lat)、弧度的 cos(lat)*cos(lon) 和弧度的 cos(lat)*sin(lon)。然后你创建一个mysql函数，像这样：
CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT,
`cos_cos1` FLOAT, `cos_sin1` FLOAT,
`sin_lat2` FLOAT,
`cos_cos2` FLOAT, `cos_sin2` FLOAT)
RETURNS float
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2);
END
This gives you the distance.
这给了你距离。
Don't forget to add an index on lat/lon so the bounding boxing can help the search instead of slowing it down (the index is already added in the CREATE TABLE query above).
不要忘记在 lat/lon 上添加索引，以便边界装箱可以帮助搜索而不是减慢搜索速度（索引已添加到上面的 CREATE TABLE 查询中）。
INDEX `lat_lon_idx` (`lat`, `lon`)
Given an old table with only lat/lon coordinates, you can set up a script to update it like this: (php using meekrodb)
给定一个只有纬度/经度坐标的旧表，您可以设置一个脚本来更新它：（使用 meekrodb 的 php）
$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates');
foreach ($users as $user)
{
$lat_rad = deg2rad($user['lat']);
$lon_rad = deg2rad($user['lon']);
DB::replace('Coordinates', array(
'object_id' => $user['id'],
'object_type' => 0,
'sin_lat' => sin($lat_rad),
'cos_cos' => cos($lat_rad)*cos($lon_rad),
'cos_sin' => cos($lat_rad)*sin($lon_rad),
'lat' => $user['lat'],
'lon' => $user['lon']
));
}
Then you optimize the actual query to only do the distance calculation when really needed, for example by bounding the circle (well, oval) from inside and outside. For that, you'll need to precalculate several metrics for the query itself:
然后您优化实际查询以仅在真正需要时进行距离计算，例如通过从内部和外部对圆（井、椭圆）进行界定。为此，您需要为查询本身预先计算几个指标：
// assuming the search center coordinates are $lat and $lon in degrees
// and radius in km is given in $distance
$lat_rad = deg2rad($lat);
$lon_rad = deg2rad($lon);
$R = 6371; // earth's radius, km
$distance_rad = $distance/$R;
$distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box
$dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box
$dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat)));
$dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box
$dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));
Given those preparations, the query goes something like this (php):
考虑到这些准备工作，查询类似于这样 (php)：
$neighbors = DB::query("SELECT id, type, lat, lon,
geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance
FROM Coordinates WHERE
lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d
HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d",
// center radian values: sin_lat, cos_cos, cos_sin
sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad),
// min_lat, max_lat, min_lon, max_lon for the outside box
$lat-$dist_deg_lat,$lat+$dist_deg_lat,
$lon-$dist_deg_lon,$lon+$dist_deg_lon,
// min_lat, max_lat, min_lon, max_lon for the inside box
$lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small,
$lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small,
// distance in radians
$distance_rad);
EXPLAIN on the above query might say that it's not using index unless there's enough results to trigger such. The index will be used when there's enough data in the coordinates table. You can add FORCE INDEX (lat_lon_idx) to the SELECT to make it use the index with no regards to the table size, so you can verify with EXPLAIN that it is working correctly.
上面查询的 EXPLAIN 可能会说它不使用索引，除非有足够的结果来触发它。当坐标表中有足够的数据时，将使用索引。您可以将 FORCE INDEX (lat_lon_idx) 添加到 SELECT 以使其使用索引而不考虑表大小，因此您可以使用 EXPLAIN 验证它是否正常工作。
With the above code samples you should have a working and scalable implementation of object search by distance with minimal error.
使用上面的代码示例，您应该能够以最小的错误实现按距离进行对象搜索的有效且可扩展的实现。
回答by O. Jones
I have had to work this out in some detail, so I'll share my result. This uses a ziptable with latitudeand longitudetables. It doesn't depend on Google Maps; rather you can adapt it to any table containing lat/long.
我必须详细地解决这个问题，所以我将分享我的结果。这使用了一个zip表latitude和longitude表。它不依赖于谷歌地图；相反，您可以将其调整为包含纬度/经度的任何表格。
SELECT zip, primary_city,
latitude, longitude, distance_in_mi
FROM (
SELECT zip, primary_city, latitude, longitude,r,
(3963.17 * ACOS(COS(RADIANS(latpoint))
* COS(RADIANS(latitude))
* COS(RADIANS(longpoint) - RADIANS(longitude))
+ SIN(RADIANS(latpoint))
* SIN(RADIANS(latitude)))) AS distance_in_mi
FROM zip
JOIN (
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
) AS p
WHERE latitude
BETWEEN latpoint - (r / 69)
AND latpoint + (r / 69)
AND longitude
BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
) d
WHERE distance_in_mi <= r
ORDER BY distance_in_mi
LIMIT 30
Look at this line in the middle of that query:
查看该查询中间的这一行：
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
This searches for the 30 nearest entries in the ziptable within 50.0 miles of the lat/long point 42.81/-70.81 . When you build this into an app, that's where you put your own point and search radius.
这将搜索zip表中距离纬度/经度点 42.81/-70.81 50.0 英里范围内最近的 30 个条目。当您将其构建到应用程序中时，您就可以在其中放置自己的点和搜索半径。
If you want to work in kilometers rather than miles, change 69to 111.045and change 3963.17to 6378.10in the query.
如果您想以公里而不是英里为单位工作，请在查询中更改69为111.045和更改3963.17为6378.10。
Here's a detailed writeup. I hope it helps somebody. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
这是一个详细的写法。我希望它可以帮助某人。 http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
回答by Harish Lalwani
SELECT *, (
6371 * acos(cos(radians(search_lat)) * cos(radians(lat) ) *
cos(radians(lng) - radians(search_lng)) + sin(radians(search_lat)) * sin(radians(lat)))
) AS distance
FROM table
WHERE lat != search_lat AND lng != search_lng AND distance < 25
ORDER BY distance
FETCH 10 ONLY
for distance of 25 km
距离 25 公里
回答by John Crenshaw
I can't comment on the above answer, but be careful with @Pavel Chuchuva's answer. That formula will not return a result if both coordinates are the same. In that case, distance is null, and so that row won't be returned with that formula as is.
我无法对上述答案发表评论，但请注意@Pavel Chuchuva 的答案。如果两个坐标相同，该公式将不会返回结果。在这种情况下，距离为空，因此该行不会按原样使用该公式返回。
I'm not a MySQL expert, but this seems to be working for me:
我不是 MySQL 专家，但这似乎对我有用：
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM markers HAVING distance < 25 OR distance IS NULL ORDER BY distance LIMIT 0 , 20;
回答by Abdul Manaf
I have written a procedure that can calculate the same, but you have to enter the latitude and longitude in the respective table.
我写了一个可以计算相同的程序，但是你必须在相应的表中输入纬度和经度。
drop procedure if exists select_lattitude_longitude;
delimiter //
create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))
begin
declare origin_lat float(10,2);
declare origin_long float(10,2);
declare dest_lat float(10,2);
declare dest_long float(10,2);
if CityName1 Not In (select Name from City_lat_lon) OR CityName2 Not In (select Name from City_lat_lon) then
select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;
else
select lattitude into origin_lat from City_lat_lon where Name=CityName1;
select longitude into origin_long from City_lat_lon where Name=CityName1;
select lattitude into dest_lat from City_lat_lon where Name=CityName2;
select longitude into dest_long from City_lat_lon where Name=CityName2;
select origin_lat as CityName1_lattitude,
origin_long as CityName1_longitude,
dest_lat as CityName2_lattitude,
dest_long as CityName2_longitude;
SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;
end if;
end ;
//
delimiter ;
回答by Sam Vloeberghs
I thought my javascript implementation would be a good reference to:
我认为我的 javascript 实现将是一个很好的参考：
/*
* Check to see if the second coord is within the precision ( meters )
* of the first coord and return accordingly
*/
function checkWithinBound(coord_one, coord_two, precision) {
var distance = 3959000 * Math.acos(
Math.cos( degree_to_radian( coord_two.lat ) ) *
Math.cos( degree_to_radian( coord_one.lat ) ) *
Math.cos(
degree_to_radian( coord_one.lng ) - degree_to_radian( coord_two.lng )
) +
Math.sin( degree_to_radian( coord_two.lat ) ) *
Math.sin( degree_to_radian( coord_one.lat ) )
);
return distance <= precision;
}
/**
* Get radian from given degree
*/
function degree_to_radian(degree) {
return degree * (Math.PI / 180);
}
回答by IMRA
calculate distance in Mysql
在Mysql中计算距离
SELECT (6371 * acos(cos(radians(lat2)) * cos(radians(lat1) ) * cos(radians(long1) -radians(long2)) + sin(radians(lat2)) * sin(radians(lat1)))) AS distance
thus distance value will be calculated and anyone can apply as required.
因此将计算距离值，任何人都可以根据需要申请。
