根据经纬度查询距离并按距离进行排序

数据库有个表，存的地址及其经纬度，想要查询每个地址距当前位置（经纬度）的距离（单位：米）并根据距离进行排序。

创建表store

CREATE TABLE `store` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `province_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `city_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `region_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `lng` decimal(10,6) unsigned DEFAULT NULL COMMENT '经度',
  `lat` decimal(10,6) unsigned DEFAULT NULL COMMENT '纬度',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3540 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

//插入数据
INSERT INTO `store` VALUES (2597, '芦庄店', '10080109', '131-1508-9880', '320000', '320200', '320211', '江苏无锡市滨湖区芦庄路58-1号', 120.313392, 31.529345);
INSERT INTO `store` VALUES (2598, '莱西商业街店', '10220040', '15921422440', '370000', '370200', '370285', '山东青岛市莱西市 山东省青岛市莱西市商业街', 120.528315, 36.865844);
INSERT INTO `store` VALUES (2599, '椒江康平路店', '10170163', '。', '330000', '331000', '331002', '浙江台州市椒江区康平路229号', 121.424017, 28.656769);
INSERT INTO `store` VALUES (2600, '长兴利时广场店', '10170161', '。', '330000', '330500', '330522', '浙江湖州市长兴县中央大道与长洲路交叉口的利时广场', 119.925811, 31.026153);
INSERT INTO `store` VALUES (2601, '豫园人民店', '10010321', '132-6252-0090', '310000', '310100', '310101', '上海市上海市黄浦区上海市黄浦区人民路399号NEO商厦1层03单元', 121.498505, 31.234871);
INSERT INTO `store` VALUES (2602, '宝杨宝龙店', '10010331', '18818292910', '310000', '310100', '310113', '上海市上海市宝山区上海市宝山区同济路669弄8号', 121.488406, 31.400972);
INSERT INTO `store` VALUES (2603, '浦江万达店', '10010333', '54386385', '310000', '310100', '310112', '上海市上海市闵行区永跃路360号', 121.518080, 31.033337);
INSERT INTO `store` VALUES (2604, '奉贤海泉店', '10010335', '021-37596218', '310000', '310100', '310120', '上海市上海市奉贤区上海市奉贤区海泉路575号1层', 121.517148, 30.848402);
INSERT INTO `store` VALUES (2605, '青浦绿地滨纷城店', '10010336', '未开业', '310000', '310100', '310118', '上海市上海市青浦区上海市青浦区外青松公路5999弄B1层B1-03-a', 121.131746, 31.165160);
INSERT INTO `store` VALUES (2606, '万嘉店', '10010337', '未开业', '310000', '310100', '310115', '上海市上海市浦东新区上海市浦东新区行德路86、92、96、100号万嘉商业广场一层33室', 121.617300, 31.299490);
INSERT INTO `store` VALUES (2607, '益江路店', '10010340', '未开业', '310000', '310100', '310115', '上海市上海市浦东新区上海市浦东新区益江路127号1楼-5室', 121.630805, 31.209385);
INSERT INTO `store` VALUES (2608, '青浦苏杭时代店', '10010342', '187-0214-7370', '310000', '310100', '310118', '上海市上海市青浦区上海市青浦区华新镇华腾公路558号苏杭时代华新店一层1F-01', 121.234843, 31.243568);
INSERT INTO `store` VALUES (2609, '玫瑰天街店', '10240011', '18828020857', '500000', '500100', '500112', '重庆市 重庆市渝北区重庆市北部新区金洲大道42号4幢1-115号', 106.552117, 29.649191);
INSERT INTO `store` VALUES (2610, '金贸时代店', '10240013', '13983980552', '500000', '500100', '500112', '重庆市 重庆市渝北区重庆市北部新区栖霞16号3幢1-3商铺', 106.572516, 29.644757);

注意：上述数据经纬度不一定准确，不过并不影响SQL测试

方法一：

SELECT
	*,
	ROUND(
		6378.138 * 2 * ASIN(
			SQRT(
			POW( SIN(( 31.163973 * PI()/ 180-lat * PI()/ 180 )/ 2 ), 2 )+ COS( 31.163973 * PI()/ 180 )* COS( lat * PI()/ 180 )* POW( SIN(( 121.404032 * PI()/ 180-lng * PI()/ 180 )/ 2 ), 2 ))) * 1000 
	) AS dis 
FROM
	store 
ORDER BY
	dis ASC；

#简化

SELECT
	*,
	ROUND(
		6378.138 * 2 * ASIN(
			SQRT(
			POW( SIN(( 31.163973 - lat ) * PI()/ 360 ), 2 ) + COS( 31.163973 * PI() / 180 ) * COS( lat * PI() / 180 ) * POW( SIN(( 121.404032 - lng ) * PI() / 360 ), 2 ))) * 1000 
	) AS dis 
FROM
	store 
ORDER BY
	dis ASC;

dis单位为m，如果想变为km的话可以将SQL语句中的 *1000去掉

方法二：

采用的这种方法，实现起来更简单，当然存储引擎可以是InnoDB。

需要用到st_distance函数

POINT()：从坐标构造点
ST_DISTANCE()：一个几何体与另一个几何体的距离，计算的结果单位是度，需要乘6371000*π/180=111195是将值转化为米。

MySQL其实在很早就提供了这种存储经纬度及相关运算的功能，这种数据类型叫做空间数据类型，而对应的索引被称为空间索引，但由于MySQL之前的版本对InnoDB支持的并不是太好，所以使用的并不多。不过MySQL5.6和MySQL5.7对此进行了优化，添加了st_distance等相关函数来支持经纬度相关的计算。

地球半径6371km

SELECT
	*,
	( st_distance ( point ( lng, lat ), point ( 121.404032, 31.163973 )) * 111195 ) AS dis 
FROM
	store 
ORDER BY
	dis ASC;

dis单位是米

处理dis为整型

SELECT
	*,
	CAST(( st_distance ( point ( lng, lat ), point ( 121.404032, 31.163973 )) * 111195 ) AS DECIMAL(10,0)) AS dis 
FROM
	store 
ORDER BY
	dis ASC;

总结

对比两种方法，‘方法一’查询效率更高一些。但是经过地图实测验证，发现‘方法二’计算的距离精度更高。