sql - 计算一列中不同条目之间的距离
问题描述
我的表中有这种列:
Table A:
geom_etrs(geometry)
"0101000020E8640000FE2EAF0B3C981C414E499E34DFE65441"
"0101000020E864..."
"0101000020E875..."
"0101000020E867..."
如何计算每个条目之间的距离(它们已经定义为POINT
)?
我想创建一个新列,其中显示 1 和 2 之间的距离,然后是 2 和 3 之间的距离,然后是 3 和 4 之间的距离,依此类推。
解决方案
select st_distance(point, lead(point,1) over (partition by rn))
from ( select point, row_number() over (partition by id) as rn
from table_1)t;
gis=# \d users
user_id | bigint |
geog | geography |
select st_astext(geog), st_astext(lead(geog,1) over (partition by rn)) from ( select geog, row_number() over (partition by user_id) as rn from users limit 10)t;
st_astext | st_astext
-------------------------------------------+-------------------------------------------
POINT(-70.0777937636872 41.6670617084209) | POINT(-70.0783833464664 41.6675384387944)
POINT(-70.0783833464664 41.6675384387944) | POINT(-70.0793901822679 41.667476122803)
POINT(-70.0793901822679 41.667476122803) | POINT(-70.0787530494335 41.6671461707966)
POINT(-70.0787530494335 41.6671461707966) | POINT(-70.07908017161 41.6663672501228)
POINT(-70.07908017161 41.6663672501228) | POINT(-70.0795407352778 41.6669886861798)
POINT(-70.0795407352778 41.6669886861798) | POINT(-70.0798881265976 41.6663775240468)
POINT(-70.0798881265976 41.6663775240468) | POINT(-70.0781470955597 41.6667824284963)
POINT(-70.0781470955597 41.6667824284963) | POINT(-70.0790447962989 41.6675773546665)
POINT(-70.0790447962989 41.6675773546665) | POINT(-70.0778760883834 41.6675017901701)
POINT(-70.0778760883834 41.6675017901701) |
gis=# select st_distance(geog, lead(geog,1) over (partition by rn)) from ( select geog, row_number() over (partition by user_id) as rn from users limit 10)t;
st_distance
--------------
72.21147623
84.13511302
64.48606246
90.70040367
78.96272466
73.78817244
151.81026032
115.69092832
97.69189128
这应该适合你
推荐阅读
- python - 无法实现此 python 程序以找到具有 2D 数组最大和的子数组的总和
- javascript - 使用虚拟对象从另一个对象数组创建对象数组
- amazon-web-services - 在 EKS AWS 中删除节点组
- swift - 在swift中将hypens添加到车辆登记号
- node.js - Mongoose - 从另一个文档的子文档填充
- javascript - 首次在网站上展示广告横幅
- vb.net - pdf itextsharp中的阿拉伯语无法正常工作
- git - 如何通过受保护分支的命令行解决合并 --squash 的冲突?
- c++ - Xcode 无法在 macOS、MacBook Air、Catalina 10.15.3 上运行 OpenGL 代码
- php - 有没有办法在 Linux 中不区分大小写地包含文件?