Distance between sequential points postgre

Question

My table is as follows

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"

How can I find the distance between two sequential points?

So I'd have id=1 and id=2 distance between them = 99m (distances would be found between [1,2],[2,3],[3,4] and so on

Later on I'd like to aggregate them (if distance < 100m aggregate)

I have not gotten very far with it

This gives me the distance but I don't know how to get the next row's geometry

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

Then I tried to read the distance as a additional column but could figure out a correct query

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

Desired result would be

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

A more general way to solve this (that doesn't rely on having a sequential id with no missing points) is to use the PostgreSQL lag windowing function (see docs).

In this case, it's as simple as:

SELECT
  id,
  dt, 
  ST_Distance(geom, lag(geom, 1) OVER (ORDER by dt ASC))
FROM testpoints;

where dt is the column holding your date-time values. How, then, to aggregate the points? One way is to use sum as a window function to see how many breaks have been encountered so far (I'm using 0.64 as a threshold here).

SELECT
  id,
  dt,
  dist,
  SUM(CASE WHEN dist > 0.64 THEN 1 ELSE 0 END) OVER (ORDER BY dt ASC) AS clustr
FROM
    (SELECT 
       id, 
       dt, 
       ST_Distance(geom, lag(geom, 1) OVER (ORDER BY dt ASC)) AS dist
     FROM testpoints) sq;

For my test points, the result now looks like this:

 id |              dt               |       dist        | clustr 
----+-------------------------------+-------------------+--------
  1 | 2015-11-11 08:15:37.996556-05 |                   |      0
  2 | 2015-11-11 08:15:39.237025-05 | 0.411143570472674 |      0
  3 | 2015-11-11 08:15:39.760647-05 |  0.18676903202556 |      0
  4 | 2015-11-11 08:15:40.123549-05 |  0.53587461849279 |      0
  5 | 2015-11-11 08:15:40.429877-05 | 0.699859353108215 |      1
  6 | 2015-11-11 08:15:40.729668-05 | 0.363468389297844 |      1
  7 | 2015-11-11 08:15:41.028922-05 | 0.649386937777059 |      2
  8 | 2015-11-11 08:15:41.32032-05  | 0.675600276096486 |      3

Now, if I want to aggregate these, I can select from the above with SELECT ST_Collect(geom) FROM query_above GROUP BY clustr, or perhaps ST_MakeLine(geom ORDER BY dt ASC) GROUP BY clustr.

Answer 2

This will give you a table with two ids and distance between those two.

SELECT b.id, a.id, a.wkb_geometry <-> b.wkb_geometry
FROM test a
JOIN test b ON (a.id = b.id + 1);

<-> gives you bbox distance, you might use <#> as well.