PHP sorting by latitude and longitude and filtering distance segments based on latitude and longitude

This article mainly introduces about PHP according to latitude and longitude sorting, according to latitude and longitude to filter the distance segment, has a certain reference value, now share to everyone, the need for friends can refer to

SQL statements: 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_l At <= 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) ignore the above SQL statement first. One by one interpreting SQL statements sorted by SQL

Distance_sql (,,, = "Round" (6378.138*2*asin (sqrt (Pow ({}*pi ()/180-{}*pi ()/180)/2), 2) +cos ({}*pi ()/180) *cos ({}* Pi ()/180) * POW (sin (({}*pi ()/180-{}*pi ()/180)/2), 2))) (*1000) "

This is a build based on the SQL sort function code

The next step is to set the data within the latitude and longitude range

(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.distanc E_sort "=", ". Distance_sql (,," Px_lon "," Px_lat ")." As Distance "=" Distance "(I (" post.km "= Htmlspecialchars_decode (I (" Post.km "((," < ")!==  = (" < ",. =" and (location.distance <= {[1]}) "((,"-")!==  = ("-",. =" and ((Locatio N.distance >= {[0]}) and (Location.distance <= {[1]}) "((," > ")!==  = (" > ",. =" and (location.distance &G t;= {[1]}) "

The data control functions in the latitude and longitude range are calculated below

/** *  * @param  $latitude    latitude     * @param  $longitude    Longitude * @param  $raidus        radius (unit: m) * @ 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 achieve sorting by latitude and longitude

Just call Distance_sql (LON1,LAT1,LON2,LAT2) incoming parameters and as an alias such as distance, then the order by sort in the SQL statement is sorted according to distance

If you filter data from a distance segment of 1000 meters-2000 meters

SQL statements are nested SQL

Select *.loation from (select *,round (6378.138*2*asin (sqrt) (Pow (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 the table location  where (Location.distance >=) and (Location.distance <= 2000))

If the implementation sorts the SQL based on the nearest location

Select *,round (6378.138*2*asin (sqrt (POW (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 () {$wheres 1 = "(Px_state = 1)";          $wheres 2 = "WHERE (1=1)";                $orderBy = "px_id desc";                        if (I ("post.location")) {//user latitude and longitude $location = Explode (",", I ("post.location"));                        $userLon = $location [0];            $userLat = $location [1];                        Latitude Screening $location = Getaround ($userLat, $userLon, 1000000); $wheres 1.= "and (((Px_lat >= {$location [" Minlat "]}) and (Px_lat <= {$location [' Maxlat ']}) and ((Px_lon >= {$loca             tion[' MINLNG '}) and (Px_lon <= {$location [' maxlng ']})) "; Filter by latitude Distance if (I ("Post.distance_sort")) {$distanceSql = ",". Distance_sql ($userLon, $US                            Erlat, "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);                $wheres 2. = "and (location.distance <= {$km [1]})";                                    }else if (Strpos ($KMSTR, "-")!== false) {$km = explode ("-", $kmStr);                 $wheres 2. = "and ((location.distance >= {$km [0]}) and (Location.distance <= {$km [1]})";                                     }else if (Strpos ($KMSTR, ">")!== false) {$km = explode (">", $KMSTR);                $wheres 2. = "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 {$wheres 1} ORDER by {$orderBy} limit {$n},{$showNum}] location {$wheres 2} ";                       $training = M ()->query ($sql);         Dump (M ()->getlastsql ());d ie; }

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!