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

Source: Internet
Author: User
Tags asin cos explode pow sin

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 explanation
SQL statements sorted by SQL
Lon1 Current user longitude lat1 current user latitude, lon2 SQL longitude field lat SQL latitude field
function distance_sql ($lon 1,$lat 1,$lon 2,$lat 2) { $sql = "Round (6378.138*2*asin (sqrt (sin ({$lat 1}*pi ()/180-{$lat 2}*pi ()/180)/2), 2) +cos ({ $lat 1} *pi ()/180) *cos ({$lat 2}*pi ()/180) * POW (sin ({$lon 1}*pi ()/180-{$lon 2}*pi ()/180)/ 2), 2)) *1000) "; return $sql ;}

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

if(I ("Post.location")){            //latitude and longitude of user            $location=Explode(",", I ("Post.location")); $userLon=$location[0]; $userLat=$location[1]; //latitude and Longitude filter            $location= Getaround ($userLat,$userLon, 1000000); $wheres 1. = "and (((Px_lat >= {$location["Minlat"]}) and (Px_lat <= {$location[' Maxlat ']}) and ((Px_lon >= {$location[' MINLNG ']}) and (Px_lon <= {$location[' MAXLNG ']})) "; //latitude and longitude distance filter            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); $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]}) "; }            }        }

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*/functionGetaround ($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 functiontraining_list () {$wheres 1= "(px_state = 1)"; $wheres 2= "WHERE (1=1)"; $orderBy= "px_id desc"; if(I ("Post.location")){            //latitude and longitude of user            $location=Explode(",", I ("Post.location")); $userLon=$location[0]; $userLat=$location[1]; //latitude and Longitude filter            $location= Getaround ($userLat,$userLon, 1000000); $wheres 1. = "and (((Px_lat >= {$location["Minlat"]}) and (Px_lat <= {$location[' Maxlat ']}) and ((Px_lon >= {$location[' MINLNG ']}) and (Px_lon <= {$location[' MAXLNG ']})) "; //latitude and longitude distance filter            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); $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 ()); die; }

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

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.