php根据经纬度排序和根据经纬度筛选距离段

不言
发布: 2023-04-02 13:50:02
原创
3576 人浏览过

这篇文章主要介绍了关于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 <= {[&#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]})"
登录后复制

下面算出经纬度范围内的数据控制函数

/**
 * 
 * @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[&#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;
     
    }
登录后复制

以上就是本文的全部内容,希望对大家的学习有所帮助,更多相关内容请关注PHP中文网!

相关推荐:

 php接口内curl请求其他接口的介绍

初步进行PHP多进程编程的介绍

以上是php根据经纬度排序和根据经纬度筛选距离段的详细内容。更多信息请关注PHP中文网其他相关文章!

相关标签:
php
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板