def list_regions():
""" Shows list of all regions and their areas. """
regions_areas = (
db.session.query(
models.Region.code.label("region_code"),
models.Region.name.label("region_name"),
db.case([(models.District.code.is_(None),
db.literal_column("'admin_area'"))],
else_=db.literal_column("'district'")).label("area_type"),
db.case([(models.District.code.is_(None), models.AdminArea.code)],
else_=models.District.code).label("area_code"),
db.case([(models.District.code.is_(None), models.AdminArea.name)],
else_=models.District.name).label("area_name")
).select_from(models.Region)
.join(models.Region.areas)
.outerjoin(models.AdminArea.districts)
.filter(models.Region.code != "GB")
.order_by("region_name", "area_name")
.all()
)
regions = {}
areas = {}
for row in regions_areas:
regions[row.region_code] = row.region_name
areas.setdefault(row.region_code, []).append(row)
return render_template("regions.html", regions=regions, areas=areas)
def _bit_array_contains(array, col):
""" SQL expression for matching integer with a bit array, equivalent to
`(1 << col) & array > 0`.
"""
return (
db.literal_column("1").op("<<")(col).op("&")(array) >
db.literal_column("0")
)
def _group_lines_stops(list_stops):
""" Groups lines and stops such that each distinct line and direction has
a group of stops associated with it.
"""
stops = [s.atco_code for s in list_stops]
separator = db.literal_column("' / '")
destinations = db.func.string_agg(
db.distinct(models.JourneyPattern.destination),
pg.aggregate_order_by(separator, models.JourneyPattern.destination)
)
array_stops = pg.array_agg(db.distinct(models.JourneyLink.stop_point_ref))
groups = (
db.session.query(
models.Service.code.label("code"),
models.JourneyPattern.direction.label("direction"),
models.Service.line.label("line"),
destinations.label("destination"),
array_stops.label("stops")
)
.select_from(models.Service)
.join(models.Service.patterns)
.join(models.JourneyPattern.links)
.filter(models.JourneyLink.stop_point_ref.in_(stops))
.group_by(models.Service.code, models.Service.line,
models.JourneyPattern.direction)
.order_by(models.Service.line, models.JourneyPattern.direction)
.all()
)
return [g._asdict() for g in groups]
def call(cls, limit):
""" Request a call, checking whether it was within the daily limit.
:param limit: The limit on number of calls each day starting at
00:00 UTC. Ignored if is None or negative.
"""
tz = db.bindparam("utc", "UTC")
one = db.literal_column("1")
today = db.func.date(db.func.timezone(tz, db.func.now()))
date_last_called = db.func.date(db.func.timezone(tz, cls.last_called))
statement = (
db.update(cls)
.values(
last_called=db.func.now(),
call_count=db.case(
(date_last_called < today, one),
else_=cls.call_count + one,
),
)
.returning(cls.call_count)
)
count = db.session.execute(statement).scalar()
if limit is None or limit < 0:
utils.logger.debug(f"Request limit {limit!r} ignored")
return True
elif count <= limit:
utils.logger.debug(f"Request was allowed: {count} <= {limit}")
return True
else:
utils.logger.warning(f"Request limit exceeded: {count} > {limit}")
return False
def list_stops(self, group_areas=True):
""" Queries all stop areas and stop points (those not already in
stop areas) within locality, ordered by name and indicator.
:param group_areas: Consolidate stops into stop areas.
"""
stops = (
db.session.query(
utils.table_name(StopPoint).label("table_name"),
StopPoint.atco_code.label("code"),
StopPoint.name.label("name"),
StopPoint.short_ind.label("short_ind"),
StopPoint.admin_area_ref.label("admin_area_ref"),
StopPoint.stop_type.label("stop_type"),
StopArea.code.label("stop_area_ref")
)
.select_from(StopPoint)
.outerjoin(
StopArea,
(StopPoint.stop_area_ref == StopArea.code) & StopArea.active
)
.filter(StopPoint.locality_ref == self.code, StopPoint.active)
)
if group_areas:
stops_outside_areas = stops.filter(
StopPoint.stop_area_ref.is_(None) |
db.not_(StopArea.active) |
(StopArea.locality_ref != self.code)
)
stop_areas = (
db.session.query(
utils.table_name(StopArea).label("table_name"),
StopArea.code.label("code"),
StopArea.name.label("name"),
StopArea.stop_count.label("short_ind"),
StopArea.admin_area_ref.label("admin_area_ref"),
StopArea.stop_area_type.label("stop_type"),
db.literal_column("NULL").label("stop_area_ref")
)
.join(StopArea.stop_points)
.group_by(StopArea.code)
.filter(StopArea.locality_ref == self.code, StopArea.active)
)
subquery = stops_outside_areas.union(stop_areas).subquery()
query = (
db.session.query(subquery)
.order_by(subquery.c.name, subquery.c.short_ind)
)
else:
query = stops.order_by(StopPoint.name, StopPoint.short_ind)
return query.all()
def _filter_journey_dates(query, date):
""" Join multiple tables used to filter journeys by valid dates (eg week
days, bank holidays or organisation working days).
It is assumed the Journey and JourneyPattern models are in the FROM
clause or joined, and the query is grouped by the journey ID.
"""
# Aggregate the matching bank holidays and operational periods before
# joining laterally. If they were joined first the query planner may pick a
# slower plan to compensate for the row count 'blowing up', but in practice
# the actual number of matching rows is very low.
# Match special period if they fall within inclusive date range
matching_periods = (
db.select([
db.func.bool_and(models.SpecialPeriod.operational)
.label("is_operational")
])
.select_from(models.SpecialPeriod)
.where(
models.SpecialPeriod.journey_ref == models.Journey.id,
models.SpecialPeriod.date_start <= date,
models.SpecialPeriod.date_end >= date,
)
.lateral("matching_periods")
)
query = query.join(matching_periods, db.true())
# Match bank holidays on the same day
matching_bank_holidays = (
db.select([
db.func.bool_and(
_bit_array_contains(
models.Journey.include_holidays,
models.BankHolidayDate.holiday_ref,
),
).label("is_operational"),
db.func.bool_or(
_bit_array_contains(
models.Journey.exclude_holidays,
models.BankHolidayDate.holiday_ref,
),
).label("not_operational")
])
.select_from(models.BankHolidayDate)
.where(models.BankHolidayDate.date == date)
.lateral("matching_bank_holidays")
)
query = query.join(matching_bank_holidays, db.true())
# Match organisations working/holiday periods - can be operational
# during holiday or working periods associated with organisation so
# working attributes need to match (eg journey running during holidays
# must match with operating periods for holidays or vice versa)
matching_organisations = (
db.select([
db.func.bool_and(
models.OperatingPeriod.id.isnot(None) &
models.ExcludedDate.id.is_(None) &
models.Organisations.operational
).label("is_operational"),
db.func.bool_or(
models.OperatingPeriod.id.isnot(None) &
models.ExcludedDate.id.is_(None) &
db.not_(models.Organisations.operational)
).label("not_operational")
])
.select_from(models.Organisations)
.join(
models.Organisation,
models.Organisations.org_ref == models.Organisation.code,
)
.join(
models.OperatingPeriod,
db.and_(
models.Organisation.code == models.OperatingPeriod.org_ref,
models.Organisations.working == models.OperatingPeriod.working,
models.OperatingPeriod.date_start <= date,
models.OperatingPeriod.date_end.is_(None) |
(models.OperatingPeriod.date_end >= date),
),
)
.outerjoin(
models.ExcludedDate,
db.and_(
models.Organisation.code == models.ExcludedDate.org_ref,
models.Organisations.working == models.ExcludedDate.working,
models.ExcludedDate.date == date,
)
)
.where(models.Organisations.journey_ref == models.Journey.id)
.lateral("matching_organisations")
)
query = query.join(matching_organisations, db.true())
# Find week of month (0 to 4) and day of week (Monday 1 to Sunday 7)
week = db.cast(db.extract("DAY", date), db.Integer) / db.literal_column("7")
weekday = db.cast(db.extract("ISODOW", date), db.Integer)
query = query.filter(
# Date must be within range for journey pattern, may be unbounded
models.JourneyPattern.date_start <= date,
models.JourneyPattern.date_end.is_(None) |
(models.JourneyPattern.date_end >= date),
# In order of precedence:
# - Do not run on special days
# - Do not run on bank holidays
# - Run on special days
# - Run on bank holidays
# - Do not run during organisation working or holiday periods
# - Run during organisation working or holiday periods
# - Run or not run on specific weeks of month
# - Run or not run on specific days of week
matching_periods.c.is_operational.isnot(None) |
matching_bank_holidays.c.is_operational.isnot(None) |
(models.Journey.weeks.is_(None) |
_bit_array_contains(models.Journey.weeks, week)) &
_bit_array_contains(models.Journey.days, weekday)
)
# Bank holidays and special dates have precedence over others so only
# include journeys if all references are either null or are operational.
# Include non-null references in WHERE so they can be checked here.
# Check organisation working/holiday periods here after grouping as
# there can be multiple periods for an organisation.
query = query.having(db.func.bool_and(
db.case([
(matching_periods.c.is_operational.isnot(None),
matching_periods.c.is_operational),
(matching_bank_holidays.c.not_operational, db.false()),
(matching_bank_holidays.c.is_operational, db.true()),
(matching_organisations.c.not_operational, db.false()),
(matching_organisations.c.is_operational, db.true()),
], else_=db.true())
))
return query
"""
Creating timetables for a service.
"""
from collections import abc
import functools
from nextbus import db, graph, models
_ONE_HOUR = db.cast(db.literal_column("'1 hour'"), db.Interval)
_GB_TZ = db.bindparam("gb", "Europe/London")
_UTC_TZ = db.bindparam("utc", "UTC")
_TRUNCATE_MIN = db.bindparam("trunc_min", "minute")
_FORMAT_TIME = db.bindparam("format_time", "HH24MI")
def _bit_array_contains(array, col):
""" SQL expression for matching integer with a bit array, equivalent to
`(1 << col) & array > 0`.
"""
return (
db.literal_column("1").op("<<")(col).op("&")(array) >
db.literal_column("0")
)
def _format_time(timestamp):
""" SQL expression to format a date or timestamp as `HHMM`, eg 0730. """
return db.func.to_char(
db.func.date_trunc(_TRUNCATE_MIN, timestamp),
_FORMAT_TIME,
def insert_service_pairs(connection):
""" Uses existing service data to update list of pairs of similar
services.
"""
service = Service.__table__
pattern = JourneyPattern.__table__
link = JourneyLink.__table__
# Temporary table for all services, direction and stops they call at
service_stops = db.Table("service_stops",
db.MetaData(),
db.Column("id",
db.Integer,
autoincrement=False,
index=True),
db.Column("stop_point_ref",
db.Text,
nullable=True,
index=True),
db.Column("outbound",
db.Boolean,
nullable=False,
index=True),
db.Column("inbound",
db.Boolean,
nullable=False,
index=True),
prefixes=["TEMPORARY"],
postgresql_on_commit="DROP")
fill_service_stops = service_stops.insert().from_select(
["id", "stop_point_ref", "outbound", "inbound"],
db.select([
service.c.id,
db.cast(link.c.stop_point_ref, db.Text),
db.func.bool_or(~pattern.c.direction),
db.func.bool_or(pattern.c.direction)
]).select_from(
service.join(pattern, service.c.id == pattern.c.service_ref).join(
link, pattern.c.id == link.c.pattern_ref)).where(
link.c.stop_point_ref.isnot(None)).group_by(
service.c.id, db.cast(link.c.stop_point_ref, db.Text)))
# Find all services sharing at least one stop
ss0 = service_stops.alias("ss0")
ss1 = service_stops.alias("ss1")
shared = (db.select([
ss0.c.id.label("id0"), ss1.c.id.label("id1")
]).distinct().select_from(
ss0.join(ss1, (ss0.c.id < ss1.c.id) &
(ss0.c.stop_point_ref == ss1.c.stop_point_ref))).alias("t"))
# Iterate over possible combinations of directions
directions = (db.select([db.column("d", db.Integer)]).select_from(
db.func.generate_series(0, 3).alias("d")).alias("d"))
# For each service, find all stops and count them
select_a = db.select([
service_stops.c.stop_point_ref
]).where((service_stops.c.id == shared.c.id0)
& ((directions.c.d.op("&")(1) > 0) & service_stops.c.inbound
| (directions.c.d.op("&")
(1) == 0) & service_stops.c.outbound)).correlate(
shared, directions)
select_b = db.select([
service_stops.c.stop_point_ref
]).where((service_stops.c.id == shared.c.id1)
& ((directions.c.d.op("&")(2) > 0) & service_stops.c.inbound
| (directions.c.d.op("&")
(2) == 0) & service_stops.c.outbound)).correlate(
shared, directions)
select_c = select_a.intersect(select_b)
count = db.func.count(db.literal_column("*")).label("count")
la = db.select([count]).select_from(select_a.alias("a")).lateral("a")
lb = db.select([count]).select_from(select_b.alias("b")).lateral("b")
lc = db.select([count]).select_from(select_c.alias("c")).lateral("c")
utils.logger.info(
"Querying all services and stops they call at to find similar services"
)
service_stops.create(connection)
connection.execute(fill_service_stops)
return [
db.select([
db.func.row_number().over(), shared.c.id0,
(directions.c.d.op("&")(1) > 0).label("dir0"), la.c.count,
shared.c.id1, (directions.c.d.op("&")(2) > 0).label("dir1"),
lb.c.count,
db.cast(lc.c.count, db.Float) /
db.func.least(la.c.count, lb.c.count)
]).where((la.c.count > 0) & (lb.c.count > 0) & (lc.c.count > 0))
]
def _select_fts_vectors():
""" Helper function to create a query for the full text search materialized
view.
Core expressions are required, because the session has not been set up
yet, though ORM models and attributes can still be used.
"""
null = db.literal_column("NULL")
region = (db.select([
utils.table_name(Region).label("table_name"),
db.cast(Region.code, db.Text).label("code"),
Region.name.label("name"),
null.label("indicator"),
null.label("street"),
null.label("stop_type"),
null.label("stop_area_ref"),
null.label("locality_name"),
null.label("district_name"),
null.label("admin_area_ref"),
null.label("admin_area_name"),
db.cast(pg.array(()), pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column((Region.name, "A")).label("vector")
]).where(Region.code != 'GB'))
admin_area = (db.select([
utils.table_name(AdminArea).label("table_name"),
db.cast(AdminArea.code, db.Text).label("code"),
AdminArea.name.label("name"),
null.label("indicator"),
null.label("street"),
null.label("stop_type"),
null.label("stop_area_ref"),
null.label("locality_name"),
null.label("district_name"),
AdminArea.code.label("admin_area_ref"),
AdminArea.name.label("admin_area_name"),
pg.array((AdminArea.code, )).label("admin_areas"),
_tsvector_column((AdminArea.name, "A")).label("vector")
]).where(AdminArea.region_ref != 'GB'))
district = (db.select([
utils.table_name(District).label("table_name"),
db.cast(District.code, db.Text).label("code"),
District.name.label("name"),
null.label("indicator"),
null.label("street"),
null.label("stop_type"),
null.label("stop_area_ref"),
null.label("locality_name"),
null.label("district_name"),
AdminArea.code.label("admin_area_ref"),
AdminArea.name.label("admin_area_name"),
db.cast(pg.array((AdminArea.code, )),
pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column((District.name, "A"),
(AdminArea.name, "C")).label("vector")
]).select_from(
District.__table__.join(AdminArea,
AdminArea.code == District.admin_area_ref)))
locality = (db.select([
utils.table_name(Locality).label("table_name"),
db.cast(Locality.code, db.Text).label("code"),
Locality.name.label("name"),
null.label("indicator"),
null.label("street"),
null.label("stop_type"),
null.label("stop_area_ref"),
null.label("locality_name"),
District.name.label("district_name"),
AdminArea.code.label("admin_area_ref"),
AdminArea.name.label("admin_area_name"),
db.cast(pg.array((AdminArea.code, )),
pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column((Locality.name, "A"),
(db.func.coalesce(District.name, ""), "C"),
(AdminArea.name, "C")).label("vector")
]).select_from(
Locality.__table__.outerjoin(
District, District.code == Locality.district_ref).join(
AdminArea, AdminArea.code == Locality.admin_area_ref)).where(
db.exists([
StopPoint.atco_code
]).where(StopPoint.locality_ref == Locality.code)))
stop_area = (db.select([
utils.table_name(StopArea).label("table_name"),
db.cast(StopArea.code, db.Text).label("code"),
StopArea.name.label("name"),
db.cast(db.func.count(StopPoint.atco_code),
db.Text).label("indicator"),
null.label("street"),
StopArea.stop_area_type.label("stop_type"),
null.label("stop_area_ref"),
Locality.name.label("locality_name"),
District.name.label("district_name"),
AdminArea.code.label("admin_area_ref"),
AdminArea.name.label("admin_area_name"),
db.cast(pg.array((AdminArea.code, )),
pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column((StopArea.name, "B"),
(db.func.coalesce(Locality.name, ""), "C"),
(db.func.coalesce(District.name, ""), "D"),
(AdminArea.name, "D")).label("vector")
]).select_from(
StopArea.__table__.join(
StopPoint, (StopArea.code == StopPoint.stop_area_ref)
& StopPoint.active).outerjoin(
Locality, Locality.code == StopArea.locality_ref).outerjoin(
District, District.code == Locality.district_ref).join(
AdminArea,
AdminArea.code == StopArea.admin_area_ref)).where(
StopArea.active).group_by(StopArea.code,
Locality.name,
District.name,
AdminArea.code))
stop_point = (db.select([
utils.table_name(StopPoint).label("table_name"),
db.cast(StopPoint.atco_code, db.Text).label("code"),
StopPoint.name.label("name"),
StopPoint.short_ind.label("indicator"),
StopPoint.street.label("street"),
StopPoint.stop_type.label("stop_type"),
StopPoint.stop_area_ref.label("stop_area_ref"),
Locality.name.label("locality_name"),
District.name.label("district_name"),
AdminArea.code.label("admin_area_ref"),
AdminArea.name.label("admin_area_name"),
db.cast(pg.array((AdminArea.code, )),
pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column((StopPoint.name, "B"),
(db.func.coalesce(StopPoint.street, ""), "B"),
(Locality.name, "C"),
(db.func.coalesce(District.name, ""), "D"),
(AdminArea.name, "D")).label("vector")
]).select_from(
StopPoint.__table__.join(
Locality, Locality.code == StopPoint.locality_ref).outerjoin(
District, District.code == Locality.district_ref).join(
AdminArea,
AdminArea.code == StopPoint.admin_area_ref)).where(
StopPoint.active))
service = (db.select([
utils.table_name(Service).label("table_name"),
Service.code.label("code"),
Service.short_description.label("name"),
Service.line.label("indicator"),
null.label("street"),
null.label("stop_type"),
null.label("stop_area_ref"),
null.label("locality_name"),
null.label("district_name"),
null.label("admin_area_ref"),
null.label("admin_area_name"),
db.cast(db.func.array_agg(db.distinct(AdminArea.code)),
pg.ARRAY(db.Text)).label("admin_areas"),
_tsvector_column(
(Service.line, "B"), (Service.description, "B"), (db.func.coalesce(
db.func.string_agg(db.distinct(Operator.name), " "), ""), "C"),
(db.func.string_agg(db.distinct(Locality.name), " "), "C"),
(db.func.coalesce(
db.func.string_agg(db.distinct(District.name), " "), ""), "D"),
(db.func.string_agg(db.distinct(AdminArea.name),
" "), "D")).label("vector")
]).select_from(
Service.__table__.join(
JourneyPattern, Service.id == JourneyPattern.service_ref).join(
LocalOperator,
(JourneyPattern.local_operator_ref == LocalOperator.code) &
(JourneyPattern.region_ref
== LocalOperator.region_ref)).outerjoin(
Operator, LocalOperator.operator_ref == Operator.code).
join(JourneyLink, JourneyPattern.id == JourneyLink.pattern_ref).join(
StopPoint, (JourneyLink.stop_point_ref == StopPoint.atco_code)
& StopPoint.active).join(
Locality, StopPoint.locality_ref == Locality.code).outerjoin(
District, Locality.district_ref == District.code).join(
AdminArea,
Locality.admin_area_ref == AdminArea.code)).group_by(
Service.id))
return [
region, admin_area, district, locality, stop_area, stop_point, service
]
def table_name(model):
""" Returns column with literal name of model table. """
return db.literal_column(f"'{model.__tablename__}'")