This blog is about my musings and thoughts. I hope you find it useful, at most, and entertaining, at least.

Quotes

Oak Island

Items for Sale

Presence Elsewhere

jim@jimkeener.com

GitHub

BitBucket

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.
select stop_id,
hour,
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
select coalesce(ohs.hour, ohp.hour) as hour,
sdl.stop_id,
sdl.dist,
prev_stop_id,
the_geom
from stop_dist_line sdl

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.)