GTFS maps

gtfsmap.sql

CREATE TABLE vta.segments AS
SELECT 
  -- make a speed placeholder, update later
  s.trip_id, s.stop_id AS start_id, e.stop_id AS end_id, s.time AS start_time, e.time AS end_time,
  trips.route_id, trips.trip_headsign, -1.01 AS speed,
  -- get the time, and deal with wrapped times like 24:21 for a trip that runs past midnight
  -- if the time is negative, add 24:00:00
  CASE 
    -- segment crosses midnight: wrap it
    WHEN SUBSTR(e.time, 1, 2)::int2 >= 24 AND SUBSTR(s.time, 1, 2)::int2 < 24
      -- convert the past-midnight time to an early-morning time
      THEN (((SUBSTR(e.time, 1, 2)::int2 % 24)::varchar || SUBSTR(e.time, 3))::time - s.time::time) + '24:00:00'::interval

    -- segment is entirely after midnight; convert to early-morning
    WHEN SUBSTR(e.time, 1, 2)::int2 >= 24 AND SUBSTR(s.time, 1, 2)::int2 >= 24
      THEN ((SUBSTR(e.time, 1, 2)::int2 % 24)::varchar || SUBSTR(e.time, 3))::time -
           ((SUBSTR(s.time, 1, 2)::int2 % 24)::varchar || SUBSTR(s.time, 3))::time

    -- normal, daytime case
    ELSE
      e.time::time - s.time::time
  END AS traversal,
  
  -- subquery: get all of the stops in between these two on this trip
  (SELECT ST_MakeLine(stops.geom) AS the_geom
     FROM vta.stop_times 
     JOIN vta.stops 
    USING (stop_id) 
    WHERE trip_id = s.trip_id AND
     stop_sequence BETWEEN s.stop_sequence AND e.stop_sequence
 GROUP BY trip_id)
   AS the_geom
   
FROM 
-- COALESCE: use departure when available, otherwise arrival
  (SELECT trip_id, stop_id, stop_sequence, COALESCE(departure_time, arrival_time) AS time,
   -- get a row number for joining. They will be sequential; non timepoints are not counted due to the WHERE clause
       ROW_NUMBER() OVER (ORDER BY trip_id, stop_sequence) AS num  
     FROM vta.stop_times
    WHERE COALESCE(departure_time, arrival_time) IS NOT NULL) 
  AS s
JOIN 
  (SELECT trip_id, stop_id, stop_sequence, COALESCE(arrival_time, departure_time) AS time,
       ROW_NUMBER() OVER (ORDER BY trip_id, stop_sequence) AS num 
     FROM vta.stop_times
    WHERE COALESCE(arrival_time, departure_time) IS NOT NULL) 
  AS e
ON (s.num = e.num - 1 AND s.trip_id = e.trip_id)
-- pull in the route id
JOIN vta.trips ON (s.trip_id = trips.trip_id);

-- units are mph
-- cast to geography so math is done on a spheroid
UPDATE vta.segments SET speed = (ST_Length(the_geom::geography) / EXTRACT (EPOCH FROM traversal)) * 2.23693629;

-- so that QGIS can load it
ALTER TABLE vta.segments ADD COLUMN id SERIAL;