def main():
print """package boston.Bus.Map.database;
import android.database.DatabaseUtils.InsertHelper;
import java.util.Collection;
public class Schema {
public static final String dbName = "transitData";
public static final String oldDb = "bostonBusMap";
private static final int INT_TRUE = 1;
private static final int INT_FALSE = 0;
public static int toInteger(boolean b) {
return b ? INT_TRUE : INT_FALSE;
}
public static boolean fromInteger(int i) {
return i == INT_TRUE;
}
"""
schemaObj = schema.getSchemaAsObject()
for tableName in (each[0] for each in inspect.getmembers(schemaObj)):
if tableName[:2] != "__":
table = getattr(schemaObj, tableName)
writeTable(table)
print """}"""
def get_create_table_sql():
obj = schema.getSchemaAsObject()
for tableName in (each[0] for each in inspect.getmembers(obj)):
if tableName[:2] != "__":
table = getattr(obj, tableName)
yield table.create()
for index in table.index():
yield index
def __init__(self, routeKeysToTitles, startingOrder):
self.currentDirection = None
self.currentRoute = None
self.sharedStops = {}
self.table = schema.getSchemaAsObject()
self.routeKeysToTitles = routeKeysToTitles
self.startingOrder = startingOrder
self.inPath = False
self.paths = []
def create_tables(conn):
ret = ""
obj = schema.getSchemaAsObject()
for tableName in (each[0] for each in inspect.getmembers(obj)):
if tableName[:2] != "__":
table = getattr(obj, tableName)
conn.execute(table.create())
for index in table.index():
conn.execute(index)
def __init__(self, cur, startingOrder, sharedStops):
self.cur = cur
self.currentDirection = None
self.currentRoute = None
self.sharedStops = sharedStops
self.table = schema.getSchemaAsObject()
self.startingOrder = startingOrder
self.inPath = False
self.paths = []
def create_tables():
ret = ""
obj = schema.getSchemaAsObject()
for tableName in (each[0] for each in inspect.getmembers(obj)):
if tableName[:2] != "__":
table = getattr(obj, tableName)
ret += table.create() + ";"
for index in table.index():
ret += index + ";" + "\n"
return ret
def write_sql(self, cur, startorder, route, gtfs_map):
supported_route_description = route + " Line"
route_rows = gtfs_map.find_routes_by_name(supported_route_description)
route_ids = set([route_row["route_id"] for route_row in route_rows])
shape_rows = itertools.chain.from_iterable((gtfs_map.find_shapes_by_route(item) for item in route_ids))
# this stores a list of list of lat, lon pairs
paths = []
shape_rows = list(sorted(shape_rows, key=lambda shape: shape["shape_id"]))
for shape_id, group_rows in itertools.groupby(shape_rows, lambda shape: shape["shape_id"]):
path = [(float(row["shape_pt_lat"]), float(row["shape_pt_lon"])) for row in group_rows]
paths.append(path)
stop_rows = itertools.chain.from_iterable(gtfs_map.find_stops_by_route(route) for route in route_ids)
pathblob = schema.Box(paths).get_blob_string()
# insert route information
obj = schema.getSchemaAsObject()
obj.routes.route.value = route
obj.routes.routetitle.value = route
obj.routes.color.value = subway_color[route]
obj.routes.oppositecolor.value = subway_color[route]
obj.routes.listorder.value = startorder
obj.routes.agencyid.value = schema.SubwayAgencyId
obj.routes.pathblob.value = pathblob
cur.execute(obj.routes.insert())
stop_ids = set()
for stop_row in stop_rows:
stop_id = stop_row["stop_id"]
if stop_id not in stop_ids:
obj.stops.tag.value = stop_row["stop_id"]
obj.stops.title.value = stop_row["stop_name"]
obj.stops.lat.value = float(stop_row["stop_lat"])
obj.stops.lon.value = float(stop_row["stop_lon"])
cur.execute(obj.stops.insert())
obj.stopmapping.route.value = route
obj.stopmapping.tag.value = stop_row["stop_id"]
cur.execute(obj.stopmapping.insert())
stop_ids.add(stop_id)
return (1)
def writeAlertMapping(routeTitles):
obj = schema.getSchemaAsObject()
routeDescriptionToAlertKey = {}
for line in filter(lambda x: x, data.split("\n")):
fields = line.split(",")
alertKey = int(fields[-1])
routeDescription = fields[0]
routeDescriptionToAlertKey[routeDescription] = alertKey
ret = {}
for routeName in routeTitles.keys():
for routeDescription in routeDescriptionToAlertKey.keys():
if routeDescription == routeName:
value = routeDescriptionToAlertKey[routeDescription]
ret[routeDescription] = value
break
if routeName not in ret:
for routeDescription in routeDescriptionToAlertKey.keys():
if routeDescription.startswith(routeName + " ") or routeDescription.startswith(routeName + "/"):
value = routeDescriptionToAlertKey[routeDescription]
ret[routeName] = value
break
#special cases
ret["Red"] = 15
ret["Orange"] = 16
ret["Blue"] = 18
#CTs
ret["701"] = 50
ret["747"] = 51
ret["708"] = 52
#silver line
ret["741"] = 20
ret["742"] = 28
ret["751"] = 53
# no info for SL5
for route, index in ret.iteritems():
obj.alerts.route.value = route
obj.alerts.alertindex.value = index
obj.alerts.insert()
def generate(self, conn, start_index):
info_url = "https://gbfs.thehubway.com/gbfs/en/station_information.json"
obj = schema.getSchemaAsObject()
info_data = json.loads(requests.get(info_url).text)
cur = conn.cursor()
obj.routes.route.value = "Hubway"
obj.routes.routetitle.value = "Hubway"
obj.routes.color.value = default_color
obj.routes.oppositecolor.value = default_color
obj.routes.listorder.value = start_index
obj.routes.agencyid.value = schema.HubwayAgencyId
obj.routes.pathblob.value = schema.Box([]).get_blob_string()
cur.execute(obj.routes.insert())
conn.commit()
cur.close()
return 1
def generate(self, conn, start_index):
url = "http://www.thehubway.com/data/stations/bikeStations.xml"
obj = schema.getSchemaAsObject()
data = requests.get(url).text
root = xml.dom.minidom.parseString(data)
cur = conn.cursor()
obj.routes.route.value = "Hubway"
obj.routes.routetitle.value = "Hubway"
obj.routes.color.value = default_color
obj.routes.oppositecolor.value = default_color
obj.routes.listorder.value = start_index
obj.routes.agencyid.value = schema.HubwayAgencyId
obj.routes.pathblob.value = schema.Box([]).get_blob_string()
cur.execute(obj.routes.insert())
for station_node in root.getElementsByTagName("station"):
stop_tag = "hubway_" + str(station_node.getElementsByTagName("id")[0].firstChild.nodeValue)
obj.stops.tag.value = stop_tag
obj.stops.title.value = str(station_node.getElementsByTagName("name")[0].firstChild.nodeValue)
obj.stops.lat.value = float(station_node.getElementsByTagName("lat")[0].firstChild.nodeValue)
obj.stops.lon.value = float(station_node.getElementsByTagName("long")[0].firstChild.nodeValue)
cur.execute(obj.stops.insert())
obj.stopmapping.route.value = "Hubway"
obj.stopmapping.tag.value = stop_tag
cur.execute(obj.stopmapping.insert())
conn.commit()
cur.close()
return 1
def write_sql(self, cur, startorder, gtfs_map):
# this is a workaround
route_order = {
"CR-Greenbush": 1,
"CR-Kingston": 2,
"CR-Middleborough": 3,
"CR-Fairmount": 4,
"CR-Providence": 5,
"CR-Franklin": 6,
"CR-Needham": 7,
"CR-Worcester": 8,
"CR-Fitchburg": 9,
"CR-Lowell": 10,
"CR-Haverhill": 11,
"CR-Newburyport": 12}
stops_inserted = set()
route_rows = list(gtfs_map.find_routes_by_route_type(schema.CommuterRailAgencyId))
for route_row in route_rows:
route_id = route_row["route_id"]
route_title = route_row["route_short_name"]
if not route_title:
route_title = route_row["route_long_name"]
trip_rows = gtfs_map.find_trips_by_route(route_id)
trip_ids = set([trip["trip_id"] for trip in trip_rows])
shape_rows = gtfs_map.find_shapes_by_route(route_id)
# this stores a list of list of lat, lon pairs
paths = []
shape_rows = list(sorted(shape_rows, key=lambda shape: shape["shape_id"]))
for shape_id, group_rows in itertools.groupby(shape_rows, lambda shape: shape["shape_id"]):
path = [(float(row["shape_pt_lat"]), float(row["shape_pt_lon"])) for row in group_rows]
paths.append(path)
stop_rows = gtfs_map.find_stops_by_route(route_id)
pathblob = schema.Box(paths).get_blob_string()
# insert route information
obj = schema.getSchemaAsObject()
obj.routes.route.value = route_id
obj.routes.routetitle.value = route_title
obj.routes.color.value = purple
obj.routes.oppositecolor.value = purple
obj.routes.listorder.value = startorder + route_order[route_id] - 1
obj.routes.agencyid.value = schema.CommuterRailAgencyId
obj.routes.pathblob.value = pathblob
cur.execute(obj.routes.insert())
for stop_row in stop_rows:
stop_id = stop_row["stop_id"]
if stop_id not in stops_inserted:
stops_inserted.add(stop_id)
obj.stops.tag.value = stop_id
obj.stops.title.value = stop_row["stop_name"]
obj.stops.lat.value = float(stop_row["stop_lat"])
obj.stops.lon.value = float(stop_row["stop_lon"])
cur.execute(obj.stops.insert())
obj.stopmapping.route.value = route_id
obj.stopmapping.tag.value = stop_row["stop_id"]
cur.execute(obj.stopmapping.insert())
return len(route_rows)
def write_sql(self, cur, startorder, route_ids, as_route, gtfs_map):
route_rows = [list(gtfs_map.find_routes_by_id(route_id))[0] for route_id in route_ids]
route_color = [route_row["route_color"] for route_row in route_rows][0]
shape_rows = itertools.chain.from_iterable((gtfs_map.find_sorted_shapes_by_route(item) for item in route_ids))
# this stores a list of list of lat, lon pairs
print("Appending paths for %s" % as_route)
paths = []
shape_rows = list(sorted(shape_rows, key=lambda shape: shape["shape_id"]))
print("Adding shapes...")
# todo: sorted?
for shape_id, group_rows in itertools.groupby(shape_rows, lambda shape: shape["shape_id"]):
path = [(float(row["shape_pt_lat"]), float(row["shape_pt_lon"])) for row in group_rows]
path = simplify_path(path)
paths.append(path)
stop_rows = itertools.chain.from_iterable(gtfs_map.find_stops_by_route(route) for route in route_ids)
pathblob = schema.Box(paths).get_blob_string()
print("Inserting route information for %s" % as_route)
# insert route information
obj = schema.getSchemaAsObject()
obj.routes.route.value = as_route
obj.routes.routetitle.value = as_route
obj.routes.color.value = int("0x%s" % route_color, 0)
obj.routes.oppositecolor.value = int("0x%s" % route_color, 0)
obj.routes.listorder.value = startorder
obj.routes.agencyid.value = schema.SubwayAgencyId
obj.routes.pathblob.value = pathblob
cur.execute(obj.routes.insert())
print("Adding stops...")
for stop_row in stop_rows:
stop_id = stop_row["stop_id"]
if stop_id not in self.stop_ids:
obj.stops.tag.value = stop_row["stop_id"]
obj.stops.title.value = stop_row["stop_name"]
obj.stops.lat.value = float(stop_row["stop_lat"])
obj.stops.lon.value = float(stop_row["stop_lon"])
obj.stops.parent.value = stop_row["parent_station"]
cur.execute(obj.stops.insert())
obj.stopmapping.route.value = as_route
obj.stopmapping.tag.value = stop_row["stop_id"]
cur.execute(obj.stopmapping.insert())
self.stop_ids.add(stop_id)
for route_id in route_ids:
print("Adding directions for {}...".format(route_id))
for trip_row in gtfs_map.find_trips_by_route(route_id):
obj.directions.dirTag.value = trip_row["trip_id"]
obj.directions.dirTitleKey.value = trip_row["trip_headsign"]
obj.directions.dirRouteKey.value = as_route
obj.directions.dirNameKey.value = ""
obj.directions.useAsUI.value = 1
cur.execute(obj.directions.insert())
print("Done for %s" % as_route)
return (1)
def write_sql(data, routeTitles, startOrder):
lines = data.split("\n")
indexes = {}
paths = {}
orderedStations = {}
first_line = lines[0].strip()
header_items = first_line.split(",")
for i in xrange(len(header_items)):
indexes[header_items[i]] = i
obj = schema.getSchemaAsObject()
for line in filter(lambda x: x, (line.strip() for line in lines[1:])):
items = line.split(",")
routeTitle = items[indexes["route_long_name"]]
if routeTitle.endswith(" Line"):
routeTitle = routeTitle[:-5]
routeKey = None
for newRouteKey, value in routeTitles.iteritems():
(newRouteTitle, order) = value
if newRouteTitle == routeTitle:
routeKey = newRouteKey
break
if not routeKey:
raise Exception("Route key doesn't match anything")
if routeKey not in routes_done:
newRouteTitle, order = routeTitles[routeKey]
routes_done[routeKey] = {"route" : routeKey,
"routetitle" : newRouteTitle,
"color" : purple,
"oppositecolor" : purple,
"listorder" : startOrder + order,
"agencyid" : schema.CommuterRailAgencyId}
lat = float(items[indexes["stop_lat"]])
lon = float(items[indexes["stop_lon"]])
stopTitle = items[indexes["stop_id"]]
direction = items[indexes["direction_id"]]
stopTag = "CRK-" + stopTitle
platformOrder = int(items[indexes["stop_sequence"]])
branch = items[indexes["Branch"]]
if stopTag not in stops_done:
obj.stops.tag.value = stopTag
obj.stops.title.value = stopTitle
obj.stops.lat.value = lat
obj.stops.lon.value = lon
obj.stops.insert()
obj.subway.platformorder.value = platformOrder
obj.subway.branch.value = branch
obj.subway.tag.value = stopTag
obj.subway.insert()
stops_done[stopTag] = True
if (stopTag, routeKey) not in stopmapping_done:
obj.stopmapping.route.value = routeKey
obj.stopmapping.tag.value = stopTag
obj.stopmapping.dirTag.value = None
obj.stopmapping.insert()
stopmapping_done[(stopTag, routeKey)] = True
if routeKey not in orderedStations:
orderedStations[routeKey] = {}
paths[routeKey] = []
innerMapping = orderedStations[routeKey]
combinedDirectionHash = createDirectionHash(direction, branch)
if combinedDirectionHash not in innerMapping:
innerMapping[combinedDirectionHash] = {}
innerInnerMapping = innerMapping[combinedDirectionHash]
innerInnerMapping[platformOrder] = (lat, lon)
for route, innerMapping in orderedStations.iteritems():
for directionHash, innerInnerMapping in innerMapping.iteritems():
floats = []
for platformOrder in sorted(innerInnerMapping.keys()):
station = innerInnerMapping[platformOrder]
floats.append(station)
paths[route].append(floats)
alreadyHandledDirections = {}
trunkBranch = "Trunk"
for directionHash, innerInnerMapping in innerMapping.iteritems():
direction, branch = directionHash.split("|")
if direction in alreadyHandledDirections:
continue
if trunkBranch == branch:
continue
branchInnerMapping = innerMapping[directionHash]
trunkDirectionHash = createDirectionHash(direction, trunkBranch)
trunkInnerMapping = innerMapping[trunkDirectionHash]
minBranchOrder = -1
for order in sorted(branchInnerMapping.keys()):
if minBranchOrder == -1:
minBranchOrder = order
else:
minBranchOrder = min(order, minBranchOrder)
maxTrunkOrder = 0
for order in sorted(trunkInnerMapping.keys()):
if order < minBranchOrder:
maxTrunkOrder = max(order, maxTrunkOrder)
points = []
if (minBranchOrder in branchInnerMapping) and (maxTrunkOrder in trunkInnerMapping):
branchStop = branchInnerMapping[minBranchOrder]
trunkStop = trunkInnerMapping[maxTrunkOrder]
points.append(trunkStop)
points.append(branchStop)
paths[routeKey].append(points)
for route, routedata in routes_done.iteritems():
for key, value in routedata.iteritems():
getattr(obj.routes, key).value = value
if route in paths:
d = schema.Box(paths[route]).get_blob_string()
obj.routes.pathblob.value = d
obj.routes.insert()
def write_sql(data, routeTitles, startOrder):
lines = data.split("\n")
indexes = {}
first_line = lines[0].strip()
header_items = first_line.split(",")
for i in xrange(len(header_items)):
indexes[header_items[i]] = i
obj = schema.getSchemaAsObject()
routes_done = {}
orderedStations = {}
paths = {}
for line in filter(lambda x: x, (line.strip() for line in lines[1:])):
items = line.split(",")
routeName = items[indexes["Line"]]
if routeName not in routes_done:
routeTitle, order = routeTitles[routeName]
routes_done[routeName] = {"route":routeName,
"routetitle":routeTitle,
"color":subway_color[routeName],
"oppositecolor":subway_color[routeName],
"listorder":startOrder + order,
"agencyid":schema.SubwayAgencyId}
platformOrder = int(items[indexes["PlatformOrder"]])
latitudeAsDegrees = float(items[indexes["stop_lat"]])
longitudeAsDegrees = float(items[indexes["stop_lon"]])
tag = items[indexes["PlatformKey"]]
title = items[indexes["stop_name"]]
branch = items[indexes["Branch"]]
obj.stops.tag.value = tag
obj.stops.title.value = title
obj.stops.lat.value = latitudeAsDegrees
obj.stops.lon.value = longitudeAsDegrees
obj.stops.insert()
obj.subway.platformorder.value = platformOrder
obj.subway.branch.value = branch
obj.subway.tag.value = tag
obj.subway.insert()
obj.stopmapping.route.value = routeName
obj.stopmapping.tag.value = tag
obj.stopmapping.dirTag.value = None
obj.stopmapping.insert()
if routeName not in orderedStations:
orderedStations[routeName] = {}
paths[routeName] = []
innerMapping = orderedStations[routeName]
direction = items[indexes["Direction"]]
combinedDirectionHash = createDirectionHash(direction, branch)
if combinedDirectionHash not in innerMapping:
innerMapping[combinedDirectionHash] = {}
innerInnerMapping = innerMapping[combinedDirectionHash]
innerInnerMapping[platformOrder] = (latitudeAsDegrees, longitudeAsDegrees)
# workaround
directions = {RedNorthToAlewife : Direction("North toward Alewife", "", RedLine, True),
RedNorthToAlewife2 : Direction("North toward Alewife", "", RedLine, True),
RedSouthToBraintree : Direction("South toward Braintree", "", RedLine, True),
RedSouthToAshmont : Direction("South toward Ashmont", "", RedLine, True),
BlueEastToWonderland : Direction("East toward Wonderland", "", BlueLine, True),
BlueWestToBowdoin : Direction("West toward Bowdoin", "", BlueLine, True),
OrangeNorthToOakGrove : Direction("North toward Oak Grove", "", OrangeLine, True),
OrangeSouthToForestHills : Direction("South toward Forest Hills", "", OrangeLine, True)}
for dirKey, direction in directions.iteritems():
obj.directions.dirTag.value = dirKey
obj.directions.dirNameKey.value = direction.name
obj.directions.dirTitleKey.value = direction.title
obj.directions.dirRouteKey.value = direction.route
obj.directions.useAsUI.value = schema.getIntFromBool(direction.useForUI)
obj.directions.insert()
for route, innerMapping in orderedStations.iteritems():
for directionHash, stations in innerMapping.iteritems():
floats = []
for platformOrder in sorted(stations.keys()):
floats.append(stations[platformOrder])
#this is kind of a hack. We need to connect the southern branches of the red line
if directionHash == "NBAshmont" or directionHash == "NBBraintree":
jfkNorthBoundOrder = 5
jfkStation = innerMapping["NBTrunk"][jfkNorthBoundOrder]
jfkLat, jfkLon = jfkStation
floats.append(jfkStation)
paths[route].append(floats)
for route, routedata in routes_done.iteritems():
for key, value in routedata.iteritems():
getattr(obj.routes, key).value = value
if route in paths:
obj.routes.pathblob.value = schema.Box(paths[route]).get_blob_string()
obj.routes.insert()
def main():
parser = argparse.ArgumentParser(description='Figure out what times a particular piece of the transit system is running.')
parser.add_argument("stop_times", type=str)
parser.add_argument("trips", type=str)
parser.add_argument("calendar", type=str)
args = parser.parse_args()
trip_to_route = {}
trip_indexes = {}
with open(args.trips) as f:
reader = csv.reader(f)
header = reader.next()
for i in xrange(len(header)):
trip_indexes[header[i]] = i
route_index = trip_indexes["route_id"]
service_index = trip_indexes["service_id"]
trip_index = trip_indexes["trip_id"]
for row in reader:
trip = row[trip_index]
route = row[route_index]
service = row[service_index]
trip_to_route[trip] = (route, service)
calendar = {}
with open(args.calendar) as f:
reader = csv.reader(f)
header = reader.next()
for i in xrange(len(header)):
trip_indexes[header[i]] = i
service_index = trip_indexes["service_id"]
monday_index = trip_indexes["monday"]
tuesday_index = trip_indexes["tuesday"]
wednesday_index = trip_indexes["wednesday"]
thursday_index = trip_indexes["thursday"]
friday_index = trip_indexes["friday"]
saturday_index = trip_indexes["saturday"]
sunday_index = trip_indexes["sunday"]
for row in reader:
service_id = row[service_index]
monday = row[monday_index]
tuesday = row[tuesday_index]
wednesday = row[wednesday_index]
thursday = row[thursday_index]
friday = row[friday_index]
saturday = row[saturday_index]
sunday = row[sunday_index]
calendar[service_id] = (int(monday), int(tuesday), int(wednesday), int(thursday), int(friday), int(saturday), int(sunday))
#print calendar
trip_intervals = {}
with open(args.stop_times) as f:
reader = csv.reader(f)
header = reader.next()
for i in xrange(len(header)):
trip_indexes[header[i]] = i
trip_index = trip_indexes["trip_id"]
arrival_index = trip_indexes["arrival_time"]
departure_index = trip_indexes["departure_time"]
for row in reader:
if arrival_index >= len(row) or departure_index >= len(row) or trip_index >= len(row):
continue
arrival = parse_time(row[arrival_index])
departure = parse_time(row[departure_index])
trip = row[trip_index]
route, service = trip_to_route[trip]
if service in calendar:
key = (route, calendar[service])
if key in trip_intervals:
old_departure, old_arrival = trip_intervals[key]
new_departure = None
new_arrival = None
if departure < old_departure:
trip_intervals[key] = (departure, trip_intervals[key][1])
if arrival > old_arrival:
trip_intervals[key] = (trip_intervals[key][0], arrival)
else:
trip_intervals[key] = (departure, arrival)
obj = schema.getSchemaAsObject()
for key, intervals in trip_intervals.iteritems():
#print repr(key) + ", " + repr(intervals)
route, days = key
begin, end = intervals
obj.bounds.route.value = route
obj.bounds.weekdays.value = make_weekdays(days)
obj.bounds.start.value = begin
obj.bounds.stop.value = end
obj.bounds.insert()
