shop 表结构
CREATE TABLE `shop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '店铺名字',
`lat` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '纬度',
`lng` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '经度',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
表数据
mysql> select * from shop;
+----+------------------+-----------+------------+
| id | name | lat | lng |
+----+------------------+-----------+------------+
| 1 | 太古汇 | 23.139455 | 113.338911 |
| 2 | 天河城(东圃店) | 23.128592 | 113.411026 |
+----+------------------+-----------+------------+
2 rows in set (0.04 sec)
计算自己距离数据库的地点距离，并且根据距离由近到远排序
类似美团外卖，计算商家距离
本人纬度经度由客户端传过来的，
TP框架实例
$lat=23.130283;
$lng=113.401529;
$pi= pi();//圆周率
$radius=6371;//地球半径，单位千米，精确到米是可以用6371000
$list=Db::connect($db)->table("shop")
->field("name,lat,lng,ACOS(SIN(($lat* $pi) / 180) * SIN((lat * $pi) / 180 ) + COS(($lat* $pi) / 180 ) * COS((lat * $pi) / 180 ) *COS(($lng* $pi) / 180 - (lng * $pi) / 180 ) ) * $radius as distance")
->order("distance asc")
->select();
var_dump($list);
生成的SQL
SELECT
name,
lat,
lng,
ACOS(
SIN(( 23.130283 * 3.1415 ) / 180 ) * SIN(( lat * 3.1415 ) / 180 ) + COS(( 23.130283 * 3.1415 ) / 180 ) * COS(( lat * 3.1415 ) / 180 ) * COS(( 113.401529 * 3.1415 ) / 180 - ( lng * 3.1415 ) / 180 )
) * 6380 AS distance
FROM
`shop`
ORDER BY
`distance` ASC
查询得到数据，distance就是具体距离，单位千米
array (size=2)
0 =>
array (size=4)
'name' => string '天河城(东圃店)' (length=24)
'lat' => string '23.128592' (length=9)
'lng' => string '113.411026' (length=10)
'distance' => float 0.99054396644128
1 =>
array (size=4)
'name' => string '太古汇' (length=9)
'lat' => string '23.139455' (length=9)
'lng' => string '113.338911' (length=10)
'distance' => float 6.4925896322261
如果觉得《mysql根据用户经纬度查询附近商家_mysql实现通过经纬度查询到店铺的距离》对你有帮助，请点赞、收藏，并留下你的观点哦！
