lib/gtfs.rb

   1 # frozen_string_literal: true
   2
   3 require "date"
   4 require "pry"
   5 require "sqlite3"
   6
   7 require "date_utils"
   8 require "database"
   9
  10 class Gtfs
  11   MULTI_PLATFORM_STOPS = {
  12     "BARRHAVEN CENTRE" => ["1A", "2A"],
  13     "BASELINE" => ["1A", "1B", "1C", "1D", "2A", "2B", "STN OFF ONLY"],
  14     "BAYSHORE" => ["1A", "2A", "3A", "4A", "4B", "A", "B", "C"],
  15     "BAYVIEW" => ["A", "B", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  16     "BEATRICE" => ["1A", "2A"],
  17     "BILLINGS BRIDGE" => ["1A", "2A", "3A", "3B", "3C", "4B", "4C", "4D"],
  18     "BLAIR" => ["A", "B", "C", "D", "E", "H", "I", "O-TRAIN", "O-TRAIN WEST / OUEST"],
  19     "CHAPEL HILL" => ["A", "B"],
  20     "CYRVILLE" => ["A", "B", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  21     "DOMINION" => ["1A", "2A", "STN OFF ONLY"],
  22     "FALLOWFIELD" => ["1A", "2A"],
  23     "GREENBORO" => ["1A", "1B", "2A"],
  24     "HERON" => ["1A", "2A", "3A", "4A"],
  25     "HURDMAN" => ["A", "B", "C", "D", "E", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  26     "INNOVATION" => ["A", "B"],
  27     "IRIS" => ["1A", "2A", "STN OFF ONLY"],
  28     "JEANNE D'ARC" => ["1A", "1B", "2A", "3A", "3B", "4A", "4B"],
  29     "LEES" => ["A", "B", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  30     "LEITRIM" => ["1A", "2A"],
  31     "LINCOLN FIELDS" => ["1A", "1B", "2A", "3A", "4A", "4B", "5A", "OFF ONLY"],
  32     "LONGFIELDS" => ["1A", "2A"],
  33     "LYCÉE CLAUDEL" => ["1A", "2A"],
  34     "LYON" => ["A", "B", "C", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  35     "MACKENZIE KING" => ["1A", "2A"],
  36     "MARKETPLACE" => ["1A", "2A"],
  37     "MILLENNIUM" => ["1A", "2A"],
  38     "NEPEAN WOODS" => ["1A", "2A"],
  39     "PARLIAMENT / PARLEMENT" => ["A", "B", "C", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  40     "PIMISI" => ["A", "B", "C", "D", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  41     "PINECREST" => ["1A", "2A", "A", "B", "C"],
  42     "PLEASANT PARK" => ["1A", "2A", "3A", "4A"],
  43     "QUEENSWAY" => ["1A", "2A", "3A", "4A"],
  44     "RIDEAU" => ["A", "B", "C", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  45     "RIVERSIDE" => ["1A", "2A"],
  46     "RIVERVIEW" => ["1A", "2A"],
  47     "SMYTH" => ["1A", "2A"],
  48     "SOUTH KEYS" => ["1A", "1B", "1C", "1D", "2A"],
  49     "STRANDHERD" => ["1A", "2A"],
  50     "ST-LAURENT" => ["A", "B", "C", "D", "O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  51     "TERON" => ["1A", "2A"],
  52     "TREMBLAY" => ["O-TRAIN EAST / EST", "O-TRAIN WEST / OUEST"],
  53     "TRIM" => ["1A", "2A"],
  54     "TUNNEY'S PASTURE" => ["A", "B", "C", "D", "E", "F", "G", "O-TRAIN", "O-TRAIN EAST / EST"],
  55     "WALKLEY" => ["1A", "2A", "3A", "4A", "4B", "4C"],
  56     "WESTBORO" => ["1A", "1B", "2A", "3A", "3A (A)", "4A", "A", "B", "OFF ONLY", "STN OFF ONLY"],
  57   }
  58
  59   def initialize
  60     @calendar = nil
  61     @date_utils = DateUtils.new
  62     @db = Database.new.db
  63   end
  64
  65   def active_schedules(date_hash: nil)
  66     target_date = date_hash || @date_utils.today
  67     base_schedules = calendar_schedules(target_date)
  68     overrides = calendar_overrides(target_date)
  69
  70     base_schedules + overrides[:add] - overrides[:remove]
  71   end
  72
  73   def trips(stop_code: nil, stop_id: nil, stop_name: nil, date_hash: nil, originating_only: false)
  74     args = [stop_code, stop_id, stop_name].compact
  75     raise "Must specify exactly one of stop_code, stop_id, or stop_name" if args.count != 1
  76
  77     if !stop_code.nil?
  78       trips_for_stop_code(stop_code: stop_code, date_hash: date_hash, originating_only: originating_only)
  79     elsif !stop_id.nil?
  80       trips_for_stop_ids(stop_ids: [stop_id], date_hash: date_hash, originating_only: originating_only)
  81     else
  82       trips_for_stop_name(stop_name: stop_name, date_hash: date_hash, originating_only: originating_only)
  83     end
  84   end
  85
  86   private
  87
  88   def trips_for_stop_code(stop_code:, date_hash:)
  89     stop_ids = @db.query("SELECT stop_id FROM stops WHERE stop_code=?", stop_code).to_a.map do |row|
  90       row.first
  91     end
  92
  93     trips_for_stop_ids(stop_ids: stop_ids, date_hash: date_hash)
  94   end
  95
  96   def trips_for_stop_ids(stop_ids:, date_hash:, originating_only: false)
  97     result = {}
  98
  99     schedules = active_schedules(date_hash: date_hash)
 100     schedule_placeholders = (["?"] * schedules.count).join(",")
 101     stop_id_placeholders = (["?"] * stop_ids.count).join(",")
 102
 103     sql_head = <<~EOS
 104       SELECT r.route_short_name,t.trip_headsign,t.direction_id,st.departure_time,t.trip_id
 105       FROM routes r
 106       INNER JOIN trips t ON t.route_id=r.route_id
 107       INNER JOIN stop_times st ON st.trip_id=t.trip_id
 108       WHERE st.stop_id IN (#{stop_id_placeholders})
 109       AND service_id IN (#{schedule_placeholders})
 110     EOS
 111
 112     sql_filter = if originating_only
 113       "AND st.stop_sequence=1\n"
 114     else
 115       ""
 116     end
 117
 118     sql_tail = <<~EOS
 119       ORDER BY r.route_short_name,st.departure_time;
 120     EOS
 121
 122     sql = "#{sql_head}#{sql_filter}#{sql_tail}"
 123
 124     @db.query(sql, stop_ids, schedules).each do |row|
 125       route = row[0..2] # [route_id, hedsign, direction_id]
 126       departure_time = row[3]
 127       trip_id = row[4]
 128
 129       if result[route].nil?
 130         result[route] = []
 131       end
 132
 133       result[route] << [departure_time, trip_id]
 134     end
 135
 136     # Sometimes OC Transpo lists the same departure under two separate schedules, *both* of which are
 137     # simulteneously in effect.  (?)
 138     # Here we compensate for that by eliminating duplicates of the same departure time.
 139     result.map do |route, departure_tuples|
 140       [
 141         route,
 142         departure_tuples.group_by do |tuple|
 143           tuple.first # departure time
 144         end.map do |group_key, group_members|
 145           group_members.first
 146         end
 147       ]
 148     end.to_h
 149   end
 150
 151   def trips_for_stop_name(stop_name:, date_hash:)
 152     result = {}
 153
 154     base_name = stop_name.upcase
 155     suffixes = MULTI_PLATFORM_STOPS[base_name] || [nil]
 156     names = suffixes.map { |suffix| [base_name, suffix].compact.join(" ") }
 157     placeholders = (["?"] * names.count).join(",")
 158
 159     stop_ids = @db.query(
 160       "SELECT stop_id FROM stops WHERE stop_name IN (#{placeholders})",
 161       names,
 162     ).to_a.map do |row|
 163       row.first
 164     end
 165
 166     trips_for_stop_ids(stop_ids: stop_ids, date_hash: date_hash)
 167   end
 168
 169   def calendar
 170     @calendar ||= begin
 171       sql = <<~EOS
 172         SELECT service_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday, start_date, end_date
 173         FROM calendar
 174         ORDER BY start_date, end_date
 175       EOS
 176       @db.query(sql).to_a
 177     end
 178   end
 179
 180   def calendar_overrides(target_date)
 181     result = {add: [], remove: []}
 182     datestamp = @date_utils.date_for(target_date)
 183     sql = <<~EOS
 184       SELECT service_id, exception_type
 185       FROM calendar_dates
 186       WHERE calendar_date = ?
 187     EOS
 188
 189     @db.query(sql).each do |row|
 190       case row[1]
 191       when "1"
 192         result[:add] << row[0]
 193       when "2"
 194         result[:remove] << row[0]
 195       else
 196         raise "Unexpected exception_type #{row[1].inspect} found in calendar_dates"
 197       end
 198     end
 199
 200     result
 201   end
 202
 203   def calendar_schedules(target_date)
 204     result = []
 205     datestamp = @date_utils.date_for(target_date)
 206     weekday = @date_utils.weekday_for(target_date)
 207     calendar.each do |service_id, mon, tue, wed, thu, fri, sat, sun, start_date, end_date|
 208       if datestamp >= start_date && datestamp <= end_date
 209         if (0 == weekday && "1" == sun) ||
 210             (1 == weekday && "1" == mon) ||
 211             (2 == weekday && "1" == tue) ||
 212             (3 == weekday && "1" == wed) ||
 213             (4 == weekday && "1" == thu) ||
 214             (5 == weekday && "1" == fri) ||
 215             (6 == weekday && "1" == sat)
 216           result << service_id
 217         end
 218       end
 219     end
 220     result
 221   end
 222 end