获取指定经纬度附近5KM的商家列表优化方案

一.功能场景:

前端定位获得了经纬度,需要请求接口获取指定经纬度附近5KM的商家列表

二.数据库商家信息表字段

CREATE TABLE `store_common` (
  `store_common_id` int(10) NOT NULL DEFAULT '0' COMMENT '商家公共表ID(来源于store表的store_id值)',
  `lon` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '经度',
  `lat` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '纬度',
  PRIMARY KEY (`store_common_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商家信息附属表';

三.原始方案

注:Thinkphp5.0框架中控制器的一个方法,这种方式用了链式操作having(),
如果表里存有100万条数据,这种方式的查询效率就很低了,
sql需要把整表100万条数据全部查出来计算了juli字段,生成了一个临时表,
然后在100万条数据的临时表总having过滤出来juli<=5km的再生成一个临时表,
然后再根据group,order,limit等等条件查询返回数据

/*
 * 首页获取附近的商家列表
 * */
public function getNearbyStoreList(){
    //距离 5KM(单位为米)
    $limit_juli = 5000;
    //经纬度
    $lat = $data["lat"];
    $lon = $data["lon"];
    //根据经纬度计算距离的sql (单位米)
    $juli = <<<JULI
        ROUND(
            6378.138 * 2 * ASIN(
                SQRT(
                    POW(
                        SIN(
                            (
                                $lat * PI() / 180 - lat * PI() / 180
                            ) / 2
                        ),
                        2
                    ) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * POW(
                        SIN(
                            (
                                $lon * PI() / 180 - lon * PI() / 180
                            ) / 2
                        ),
                        2
                    )
                )
            ) * 1000
        )

JULI;

    //获取分页数据
    $res = Db::name('store')
    ->field(['store.store_id','store.store_name','store.optimized_state','store.store_banner','store.operate_state',
    $juli=>'juli','store_common.store_logo','store_common.address','store_common.store_star','store_common.class_id'])
    ->leftJoin('store_common','store.store_id=store_common.store_common_id')
    ->group('store.store_id')
    ->having("juli <= $limit_juli")
    ->order('juli ASC ')
    ->limit(10)
    ->page(1)
    ->select();
}

四.优化方案

1.创建一个根据经纬度,获取'MBR'数据的工具类

<?php
/**
 * 使用方法:
 * 1.根据经纬度,获取'MBR'数据
 * @param float $lat 纬度
 * @param float $lon 经度
 * @param float $distance 距离(单位公里 如5就是5公里)
 * @return array 'MBR'数据
 * 使用实例:
 * $gpsdis = (new Getmbr)->Main($lat,$lon,$distance)
 *
 * 2.根据经纬度,计算2个点之间的距离。
 * @param float $lat 纬度1
 * @param float $lon 经度1
 * @param float $lat 纬度2
 * @param float $lon 经度2
 * @return float 距离(公里、千米)
 * 使用实例:
 * $distance = (new Getmbr)->Distance($lat1, $lon1, $lat2, $lon2)
 *
 * 说明:
 * 这个根据一个经纬度坐标、距离然后求另外一个经纬度坐标的作用,
 * 主要就是确定一个最小外包矩形(Minimum bounding rectangle,简称MBR)。
 * 例如,我要找一个坐标点(lat,lon)的5公里范围内的所有商户信息、景点信息等。
 * 这个MBR就是一个最大的范围,
 * 这个矩形是包含5公里范围内所有这些有效信息的一个最小矩形。
 * 利用公式,求出四个方向0度、90度、180度、270度方向上的四个坐标点就可以得到这个MBR。
 *
 * 如果有一个应用,表里存有100万的数据,数据包含一个lat、lon的经纬度信息。
 * 就可以先根据输入的经纬度和距离得到一个MBR,然后通过类似
    SELECT Id
    FROM IdInfoTable
    WHERE latitude >= minLat AND latitude < maxLat
    AND longitude >= minLon AND longitude < maxLon
 *
 */
namespace app\index\controller;

class Getmbr {

    //最大纬度值
    public $MaxLatitude;
    //最小纬度值
    public $MinLatitude;

    public $MaxLongitude;

    public $MinLongitude;

    public $Ea = 6378137;     //   赤道半径

    public $Eb = 6356725;     //   极半径

    const EARTH_RADIUS = 6371.0;//km 地球半径 平均值,千米

    /**
     * 获取'MBR'数据
     * @param float $lat 纬度
     * @param float $lon 经度
     * @param float $distance 距离(单位公里 如5就是5公里)
     * @return array 'MBR'数据
     * */
    public function Main($lat,$lon,$distance)
    {
        $this->GetMBR($lat, $lon, $distance);
        //返回'MBR'数据
        return [
            'MaxLatitude'=>$this->MaxLatitude,
            'MinLatitude'=>$this->MinLatitude,
            'MaxLongitude'=>$this->MaxLongitude,
            'MinLongitude'=>$this->MinLongitude
        ];
    }

    private function GetMBR($centorlatitude, $centorLogitude, $distance)
    {
        //以下为核心代码
        $range  = 180 / pi() * $distance / 6372.797; //里面的 $distance 就代表搜索 $distance 之内,单位km
        $lngR   = $range / cos($centorlatitude * pi() / 180);
        $this->MaxLatitude = $centorlatitude + $range; //最大纬度
        $this->MinLatitude = $centorlatitude - $range; //最小纬度
        $this->MaxLongitude = $centorLogitude + $lngR; //最大经度
        $this->MinLongitude = $centorLogitude - $lngR; //最小经度
        //得出这四个值以后,就可以根据你数据库里存的经纬度信息查找记录了~
    }

    /**
     * 将角度换算为弧度。
     * @param float $degrees 角度
     * @return float 弧度
     * */
    private function ConvertDegreesToRadians($degrees)
    {

        return $degrees * M_PI / 180;

    }

    /**
     * 根据经纬度,计算2个点之间的距离。
     * @param float $lat 纬度1
     * @param float $lon 经度1
     * @param float $lat 纬度2
     * @param float $lon 经度2
     * @return float 距离(公里、千米)
     * */
    public function Distance($lat1, $lon1, $lat2, $lon2){
        //用haversine公式计算球面两点间的距离。
        //经纬度转换成弧度
        $lat1 = $this->ConvertDegreesToRadians($lat1);
        $lon1 = $this->ConvertDegreesToRadians($lon1);
        $lat2 = $this->ConvertDegreesToRadians($lat2);
        $lon2 = $this->ConvertDegreesToRadians($lon2);

        //差值
        $vLon = abs($lon1 - $lon2);
        $vLat = abs($lat1 - $lat2);

        //h is the great circle distance in radians, great circle就是一个球体上的切面,它的圆心即是球心的一个周长最大的圆。
        $h = $this->HaverSin($vLat) + cos($lat1) * cos($lat2) * $this->HaverSin($vLon);

        $distance = 2 * self::EARTH_RADIUS * asin(sqrt($h));

        return $distance;
    }

    private function HaverSin($theta)
    {
        $v = sin($theta / 2);
        return $v * $v;
    }


}

2.接口控制器中优化方案
注:Thinkphp5.0框架中控制器的一个方法,
如果表里存有100万条数据,数据包含一个lat、lon的经纬度信息。
先根据输入的经纬度和距离得到一个MBR,然后通过类似
WHEREstore_common.latBETWEEN '30.47739319846' AND '30.65720680154'
AND
store_common.lonBETWEEN '103.95608255801' AND '104.16491744199'
的where条件把整表100万条数据中经纬度在5KM范围内的数据查出来计算了juli字段,
生成了一个临时表(比方说5KM范围内数据库中只有200个商家),
然后在200条数据的临时表中再根据group,order,limit等等条件查询返回数据

/*
 * 首页获取附近的商家列表
 * */
public function getNearbyStoreList(){
    //距离 5KM(单位为KM)
    $limit_juli = 5;
    //经纬度
    $lat = $data["lat"];
    $lon = $data["lon"];
    //根据经纬度计算距离的sql (单位米)
    $juli = <<<JULI
        ROUND(
            6378.138 * 2 * ASIN(
                SQRT(
                    POW(
                        SIN(
                            (
                                $lat * PI() / 180 - lat * PI() / 180
                            ) / 2
                        ),
                        2
                    ) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * POW(
                        SIN(
                            (
                                $lon * PI() / 180 - lon * PI() / 180
                            ) / 2
                        ),
                        2
                    )
                )
            ) * 1000
        )

JULI;

    //根据经纬度,获取'MBR'数据
    $gpsdis = (new Getmbr)->Main($lat,$lon,$limit_juli);

    //获取分页数据
    $res = Db::name('store')
    ->field(['store.store_id','store.store_name','store.optimized_state','store.store_banner','store.operate_state',
    $juli=>'juli','store_common.store_logo','store_common.address','store_common.store_star','store_common.class_id','store_common.lat','store_common.lon'])
    ->leftJoin('store_common','store.store_id=store_common.store_common_id')
    ->where([
        ['store_common.lat','between',"{$gpsdis['MinLatitude']},{$gpsdis['MaxLatitude']}"],
        ['store_common.lon','between',"{$gpsdis['MinLongitude']},{$gpsdis['MaxLongitude']}"]
    ])
    ->group('store.store_id')
    ->order('juli ASC ')
    ->limit(10)
    ->page(1)
    ->select();

}
暂无评论

请到【后台 - 用户 - 我的个人资料】中填写个人说明。

发表评论