def get_by_route_id(route_id, fields, day=None): return db.get_many(""" SELECT %s FROM trips JOIN calendar on trips.service_id = calendar.service_id WHERE route_id = '%s' %s """ % (', '.join(fields), route_id, '' if day is None else 'AND %s = TRUE' % day))
def get_by_shape(id): # There actually may be more than one, but let's start here points = db.get_many(""" SELECT shape_pt_lon, shape_pt_lat FROM shapes WHERE shape_id = '%s' ORDER BY shape_pt_sequence """ % id) return points
def get_path_by_shape_id(shape_id): return db.get_many(""" SELECT point_lon, point_lat FROM shapes JOIN points on shapes.point_id = points.point_id WHERE shape_id = '%s' ORDER BY shape_pt_sequence """ % shape_id)
def get_by_route(route): # There actually may be more than one, but let's start here points = db.get_many(""" SELECT shape_pt_lon, shape_pt_lat FROM shapes WHERE shape_id = ( SELECT shape_id FROM trips WHERE route_id = '%s' LIMIT 1 ) ORDER BY shape_pt_sequence """ % route) return points
def get_by_trip_ids(trip_ids, fields): results = db.get_many(""" SELECT trip_id, %s FROM stops JOIN stop_times on stop_times.stop_id = stops.stop_id JOIN stations on stations.stop_id = stops.parent_station WHERE stop_times.trip_id in (%s) AND stop_times.drop_off_type = 0 AND stop_times.pickup_type = 0 ORDER BY stop_sequence """ % (', '.join(fields), ', '.join(["'%s'" % id for id in trip_ids]))) return utils.collect_on(results, 0, remove_key=True)
def get_paths(route_id): return db.get_many(""" SELECT shape_id, points.point_id, shape_pt_sequence, point_lon, point_lat FROM shapes JOIN points on shapes.point_id = points.point_id WHERE shape_id IN ( SELECT DISTINCT shapes.shape_id FROM shapes JOIN trips ON trips.shape_id = shapes.shape_id WHERE route_id = '%s' ) ORDER BY shape_id, shape_pt_sequence """ % route_id)