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

Source: Internet
Author: User
Tags asin cos pow
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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.