About Me!
This blog is about my musings and thoughts. I hope you find it useful, at most, and entertaining, at least.
Other Pages
Presence Elsewhere
Visualizing System-wide Headway
Date: 2019-11-13
Tags: postgis postgres gis gtfs transit
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.