2

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? – ypercubeᵀᴹ Nov 8 '17 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 – a_horse_with_no_name Nov 8 '17 at 21:04
  • Can you dump actual data and your desired output? Such as the GPS points. – Evan Carroll Nov 8 '17 at 21:11
  • 2/3 of the addition problems are wrong. =( – Evan Carroll Nov 8 '17 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 Nov 9 '17 at 9:25
2

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 
values
  (1, NULL),
  (2,  2.3),
  (3,  4.4),
  (4,  1.7),
  (5,  1.2),
  (6,  2.7);

insert into trip
values
  (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.

|improve this answer|||||
  • +1 for the non-GIS solution. If he can't use GIS this is the way to go. – Evan Carroll Nov 8 '17 at 21:30
0

GIS Solution

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

CREATE EXTENSION 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.

BEGIN;
  ALTER TABLE foo
    ADD COLUMN geog geography;
  ALTER TABLE foo
    SET geog = ST_MakePoint(long,lat);
  ALTER TABLE foo
    DROP COLUMN long,
    DROP COLUMN lat;
COMMIT;

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.

|improve this answer|||||
0

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, 
       t.end_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 ;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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