def test_custom_queries(self): dao = Dao(DAO_URL, sql_logging=SQL_LOG) dao.load_gtfs(DUMMY_GTFS) # A simple custom query: count the number of stops per type (stop/station) # SQL equivalent: SELECT stop.location_type, count(stop.location_type) FROM stop GROUP BY stop.location_type for type, stop_count in dao.session() \ .query(Stop.location_type, func.count(Stop.location_type)) \ .group_by(Stop.location_type) \ .all(): # print("type %d : %d stops" % (type, stop_count)) if type == Stop.TYPE_STATION: self.assertTrue(stop_count == 3) if type == Stop.TYPE_STOP: self.assertTrue(stop_count > 15 and stop_count < 30) # A more complex custom query: count the number of trips per calendar date per route on june/july from_date = CalendarDate.ymd(2016, 6, 1) to_date = CalendarDate.ymd(2016, 7, 31) for date, route, trip_count in dao.session() \ .query(CalendarDate.date, Route, func.count(Trip.trip_id)) \ .join(Calendar).join(Trip).join(Route) \ .filter((func.date(CalendarDate.date) >= from_date.date) & (func.date(CalendarDate.date) <= to_date.date)) \ .group_by(CalendarDate.date, Route.route_short_name) \ .all(): # print("%s / %20s : %d trips" % (date, route.route_short_name + " " + route.route_long_name, trip_count)) self.assertTrue(date >= from_date.as_date()) self.assertTrue(date <= to_date.as_date()) self.assertTrue(trip_count > 0)
def test_shapes(self): dao = Dao() f1 = FeedInfo("") a1 = Agency("", "A1", "Agency 1", agency_url="http://www.agency.fr/", agency_timezone="Europe/Paris") r1 = Route("", "R1", "A1", 3, route_short_name="R1", route_long_name="Route 1") c1 = Calendar("", "C1") c1.dates = [ CalendarDate.ymd(2016, 1, 31), CalendarDate.ymd(2016, 2, 1) ] s1 = Stop("", "S1", "Stop 1", 45.0, 0.0) s2 = Stop("", "S2", "Stop 2", 45.1, 0.1) s3 = Stop("", "S3", "Stop 3", 45.2, 0.2) t1 = Trip("", "T1", "R1", "C1") t1.stop_times = [ StopTime(None, None, "S1", 0, 28800, 28800, 0.0), StopTime(None, None, "S2", 1, 29400, 29400, 2.0), StopTime(None, None, "S3", 2, 30000, 30000, 4.0) ] t2 = Trip("", "T2", "R1", "C1") t2.stop_times = [ StopTime(None, None, "S2", 0, 30600, 30600, 0.0), StopTime(None, None, "S1", 1, 31000, 31000, 1.0) ] sh1 = Shape("", "Sh1") sh1.points = [ ShapePoint(None, None, 0, 45.00, 0.00, 0.0), ShapePoint(None, None, 1, 45.05, 0.10, 1.0), ShapePoint(None, None, 2, 45.10, 0.10, 2.0), ShapePoint(None, None, 3, 45.15, 0.20, 3.0), ShapePoint(None, None, 4, 45.20, 0.20, 4.0) ] t1.shape = sh1 dao.add_all([ f1, a1, r1, c1, s1, s2, s3, t1, t2, sh1 ]) dao.commit() t = dao.trip("T1") self.assertTrue(t.shape.shape_id == "Sh1") self.assertTrue(len(t.shape.points) == 5) t = dao.trip("T2") self.assertTrue(t.shape == None)
def test_calendar_date_range(self): d1 = CalendarDate.ymd(2016, 1, 1) d2 = CalendarDate.ymd(2016, 2, 1) n = 0 for d in CalendarDate.range(d1, d2): self.assertTrue(d >= d1) self.assertTrue(d < d2) n += 1 self.assertEqual(n, 31)
def test_complex_queries(self): dao = Dao(DAO_URL, sql_logging=SQL_LOG) dao.load_gtfs(DUMMY_GTFS) # Get the list of departures: # 1) from "Porte de Bourgogne" # 2) on 4th July # 3) between 10:00 and 14:00 # 4) on route type BUS # 5) not the last of trip (only departing) porte_bourgogne = dao.stop("BBG") july4 = CalendarDate.ymd(2016, 7, 4) from_time = gtfstime(10, 00) to_time = gtfstime(14, 00) departures = dao.stoptimes( fltr=(StopTime.stop == porte_bourgogne) & (StopTime.departure_time >= from_time) & (StopTime.departure_time <= to_time) & (Route.route_type == Route.TYPE_BUS) & (func.date(CalendarDate.date) == july4.date), prefetch_trips=True) n = 0 for dep in departures: self.assertTrue(dep.stop == porte_bourgogne) self.assertTrue(july4 in dep.trip.calendar.dates) self.assertTrue(dep.trip.route.route_type == Route.TYPE_BUS) self.assertTrue(dep.departure_time >= from_time and dep.departure_time <= to_time) n += 1 self.assertTrue(n > 10) # Plage is a stop that is used only in summer (hence the name!) plage = dao.stop("BPG") # Get the list of stops used by some route: # 1) All-year round route_red = dao.route("BR") stoplist_all = list(dao.stops(fltr=Trip.route == route_red)) # 2) Only in january from_date = CalendarDate.ymd(2016, 1, 1) to_date = CalendarDate.ymd(2016, 1, 31) stoplist_jan = list(dao.stops( fltr=(Trip.route == route_red) & (func.date(CalendarDate.date) >= from_date.date) & (func.date(CalendarDate.date) <= to_date.date))) # Now, make some tests self.assertTrue(len(stoplist_all) > 5) self.assertTrue(plage in stoplist_all) self.assertFalse(plage in stoplist_jan) stoplist = list(stoplist_all) stoplist.remove(plage) self.assertTrue(set(stoplist) == set(stoplist_jan)) # Get all routes passing by the set of stops routes = dao.routes(fltr=or_(StopTime.stop == stop for stop in stoplist_jan)) stopset = set() for route in routes: for trip in route.trips: for stoptime in trip.stop_times: stopset.add(stoptime.stop) self.assertTrue(set(stoplist_jan).issubset(stopset))
def test_calendar_date_set(self): d1 = CalendarDate.ymd(2015, 12, 31) d2 = CalendarDate.ymd(2016, 1, 1) dates = set([ d1, d2 ]) self.assertTrue(len(dates) == 2) d2b = CalendarDate.ymd(2016, 1, 1) dates.add(d2b) self.assertTrue(len(dates) == 2) d1b = CalendarDate.ymd(2015, 12, 31) d4 = CalendarDate.ymd(2015, 1, 2) dates.add(d1b) dates.add(d4) self.assertTrue(len(dates) == 3)
def test_calendar_date(self): self.assertEqual(True, True) d1 = CalendarDate.ymd(2015, 12, 31) d2 = CalendarDate.ymd(2016, 1, 1) self.assertEquals(d1.next_day(), d2) self.assertTrue(d1 < d2) self.assertTrue(d1 <= d2) self.assertFalse(d1 > d2) self.assertFalse(d1 >= d2) self.assertFalse(d1 == d2) dt = datetime.date(2015, 12, 31) self.assertTrue(d1 == dt) dates = [ CalendarDate.ymd(2016, 1, 1), CalendarDate.ymd(2016, 1, 2) ] self.assertTrue(CalendarDate.ymd(2016, 1, 2) in dates)
def test_trip(self): dao = Dao() f1 = FeedInfo("F1") a1 = Agency("F1", "A1", "Agency 1", agency_url="http://www.agency.fr/", agency_timezone="Europe/Paris") r1 = Route("F1", "R1", "A1", 3, route_short_name="R1", route_long_name="Route 1") c1 = Calendar("F1", "C1") c1.dates = [ d for d in CalendarDate.range(CalendarDate.ymd(2016, 1, 1), CalendarDate.ymd(2016, 1, 31).next_day()) ] s1 = Stop("F1", "S1", "Stop 1", 45.0, 0.0) s2 = Stop("F1", "S2", "Stop 2", 45.1, 0.1) s3 = Stop("F1", "S3", "Stop 3", 45.2, 0.2) t1 = Trip("F1", "T1", "R1", "C1") t11 = StopTime("F1", "T1", "S1", 0, 28800, 28800, 0.0) t12 = StopTime("F1", "T1", "S2", 1, 29400, 29400, 0.0) t13 = StopTime("F1", "T1", "S3", 2, 30000, 30000, 0.0) t2 = Trip("F1", "T2", "R1", "C1") # Order is not important for now t2.stop_times.append(StopTime(None, None, "S1", 1, 31000, 31000, 0.0)) t2.stop_times.append(StopTime(None, None, "S2", 0, 30600, 30600, 0.0)) dao.add_all([ f1, a1, r1, c1, s1, s2, s3, t1, t11, t12, t13, t2 ]) # Commit is needed to re-order stop times of T2 dao.commit() cal = dao.calendar("C1", feed_id="F1") for trip in cal.trips: self.assertTrue(trip.calendar.service_id == "C1") for stoptime in trip.stop_times: self.assertTrue(stoptime.trip.calendar.service_id == "C1") stop = dao.stop("S2", feed_id="F1") for stoptime in stop.stop_times: self.assertTrue(stoptime.stop.stop_id == "S2") self.assertTrue(stoptime.trip.trip_id.startswith("T")) trip = dao.trip("T1", feed_id="F1") self.assertTrue(len(trip.stop_times) == 3) trip = dao.trip("T2", feed_id="F1") self.assertTrue(len(trip.stop_times) == 2) for trip in dao.trips(prefetch_stop_times=True): last_stop_seq = -1 for stoptime in trip.stop_times: self.assertTrue(stoptime.stop_sequence > last_stop_seq) last_stop_seq = stoptime.stop_sequence for trip in dao.trips(): for stoptime1, stoptime2 in trip.hops(): self.assertTrue(stoptime1.trip == stoptime2.trip) self.assertTrue(stoptime1.stop_sequence + 1 == stoptime2.stop_sequence)
def test_calendar_date_out_of_range(self): broke = False try: d1 = CalendarDate.ymd(2015, 12, 32) # @UnusedVariable except(ValueError): broke = True self.assertTrue(broke)
def test_entities_creation(self): dao = Dao() f1 = FeedInfo("F1") a1 = Agency("F1", "A1", "Agency 1", agency_url="http://www.agency.fr/", agency_timezone="Europe/Paris") r1 = Route("F1", "R1", "A1", 3, route_short_name="R1", route_long_name="Route 1") r2 = Route("F1", "R2", "A1", 3, route_short_name="R2") c1 = Calendar("F1", "C1") c1.dates = [ CalendarDate.ymd(2015, 11, 13), CalendarDate.ymd(2015, 11, 14) ] dao.add_all([ f1, a1, r1, r2, c1 ]) self.assertTrue(len(dao.feeds()) == 1) self.assertTrue(len(dao.agencies()) == 1) a1b = dao.agency("A1", feed_id="F1", prefetch_routes=True) self.assertTrue(a1b.agency_name == "Agency 1") self.assertTrue(len(a1b.routes) == 2) r1b = dao.route("R1", feed_id="F1") self.assertTrue(r1b.route_short_name == "R1") self.assertTrue(r1b.route_long_name == "Route 1") self.assertTrue(r1b.route_type == 3) r42 = dao.route("R42", feed_id="F1") self.assertTrue(r42 is None)
def test_demo(self): dao = Dao(DAO_URL, sql_logging=False) dao.load_gtfs(DUMMY_GTFS) print("List of stops named '...Bordeaux...':") stops_bordeaux = list(dao.stops(fltr=(Stop.stop_name.ilike('%Bordeaux%')) & (Stop.location_type == Stop.TYPE_STOP))) for stop in stops_bordeaux: print(stop.stop_name) print("List of routes passing by those stops:") routes_bordeaux = dao.routes(fltr=or_(StopTime.stop == stop for stop in stops_bordeaux)) for route in routes_bordeaux: print("%s - %s" % (route.route_short_name, route.route_long_name)) july4 = CalendarDate.ymd(2016, 7, 4) print("All departures from those stops on %s:" % (july4.as_date())) departures = list(dao.stoptimes(fltr=(or_(StopTime.stop == stop for stop in stops_bordeaux)) & (StopTime.departure_time != None) & (func.date(CalendarDate.date) == july4.date))) print("There is %d departures" % (len(departures))) for departure in departures: print("%30.30s %10.10s %-20.20s > %s" % (departure.stop.stop_name, fmttime(departure.departure_time), departure.trip.route.route_long_name, departure.trip.trip_headsign)) print("Number of departures and time range per stop on %s:" % (july4.as_date())) departure_by_stop = defaultdict(list) for departure in departures: departure_by_stop[departure.stop].append(departure) for stop, deps in departure_by_stop.items(): min_dep = min(d.departure_time for d in deps) max_dep = max(d.departure_time for d in deps) print("%30.30s %3d departures (from %s to %s)" % (stop.stop_name, len(deps), fmttime(min_dep), fmttime(max_dep))) # Compute the average distance and time to next stop by route type ntd = [ [0, 0, 0.0] for type in range(0, Route.TYPE_FUNICULAR + 1) ] for departure in departures: # The following is guaranteed to succeed as we have departure_time == Null for last stop time in trip next_arrival = departure.trip.stop_times[departure.stop_sequence + 1] hop_dist = next_arrival.shape_dist_traveled - departure.shape_dist_traveled hop_time = next_arrival.arrival_time - departure.departure_time route_type = departure.trip.route.route_type ntd[route_type][0] += 1 ntd[route_type][1] += hop_time ntd[route_type][2] += hop_dist for route_type in range(0, len(ntd)): n, t, d = ntd[route_type] if n > 0: print("The average distance to the next stop on those departures for route type %d is %.2f meters" % (route_type, d / n)) print("The average time in sec to the next stop on those departures for route type %d is %s" % (route_type, fmttime(t / n)))
def test_demo(self): dao = Dao(DAO_URL, sql_logging=False) dao.load_gtfs(DUMMY_GTFS) print("List of stops named '...Bordeaux...':") stops_bordeaux = list( dao.stops(fltr=(Stop.stop_name.ilike('%Bordeaux%')) & (Stop.location_type == Stop.TYPE_STOP))) for stop in stops_bordeaux: print(stop.stop_name) print("List of routes passing by those stops:") routes_bordeaux = dao.routes(fltr=or_(StopTime.stop == stop for stop in stops_bordeaux)) for route in routes_bordeaux: print("%s - %s" % (route.route_short_name, route.route_long_name)) july4 = CalendarDate.ymd(2016, 7, 4) print("All departures from those stops on %s:" % (july4.as_date())) departures = list( dao.stoptimes(fltr=(or_(StopTime.stop == stop for stop in stops_bordeaux)) & (StopTime.departure_time != None) & (func.date(CalendarDate.date) == july4.date))) print("There is %d departures" % (len(departures))) for departure in departures: print("%30.30s %10.10s %-20.20s > %s" % (departure.stop.stop_name, fmttime(departure.departure_time), departure.trip.route.route_long_name, departure.trip.trip_headsign)) print("Number of departures and time range per stop on %s:" % (july4.as_date())) departure_by_stop = defaultdict(list) for departure in departures: departure_by_stop[departure.stop].append(departure) for stop, deps in departure_by_stop.items(): min_dep = min(d.departure_time for d in deps) max_dep = max(d.departure_time for d in deps) print("%30.30s %3d departures (from %s to %s)" % (stop.stop_name, len(deps), fmttime(min_dep), fmttime(max_dep))) # Compute the average distance and time to next stop by route type ntd = [[0, 0, 0.0] for type in range(0, Route.TYPE_FUNICULAR + 1)] for departure in departures: # The following is guaranteed to succeed as we have departure_time == Null for last stop time in trip next_arrival = departure.trip.stop_times[departure.stop_sequence + 1] hop_dist = next_arrival.shape_dist_traveled - departure.shape_dist_traveled hop_time = next_arrival.arrival_time - departure.departure_time route_type = departure.trip.route.route_type ntd[route_type][0] += 1 ntd[route_type][1] += hop_time ntd[route_type][2] += hop_dist for route_type in range(0, len(ntd)): n, t, d = ntd[route_type] if n > 0: print( "The average distance to the next stop on those departures for route type %d is %.2f meters" % (route_type, d / n)) print( "The average time in sec to the next stop on those departures for route type %d is %s" % (route_type, fmttime(t / n)))
def test_calendar_date_convert(self): d1 = CalendarDate.fromYYYYMMDD("20151231") d2 = CalendarDate.ymd(2015, 12, 31) self.assertTrue(d1 == d2)
def test_gtfs_data(self): dao = Dao(DAO_URL, sql_logging=False) dao.load_gtfs(DUMMY_GTFS) # Check feed feed = dao.feed() self.assertTrue(feed.feed_id == "") self.assertTrue(feed.feed_publisher_name == "Mecatran") self.assertTrue(feed.feed_publisher_url == "http://www.mecatran.com/") self.assertTrue(feed.feed_contact_email == "*****@*****.**") self.assertTrue(feed.feed_lang == "fr") self.assertTrue(len(dao.agencies()) == 2) self.assertTrue(len(dao.routes()) == 3) self.assertTrue(len(feed.agencies) == 2) self.assertTrue(len(feed.routes) == 3) # Check agencies at = dao.agency("AT") self.assertTrue(at.agency_name == "Agency Train") self.assertTrue(len(at.routes) == 1) ab = dao.agency("AB") self.assertTrue(ab.agency_name == "Agency Bus") self.assertTrue(len(ab.routes) == 2) # Check calendars week = dao.calendar("WEEK") self.assertTrue(len(week.dates) == 253) summer = dao.calendar("SUMMER") self.assertTrue(len(summer.dates) == 42) mon = dao.calendar("MONDAY") self.assertTrue(len(mon.dates) == 49) sat = dao.calendar("SAT") self.assertTrue(len(sat.dates) == 53) for date in mon.dates: self.assertTrue(date.dow() == 0) for date in sat.dates: self.assertTrue(date.dow() == 5) for date in week.dates: self.assertTrue(date.dow() >= 0 and date.dow() <= 4) for date in summer.dates: self.assertTrue(date >= CalendarDate.ymd(2016, 7, 1) and date <= CalendarDate.ymd(2016, 8, 31)) empty = dao.calendars(func.date(CalendarDate.date) == datetime.date(2016, 5, 1)) # OR USE: empty = dao.calendars(CalendarDate.date == "2016-05-01") self.assertTrue(len(empty) == 0) july4 = CalendarDate.ymd(2016, 7, 4) summer_mon = dao.calendars(func.date(CalendarDate.date) == july4.date) n = 0 for cal in summer_mon: self.assertTrue(july4 in cal.dates) n += 1 self.assertTrue(n == 3) # Check stops sbq = dao.stop("BQ") self.assertAlmostEqual(sbq.stop_lat, 44.844, places=2) self.assertAlmostEqual(sbq.stop_lon, -0.573, places=2) self.assertTrue(sbq.stop_name == "Bordeaux Quinconces") n = 0 for stop in dao.stops(Stop.stop_name.like("Gare%")): self.assertTrue(stop.stop_name.startswith("Gare")) n += 1 self.assertTrue(n == 7) n = 0 for stop in dao.stops(fltr=dao.in_area(RectangularArea(44.7, -0.6, 44.9, -0.4))): self.assertTrue(stop.stop_lat >= 44.7 and stop.stop_lat <= 44.9 and stop.stop_lon >= -0.6 and stop.stop_lon <= -0.4) n += 1 self.assertTrue(n == 16) for station in dao.stops(Stop.location_type == Stop.TYPE_STATION): self.assertTrue(station.location_type == Stop.TYPE_STATION) self.assertTrue(len(station.sub_stops) >= 2) for stop in station.sub_stops: self.assertTrue(stop.parent_station == station) # Check zones z_inexistant = dao.zone("ZX") self.assertTrue(z_inexistant is None) z1 = dao.zone("Z1") self.assertEquals(16, len(z1.stops)) z2 = dao.zone("Z2") self.assertEquals(4, len(z2.stops)) # Check transfers transfers = dao.transfers() self.assertTrue(len(transfers) == 3) transfers = dao.transfers(fltr=(dao.transfer_from_stop().stop_id == 'GBSJB')) self.assertTrue(len(transfers) == 1) self.assertTrue(transfers[0].from_stop.stop_id == 'GBSJB') # Check routes tgv = dao.route("TGVBP") self.assertTrue(tgv.agency == at) self.assertTrue(tgv.route_type == 2) r1 = dao.route("BR") self.assertTrue(r1.route_short_name == "R1") self.assertTrue(r1.route_long_name == "Bus Red") n = 0 for route in dao.routes(Route.route_type == 3): self.assertTrue(route.route_type == 3) n += 1 self.assertTrue(n == 2) # Check trip for route n = 0 trips = dao.trips(fltr=Route.route_type == Route.TYPE_BUS) for trip in trips: self.assertTrue(trip.route.route_type == Route.TYPE_BUS) n += 1 self.assertTrue(n > 20) # Check trips on date trips = dao.trips(fltr=func.date(CalendarDate.date) == july4.date, prefetch_calendars=True) n = 0 for trip in trips: self.assertTrue(july4 in trip.calendar.dates) n += 1 self.assertTrue(n > 30)
def test_calendar_date_convert(self): d1 = CalendarDate.fromYYYYMMDD("20151231") d2 = CalendarDate.ymd(2015, 12, 31) self.assertTrue(d1 == d2)
def test_trip(self): dao = Dao() f1 = FeedInfo("F1") a1 = Agency("F1", "A1", "Agency 1", agency_url="http://www.agency.fr/", agency_timezone="Europe/Paris") r1 = Route("F1", "R1", "A1", 3, route_short_name="R1", route_long_name="Route 1") c1 = Calendar("F1", "C1") c1.dates = [ d for d in CalendarDate.range( CalendarDate.ymd(2016, 1, 1), CalendarDate.ymd(2016, 1, 31).next_day()) ] s1 = Stop("F1", "S1", "Stop 1", 45.0, 0.0) s2 = Stop("F1", "S2", "Stop 2", 45.1, 0.1) s3 = Stop("F1", "S3", "Stop 3", 45.2, 0.2) t1 = Trip("F1", "T1", "R1", "C1") t1.direction_id = 0 t11 = StopTime("F1", "T1", "S1", 0, 28800, 28800, 0.0) t12 = StopTime("F1", "T1", "S2", 1, 29400, 29400, 0.0) t13 = StopTime("F1", "T1", "S3", 2, 30000, 30000, 0.0) t2 = Trip("F1", "T2", "R1", "C1") t2.direction_id = 1 # Order is not important for now t2.stop_times.append(StopTime(None, None, "S1", 1, 31000, 31000, 0.0)) t2.stop_times.append(StopTime(None, None, "S2", 0, 30600, 30600, 0.0)) dao.add_all([f1, a1, r1, c1, s1, s2, s3, t1, t11, t12, t13, t2]) # Commit is needed to re-order stop times of T2 dao.commit() cal = dao.calendar("C1", feed_id="F1") for trip in cal.trips: self.assertTrue(trip.calendar.service_id == "C1") for stoptime in trip.stop_times: self.assertTrue(stoptime.trip.calendar.service_id == "C1") stop = dao.stop("S2", feed_id="F1") for stoptime in stop.stop_times: self.assertTrue(stoptime.stop.stop_id == "S2") self.assertTrue(stoptime.trip.trip_id.startswith("T")) trip = dao.trip("T1", feed_id="F1") self.assertTrue(len(trip.stop_times) == 3) trip = dao.trip("T2", feed_id="F1") self.assertTrue(len(trip.stop_times) == 2) for trip in dao.trips(prefetch_stop_times=True): last_stop_seq = -1 for stoptime in trip.stop_times: self.assertTrue(stoptime.stop_sequence > last_stop_seq) last_stop_seq = stoptime.stop_sequence for trip in dao.trips(): for stoptime1, stoptime2 in trip.hops(): self.assertTrue(stoptime1.trip == stoptime2.trip) self.assertTrue(stoptime1.stop_sequence + 1 == stoptime2.stop_sequence) trips = list(dao.trips(fltr=Trip.direction_id == 0)) self.assertTrue(len(trips) == 1) trips = list(dao.trips(fltr=Trip.direction_id == 1)) self.assertTrue(len(trips) == 1)