風雲論壇后台开发 → 浏览:帖子主题
* 帖子主题:MySQL 内置的坐标距离计算方法
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 1 楼 ] 回复
普通计算两点之间距离:
st_distance(point(x1, y1), point(x2, y2))
如果需要转为米,需要 *111195,而且由于地球是球形,实际结果会有偏差

计算GEO坐标之间的距离(单位:米):
st_distance_sphere(point(lon1, lat1), point(lon2, lat2))

注意,GEO 坐标顺序为 lon, lat
2022/11/24 13:34:22 IP:已设置保密
pojin (ID: 2)
等级:精灵王
积分:159
发帖:1
来自:保密
注册:2022/3/30 11:42:27
造访:2023/3/5 15:16:37
[ 第 2 楼 ] 回复
MySQL 还干这事?
站长回复:是的,功能还很强大😁
2022/11/24 13:51:43 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 3 楼 ] 回复
补充说明:
关于 point(x, y) 的参数,有的认为应该是 lat, lon,也有的人认为应该是 lon, lat。
如果是矩形的坐标系,两个参数的顺序其实没什么区别,但用在球面上就可能会产生一些误差了。
实际编程中,大多使用 LatLon 的顺序,但是我翻阅了 MySQL 官方文档,找到了这么一句话:
Point X and Y coordinates are interpreted as longitude and latitude
所以,正确的顺序应该是 lon(经度),lat(纬度)
2022/11/24 13:55:05 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 4 楼 ] 回复
外卖配送类应用有个距离计算,用这个就很合适。
2022/11/24 13:56:32 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 5 楼 ] 回复
PostgreSQL 也支持这个功能,但是,需要安装 postgis 扩展,此扩展比 postgresql 本身还大,共70个依赖(postgresql 才 19个),直接安装 postgis 时会自动安装 postgresql。

安装完postgis后(Alpine命令:apk add postgis),需要使用 postgres 账号执行操作,其他账号无权限,例如:
docker exec -ti -u postgres 容器ID psql
\c youdatabase
create extension postgis;
添加扩展后,会往当前库里面创建一个 spatial_ref_sys 表并插入 8500 行 GIS 配置数据。

调用也稍微不一样,方法是 st_distancesphere:
select st_distancesphere(point(0, 0)::geometry, point(1, 1)::geometry)
需要声明类型,否则会提示参数类型不符。

如果不想安装 postgis,也可以直接用 postgresql 内置的两点距离计算方法 point_distance(point(0, 0), point(1, 0)) * 104567(单位:米),由于两点角度不一样,104567 是个估算参数,例如,在横向测量可以使用 97955,纵向可以使用 111195,45度角可以使用101500。使用折中参数的话,距离计算误差基本在 10% 以内(5公里以内的商家列出来是 4.5 ~ 5.5 公里之间的高椭圆形)。

实际上,根据纬度的不同,横向测量参数也有变化,计算方法实际应该是:111195*cos(纬度*3.14159/180),例如长沙地区是111195 * cos(28.2244 / 57.295827) ≈ 97974。

可以使用 gis 函数对比出该缩放比例:
select *, pt2/pt1 as scalerate from (
    select point_distance(point(116.420146, 39.897679), point(116.415122, 39.847263)) as pt1,
    st_distancesphere(point(116.420146, 39.897679)::geometry, point(116.415122, 39.847263)::geometry) as pt2
) a
2023/2/19 11:10:34 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 6 楼 ] 回复
在 chatGPT 的帮助下,写了一个 pgsql 计算经纬度距离的方法:
create or replace function distance_sphere
(in lon1 numeric, in lat1 numeric, in lon2 numeric, in lat2 numeric)
returns numeric
return asin(
    sqrt(
        sin(radians((lat1 - lat2) / 2)) ^ 2 +
        sin(radians((lon1 - lon2) / 2)) ^ 2 *
        cos(radians(lat1)) * cos(radians(lat2))
    )
) * 6371004 * 2
注意,这里的 x ^ 2 不是逻辑异或运算,而是计算 x 的平方(pow(x, 2));radians(lat1) 是弧度计算方法,结果和 lat1 * 3.14159 / 180 一样,但更精准,不支持该方法的数据库可以改成计算方式。公式是通用的,其他数据库和开发语言也适用里面的公式。

调用方法示例:
select distance_sphere(116.420146, 39.897679, 116.415122, 39.847263)

和 postgis 的 st_distancesphere 做了对比,5公里误差只有4毫米。这样的话,就可以不用安装庞大的 postgis 了。
2023/2/19 22:00:06 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:光明使者
积分:635
发帖:34
来自:保密
注册:2022/3/30 15:28:53
造访:2023/3/16 19:42:58
[ 第 7 楼 ] 回复
也可以这样,少了平方和平方根的计算:
create or replace function distance_sphere
(in lon1 numeric, in lat1 numeric, in lon2 numeric, in lat2 numeric)
returns numeric return acos(
    sin(radians(lat1)) * sin(radians(lat2)) +
    cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon1 - lon2))
) * 6371004
2023/2/20 8:32:18 IP:已设置保密
pojin (ID: 2)
等级:精灵王
积分:159
发帖:1
来自:保密
注册:2022/3/30 11:42:27
造访:2023/3/5 15:16:37
[ 第 8 楼 ] 回复
这个方法如何使用?调用是知道的,方法写在哪里呢?

======================================
版主回复(@2023/02/20 11:19:14):
上面创建的是pgsql方法。如果数据库不支持创建方法或存储过程,可以直接使用:
select acos(
    sin(radians(lat1)) * sin(radians(lat2)) +
    cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon1 - lon2))
) * 6371004 as juli
😊😊😊
2023/2/20 10:56:18 IP:已设置保密
分页: 1, 共 1 页
快速回复主题
账号/密码
用户: 没有注册? 密码:
评论内容