st_distance_sphere函数是mysql5.7提供的,可以直接查询两个经纬度之间相距多少米,它接受两个参数,每个参数是一个点的经度和纬度
表结构:
CREATE TABLE `video_alarm` (
`alarm_id` char(50) NOT NULL,
`alarm_type` varchar(20) DEFAULT NULL COMMENT '预警类型',
`alarm_time` datetime DEFAULT NULL COMMENT '预警发生事件',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`delete_flag` int(1) unsigned zerofill DEFAULT '0' COMMENT '删除标记',
`lng` decimal(15,12) DEFAULT NULL COMMENT '经度',
`lat` decimal(15,12) DEFAULT NULL COMMENT '纬度',
`address_name` varchar(20) DEFAULT NULL COMMENT '坐标地址名称',
PRIMARY KEY (`alarm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中保存经纬度用的是DECIMAL类型,DECIMAL是MySQL中存在的精准数据类型,以字符串的形式保存精确的原始数值
DECIMAL(P,S)
P是表示有效数字数的精度。 P范围为1〜65。
S是表示小数点后的位数。 S的范围是0~30。MySQL要求S小于或等于(<=)P。
MySQL 中 DECIMAL类型可以接受字符串和小数类型的值,并在插入时进行合适的类型转换,以满足列定义的精度和要求。
例如下面两种插入方式都可以正常新增
INSERT INTO video_alarm (alarm_id,lng,lat) VALUES ('1','116.439425900000','116.439425900000')
INSERT INTO video_alarm (alarm_id,lng,lat) VALUES ('2',116.439425900000,116.439425900000)
测试数据:
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('7fa919b42ba34e51bfedb7209874fde6', '车辆', '2024-06-04 14:57:19', '2024-06-04 14:57:19', 0, 116.439425900000, 39.961025400000, '怡和阳光大厦C座');
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('8a9f4dc704964f498bef323f9d173885', '徘徊', '2024-06-04 15:04:01', '2024-06-04 15:04:01', 0, 116.439192400000, 39.964094600000, '柳芳地铁站');
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('a6683c105b3b95d5618c9e03409c1dbb', '安全帽检测', '2024-06-05 15:17:56', '2024-06-05 15:17:56', 0, 116.439246300000, 39.962145300000, '林达大厦');
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('c5b42336fde41840f464f80f8c03d4b8', '行人', '2024-06-08 18:14:02', '2024-06-08 18:14:02', 0, 116.369483900000, 40.087734500000, '回龙观东大街地铁站');
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('c852d530681764e80e868f85bf3a9260', '车辆入侵', '2024-06-05 10:07:16', '2024-06-05 10:07:16', 0, 116.367319000000, 40.076793700000, '霍营地铁站');
INSERT INTO `video_alarm` (`alarm_id`, `alarm_type`, `alarm_time`, `update_time`, `delete_flag`, `lng`, `lat`, `address_name`) VALUES ('e4d228accdba4c5c95789084f8a0b106', '车辆', '2024-06-04 14:47:43', '2024-06-04 14:47:43', 0, 116.377945900000, 40.112587100000, '温都水城文化广场');
距离计算并从近到远排序:
SELECT address_name AS 地点,
ST_Distance_Sphere(POINT(116.439425900000,39.961025400000),POINT(lng,lat)) AS 距离
FROM video_alarm
ORDER BY 距离
运行结果:
地点 | 距离 |
怡和阳光大厦C座 | 0 |
林达大厦 | 125.46416817244642 |
柳芳地铁站 | 341.85843822828775 |
霍营地铁站 | 14262.313004760517 |
回龙观东大街地铁站 | 15296.370344917823 |
温都水城文化广场 | 17646.923764263298 |