def endElement(self, name): if name == "direction": self.currentDirection = None elif name == "route": self.currentRoute = None if len(self.paths) > 0: self.table.routes.pathblob.value = schema.Box(self.paths).get_blob_string() self.paths = [] self.cur.execute(self.table.routes.insert()) elif name == "path": self.inPath = False self.currentPathPoints = simplify_path(self.currentPathPoints) self.paths.append(self.currentPathPoints)
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(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, "CapeFlyer": 13 } 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_sorted_shapes_by_route(route_id) # this stores a list of list of lat, lon pairs paths = [] 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 = 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"]) obj.stops.parent.value = stop_row["parent_station"] cur.execute(obj.stops.insert()) obj.stopmapping.route.value = route_id obj.stopmapping.tag.value = stop_row["stop_id"] cur.execute(obj.stopmapping.insert()) 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 = route_id obj.directions.dirNameKey.value = "" obj.directions.useAsUI.value = 1 cur.execute(obj.directions.insert()) return len(route_rows)