PHP sorts according to longitude and latitude and filters distance segments according to longitude and latitude

This article mainly introduces about php sorting according to longitude and latitude, and filtering distance segments according to longitude and latitude. It has a certain reference value. Now I share it with you. Friends in need can refer to it.

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) "

This is a generated Sort function code according to SQL

The following is to set the data within the longitude and latitude range

(I("post.location"
             = (",",I("post.location" = [0 = [1
             = getAround(,,1000000.=" and (((px_lat >= {["minLat"]}) and (px_lat <= {[&#39;maxLat&#39;]})) and ((px_lon >= {['minLng']}) and (px_lon <= {[&#39;maxLng&#39;]})))"
            (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]})"

The following is to calculate the data control function within the longitude and latitude range

/**
 * 
 * @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);
}

To implement sorting according to longitude and latitude

Just call distance_sql(lon1,lat1,lon2,lat2) directly to pass in the parameters and as an alias such as as distance, and then order by in the sql statement is sorted according to distance

If the filter distance segment is 1000 Meter-2000 meter data

Then nest the sql statement 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))

If you implement sorting sql based on the nearest location

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[&#39;maxLat&#39;]})) and ((px_lon >= {$location[&#39;minLng&#39;]}) and (px_lon <= {$location[&#39;maxLng&#39;]})))";             // 经纬度距离筛选
            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;
     
    }

The above is the entire content of this article, I hope it will be helpful to everyone Learning will be helpful. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Introduction to curl requesting other interfaces in the php interface

Initial introduction to PHP multi-process programming

The above is the detailed content of PHP sorts according to longitude and latitude and filters distance segments according to longitude and latitude. For more information, please follow other related articles on the PHP Chinese website!