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()