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)