TP通过经纬度查询附近商家

上代码:

 /**
     * 根据经纬度获取最近商家 function
     *
     * @Author XiaoLiu wen@zmmz.com
     * @DateTime 2021-04-29 18:27:28 
     * @param string $latitude
     * @param string $longitude
     * @return void
     */
    public function userTerminalShop($latitude='39.95334243774414',$longitude='116.83504486083984',$sort_id='all',$offset=1,$search=''){
        $where = [];
        // $point=$this->returnSquarePoint($longitude,$latitude,300000);//300000
        // $where['latitude']  = array(array('gt',$point['minLat']),array('lt',$point['maxLat'])); 
        // $where['longitude']  = array(array('gt',$point['minLon']),array('lt',$point['maxLon']));
        

        $EARTH=6378.138; //固定参数 地球半径
        $PI=3.1415926535898; //固定参数 圆周率
        $where = [];
        if($sort_id!='all'){
            $where['range_id'] = $sort_id;
        }
        if($search){
            $where['name'] =array('like', '%' . $search . '%');
        }
        $result = UserProve::hasWhere('User', [], 'User.id AS user_id,User.nickname')->where(['review'=>1,'logo'=>['<>','']])->where($where)
        ->field("UserProve.id,name,logo,latitude,longitude,TRUNCATE((2 * $EARTH* ASIN(SQRT(POW(SIN($PI*(" . $latitude . "-latitude)/360),2)+COS($PI*" . $latitude . "/180)* COS(latitude * $PI/180)*POW(SIN($PI*(" . $longitude . "-longitude)/360),2)))),2) as distance")
        ->order('distance')
        ->alias('UserProve')
        ->buildSql();
        $arr = UserProve::table($result)->alias('tmp_table_name')->where('distance', '<', 2000000)
        ->with(['coupon' => function ($query) {
            $query->field('id,user_id,user_prove_id,title,amount,lowest,type');
        }])
        ->paginate(
            20,
            false,
            [
                'page' => $offset,
            ]
        )->toArray();
            $list = $arr['data'];
        foreach($list as $key=>$val){
            //结算需要多长时间
            $list[$key]['estimated_time'] = $this->convertToHoursMins(floor($val['distance']*1000/1000*1));
            
        }
        $this->success('',$list);
    }

public function convertToHoursMins($time, $format = '%2d:%02d') {

        $val = '1分钟';

        if ($time < 1) {
    
            return $val;
        }
        $hours = floor($time / 60);
        $minutes = ($time % 60);
        if($hours){
            $val = $hours.'小时';
            if($minutes){
                $val.=$minutes.'分钟';
            }
        }
        if(!$hours&&$minutes){
            $val = $minutes.'分钟';
        }
        return $val;
        return sprintf($format, $hours, $minutes);
    
    }

SQL代码:
把红色背景的经纬度改成自己的

SELECT * FROM ( SELECT `User`.`id` AS user_id,`User`.`nickname`,UserProve.id ,NAME,logo,
TRUNCATE((2 * 6378.138* ASIN(SQRT(POW(SIN(3.1415926535898*(39.98246-latitude)\/360),2)+
COS(3.1415926535898*39.98246\/180)* COS(latitude * 3.1415926535898\/180)*POW
(SIN(3.1415926535898*(117.07822-longitude)\/360),2)))),2) AS distance FROM `m_user_prove` `UserProve` 
RIGHT JOIN `m_user` `User` ON `UserProve`.`user_id`=`User`.`id`
 WHERE  `review` = 1 ORDER BY `distance` LIMIT 3 ) `tmp_table_name` WHERE  `distance` < 30