正文 6706字数 26,933阅读


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)
Run code
Cut to clipboard


    根据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; }
    Run code
    Cut to clipboard


      这是一个生成根据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 = I("post.km");　　　　　　　　// 距离 1千米-1万米之前 // $kmStr = "1000,10000";　　　　　　　　　　　　　　　　// 距离小于1千米　　　　　　　　　　//$kmStr = "<1000";　　　　　　　　　　　//距离大于1千米　　　　　　　　//$kmStr = ">1000"; 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]})"; } } }
      Run code
      Cut to clipboard


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

        /** * * @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); }
        Run code
        Cut to clipboard


          要实现根据经纬度排序

          就直接调用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))
          Run code
          Cut to clipboard


            如果实现根据最近位置排序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
            Run code
            Cut to clipboard


              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; }
              Run code
              Cut to clipboard