]> jaekl.net Git - oct_sched.git/blob - lib/gtfs.rb
Add cancellations_loader, and rework gtfs to handle a stop_code with multiple stop_ids.
[oct_sched.git] / lib / gtfs.rb
1 # frozen_string_literal: true
2
3 require "date"
4 require "pry"
5 require "sqlite3"
6
7 class Gtfs
8   def initialize
9     @calendar = nil
10   end
11
12   def active_schedules(date_hash: nil)
13     target_date = date_hash || today
14     base_schedules = calendar_schedules(target_date)
15     overrides = calendar_overrides(target_date)
16
17     base_schedules + overrides[:add] - overrides[:remove]
18   end
19
20   def trips_for_stop(stop_code:, date_hash: nil)
21     result = {}
22
23     schedules = active_schedules(date_hash: date_hash)
24     schedule_placeholders = (["?"] * schedules.count).join(",")
25     stop_ids = db.query("SELECT stop_id FROM stops WHERE stop_code=?", stop_code).to_a.map do |row|
26       row.first
27     end
28     stop_id_placeholders = (["?"] * stop_ids.count).join(",")
29
30     sql = <<~EOS
31       SELECT r.route_short_name,t.trip_headsign,t.direction_id,st.departure_time
32       FROM routes r
33       INNER JOIN trips t ON t.route_id=r.route_id
34       INNER JOIN stop_times st ON st.trip_id=t.trip_id
35       WHERE st.stop_id IN (#{stop_id_placeholders})
36       AND service_id IN (#{schedule_placeholders})
37       ORDER BY r.route_short_name,st.departure_time;
38     EOS
39
40     db.query(sql, stop_ids, schedules).each do |row|
41       route = row[0..2] # [route_id, hedsign, direction_id]
42       departure_time = row[3]
43
44       if result[route].nil?
45         result[route] = []
46       end
47
48       result[route] << departure_time
49     end
50
51     # Sometimes OC Transpo lists the same departure under two separate schedules, *both* of which are
52     # simulteneously in effect.  (?)
53     # Here we compensate for that by eliminating duplicates of the same departure time.
54     result.map do |route, departures|
55       [route, departures.uniq]
56     end.to_h
57   end
58
59   private
60
61   def calendar
62     @calendar ||= begin
63       sql = <<~EOS
64         SELECT service_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday, start_date, end_date
65         FROM calendar
66         ORDER BY start_date, end_date
67       EOS
68       db.query(sql).to_a
69     end
70   end
71
72   def calendar_overrides(target_date)
73     result = {add: [], remove: []}
74     datestamp = date_for(target_date)
75     sql = <<~EOS
76       SELECT service_id, exception_type
77       FROM calendar_dates
78       WHERE calendar_date = ?
79     EOS
80
81     db.query(sql).each do |row|
82       case row[1]
83       when "1"
84         result[:add] << row[0]
85       when "2"
86         result[:remove] << row[0]
87       else
88         raise "Unexpected exception_type #{row[1].inspect} found in calendar_dates"
89       end
90     end
91
92     result
93   end
94
95   def calendar_schedules(target_date)
96     result = []
97     datestamp = date_for(target_date)
98     weekday = weekday_for(target_date)
99     calendar.each do |service_id, mon, tue, wed, thu, fri, sat, sun, start_date, end_date|
100       if datestamp >= start_date && datestamp <= end_date
101         if (0 == weekday && "1" == sun) ||
102             (1 == weekday && "1" == mon) ||
103             (2 == weekday && "1" == tue) ||
104             (3 == weekday && "1" == wed) ||
105             (4 == weekday && "1" == thu) ||
106             (5 == weekday && "1" == fri) ||
107             (6 == weekday && "1" == sat)
108           result << service_id
109         end
110       end
111     end
112     result
113   end
114
115   def date_for(date_hash)
116     "#{date_hash[:year]}#{date_hash[:month].to_s.rjust(2, "0")}#{date_hash[:day].to_s.rjust(2, "0")}"
117   end
118
119   def db
120     @db ||= SQLite3::Database.new("gtfs.db")
121   end
122
123   def today
124     # Beware:  This assumes your system is in America/Toronto time.
125     now = Date.today
126     {
127       year: now.year,
128       month: now.month,
129       day: now.day,
130     }
131   end
132
133   def weekday_for(date_hash)
134     Date.new(date_hash[:year], date_hash[:month], date_hash[:day]).wday
135   end
136 end