MySQL之空间函数**ST_Distance_Sphere

MySQL之空间函数ST_Distance_Sphere

用来计算两个经纬度之间的球体距离

SELECT ST_Distance_Sphere(POINT(116.4025249,39.9251859),POINT(116.4025249,39.9250644)) AS distant;

上述算出的单位是米

接下来尝试一下建表

CREATE TABLE `map` (
 `id` int(11) NOT NULL,
 `address` varchar(255) NOT NULL DEFAULT '',
 `location` geometry NOT NULL,
 PRIMARY KEY (`id`),
 SPATIAL KEY `idx_location` (`location`)
)

插入数据

INSERT INTO map (id, address, location) VALUES (1, '天安门', ST_GeomFromText('POINT(116.403977 39.915118)'));
INSERT INTO map (id, address, location) VALUES (2, '八达岭', ST_GeomFromText('POINT(116.040585 40.363932)'));

测试北京动物园到天安门和八达岭的距离，（116.343903,39.948574）为北京动物园的坐标

SELECT address, ST_Distance_Sphere(POINT(116.343903,39.948574),location) AS distant FROM map;

结果图如下