async def get_trip_row_from_id(self, trip_id: str):
trips_table = db.get_table("trips")
async with db.acquire_conn() as conn:
res = await conn.execute(trips_table.select().where(
trips_table.c.system == self.system.value).where(
trips_table.c.trip_id == trip_id))
return await res.fetchone()
async def write_trip_paths(routes_for_shape_id, paths):
values = []
for (shape_id, path) in paths.items():
values.append(
{
"system": TRANSIT_SYSTEM.value,
"shape_id": shape_id,
"routes": routes_for_shape_id.get(shape_id),
"shape": "LINESTRING({})".format(
", ".join(["{} {}".format(p.x, p.y) for p in path])
),
}
)
table = db.get_table("trip_paths")
stmt = insert(table).values(values)
stmt = stmt.on_conflict_do_update(
index_elements=[table.c.system, table.c.shape_id],
set_={"shape": stmt.excluded.shape},
)
async with db.acquire_conn() as conn:
res = await conn.execute(
table.delete().where(table.c.system == TRANSIT_SYSTEM.value)
)
logging.info("Deleted %d rows from %s", res.rowcount, table.name)
await conn.execute(stmt)
logging.info("Inserted %d rows into %s", len(values), table.name)
async def query_realtime_stop_times(self, system: TransitSystem,
route_id: str, stop_ids: List[str]):
async with db.acquire_conn() as conn:
res = await conn.execute(
"""
select
rst.stop_id,
rst.departure,
rst.start_date,
st.departure_time as scheduled_departure
from realtime_stop_times as rst
left outer join stop_times as st
on
rst.trip_id = st.trip_id
and rst.stop_id = st.stop_id
where
rst.system = %s
and rst.route_id = %s
and rst.stop_id in %s
and rst.departure >= %s
""",
system.value,
route_id,
tuple(stop_ids),
datetime.now(timezone.utc) - timedelta(days=1),
)
return await res.fetchall()
async def process_feed(feed_id: str):
feed_message = await nyc.get_data(feed_id)
request_time = datetime.now(timezone.utc)
if not feed_message.IsInitialized():
raise Exception(
"Unable to parse NYC MTA feed {}: (FeedMessage not initialized)".
format(feed_id))
timestamp = feed_message.header.timestamp
json_str = google.protobuf.json_format.MessageToJson(feed_message)
logging.info(
"NYC MTA feed {}: {} ({:.3f} seconds ago), {} JSON bytes".format(
feed_id, timestamp,
time.time() - timestamp, len(json_str)))
async with db.acquire_conn() as conn:
table = db.get_table("realtime_raw")
await conn.execute(
insert(table).values(
system=gtfs.TransitSystem.NYC_MTA.value,
feed_id=feed_id,
time=datetime.fromtimestamp(timestamp, timezone.utc),
json=json.loads(json_str),
raw=feed_message.SerializeToString(),
update_time=request_time,
).on_conflict_do_update(table.primary_key,
set_=dict(update_time=request_time)))
async def query_timezone(self, system: TransitSystem):
agency = db.get_table("agency")
async with db.acquire_conn() as conn:
res = await conn.execute(
agency.select().where(agency.c.system == system.value))
row = await res.fetchone()
return gettz(row.agency_timezone)
async def _load_edges(transit_system: TransitSystem) -> List[Edge]:
edges = []
async with db.acquire_conn() as conn:
query = """
select
id,
node_id1,
node_id2,
routes,
ST_AsGeoJSON(path)::json as geojson,
ST_Length(path::geography) as length
from map_edges
where system=%s
"""
async for row in conn.execute(query, transit_system.value):
edges.append(
Edge(
id=row.id,
node_id1=row.node_id1,
node_id2=row.node_id2,
geojson=row.geojson,
length=row.length,
route_ids=row.routes,
)
)
return edges
async def get_stop_exists(self, stop_id: str) -> bool:
table = db.get_table("stops")
async with db.acquire_conn() as conn:
res = await conn.scalar(
sa.select([table.c.stop_id
]).where(table.c.system == self.system.value).where(
table.c.stop_id == stop_id))
return res is not None
async def delete_tables(transit_system: gtfs.TransitSystem):
# Order matters due to foreign keys
tables = reversed([db.get_table(spec.table) for spec in IMPORT_SPECS])
async with db.acquire_conn() as conn:
for table in tables:
res = await conn.execute(
table.delete().where(table.c.system == transit_system.value)
)
logging.info("Deleted %d rows from %s", res.rowcount, table.name)
async def query_route(self, system: gtfs.TransitSystem, route_id: str):
routes = db.get_table("routes")
async with db.acquire_conn() as conn:
res = await conn.execute(
routes.select().where(routes.c.system == system.value).where(
routes.c.route_id == route_id))
route = await res.fetchone()
assert route is not None
return route
async def query_stop(self, system: gtfs.TransitSystem, stop_id: str):
stops = db.get_table("stops")
async with db.acquire_conn() as conn:
res = await conn.execute(
stops.select().where(stops.c.system == system.value).where(
stops.c.stop_id == stop_id))
stop = await res.fetchone()
assert stop is not None
return stop
async def query_stop_and_parents(self, system: TransitSystem,
stop_id: str):
stops = db.get_table("stops")
async with db.acquire_conn() as conn:
res = await conn.execute(
stops.select().where(stops.c.system == system.value).where(
sa.or_(stops.c.stop_id == stop_id,
stops.c.parent_station == stop_id)))
return await res.fetchall()
async def get_trip_row_from_descriptor(
self, trip_descriptor: gtfs.TripDescriptor):
row = await self.get_trip_row_from_id(trip_descriptor.trip_id)
if row is not None:
return row
# table = db.get_table('trips')
# async with db.acquire_conn() as conn:
# res = await conn.execute(
# table.select()
# .where(table.c.system == self.system.value)
# .where(table.c.trip_id.like("%" + trip_descriptor.trip_id + "%"))
# )
# row = await res.fetchone()
# if row is not None:
# return row
if self.system is not gtfs.TransitSystem.NYC_MTA:
return None
# Monday = 0, Sunday = 6
day_of_week = trip_descriptor.start_date.weekday()
if day_of_week < 5:
service_day = nyc.ServiceDay.WEEKDAY
elif day_of_week == 5:
service_day = nyc.ServiceDay.SATURDAY
elif day_of_week == 6:
service_day = nyc.ServiceDay.SUNDAY
else:
raise ValueError("Unexpected day of week {} for {}".format(
day_of_week, trip_descriptor.start_date))
table = db.get_table("mta_trip_id")
async with db.acquire_conn() as conn:
res = await conn.execute(table.select(
).where(table.c.system == self.system.value).where(
table.c.alternate_trip_id == trip_descriptor.trip_id).where(
table.c.service_day == service_day))
rows = await res.fetchall()
if len(rows) == 0:
return None
if len(rows) > 1:
trip_ids = [row["trip_id"] for row in rows]
logging.info(
"%s: (%s, %s/%s) has multiple trip IDs: (%s)",
self.system,
trip_descriptor.trip_id,
trip_descriptor.start_date,
service_day,
", ".join(trip_ids),
)
return None
trip_id = rows[0]["trip_id"]
return await self.get_trip_row_from_id(trip_id)
async def get_routes_for_shape_id() -> Dict[str, Set[str]]:
query = """
select shape_id, array_agg(distinct route_id) as routes
from trips
where shape_id is not null
group by shape_id
"""
routes_for_shape_id = {}
async with db.acquire_conn() as conn:
async for row in conn.execute(query):
routes_for_shape_id[row.shape_id] = set(row.routes)
logging.info("%d shapes have routes", len(routes_for_shape_id))
return routes_for_shape_id
async def _get_stop_id_from_stop_seq(self, trip_id: str,
stop_seq: int) -> str:
table = db.get_table("stop_times")
async with db.acquire_conn() as conn:
stop_id = await conn.scalar(
sa.select([table.c.stop_id
]).where(table.c.system == self.system.value).where(
table.c.trip_id == trip_id).where(
table.c.stop_sequence == stop_seq))
if stop_id is None:
raise Exception(
"No stop_id for (trip_id, seq): ({}, {})".format(
trip_id, stop_seq))
return stop_id
async def query_stop_ids(self, system: gtfs.TransitSystem, route_id: str):
trips = db.get_table("trips")
stop_times = db.get_table("stop_times")
async with db.acquire_conn() as conn:
res = await conn.execute(
sa.select([stop_times.c.stop_id]).distinct().where(
sa.and_(
trips.c.system == system.value,
trips.c.route_id == route_id,
trips.c.trip_id == stop_times.c.trip_id,
)))
rows = await res.fetchall()
return [row["stop_id"] for row in rows]
async def write(self, transit_system: TransitSystem):
nodes_table = db.get_table("map_nodes")
edges_table = db.get_table("map_edges")
nodes_values = [
{
"system": transit_system.value,
"id": node.id,
"edge_ids": [edge.id for edge in node.edges],
"loc": node.postgis_point(),
"stop_ids": list(node.stop_ids) or None,
}
for node in self.nodes.values()
]
edges_values = []
for edge in self.edges.values():
# The nodes are in the same order as the path
(node1, node2) = edge.nodes
if node1.point == edge.points[-1]:
(node1, node2) = (node2, node1)
edges_values.append(
{
"system": transit_system.value,
"id": edge.id,
"node_id1": node1.id,
"node_id2": node2.id,
"path": edge.postgis_linestring(),
"routes": list(edge.routes),
}
)
async with db.acquire_conn() as conn:
res = await conn.execute(
nodes_table.delete().where(nodes_table.c.system == transit_system.value)
)
logging.info("Deleted %d rows from %s", res.rowcount, nodes_table.name)
res = await conn.execute(nodes_table.insert().values(nodes_values))
logging.info("Inserted %d rows into %s", res.rowcount, nodes_table.name)
res = await conn.execute(
edges_table.delete().where(edges_table.c.system == transit_system.value)
)
logging.info("Deleted %d rows from %s", res.rowcount, edges_table.name)
res = await conn.execute(edges_table.insert().values(edges_values))
logging.info("Inserted %d rows into %s", res.rowcount, edges_table.name)
async def import_generic(
transit_system: gtfs.TransitSystem,
csv_path: str,
table: sa.Table,
spec: ImportSpec,
):
with open(csv_path) as csv_file:
reader = csv.DictReader(csv_file)
db_rows = []
for csv_row in reader:
db_row_spec = {}
db_row_spec["system"] = transit_system.value
for col in spec.copy:
db_row_spec[col] = csv_row[col] if csv_row[col] != "" else None
for col_name, generator in spec.compute.items():
db_row_spec[col_name] = generator(csv_row)
db_rows.extend(get_db_rows(db_row_spec))
async with db.acquire_conn() as conn:
await import_rows_batch(conn, table, db_rows)
async def _load_nodes(transit_system: TransitSystem) -> List[Node]:
nodes = []
async with db.acquire_conn() as conn:
query = """
select
id,
edge_ids,
ST_AsGeoJSON(loc)::json as geojson,
stop_ids
from map_nodes
where system=%s
"""
async for row in conn.execute(query, transit_system.value):
nodes.append(
Node(
id=row.id,
edge_ids=row.edge_ids,
geojson=row.geojson,
stop_ids=row.stop_ids,
)
)
return nodes
async def import_lines(filename: str):
with open(filename) as file:
geodata = json.load(file)
assert geodata["type"] == "FeatureCollection"
logging.info("%s has %d lines", filename, len(geodata["features"]))
async with db.acquire_conn() as conn:
res = await conn.execute(db.get_table("nyc_subway_lines").delete())
logging.info("Deleted %d lines", res.rowcount)
insert_stmt = """
insert into nyc_subway_lines (objectid, lines, shape_len, path)
values (
%(objectid)s,
%(lines)s,
%(shape_len)s,
ST_GeomFromGeoJSON(%(path)s)
)
"""
values = []
for feature in geodata["features"]:
assert feature["type"] == "Feature"
assert feature["geometry"]["type"] == "LineString"
props = feature["properties"]
values.append({
"objectid": props["objectid"],
"lines": props["name"].split("-"),
"shape_len": float(props["shape_len"]),
"path": json.dumps(feature["geometry"]),
})
async def insert(value):
async with db.acquire_conn() as conn:
await conn.execute(insert_stmt, value)
await asyncio.gather(*[insert(value) for value in values])
logging.info("Inserted %d lines", len(values))
async def process_trip_update(self, update: gtfs.TripUpdate,
message: gtfs.FeedMessage):
trip = await self.parser.get_trip_row_from_descriptor(update.trip)
if trip is None:
if not message.is_trip_replaced(update.trip.route_id):
logging.warning(
"TripUpdate trip not scheduled or replaced: (%s, %s, %s, %s) at %s",
update.trip.trip_id,
update.trip.train_id,
update.trip.route_id,
update.trip.start_date,
message.timestamp,
)
table = db.get_table("realtime_stop_times2")
raw_table = db.get_table("realtime_raw_stop_times")
# All updates in this function will share the same update_time
now = datetime.now(timezone.utc)
route_id = update.trip.route_id
start_date = update.trip.start_date
trip_id = trip["trip_id"] if trip else update.trip.trip_id
# train_id is a primary key and cannot be None
train_id = update.trip.train_id or ""
# Get last few entries for this trip
async with db.acquire_conn() as conn:
res = await conn.execute(raw_table.select(
).where(raw_table.c.system == self.system.value).where(
raw_table.c.route_id == route_id).where(
raw_table.c.start_date == start_date).where(
raw_table.c.trip_id == trip_id).where(
raw_table.c.train_id == train_id).where(
raw_table.c.time < message.timestamp).order_by(
raw_table.c.time.desc()).limit(5))
rows = await res.fetchall()
previous_stop_times = [[
self.deserialize_stop_time(obj) for obj in row.stop_times
] for row in rows]
# Determine most likely stop order
# Remove any stops that are out of order
stop_time_updates = update.stop_time_updates.copy()
# Update stop information in into realtime_stop_times2
if len(stop_time_updates) > 0:
values = []
for stu in stop_time_updates:
values.append({
"system":
self.system.value,
"route_id":
route_id,
"start_date":
start_date,
"trip_id":
trip_id,
"train_id":
train_id,
"stop_id":
stu.stop_id,
"arrival":
stu.arrival,
"departure":
stu.departure,
"departure_or_arrival":
stu.departure
if stu.departure is not None else stu.arrival,
"time":
update.timestamp or message.timestamp,
})
stmt = insert(table).values(values)
stmt = stmt.on_conflict_do_update(
index_elements=[
table.c.system,
table.c.route_id,
table.c.start_date,
table.c.trip_id,
table.c.train_id,
table.c.stop_id,
],
set_={
"arrival": stmt.excluded.arrival,
"departure": stmt.excluded.departure,
"departure_or_arrival": stmt.excluded.departure_or_arrival,
"time": stmt.excluded.time,
},
where=(table.c.time <= stmt.excluded.time),
)
async with db.acquire_conn() as conn:
await conn.execute(stmt)
# Deleted any outdated stop info from the last entry
# 1. If there are no stops in the current stop list, the train
# has completed it's route and we do not remove any stops.
# 2. If the train arrived before the current time, assume it
# arrived.
# 3. Otherwise, see if it would arrive before the earliest train in the
# current stop list. If it does, assume it is omitted from the
# current stop list because it already arrived early.
if len(previous_stop_times) > 0 and len(stop_time_updates) > 0:
current_stop_ids = [stu.stop_id for stu in stop_time_updates]
earliest_stop_time = min([
stu.arrival if stu.arrival is not None else stu.departure
for stu in stop_time_updates
])
outdated_stop_ids = []
for stu in previous_stop_times[0]:
time = stu.arrival if stu.arrival is not None else stu.departure
if (stu.stop_id not in current_stop_ids
and time > message.timestamp
and time >= earliest_stop_time):
outdated_stop_ids.append(stu.stop_id)
if len(outdated_stop_ids) > 0:
logging.info(
"Removing stops [%s] from trip (%s, %s, %s, %s) based on "
"updated information at %s",
", ".join(outdated_stop_ids),
trip_id,
train_id,
route_id,
start_date,
message.timestamp,
)
async with db.acquire_conn() as conn:
await conn.execute(table.delete().where(
table.c.system == self.system.value
).where(table.c.route_id == route_id).where(
table.c.start_date == start_date).where(
table.c.trip_id == trip_id).where(
table.c.train_id == train_id).where(
table.c.stop_id.in_(outdated_stop_ids)))
# Insert stops data into realtime_raw_stop_times
values = {
"system":
self.system.value,
"route_id":
route_id,
"start_date":
start_date,
"trip_id":
trip_id,
"train_id":
train_id,
"time":
message.timestamp,
"stop_times":
[self.serialize_stop_time(stu) for stu in stop_time_updates],
"update_time":
now,
}
stmt = insert(raw_table).values(values)
stmt = stmt.on_conflict_do_update(
index_elements=[
raw_table.c.system,
raw_table.c.route_id,
raw_table.c.start_date,
raw_table.c.trip_id,
raw_table.c.train_id,
raw_table.c.time,
],
set_={
"stop_times": stmt.excluded.stop_times,
"update_time": stmt.excluded.update_time,
},
)
async with db.acquire_conn() as conn:
await conn.execute(stmt)
async def insert(value):
async with db.acquire_conn() as conn:
await conn.execute(insert_stmt, value)
async def import_stations(filename: str):
with open(filename) as file:
geodata = json.load(file)
assert geodata["type"] == "FeatureCollection"
logging.info("%s has %d stations", filename, len(geodata["features"]))
async with db.acquire_conn() as conn:
res = await conn.execute(db.get_table("nyc_subway_stations").delete())
logging.info("Deleted %d stations", res.rowcount)
insert_stmt = """
insert into nyc_subway_stations (objectid, name, notes, lines, loc)
values (
%(objectid)s,
%(name)s,
%(notes)s,
%(lines)s,
ST_GeomFromGeoJSON(%(loc)s)
)
"""
values = []
for feature in geodata["features"]:
assert feature["type"] == "Feature"
assert feature["geometry"]["type"] == "Point"
props = feature["properties"]
values.append({
"objectid": props["objectid"],
"name": props["name"],
"notes": props["notes"],
"lines": props["line"].split("-"),
"loc": json.dumps(feature["geometry"]),
})
async def insert(value):
async with db.acquire_conn() as conn:
await conn.execute(insert_stmt, value)
await asyncio.gather(*[insert(value) for value in values])
logging.info("Inserted %d stations", len(values))
stmt = """
select
distinct on (stop_id)
name,
objectid,
array_to_string(lines, '-') as line,
loc,
stop_name,
stop_id,
stop_loc,
ST_Distance(loc::geography, stop_loc::geography) as distance
from stops s
cross join nyc_subway_stations nss
where parent_station is null
order by
stop_id,
distance asc
"""
async with db.acquire_conn() as conn:
res = await conn.execute(stmt)
rows = await res.fetchall()
failed = False
o_to_s = {}
s_to_o = {}
for row in rows:
# Staten Island is very far away and has no subway data, so let's ignore
# everything more than 1km away from the nearest GeoJSON station
if row.distance >= 1000:
continue
# Some stops have no station
if row.stop_id in NO_STATION:
continue
if row.stop_id in STATION_EQUIVALENTS:
(name, line) = STATION_EQUIVALENTS[row.stop_id]
features = [
f for f in geodata["features"]
if f["properties"]["name"] == name
and f["properties"]["line"] == line
]
if len(features) != 1:
raise Exception(
"{}, {} does not uniquely describe station: [{}]".format(
name,
line,
", ".join(
[f["properties"]["objectid"] for f in features]),
))
o = features[0]["properties"]["objectid"]
s = row.stop_id
if o not in o_to_s:
o_to_s[o] = []
o_to_s[o].append(s)
if s not in s_to_o:
s_to_o[s] = []
s_to_o[s].append(o)
continue
if normalize_name(row.name) == normalize_name(row.stop_name):
o = row.objectid
s = row.stop_id
s = row.stop_id
if o not in o_to_s:
o_to_s[o] = []
o_to_s[o].append(s)
if s not in s_to_o:
s_to_o[s] = []
s_to_o[s].append(o)
continue
print("Name mismatch: {} ({}), {}, {}, {} ({}), {}".format(
row.stop_name,
row.stop_id,
normalize_name(row.stop_name),
normalize_name(row.name),
row.name,
row.line,
row.distance,
))
failed = True
if failed:
raise Exception("Cannot write data, resolve mismatches first")
# Duplicates expected here becuase stops.txt has finer granularity
# on some stations where platforms are stacked (e.g. a station is a single
# level of platforms, instead of both of them)
expected_duplicate_objectids = {
# Queensboro Plaza
"103": ["718", "R09"],
# 145 St
"295": ["A12", "D13"],
# Coney Island - Stillwell Ave
"469": ["D43", "N12"],
}
overloaded_objects = {
k: v
for (k, v) in o_to_s.items()
if len(set(v)) > 1 and (k,
v) not in expected_duplicate_objectids.items()
}
if len(overloaded_objects) > 0:
logging.info(
"Multiple stop_ids for objectid: %s",
overloaded_objects,
)
raise Exception("Cannot write data, resolve overloaded stop_ids")
overloaded_stops = {k: v for (k, v) in s_to_o.items() if len(set(v)) > 1}
if len(overloaded_stops) > 0:
logging.info("Multiple objectids for stop_id: %s", overloaded_stops)
raise Exception("Cannot write data, resolve overloaded objectids")
async with db.acquire_conn() as conn:
res = await conn.execute(db.get_table("stops").select())
all_stops = await res.fetchall()
res = await conn.execute(db.get_table("nyc_subway_stations").select())
all_imported_stations = await res.fetchall()
# all_stops_by_id = {s.stop_id: s for s in all_stops}
all_imported_stations_by_id = {
s.objectid: s
for s in all_imported_stations
}
stmt = db.get_table("map_stops").insert()
values = []
for stop in all_stops:
station_id = stop.parent_station or stop.stop_id
# station = all_stops_by_id[station_id]
if station_id in s_to_o:
# We just imported this station and will use it's data in the map
objectid = c.only(s_to_o[station_id])
loc = all_imported_stations_by_id[objectid].loc
elif station_id in NO_STATION:
# This station is not in the map
logging.info("Skipping stop %s (%s) in map", stop.stop_id,
stop.stop_name)
continue
else:
# We did not import any data for this station, use the existing
# location data (e.g. for the Staten Island Railway)
# loc = stop.stop_loc
continue
values.append({
"system": gtfs.TransitSystem.NYC_MTA.value,
"stop_id": stop.stop_id,
"loc": loc,
})
async with db.acquire_conn() as conn:
deleted = await conn.execute(db.get_table("map_stops").delete())
logging.info("Deleted %d stops from map_stops", deleted.rowcount)
await conn.execute(db.get_table("map_stops").insert().values(values))
logging.info("Wrote %d stops to map_stops", len(values))
async def _write_trip_update(self, update: gtfs.TripUpdate,
message: gtfs.FeedMessage):
trip = await self.parser.get_trip_row_from_descriptor(update.trip)
if trip is None:
if not message.is_trip_replaced(update.trip.route_id):
logging.warning(
"TripUpdate trip not scheduled or replaced: (%s, %s, %s) at %s",
update.trip.trip_id,
update.trip.route_id,
update.trip.start_date,
message.timestamp,
)
def get_insert_values(stop_time_update) -> Optional[Tuple[str, Dict]]:
values = {
"system": self.system.value,
"route_id": update.trip.route_id,
"stop_id": stop_time_update.stop_id,
"start_date": update.trip.start_date,
"trip_id":
trip["trip_id"] if trip is not None else update.trip.trip_id,
"timestamp": update.timestamp,
"arrival": stop_time_update.arrival,
"departure": stop_time_update.departure,
"update_time": message.timestamp,
}
key = "||".join([
values["system"],
values["route_id"],
values["stop_id"],
str(values["start_date"]),
])
return (key, values)
insert_key_values = [
get_insert_values(update) for update in update.stop_time_updates
]
if len(insert_key_values) == 0:
return
# Sometimes we get data that updates the same trip twice for the same
# stop. We can't update them both in the same DB update because that
# can conflict. Resolve by picking one semi-arbitrarily.
insert_values = list(dict(insert_key_values).values())
table = db.get_table("realtime_stop_times")
stmt = insert(table).values(insert_values)
stmt = stmt.on_conflict_do_update(
index_elements=[
table.c.system,
table.c.route_id,
table.c.stop_id,
table.c.start_date,
table.c.trip_id,
],
set_={
"timestamp": stmt.excluded.timestamp,
"arrival": stmt.excluded.arrival,
"departure": stmt.excluded.departure,
"update_time": stmt.excluded.update_time,
},
where=(table.c.update_time <= stmt.excluded.update_time),
)
async with db.acquire_conn() as conn:
await conn.execute(stmt)
async def _write_vehicle_position(self, position: gtfs.VehiclePosition,
message: gtfs.FeedMessage):
trip = await self.parser.get_trip_row_from_descriptor(position.trip)
if trip is None:
if not message.is_trip_replaced(position.trip.route_id):
logging.warning(
"VehiclePosition trip not scheduled or replaced: "
"(%s, %s, %s) at %s",
position.trip.trip_id,
position.trip.route_id,
position.trip.start_date,
message.timestamp,
)
if position.stop_id is not None:
stop_id = position.stop_id
else:
# Guess from current_stop_sequence and stop_times
# For some reason this is not always consistent with stop_id,
# which is why we use stop_id if it exists (above)
assert position.current_stop_sequence is not None
if trip is None:
logging.debug(
"Cannot write VehiclePosition: no stop_id or trip_id for "
"(%s, %s, %s) at %s",
position.trip.trip_id,
position.trip.route_id,
position.trip.start_date,
message.timestamp,
)
return
current_stop_sequence = position.current_stop_sequence
# Give up on current_stop_sequence because it is weird. It starts
# at 0 which is invalid. It also goes over the number of stops in
# the table. For the L train, there are no entries with
# current_stop_sequence larger than 22, even though there are trips
# with 24 stops (e.g. BFA19SUPP-L047-Weekday-99_048500_L..S01R).
# It seems like others have this problem as well:
# https://groups.google.com/forum/#!topic/mtadeveloperresources/x8-f1biU-l0
if self.system == gtfs.TransitSystem.NYC_MTA:
return
stop_id = await self.parser.get_stop_id_from_stop_seq(
trip["trip_id"], current_stop_sequence)
# For some reason, stop_id does not always exist (wtf MTA?).
# In that case, don't do any writes because that will fail on the
# foreign key constraint.
stop_exists = await self.parser.get_stop_exists(stop_id)
if not stop_exists:
logging.debug(
"Encountered nonexistent stop %s in vehicle position "
"(%s, %s, %s) at %s",
stop_id,
position.trip.trip_id,
position.trip.route_id,
position.trip.start_date,
message.timestamp,
)
return
table = db.get_table("realtime_vehicle_positions")
values = {
"system": self.system.value,
"route_id": position.trip.route_id,
"stop_id": stop_id,
"start_date": position.trip.start_date,
"trip_id":
trip["trip_id"] if trip is not None else position.trip.trip_id,
"timestamp": position.timestamp or message.timestamp,
"status": position.current_status,
"update_time": message.timestamp,
}
stmt = insert(table).values(values)
stmt = stmt.on_conflict_do_update(
index_elements=[
table.c.system,
table.c.route_id,
table.c.stop_id,
table.c.start_date,
table.c.trip_id,
table.c.timestamp,
],
set_={
"status": stmt.excluded.status,
"update_time": stmt.excluded.update_time,
},
where=(table.c.update_time <= stmt.excluded.update_time),
)
async with db.acquire_conn() as conn:
await conn.execute(stmt)
