mysql实现通过经纬度查询到店铺的距离

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