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