PostGIS distance query between actual and previous point in table

Question

Goord morning, i´m working with the following table in a postgres database(with postgis function):

[id], [time], [geom] (but the id is the same) (order by time)

That means, i have for each timestamp one specific point! And now i want to calculate the distance between these Points,that mean the actual Point with the previous Point. (not always with the first one!).

i used the following query to calculate the distance manually for two following points:

"select id,time,
ST_DISTANCE((SELECT geom FROM table WHERE time = '2017-01-01 07:03:59'),
(SELECT geom FROM table WHERE time = '2017-01-01 07:04:04')) as distance 
from(select id,time,st_dumppoints(geom) as dp from table where id = 145) as t1 order by zeit
"

So with this i only have ONE distance between two specific points and it would be very hard to do it manually for every row in this way. Does anyone have an idea to calculate the distances between each points automatically?

Thanks a lot!

Answer 1

You can use the LAG function to associate the previous row's geom value with each row. Then you can query the distance between geom and prev_geom. Note the first prev_geom will be NULL, so you need to handle that as suits your needs.

SELECT id
      ,time
      ,geom
      ,ST_DISTANCE(geom, prev_geom) AS distance
  FROM (SELECT id
              ,time
              ,geom
              ,LAG(geom) OVER (ORDER BY time) AS prev_geom
          FROM table
       ) x