[整理] MySQL 使用空间函数 st_distance_sphere ，通过经纬度查询最近的地点

目录

• 创建数据表
• 插入数据
• 函数st_distance_sphere 和 st_distance
• 查询距离成都最近的5个地方

创建数据表

create table data_point
(
    ID    int auto_increment primary key,
    city  varchar(64) not null,
    pt geometry    not null
)

插入数据

INSERT INTO data_point (city, pt) VALUES ('成都',ST_GeomFromText('POINT(104.040 30.400)'));
INSERT INTO data_point (city, pt) VALUES ('巴中',ST_GeomFromText('POINT(106.43 31.51)'));
INSERT INTO data_point (city, pt) VALUES ('崇州',ST_GeomFromText('POINT(103.4 30.39)'));
INSERT INTO data_point (city, pt) VALUES ('达州',ST_GeomFromText('POINT(107.29 31.14)'));
INSERT INTO data_point (city, pt) VALUES ('德阳',ST_GeomFromText('POINT(104.22 31.09)'));
INSERT INTO data_point (city, pt) VALUES ('都江堰',ST_GeomFromText('POINT(103.37 31.01)'));
INSERT INTO data_point (city, pt) VALUES ('峨眉山',ST_GeomFromText('POINT(103.29 29.36)'));
INSERT INTO data_point (city, pt) VALUES ('涪陵',ST_GeomFromText('POINT(107.22 29.42)'));
INSERT INTO data_point (city, pt) VALUES ('广汉',ST_GeomFromText('POINT(104.15 30.58)'));
INSERT INTO data_point (city, pt) VALUES ('广元',ST_GeomFromText('POINT(105.51 32.28)'));
INSERT INTO data_point (city, pt) VALUES ('华蓥',ST_GeomFromText('POINT(106.44 30.26)'));
INSERT INTO data_point (city, pt) VALUES ('简阳',ST_GeomFromText('POINT(104.32 30.24)'));
INSERT INTO data_point (city, pt) VALUES ('江油',ST_GeomFromText('POINT(104.42 31.48)'));
INSERT INTO data_point (city, pt) VALUES ('阆中',ST_GeomFromText('POINT(105.58 31.36)'));
INSERT INTO data_point (city, pt) VALUES ('乐山',ST_GeomFromText('POINT(103.44 29.36)'));
INSERT INTO data_point (city, pt) VALUES ('泸州',ST_GeomFromText('POINT(105.24 28.54)'));
INSERT INTO data_point (city, pt) VALUES ('绵阳',ST_GeomFromText('POINT(104.42 31.3)'));
INSERT INTO data_point (city, pt) VALUES ('南充',ST_GeomFromText('POINT(106.04 30.49)'));
INSERT INTO data_point (city, pt) VALUES ('内江',ST_GeomFromText('POINT(105.02 29.36'));
INSERT INTO data_point (city, pt) VALUES ('攀枝花',ST_GeomFromText('POINT(101.43 26.34)'));
INSERT INTO data_point (city, pt) VALUES ('彭州',ST_GeomFromText('POINT(103.57 30.59)'));
INSERT INTO data_point (city, pt) VALUES ('邛崃',ST_GeomFromText('POINT(103.28 30.26)'));
INSERT INTO data_point (city, pt) VALUES ('遂宁',ST_GeomFromText('POINT(105.33 30.31)'));
INSERT INTO data_point (city, pt) VALUES ('万县',ST_GeomFromText('POINT(108.21 30.5)'));
INSERT INTO data_point (city, pt) VALUES ('万源',ST_GeomFromText('POINT(108.03 32.03)'));
INSERT INTO data_point (city, pt) VALUES ('西昌',ST_GeomFromText('POINT(102.16 27.54)'));
INSERT INTO data_point (city, pt) VALUES ('雅安',ST_GeomFromText('POINT(102.59 29.59)'));
INSERT INTO data_point (city, pt) VALUES ('宜宾',ST_GeomFromText('POINT(104.34 28.47)'));
INSERT INTO data_point (city, pt) VALUES ('自贡',ST_GeomFromText('POINT(104.46 29.23)'));
INSERT INTO data_point (city, pt) VALUES ('资阳',ST_GeomFromText('POINT(104.38 30.09)'));

函数st_distance_sphere 和 st_distance

SELECT st_distance_sphere(POINT(121.590346, 31.388096),POINT(121.590345, 31.388095)) AS distant

结果（单位：米）
0.14620023407776306

SELECT st_distance(POINT(121.590346, 31.388096),POINT(121.590345, 31.388095))*111195 AS distant

结果：
0.15725347695038774

st_distance 计算的结果单位是度，需要乘111195（地球半径6371000*PI/180）是将值转化为米。

为什么不一样呢？

因为在不同纬度，每度的长度是不一样的。所以 st_distance_sphere 的结果是准确的。

查询距离成都最近的5个地方

SELECT city,distance FROM (SELECT *, ST_DISTANCE_SPHERE(pt, POINT(104.040,30.400)) AS DISTANCE FROM data_point) A
ORDER BY distance ASC LIMIT 5

结果：

"成都"	"0"
"广汉"	"22620.652448243116"
"简阳"	"32231.016053941366"
"资阳"	"47485.51587937459"
"彭州"	"49741.88669713098"