PHP sorts according to longitude and latitude and filters distance segments according to longitude and latitude-PHP Tutorial-php.cn

This article mainly introduces about php sorting according to longitude and latitude, and filtering distance segments according to longitude and latitude. It has a certain reference value. Now I share it with you. Friends in need can refer to it.

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) 先忽略上面这条SQL语句。一一解释 根据SQL排序的SQl语句

Copy after login

distance_sql(,,, = "round(6378.138*2*asin(sqrt(pow(sin( ({}*pi()/180-{}*pi()/180)/2),2)+cos({}*pi()/180)*cos({}*pi()/180)* pow(sin( ({}*pi()/180-{}*pi()/180)/2),2)))*1000) "

Copy after login

This is a generated Sort function code according to SQL

The following is to set the data within the longitude and latitude 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.distance_sort" = ",".distance_sql(,,"px_lon","px_lat")." as distance" = " distance"(I("post.km" = htmlspecialchars_decode(I("post.km"((,"<") !== = ("<", .= " and (location.distance <= {[1]})" ((,"-") !== = ("-", .= " and ((location.distance >= {[0]}) and (location.distance <= {[1]}))" ((,">") !== = (">", .= " and (location.distance >= {[1]})"

Copy after login

The following is to calculate the data control function within the longitude and latitude range

/** * * @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); }

Copy after login

To implement sorting according to longitude and latitude

Just call distance_sql(lon1,lat1,lon2,lat2) directly to pass in the parameters and as an alias such as as distance, and then order by in the sql statement is sorted according to distance

If the filter distance segment is 1000 Meter-2000 meter data

Then nest the sql statement 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))

Copy after login

If you implement sorting sql based on the nearest location

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

Copy after login

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; }

Copy after login

The above is the entire content of this article, I hope it will be helpful to everyone Learning will be helpful. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Introduction to curl requesting other interfaces in the php interface

Initial introduction to PHP multi-process programming

The above is the detailed content of PHP sorts according to longitude and latitude and filters distance segments according to longitude and latitude. For more information, please follow other related articles on the PHP Chinese website!