【转】MySql根据经纬度获取附近的商家

创建geo表

?

1 2 3 4 5 6 7

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 ) );

　　

?

1 2 3 4 5 6

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,"国际馆");

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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是经度，6371是地球的半径。

 

其他类似的资料：

https://www.jianshu.com/p/2d801e9cbadd

https://www.cnblogs.com/jiqing9006/p/8954831.html

https://blog.csdn.net/qq_42093488/article/details/81234990

https://blog.csdn.net/lvqingyao520/article/details/69667000

https://blog.csdn.net/Connie1451/article/details/80528737