数据库有个表,存的地址及其经纬度,想要查询每个地址距当前位置(经纬度)的距离(单位:米)并根据距离进行排序。
创建表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;
总结
对比两种方法,‘方法一’查询效率更高一些。但是经过地图实测验证,发现‘方法二’计算的距离精度更高。