一.功能场景:
前端定位获得了经纬度,需要请求接口获取指定经纬度附近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'store_common
AND.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();
}
