大圆距离(半正矢公式)

小开

最佳答案

下面是 SQL 语句，它将查找距离37，-122坐标半径25英里范围内最近的20个位置。它根据该行的纬度/经度和目标纬度/经度计算距离，然后只请求距离值小于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;

小开

$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));

经纬度是弧度。

所以

SELECT
acos(
cos(radians( $latitude0 ))
* cos(radians( $latitude1 ))
* cos(radians( $longitude0 ) - radians( $longitude1 ))
+ sin(radians( $latitude0 ))
* sin(radians( $latitude1 ))
) AS greatCircleDistance
FROM yourTable;

是您的 SQL 查询

要得到以公里或英里为单位的结果，请将结果乘以地球的平均半径(3959英里、 6371公里或 3440海里)

您在示例中计算的是一个边界框。如果将坐标数据放在支持空间的 MySQL 列中，则可以使用 MySQL 的内建功能查询数据。

SELECT
id
FROM spatialEnabledTable
WHERE
MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))

小开

我已经写了一个程序，可以计算相同的, 但你必须在相应的表格中输入经纬度。

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 ;

小开

如果将帮助器字段添加到坐标表中，则可以提高查询的响应时间。

像这样:

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

如果您正在使用 TokuDB，那么如果添加集群，您将获得更好的性能任何一个谓词的索引，例如:

alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);

您需要以度为单位的基本 lat 和 lon，以弧度为单位的 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

这样你就知道距离了。

不要忘记在 lat/lon 上添加索引，这样边界装箱可以帮助搜索，而不是减慢搜索速度(索引已经添加到上面的 CREATE TABLE 查询中)。

INDEX `lat_lon_idx` (`lat`, `lon`)

给定一个只有 lat/lon 坐标的旧表，您可以设置一个脚本来更新它，如下所示: (php using meekrodb)

$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']
));
}

然后对实际查询进行优化，以便只在真正需要时进行距离计算，例如从内部和外部对圆(好吧，椭圆)进行限定。为此，您需要为查询本身预先计算几个指标:

// 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));

考虑到这些准备工作，查询如下所示(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 可能会说，除非有足够的结果来触发这样的操作，否则它不会使用 index。当坐标表中有足够的数据时，将使用索引。你可以加 FORCE INDEX (lat _ lon _ idx) 让它使用索引而不考虑表的大小，所以您可以用 EXPLAIN 验证它是否正常工作。

使用上面的代码示例，您应该能够以最小的误差实现按距离进行对象搜索。

小开

我认为我的 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);
}

小开

我不能对上面的回答作出评论，但是要注意@Pavel Chuchuva 的回答。如果两个坐标相同，该公式将不返回结果。在这种情况下，距离为空，因此该行不会按原样返回该公式。

我不是一个 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;

小开

我不得不详细解决这个问题，所以我将分享我的结果。这使用一个包含 latitude和 longitude表的 zip表。它不依赖于 Google Maps; 相反，您可以将其改写为包含 lat/long 的任何表。

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

看看查询中间的这行:

    SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r

这将搜索距 lat/long 点42.81/-70.8150.0英里范围内 zip表中最近的30个条目。当你把它构建成一个应用程序时，你可以把你自己的点和搜索半径放在那里。

如果希望以公里而不是英里为单位工作，请在查询中将 69更改为 111.045，并将 3963.17更改为 6378.10。

这是一份详细的报告。我希望它能帮到某些人

小开

 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

25公里的距离

小开

用 Mysql 计算距离

 SELECT (6371 * acos(cos(radians(lat2)) * cos(radians(lat1) ) * cos(radians(long1) -radians(long2)) + sin(radians(lat2)) * sin(radians(lat1)))) AS distance

这样就可以计算出距离值，任何人都可以根据需要申请。