postgresql - 连续点postgres之间的距离

Question

我的表格如下

1;"2015-10-02";"POINT(lat,lon) as geometry"
2;"2015-10-03";"POINT(lat,lon) as geometry"
3;"2015-10-04";"POINT(lat,lon) as geometry"

如何找到两个连续点之间的距离？

所以我有 id=1 和 id=2 它们之间的距离 = 99m(距离会在 [1,2]、[2,3]、[3,4] 等之间找到

然后如果距离 < 100m 聚合它们

我还没有深入研究

这给了我距离，但我不知道如何获得下一行的几何形状

SELECT st_distance_sphere(t.latlon,next.latlon) from test as t where id=1

然后我尝试将距离作为附加列读取，但可以找出正确的查询

UPDATE test SET dist=ST_Distance(test.latlon, next.geom) 
FROM (SELECT latlon FROM test WHERE id = test.id + 1) into b;

1;"2015-10-02";"POINT(lat,lon) as geometry";distance between 1 and 2
2;"2015-10-03";"POINT(lat,lon) as geometry";distance between 2 and 3
3;"2015-10-04";"POINT(lat,lon) as geometry";distance between 3 and 4

Answer 1

要获取当前点和下一个点之间的距离，您可以使用 window function lead 像这样:

select
        test.*,
        st_distance(latlon, lead(latlon, 1, latlon) over(order by id)) as distance
    from test;