Make Transit Speed Maps from GTFS

lines.sql

-- (C) Matthew Conway, CC-BY-NC-SA
SELECT ROW_NUMBER() OVER () AS oid, t1.trip_id, t1.stop_id as from_id, t1.departure_time as from_time, t1.stop_sequence AS from_seq,
       t2.stop_id as to_id, t2.arrival_time as to_time, t2.stop_sequence AS to_seq,
       EXTRACT(EPOCH FROM t2.arrival_time::time - t1.departure_time::time) AS traversal,
       (SELECT ST_MakeLine(geom) AS the_geom
          FROM vta.stop_times 
          JOIN vta.stops USING (stop_id)
        WHERE trip_id = t1.trip_id AND stop_sequence BETWEEN t1.stop_sequence AND t2.stop_sequence) AS the_geom
        INTO vta.trip_segments
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY trip_id, departure_time NULLS LAST) AS num FROM vta.stop_times WHERE departure_time IS NOT NULL) t1
JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY trip_id, departure_time NULLS LAST) AS num FROM vta.stop_times WHERE arrival_time IS NOT NULL) t2
ON (t1.trip_id = t2.trip_id
    AND (t1.num + 1) = t2.num)
WHERE (SUBSTR(t1.departure_time, 1, 2)::int2 <= 23 AND
       SUBSTR(t2.arrival_time, 1, 2)::int2 <= 23);
ALTER TABLE vta.trip_segments ADD COLUMN length FLOAT;
ALTER TABLE vta.trip_segments ADD COLUMN mph FLOAT;
-- I used 26943 because this data is in the Bay Area. Select an appropriate coordinate system, and be sure
-- to update the conversion factors if your coordinate system does not use meters!
UPDATE vta.trip_segments SET length = ST_Length(transform(the_geom, 26943));
UPDATE vta.trip_segments SET mph = (length/traversal)*2.23693629;

-- get route ids, for filtering
-- you may not need this
UPDATE vta.trip_segments SET route_id = (SELECT route_id FROM vta.trips WHERE trips.trip_id = trip_segments.trip_id);

CREATE VIEW vta.trip_segments_bytime AS SELECT * FROM vta.trip_segments ORDER BY traversal DESC;

SELECT avg(mph) AS mph, avg(traversal) AS traversal, max(length) AS length, count(*) AS trips, route_id, the_geom
INTO vta.aggregated_trip_segments
FROM vta.trip_segments 
GROUP BY route_id, the_geom;