# Distance between sequential points postgre

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
``````
• – yxcv
Nov 11, 2015 at 15:59

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`.

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.