1、新建表

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

`id` int(11) unsigned NOT NULL auto_increment COMMENT '自增主鍵',

`name` varchar(50) NOT NULL COMMENT '名稱',

`lon` double(9,6) NOT NULL COMMENT '經度',

`lat` double(8,6) NOT NULL COMMENT '緯度',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商戶表';

2、插入數據

INSERT INTO `customer` VALUES ('1', '天津市區', '117.315575', '39.133462');

INSERT INTO `customer` VALUES ('2', '北京市區', '116.407999', '39.894073');

INSERT INTO `customer` VALUES ('3', '保定', '115.557124', '38.853490');

INSERT INTO `customer` VALUES ('4', '石家莊', '114.646458', '38.072369');

INSERT INTO `customer` VALUES ('5', '昌平區1', '116.367180', '40.009561');

INSERT INTO `customer` VALUES ('6', '海淀區2', '116.313425', '39.973078');

INSERT INTO `customer` VALUES ('7', '海淀區1', '116.329236', '39.987231');

3、計算方法

一：

SELECT

*,

ROUND(

6378.138 * 2 * ASIN(

SQRT(

POW(

SIN(

(

40.0497810000 * PI() / 180 - lat * PI() / 180

) / 2

),

2

) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(

SIN(

(

116.3424590000 * PI() / 180 - lon * PI() / 180

) / 2

),

2

)

)

)

) AS juli

FROM

customer

ORDER BY

juli ASC

二：

st_distance 計算的結果單位是度，需要乘111195(地球半徑6371000*PI/180)是將值轉化為米。

SELECT

*,

(st_distance (point (lon,lat),point (116.3424590000,40.0497810000))*111195/1000 )as juli

FROM

customer

ORDER BY

juli ASC

總結

以上是生活随笔為你收集整理的mysql 经纬度 距离 排序_MySQL根据经纬度按距离排序的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。