mysql根据用户经纬度查询附近商家,mysql根据经纬度获取附近的商家

创建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

}

}