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)

  • Can you explain the 2 6.1 # (4.4 + 1.7) result? Commented Nov 8, 2017 at 20:43
  • I don't understand 9.4 # (2.3 + 4.4 + 1.7) where does 9.4 come from? 2.3 + 4.4 + 1.7 = 8.4
    – user1822
    Commented Nov 8, 2017 at 21:04
  • Can you dump actual data and your desired output? Such as the GPS points. Commented Nov 8, 2017 at 21:11
  • 2/3 of the addition problems are wrong. =( Commented Nov 8, 2017 at 21:28
  • Could you please post your table structures as DDL (CREATE TABLE blah (...);) and data as DDL (INSERT INTO blah VALUES (...);), or provide an SQLFiddle or similar!
    – Vérace
    Commented Nov 9, 2017 at 9:25

3 Answers 3


I am not entirely sure I understood you, but maybe something like this:

select t.id, 
       sum(p.dist) as trip_distance, 
       array_agg(p.dist) as dist_values,
       array_agg(p.id) as points
from gpspoints p
  join trip t on p.id between t.start_point and t.end_point
group by t.id;

For the following sample data:

create table gpspoints  (id int primary key, dist numeric);
create table trip (id integer primary key, start_point int, end_point int);

insert into gpspoints 
  (1, NULL),
  (2,  2.3),
  (3,  4.4),
  (4,  1.7),
  (5,  1.2),
  (6,  2.7);

insert into trip
  (1, 1, 4),
  (2, 2, 3),
  (3, 3, 5);

The query returns:

id | trip_distance | dist_values        | points   
 1 |           8.4 | {NULL,2.3,4.4,1.7} | {1,2,3,4}
 2 |           6.7 | {2.3,4.4}          | {2,3}    
 3 |           7.3 | {4.4,1.7,1.2}      | {3,4,5}  

The columns dist_values and points are just there for debugging purposes to validate that the correct points were included.

  • +1 for the non-GIS solution. If he can't use GIS this is the way to go. Commented Nov 8, 2017 at 21:30

GIS Solution

This is a GIS problem at the core. Use PostGIS.


Select the points and use the aggregate ST_MakeLine(.. ORDER BY) They're ordered. It'll look like this. ST_Length will return the length in meters.

SELECT ST_Length(ST_MakeLine(geog ORDER BY id)) AS length_in_meters
FROM foo
CROSS JOIN LATERAL ST_MakePoint(long,lat)::geog AS geog
WHERE ID between start AND end;

This assumes somewhere you have the points collected in time table.

id | long | lat
0  | ...  | ...

Hint, if that assumption is right, you should be storing these as geog and not long and lat.

    ADD COLUMN geog geography;
    SET geog = ST_MakePoint(long,lat);
    DROP COLUMN long,
    DROP COLUMN lat;

I used a CROSS JOIN LATERAL to isolate the complexity. If the types were GIS types you wouldn't need that. So having run the above, you just delete that line.


Another way is to first calculate the distances from point 1 using a window function and then join twice to the trip table:

with g as 
  ( select id, 
           coalesce(dist, 0)  as dist, 
           coalesce(sum(dist) over (order by id), 0)  as sd 
    from gpspoints
select t.start_point, 
       g2.sd - g1.sd + g1.dist  as length
from trip as t 
  join g as g1 on t.start_point = g1.id
  join g as g2 on t.end_point = g2.id 
order by t.id ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.