mysql 根据地址经纬度查询距离
SELECT *,
round(
(
6371 * acos (
cos ( radians(34.72468) )
* cos( radians( job_lat ) )
* cos( radians( job_lng ) - radians(113.6401) )
+ sin ( radians(34.72468) )
* sin( radians( job_lat ) )
)
)
,2)AS distance
FROM
test_1
WHERE 1=1 HAVING distance < 50
ORDER BY distance
郑州:经度:113.6401 纬度:34.72468
查询的表中必须要含有经纬度。
创建数据信息:
CREATE TABLE `test_1` (
`job_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'job表的id',
`job_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作地址',
`job_lng` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经度',
`job_lat` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '纬度',
PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '兼友圈兼职工作' ROW_FORMAT = Dynamic;
INSERT INTO `test_1` VALUES ('02b591d465414eda941fcf634ecbaa3120200818145124', '郑州市二七万达广场', '113.642998', '34.718029');
INSERT INTO `test_1` VALUES ('0b7ade3b3b0c4574814a7bbdca2cb88420200818145124', '郑州市火车站', '113.661537', '34.744843');
INSERT INTO `test_1` VALUES ('103fc835d64843fa8715ced59dfb423b20200818145125', '郑州市郑州东站', '113.779694', '34.759293');
INSERT INTO `test_1` VALUES ('5956b3f5e7b44eb9a55f72b89e8d62a420200818145125', '郑州会展中心', '113.730286', '34.773014');
INSERT INTO `test_1` VALUES ('b196ee36d13448d490c839e6a1149d2820200818145121', '郑州市河南省人民医院', '113.694672', '34.773144');
INSERT INTO `test_1` VALUES ('d9daa69cbbb246218537cb53fdf241d120200818145124', '郑州市河南省体育场', '113.665611', '34.768169');
INSERT INTO `test_1` VALUES ('f0ca5818936245ca8b965e18b4ce65a120200818145125', '郑州市郑州南站', '113.684013', '34.710991');