mysql根据经纬度搜周边_mysql根据经纬度获取附近的商家

创建测试表

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;