Shulou(Shulou.com)06/01 Report--

Environment

MySQL5.6

Https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-distance

Table structure and data

DROP TABLE IF EXISTS `locationpoint`; CREATE TABLE `locationpoint` (`id` int (11) NOT NULL AUTO_INCREMENT, `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 AUTO_INCREMENT = 1156 DEFAULT CHARSET = utf8 INSERT INTO `locationpoint`Vales (1, 'Shandong', 'Jinan', 116.938477, 36.597889), (2, 'Hebei', 'Shijiazhuang', 114.477539, 38.030786), (3, 'Zhejiang', 'Hangzhou', 120.058594, 30.334954), (4, 'Henan', 'Zhengzhou', 113.629, 34.744), (5, 'Anhui Province', 'Hefei' 117.170, 31.520)

Query mode

(taking Hohhot in Inner Mongolia Autonomous region as the computing center)

SELECT id, city, longitude, latitude, round (st_distance (point (longitude, latitude), point (111.621094) 40.913513)) / 0.0111) * 1000) AS distanceFROM locationpointORDER BY distance

Query result

These are the details of how MySQL calculates the two-coordinate distance and sorts it. Please pay more attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.