Postgis calculating distances between polypoints on big dataset

Question

I have a table that looks like this:

CREATE TABLE public.gps_data
(
  gpstimestamp timestamp with time zone,
  geom geometry(Geometry,27700),
  elevation double precision,
  accuracy double precision,
  bearing double precision,
  speedms double precision,
  subjectid character(100),
  id integer,
  curveid integer,
  distance double precision,
  usable integer
)

This is filled with about 6 million poly points. My aim is to calculate distances between those points, whereby the distance stored per row will be the distance of the current point to the point that comes before it if ordered by gpstimestamp or id.

Right now I'm doing that with the following query from php:

SELECT ST_Distance(a.geom, b.geom) FROM testdata a, testdata b WHERE     a.id=" . $i . " AND b.id=" . ($i - 1)

With $i running through the number of rows I have. Using this method I have to do 1 query per row which of course takes forever (on my laptop i get about 2000 queries per minute).

Answer 1

This should be possible with the window functions of postgres. With lag and lead you can define which rows are being compared with the second parameter (here set to 1 as example) you can also compare rows that are further away.

ST_Distance(geom, lag(geom,1) OVER (ORDER BY gpstimestamp))

Did test it with a dataset of 28k polygons and it was finished in ~ 1 second, on a medium fast desktop pc.