MySQL怎么計算兩坐標距離并排序

MySQL怎么計算兩坐標距離并排序

環境

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怎么計算兩坐標距離并排序

推薦:《mysql教程

? 版權聲明
THE END
喜歡就支持一下吧
點贊7 分享