這篇文章主要介紹了關於php根據經緯度排序，根據經緯度篩選距離段，有著一定的參考價值，現在分享給大家，有需要的朋友可以參考一下
SQL 语句：
select location.* from (select *,round(6378.138*2*asin(sqrt(pow(sin( (36.668530*pi()/180-px_lat*pi()/180)/2),2)+cos(36.668530*pi()/180)*cos(px_lat*pi()/180)* pow(sin( (117.020359*pi()/180-px_lon*pi()/180)/2),2)))*1000) as distance from bsx_training where (px_state = 1) and (type_id != '') and (((px_lat >= 27.683290277922) and (px_lat <= 45.653769722078)) and ((px_lon >= 105.81826766053) and (px_lon <= 128.22245033947))) order by distance limit 0,10) location where (1=1) and (location.distance <= 500)
先忽略上面这条SQL语句。一一解释
根据SQL排序的SQl语句
登入後複製
distance_sql(,,, = "round(6378.138*2*asin(sqrt(pow(sin( ({}*pi()/180-{}*pi()/180)/2),2)+cos({}*pi()/180)*cos({}*pi()/180)* pow(sin( ({}*pi()/180-{}*pi()/180)/2),2)))*1000) "登入後複製
這是一個生成根據SQL排序函數代碼
接下來下面是設定經緯度範圍內的資料
(I("post.location"
= (",",I("post.location" = [0 = [1
= getAround(,,1000000.=" and (((px_lat >= {["minLat"]}) and (px_lat <= {['maxLat']})) and ((px_lon >= {['minLng']}) and (px_lon <= {['maxLng']})))"
(I("post.distance_sort" = ",".distance_sql(,,"px_lon","px_lat")." as distance" = " distance"(I("post.km" = htmlspecialchars_decode(I("post.km"((,"<") !== = ("<", .= " and (location.distance <= {[1]})" ((,"-") !== = ("-", .= " and ((location.distance >= {[0]}) and (location.distance <= {[1]}))" ((,">") !== = (">", .= " and (location.distance >= {[1]})"登入後複製
下面算出經緯度範圍內的資料控制函數
/**
*
* @param $latitude 纬度
* @param $longitude 经度
* @param $raidus 半径范围(单位：米)
* @return multitype:number */
function getAround($latitude,$longitude,$raidus)
{
$PI = 3.14159265;
$degree = (24901*1609)/360.0;
$dpmLat = 1/$degree;
$radiusLat = $dpmLat*$raidus;
$minLat = $latitude - $radiusLat;
$maxLat = $latitude + $radiusLat;
$mpdLng = $degree*cos($latitude * ($PI/180));
$dpmLng = 1 / $mpdLng;
$radiusLng = $dpmLng*$raidus;
$minLng = $longitude - $radiusLng;
$maxLng = $longitude + $radiusLng;
return array (minLat=>$minLat, maxLat=>$maxLat, minLng=>$minLng, maxLng=>$maxLng);
}登入後複製
要實現根據經緯度排序
就直接呼叫distance_sql(lon1,lat1,lon2,lat2)傳入參數並且as 一個別名例如as distance， 然後sql語句中order by 排序根據 distance排序
#如果篩選距離段1000米-2000公尺的資料
那就sql語句巢狀sql
select *.loation from (select *,round(6378.138*2*asin(sqrt(pow(sin( (36.668530*pi()/180-px_lat*pi()/180)/2),2)+cos(36.668530*pi()/180)*cos(px_lat*pi()/180)* pow(sin( (117.020359*pi()/180-px_lon*pi()/180)/2),2)))*1000) as distance) from table location where (location.distance >= 1000) and (location.distance <= 2000))
登入後複製
如果實作依照最近位置排序sql
select *,round(6378.138*2*asin(sqrt(pow(sin( (36.668530*pi()/180-px_lat*pi()/180)/2),2)+cos(36.668530*pi()/180)*cos(px_lat*pi()/180)* pow(sin( (117.020359*pi()/180-px_lon*pi()/180)/2),2)))*1000) as distance order by distance
登入後複製
public function training_list()
{
$wheres1 = "(px_state = 1)";
$wheres2 = " where (1=1)";
$orderBy = " px_id desc";
if(I("post.location")){ // 用户经纬度
$location = explode(",",I("post.location"));
$userLon = $location[0];
$userLat = $location[1]; // 经纬度筛选
$location = getAround($userLat,$userLon,1000000);
$wheres1.=" and (((px_lat >= {$location["minLat"]}) and (px_lat <= {$location['maxLat']})) and ((px_lon >= {$location['minLng']}) and (px_lon <= {$location['maxLng']})))"; // 经纬度距离筛选
if(I("post.distance_sort")){
$distanceSql = ",".distance_sql($userLon,$userLat,"px_lon","px_lat")." as distance";
$orderBy = " distance";
} if(I("post.km")){
$kmStr = htmlspecialchars_decode(I("post.km"));
if(strpos($kmStr,"<") !== false){
$km = explode("<",$kmStr);
$wheres2 .= " and (location.distance <= {$km[1]})";
}else if(strpos($kmStr,"-") !== false){
$km = explode("-",$kmStr);
$wheres2 .= " and ((location.distance >= {$km[0]}) and (location.distance <= {$km[1]}))";
}else if(strpos($kmStr,">") !== false){
$km = explode(">",$kmStr);
$wheres2 .= " and (location.distance >= {$km[1]})";
}
}
}
$showNum = 10;
if(I("post.page")){ $page = I("post.page");
}else{ $page = 1;
} $n = ($page-1)*$showNum;
$field = "*{$distanceSql}";
$sql = "select location.* from (select {$field} from bsx_training where {$wheres1} order by {$orderBy} limit {$n},{$showNum}) location {$wheres2}"; $training = M()->query($sql);
dump(M()->getlastsql());die;
}登入後複製
以上就是本文的全部內容，希望對大家的學習有幫助，更多相關內容請關注PHP中文網！
相關推薦：
php介面內curl請求其他介面的介紹
初步進行PHP多進程程式設計的介紹
以上是php根據經緯度排序和根據經緯度篩選距離段的詳細內容。更多資訊請關注PHP中文網其他相關文章！
