[整理] MySQL 使用空间函数 st_distance_sphere ，通过经纬度查询最近的地点
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[整理] MySQL 使用空间函数 st_distance_sphere ，通过经纬度查询最近的地点，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3597字，纯文字阅读大概需要6分钟。
内容图文
创建数据表
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"
原文：https://www.cnblogs.com/feily/p/15179938.html
内容总结
以上是互联网集市为您收集整理的[整理] MySQL 使用空间函数 st_distance_sphere ，通过经纬度查询最近的地点全部内容，希望文章能够帮你解决[整理] MySQL 使用空间函数 st_distance_sphere ，通过经纬度查询最近的地点所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。