php mysql比较long和lat，返回10英里以下的值 [英] php mysql compare long and lat, return ones under 10 miles
问题描述
我希望使用经纬度和经度值找到两个位置之间的距离(以英里为单位)，并检查它们之间的半径是否在10英里之内.
用户登录时，其经/纬度值将保存在会话中
$_SESSION['lat']
$_SESSION['long']
我有2个功能
这会计算出以英里为单位的距离并返回一个四舍五入的值
function distance($lat1, $lng1, $lat2, $lng2){
$pi80 = M_PI / 180;
$lat1 *= $pi80;
$lng1 *= $pi80;
$lat2 *= $pi80;
$lng2 *= $pi80;
$r = 6372.797; // mean radius of Earth in km
$dlat = $lat2 - $lat1;
$dlng = $lng2 - $lng1;
$a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
$c = 2 * atan2(sqrt($a), sqrt(1 - $a));
$km = $r * $c;
return floor($km * 0.621371192);
}
如果两组纬度和经度之间的距离小于10，则此变量将返回布尔值.
function is_within_10_miles($lat1, $lng1, $lat2, $lng2){
$d = distance($lat1, $lng1, $lat2, $lng2);
if( $d <= 10 ){
return True;
}else{
return False;
}
}
这两个函数都按预期工作，如果我给出两组经纬度，并且它们之间的距离为20英里，则我的is_within_10_miles()函数将返回false.
现在，我有一个位置"数据库(4个字段-ID，名称，纬度，经度).
我想找到半径10英里以内的所有位置.
有什么想法吗?
我可以遍历所有，并像这样对它们执行is_within_10_miles()
$query = "SELECT * FROM `locations`";
$result = mysql_query($query);
while($location = mysql_fetch_assoc($result)){
echo $location['name']." is ";
echo distance($lat2, $lon2, $location['lat'], $location['lon']);
echo " miles form your house, is it with a 10 mile radius? ";
if( is_within_10_miles($lat2, $lon2, $location['lat'], $location['lon']) ){
echo "yeah";
}else{
echo "no";
}
echo "<br>";
}
示例结果将是
goodison park is 7 miles form your house, is it with a 10 mile radius? yeah
我需要以某种方式在查询中执行is_within_10_miles函数.
编辑编辑
此图例来自 http://www.zcentric.com/blog/2007 /03/calculate_distance_in_mysql_wi.html 提出了这个...
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC
它实际上有效.问题是我想选择*行，而不是一一选择.我该怎么办?
您可能不需要在代码中执行此操作，您可能可以在数据库中完成所有操作.如果您使用空间索引. 用于空间索引的MySQL文档
编辑以反映您的
我认为您想要这样的东西:
SELECT *, ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM locations HAVING distance<='10' ORDER BY distance ASC
VIA:http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html :
Hay i want to find the distance (in miles) between 2 locations using lat and long values, and check if they are within a 10 mile radius of each other.
When a user logs in, their lat/long values are saved in a session
$_SESSION['lat']
$_SESSION['long']
I have 2 functions
This one works out the distance in miles and returns a rounded value
function distance($lat1, $lng1, $lat2, $lng2){
$pi80 = M_PI / 180;
$lat1 *= $pi80;
$lng1 *= $pi80;
$lat2 *= $pi80;
$lng2 *= $pi80;
$r = 6372.797; // mean radius of Earth in km
$dlat = $lat2 - $lat1;
$dlng = $lng2 - $lng1;
$a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
$c = 2 * atan2(sqrt($a), sqrt(1 - $a));
$km = $r * $c;
return floor($km * 0.621371192);
}
This one returns a bool if the distance between the 2 sets of lat and long's is under 10.
function is_within_10_miles($lat1, $lng1, $lat2, $lng2){
$d = distance($lat1, $lng1, $lat2, $lng2);
if( $d <= 10 ){
return True;
}else{
return False;
}
}
Both functions work as expected, if i give 2 sets of lat/longs and the distance between them is say 20 miles, my is_within_10_miles() function returns false.
Now, I have a database of 'locations' (4 fields - ID, name, lat, long).
I want to find all locations that are within a 10 mile radius.
Any ideas?
EDIT: I can loop through ALL the and perform the is_within_10_miles() on them like this
$query = "SELECT * FROM `locations`";
$result = mysql_query($query);
while($location = mysql_fetch_assoc($result)){
echo $location['name']." is ";
echo distance($lat2, $lon2, $location['lat'], $location['lon']);
echo " miles form your house, is it with a 10 mile radius? ";
if( is_within_10_miles($lat2, $lon2, $location['lat'], $location['lon']) ){
echo "yeah";
}else{
echo "no";
}
echo "<br>";
}
A sample result would be
goodison park is 7 miles form your house, is it with a 10 mile radius? yeah
I need to somehow perform the is_within_10_miles function within my query.
EDIT EDIT
This legend from http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html came up with this...
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC
It actually works. Problem is that i want to select * rows, rather than selecting them one by one. How do i do that?
You probably don't need to do this in code, you can probably do this all in the DB. if you use a spatial index. MySQL docuemtnation for spatial index
EDIT to reflect your edit:
I think you want something like this:
SELECT *, ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM locations HAVING distance<='10' ORDER BY distance ASC
VIA: http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html:
这篇关于php mysql比较long和lat，返回10英里以下的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
