建立geo表
create table geo(
geo_id INT NOT NULL AUTO_INCREMENT,
lng float NOT NULL,
lat float NOT NULL,
name VARCHAR(100) NULL,
PRIMARY KEY ( geo_id )
);
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.302416,33.958887,"實驗國小");
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.303997,33.95188,"宿遷市人民醫院");
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.302991,33.935828,"宿遷學院");
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.28215,33.959307,"金陵名府");
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.290081,33.925404,"潤園");
INSERT INTO `geo`(`lng`, `lat`, `name`) VALUES (118.354751,33.959007,"國際館");
下面是google給的解決方案(基于公裡km)。
SELECT
geo_id, `name`,(
6371 * acos (
cos ( radians(33.958887) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(118.302416) )
+ sin ( radians(33.958887) )
* sin( radians( lat ) )
)
) AS distance
FROM geo
HAVING distance < 20
ORDER BY distance
LIMIT 0 , 20；
其中33.958887是緯度，118.302416是經度。
牛了個逼的。
public function get_near_business() {
if (!$this->validate->validate('lng', ['require','regex|-?((0|1?[0-7]?[0-9]?)(([.][0-9]{1,4})?)|180(([.][0]{1,4})?))'])) { // 經度
$this->json->setErr(10001, '缺少lng或lng格式不正确');
$this->json->Send();
}
if (!$this->validate->validate('lat', ['require','regex|-?((0|[1-8]?[0-9]?)(([.][0-9]{1,4})?)|90(([.][0]{1,4})?))'])) { // 緯度
$this->json->setErr(10001, '缺少lat或lng格式不正确');
$this->json->Send();
}
if (!isset($_POST['page']) || $_POST['page'] < 1) {
$_POST['page'] = 1;
}
// 分頁擷取附近的商家資訊
$sql_count = "SELECT id, `name`,( 6371 * acos (
cos ( radians(".$_POST['lat'].") )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(".$_POST['lng'].") )
+ sin ( radians(".$_POST['lat'].") )
* sin( radians( lat ) )
)
) AS distance
FROM tf_my_business
WHERE `status` = 1
HAVING distance < 10";
$count = count(M()->query($sql_count));
$hasPage = ceil($count / C('PAGE.LIMIT'));
$hasPage = $hasPage ? $hasPage : 1;
if ($_POST['page'] > $hasPage) {
$this->json->setErr(10002, '頁數有誤');
$this->json->Send();
}
$limit = (($_POST['page'] - 1) * C('PAGE.LIMIT')) . "," .C('PAGE.LIMIT');
$sql_list = "SELECT `id`,`name`,`info`,( 6371 * acos (
cos ( radians(".$_POST['lat'].") )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(".$_POST['lng'].") )
+ sin ( radians(".$_POST['lat'].") )
* sin( radians( lat ) )
)
) AS distance
FROM tf_my_business
WHERE `status` = 1
HAVING distance < 10
ORDER BY distance ASC
LIMIT ".$limit;
$datalist = M()->query($sql_list);
if ($datalist) {
// 處理datalist
$my_business_img_model = M('my_business_img');
foreach ($datalist as &$v) {
// 擷取主圖
$my_business_img_info = $my_business_img_model->where(['m_b_id'=>$v['id'],'type'=>1,'is_main'=>1])->find();
$v['img'] = $my_business_img_info['img'];
if (strpos($v['img'],'cdn') === false) {
$v['img'] = C('CDN.URI').$v['img'];
}
}
$data['datalist'] = $datalist;
$data['current_page'] = $_POST['page'];
$data['hasPage'] = $hasPage;
$this->json->setErr(0, '擷取成功');
$this->json->setAttr('data',$data);
$this->json->Send();
} else {
$this->json->setErr(10003, '暫無資料');
$this->json->Send();
}
}
{
"errno": 0,
"errdesc": "擷取成功",
"timestamp": 1524749336,
"data": {
"datalist": [
{
"id": "3",
"name": "京東",
"info": "京東",
"distance": "4.665364145881758",
"img": "https://cdn.caomall.net/1524732806744365022.png"
}
],
"current_page": "1",
"hasPage": 1
}
}