如何按距离排序 php,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语句

// lon1当前用户经度 lat1当前用户纬度，lon2 sql的经度字段 lat sql的纬度字段

function distance_sql($lon1,$lat1,$lon2,$lat2) {$sql = "round(6378.138*2*asin(sqrt(pow(sin( ({$lat1}*pi()/180-{$lat2}*pi()/180)/2),2)+cos({$lat1}*pi()/180)*cos({$lat2}*pi()/180)* pow(sin( ({$lon1}*pi()/180-{$lon2}*pi()/180)/2),2)))*1000) ";return $sql; }

这是一个生成根据SQL排序函数代码

接下来下面是设置经纬度范围内的数据

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,"= {$km[0]}) and (location.distance <= {$km[1]}))"; }else if(strpos($kmStr,">") !== false){$km = explode(">",$kmStr);$wheres2 .= " and (location.distance >= {$km[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); }

要实现根据经纬度排序

如果筛选距离段 1000米-2000米的数据

如果实现根据最近位置排序sql

public functiontraining_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,"= {$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; }

原文：https://www.cnblogs.com/liuxinruif0/p/9264343.html