mysql使用sql语句根据经纬度计算距离排序

CREATE TABLE `locationpoint` (
  `id` int(11) NOT NULL,
  `province` varchar(20) NOT NULL,
  `city` varchar(20) NOT NULL,
  `longitude` double(10,3) NOT NULL,
  `latitude` double(10,3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `locationPoint` VALUES ('1147','安徽省', '合肥', '117.170', '31.520');
INSERT INTO `locationPoint` VALUES ('1148','安徽省', '安庆', '117.020', '30.310');
INSERT INTO `locationPoint` VALUES ('1149','安徽省', '蚌埠', '117.210', '32.560');
INSERT INTO `locationPoint` VALUES ('1150','安徽省', '亳州', '115.470', '33.520');
INSERT INTO `locationPoint` VALUES ('1151','安徽省', '巢湖', '117.520', '31.360');
INSERT INTO `locationPoint` VALUES ('1152','安徽省', '滁州', '118.180', '32.180');
INSERT INTO `locationPoint` VALUES ('1153','安徽省', '阜阳', '115.480', '32.540');
INSERT INTO `locationPoint` VALUES ('1154','安徽省', '贵池', '117.280', '30.390');
INSERT INTO `locationPoint` VALUES ('1155','安徽省', '淮北', '116.470', '33.570');

　　

SELECT  
    city,  
    longitude,  
    latitude,  
    ROUND(  
        6371.393 * 2 * ASIN(  
            SQRT(  
                POW(  
                    SIN(  
                        (  
                            22.53462 * 3.1415926 / 180 - latitude * PI() / 180  
                        ) / 2  
                    ),  
                    2  
                ) + COS(22.53462 * 3.1415926 / 180) * COS(latitude * PI() / 180) * POW(  
                    SIN(  
                        (  
                            113.9602 * 3.1415926 / 180 - longitude * PI() / 180  
                        ) / 2  
                    ),  
                    2  
                )  
            )  
        ) * 1000  
    ) AS distance_um  
FROM  
    locationpoint  
ORDER BY  
    distance_um ASC  

 

转载于:https://www.cnblogs.com/zyf-yxm/p/9330761.html