查询MySQL以获取给定英里半径内的纬度和经度坐标 [英] Querying MySQL for latitude and longitude coordinates that are within a given mile radius
问题描述
我目前有一个结构如下的MySQL表:
I currently have a MySQL table that is structured as follows:
id name lon lat
----- ----- ----------- -----------
1 Mark -76.316528 40.036027
2 John -95.995102 41.25716
3 Paul -82.337036 29.645095
4 Dave -82.337036 29.645095
5 Chris -76.316528 40.036027
我当前查询数据库以查看用户的位置是否在给定位置的特定英里半径内的方法是通过
The way I currently query the DB to see if a user's location is within a certain mile radius of a given location is by doing this.
function Haversine($lat_from, $lon_from, $lat_to, $lon_to) {
$radius = 6371000;
$delta_lat = deg2rad($lat_to-$lat_from);
$delta_lon = deg2rad($lon_to-$lon_from);
$a = sin($delta_lat/2) * sin($delta_lat/2) +
cos(deg2rad($lat_from)) * cos(deg2rad($lat_to)) *
sin($delta_lon/2) * sin($delta_lon/2);
$c = 2*atan2(sqrt($a), sqrt(1-$a));
// Convert the distance from meters to miles
return ceil(($radius*$c)*0.000621371);
}
// Set the given location to NYC
$my_lon = -73.9844;
$my_lat = 40.7590;
// Query the DB for all of the users
$sql = "SELECT * FROM users";
$result = mysqli_query($con, $sql)or die(mysqli_error($con));
$count = mysqli_num_rows($result);
$i = 0;
while($row = mysqli_fetch_assoc($result)) {
$lon[$i] = $row['lon'];
$lat[$i] = $row['lat'];
$i++;
}
for($i=0;$i<$count;$i++) {
// Calculate the distance between each user's location and my location
$distance = Haversine($my_lat, $my_lon, $lat[$i], $lon[$i);
if($distance < 50) {
echo "Close enough";
}
}
这仅适用于表中的几百行.但是,既然我有成千上万的行,那么事实证明检查这么多行非常耗时.我想知道是否有一种方法可以使用 Haversine 公式仅查询在50英里半径内.
This works well with only a few hundred rows in the table. But, now that I have tens of thousands of rows, checking so many rows has proven to be very time consuming. I'm wondering if there is a way to use the Haversine formula to only query rows that are within the 50 mile radius.
推荐答案
余弦公式的球形定律
(37和-122是半径中心的纬度和经度)
Spherical Law of Cosines Formula
(37 and -122 are the latitude and longitude of your radius center)
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) )
* cos( radians( long ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM myTable
HAVING distance < 50
ORDER BY distance
功能
- 最快
- 类似于Harvesine公式的精度
Haversine配方
SELECT id, 3956 * 2 * ASIN(SQRT(POWER(SIN((37 - abs(lat)) * pi()/180 / 2), 2)
+ COS(37 * pi()/180 ) * COS(abs(lat) * pi()/180)
* POWER(SIN((-122 - long) * pi()/180 / 2), 2) )) as distance
FROM myTable
HAVING distance < 50
ORDER BY distance
功能
- 快速
- 对浮点错误更健壮
请注意, 3959是以英里为单位的地球半径.地球半径(以公里为单位):6371
Note that 3959 is the Earth radius in miles. Earth radius in km: 6371
您可以在此处
这篇关于查询MySQL以获取给定英里半径内的纬度和经度坐标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!