PHP,Mysql根据经纬度计算距离并排序

weixin_396

已于 2024-11-19 17:35:58 修改

阅读量599

点赞数

分类专栏： php 文章标签： php mysql

于 2021-09-17 13:27:38 首次发布

原文链接：https://www.cnblogs.com/rnckty/p/5498764.html

版权

php 专栏收录该内容

20 篇文章

订阅专栏

MySQL函数 st_distance_sphere 更准确，需要MySQL5.7.6以上版本才支持

round(st_distance_sphere (point (lng,lat),point ($lng,$lat)),0) AS juli

select *,(2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*(111.86141967773438-lng)/360),2)+COS(PI()*33.07078170776367/180)* COS(lat * PI()/180)*POW(SIN(PI()*(33.07078170776367-lat)/360),2)))) as juli from `area` 
order by juli asc limit 0,20

例子

 //诊所列表
    public function clinicList(){
        $page=input('page')?input('page'):1;
        $pagesize=input('pagesize')?input('pagesize'):10;
        $where['status']='normal';
        $city=input('city');
        if($city){
            $where['address']=['like',"%$city%"];
        }
        $key=input('key');
        if($key){
            $where['name']=['like',"%$key%"];
        }
        $orders='weigh desc';
        $order=input('order');//距离排序
        if($order==1){
            $lat=input('lat');
            $lng=input('lng');
            //$where1=',ROUND((2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*('.$lng.'-lng)/360),2)+COS(PI()*'.$lat.'/180)* COS(lat * PI()/180)*POW(SIN(PI()*('.$lat.'-lat)/360),2)))),2) as juli';
            //MySQL函数更准确
            $where1=",round(st_distance_sphere (point (`lng`,lat),point ($lng,$lat)),0) AS juli";
            $orders='juli asc';
        }
        $count=db('clinic')->where($where)->count();
        $list=db('clinic')->field('user_id,name,image,phone,address,lat,lng'.@$where1)->where($where)->page($page,$pagesize)->order($orders)->select();
        if(!$list){
            $this->error('暂无更多');
        }
        return json(['code'=>1,'msg'=>'ok','count'=>$count,'list'=>$list]);
    }

/**
     * @ApiInternal
     * 根据经纬度算距离，返回结果单位是公里，先纬度latitude，后经度longitude
     * @param $lat1
     * @param $lng1
     * @param $lat2
     * @param $lng2
     * @return float|int
     */
    public function GetDistance($lat1, $lng1, $lat2, $lng2)
    {
        $EARTH_RADIUS = 6378.137;
        $radLat1 = $this->rad($lat1);
        $radLat2 = $this->rad($lat2);
        $a = $radLat1 - $radLat2;
        $b = $this->rad($lng1) - $this->rad($lng2);
        $s = 2 * asin(sqrt(pow(sin($a / 2), 2) + cos($radLat1) * cos($radLat2) * pow(sin($b / 2), 2)));
        $s = $s * $EARTH_RADIUS;
        $s = round($s * 10000) / 10000;
        return $s;
    }


    private function rad($d)
    {
        return $d * M_PI / 180.0;
    }


/**
 * 获取两个经纬度之间的距离
 * @param string $lat1 纬一
 * @param String $lng1 经一
 * @param String $lat2 纬二
 * @param String $lng2 经二
 * @return float  返回两点之间的距离，公里
 */
function calcDistance($lat1, $lng1, $lat2, $lng2)
{
    if (empty($lat1) || empty($lng1) || empty($lat2) || empty($lng2)) {
        return false;
    }

    /** 转换数据类型为 double */
    $lat1 = doubleval($lat1);
    $lng1 = doubleval($lng1);
    $lat2 = doubleval($lat2);
    $lng2 = doubleval($lng2);

    $theta = $lng1 - $lng2;
    $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
    $dist = acos($dist);
    $dist = rad2deg($dist);
    $miles = $dist * 60 * 1.1515;

    return ($miles * 1.609344);
}