This blog is about my musings and thoughts. I hope you find it useful, at most, and entertaining, at least.
I was bored this evening and decided to visualized the system-wide headway, that is the amount of time between buses at a stop for every stop in the system.
Given the stop-distance table from the previous post, I set out to compute the headway for each stop segment (the portion of a route between stops).
-- Compute the max headway for each stop per hour. create table overall_headway as select stop_id, hour, max(headway) as max_headway from ( select stop_id, extract(hour from departure_time) as hour, departure_time, lag(departure_time, 1) over w as prev_departure_time, extract(epoch from (departure_time - lag(departure_time, 1) over w))/60 as headway from gtfs.stops_3365 join gtfs.stop_times using (stop_id) window w as (partition by stop_id order by departure_time asc) )x group by stop_id, hour; -- Assign the segments created for the stop-distance calculation -- a headway from overall_headway. create table stop_line_headway as select coalesce(ohs.hour, ohp.hour) as hour, sdl.stop_id, sdl.dist, prev_stop_id, min(least(ohs.max_headway, ohp.max_headway)) as headway, the_geom from stop_dist_line sdl left join overall_headway ohs on sdl.stop_id = ohs.stop_id left join overall_headway ohp on sdl.prev_stop_id = ohp.stop_id where (ohs.hour is null or ohp.hour is null or (ohs.hour = ohp.hour)) and st_length(the_geom) < 2500 group by coalesce(ohs.hour, ohp.hour), sdl.stop_id, sdl.dist, prev_stop_id, the_geom; alter table stop_line_headway add id serial primary key;
I then pulled this into QGIS, colored by graduation, and filtered per hour.
For each our, I exported a PNG of the map and tried by hand at KDEnlive for the
first time. (Although
ffmpeg could have gotten the job done as well.)
Raw video, images, and more details on building the map to come.