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

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