Using postgresql 10
I have a table gpspoints which among other things contains
id dist 1 NULL 2 2.3 3 4.4 4 1.7 5 1.2 6 2.7
and so on
Dist is distance in meter since the previous point In addition, I have a table trip that gives the first and last id from the gpstable for a trip. Each point may belong to several overlapping trip.
The trip table is then like:
id start end 1 1 4 2 2 3 3 3 5
When I do my query I know that start < end and there will never be more than one defined trip for each gpspoint
I would like to add up the length of the trip for each point that is defined as a start point, i.e.
start length 1 9.4 # (2.3 + 4.4 + 1.7) 2 6.1 # (4.4 + 1.7) 3 7.3 # (4.4 + 1.7 + 1.2)
Writing this, I realize that a possible solution would be to make a new table containing the trip id and the gpspointid for each trip. But as the gpspoint table has a few millions rows and there may be up to a few hundred trips that each point is a member of (Each of the trips may contain a few hundred points) and the trips are clearly defined by their start and end point, my question is still
Is there a way to sum a number between two given id with varying distance between the ids? (if there always had been the same number of points in a trip, I could have made the sums using windowing functions)