環境
mysql5.6
https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-distance
表結構及數據
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` VALUES?(1,?'山東',?'濟南',?116.938477,?36.597889), ???????(2,?'河北',?'石家莊',?114.477539,?38.030786), ???????(3,?'浙江',?'杭州',?120.058594,?30.334954), ???????(4,?'河南',?'鄭州',?113.629,?34.744), ???????(5,?'安徽省',?'合肥',?117.170,?31.520);
查詢方式
(以內蒙古自治區呼和浩特市為計算中心)
SELECT?id, ???????city, ???????longitude, ???????latitude, ???????round( ???????????????????( ???????????????????????????st_distance( ???????????????????????????????????point(longitude,?latitude), ???????????????????????????????????point(111.621094,?40.913513) ???????????????????????????????)?/?0.0111 ???????????????????????)?*?1000 ???????????) ???????????AS?distance FROM?locationpoint ORDER?BY?distance;
查詢結果
推薦:《mysql教程》
? 版權聲明
文章版權歸作者所有,未經允許請勿轉載。
THE END