数据库中每个用户的经纬度存储在两个字段中(lat, lon)
每个字段的格式为:
lon | -1.403976
lat | 53.428691
如果用户搜索100英里内的其他用户,我执行以下操作以计算适当的纬度/纬度范围($lat和$lon是当前用户的值)
$R = 3960; // earth's mean radius
$rad = '100';
// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));
$maxLat=number_format((float)$maxLat, 6, '.', '');
$minLat=number_format((float)$minLat, 6, '.', '');
$maxLon=number_format((float)$maxLon, 6, '.', '');
$minLon=number_format((float)$minLon, 6, '.', '');
然后我可以执行如下查询:
$query = "SELECT * FROM table WHERE lon BETWEEN '$minLon' AND '$maxLon' AND lat BETWEEN '$minLat' AND '$maxLat'";
这工作得很好,我使用一个函数在输出阶段计算和显示用户之间的实际距离,但是我希望能够在查询阶段通过减少或增加距离来对结果进行排序。
有什么办法吗?
记得毕达哥拉斯吗?
$sql = "SELECT * FROM table
WHERE lon BETWEEN '$minLon' AND '$maxLon'
AND lat BETWEEN '$minLat' AND '$maxLat'
ORDER BY (POW((lon-$lon),2) + POW((lat-$lat),2))";
从技术上讲,这是距离的平方,而不是实际距离,但因为你只是用它来排序,这无关紧要。
这使用了平面距离公式,对于小距离应该很好。
然而:
如果您想更精确或使用更长的距离,请使用以下公式以弧度表示大圆距离:
dist = acos[ sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lng1-lng2) ]
(要得到以实际单位而不是弧度为单位的距离,将其乘以地球的半径。但是,这对于排序目的来说是不必要的。)
纬度和经度被MySQL计算引擎假设为弧度,所以如果它以度存储(很可能是),你必须将每个值乘以pi/180,大约为0.01745:
$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table
WHERE lon BETWEEN '$minLon' AND '$maxLon'
AND lat BETWEEN '$minLat' AND '$maxLat'
ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";
甚至:
$sf = 3.14159 / 180; // scaling factor
$er = 6350; // earth radius in miles, approximate
$mr = 100; // max radius
$sql = "SELECT * FROM table
WHERE $mr >= $er * ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))
ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";
仅使用SELECT * FROM Table WHERE lat between $minlat and $maxlat
是不够准确的。
查询距离的正确方法是用弧度坐标。
<?php
$sql = "SELECT * FROM Table WHERE acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 1000";
这里有一个方便的参考- http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates
例如:
<?php
$distance = 100;
$current_lat = 1.3963;
$current_lon = -0.6981;
$earths_radius = 6371;
$sql = "SELECT * FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance";
如果你想排序并显示距离:
<?php
$distance = 100;
$current_lat = 1.3963;
$current_lon = -0.6981;
$earths_radius = 6371;
$sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";
为@Blazemonger编辑,避免疑问:)如果你想用度而不是弧度来工作:
<?php
$current_lat_deg = 80.00209691585;
$current_lon_deg = -39.99818366895;
$radians_to_degs = 57.2957795;
$distance = 100;
$current_lat = $current_lat_deg / $radians_to_degs;
$current_lon = $current_lon_deg / $radians_to_degs;
$earths_radius = 6371;
$sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";
你可以很容易地把它包装成一个类,从上面提供的信息中接受弧度或度。
这个公式给了我正确的结果(与上面的解决方案相反)。通过使用谷歌地图"测量距离"功能(直接距离,而不是交通距离)确认。
SELECT
*,
( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) ) AS `distance`
FROM `locations`
ORDER BY `distance` ASC
:latitude
和:longitude
是PDO函数的占位符。如果愿意,可以用实际值替换它们。latitude
和longitude
为列名。
3959
为地球半径,单位为英里;distance
输出也将以英里为单位。若要更改为km,请将3959
替换为6371
。
以上答案在格林威治子午线上都不正确。哈弗斯公式:
// 6371 is the Earth's radius in km
6371 * 2 * ASIN(SQRT(
POWER(SIN((lat - abs(:latitude)) * pi()/180 / 2), 2)
+ COS(lat * pi()/180 ) * COS(abs(:latitude) * pi()/180)
* POWER(SIN((lon - :longitude) * pi()/180 / 2), 2)
)) as distance
,我从这里取的,在这个类似问题的答案中引用,确实有效。
不会给你按平面距离排序的结果(不考虑地球的曲率),但小半径应该可以。
SELECT * from table where lon between '$minLon' and '$maxLon' and lat between '$minLat' and '$maxLat' order by (abs(lon-$lon)/2) + (abs(lat-$lat)/2);