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 matching_groups(cls, query, admin_areas=None):
""" Finds all admin areas and table names covering matching results for
a query.
The areas and groups are sorted into two sets, to be used for
filtering.
:param query: web search query as string.
:param admin_areas: Filter by admin areas to get matching groups
:returns: A tuple with a dict of groups and a dict with
administrative area references and names
"""
if not _test_query(query):
return None
array_t = pg.array_agg(db.distinct(cls.table_name))
if admin_areas is not None:
# Filter array of tables by whether aggregated rows' admin areas
# match those already selected
array_t = array_t.filter(cls.admin_areas.overlap(admin_areas))
array_areas = pg.array((AdminArea.code, AdminArea.name))
array_a = pg.array_agg(db.distinct(array_areas))
result = (db.session.query(
array_t.label("tables"), array_a.label("areas")).select_from(
db.func.unnest(cls.admin_areas).alias("unnest_areas")).join(
AdminArea,
db.column("unnest_areas") == AdminArea.code).filter(
cls.match(query)).one())
# All data should have been aggregated into one row
tables = set(result.tables) if result.tables is not None else set()
groups = {
g: n
for g, n in cls.GROUP_NAMES.items() if tables & cls.GROUPS[g]
}
areas = dict(result.areas) if result.areas is not None else {}
return groups, areas
def get_services(self):
""" Queries and returns two datasets for services and operators at this
stoplist including the origin and destination of these services,
grouped by service ID and direction. Services are also checked for
whether they terminate at this stop or not. Operators are returned
as a dict of local operator codes and operator names.
"""
# Checks if associated link is not last in sequence
link = db.aliased(JourneyLink)
next_link = (
db.session.query(link.id)
.filter(link.pattern_ref == JourneyLink.pattern_ref,
link.sequence == JourneyLink.sequence + 1)
.as_scalar()
)
# Give service instance name in keyed tuple object
service = db.aliased(Service, name="service")
operator = pg.array((
LocalOperator.code,
db.func.coalesce(Operator.name, LocalOperator.name)
))
query_services = (
db.session.query(
service,
JourneyPattern.direction,
db.func.string_agg(JourneyPattern.origin.distinct(), ' / ')
.label("origin"),
db.func.string_agg(JourneyPattern.destination.distinct(), ' / ')
.label("destination"),
(db.func.count(next_link) == 0).label("terminates"),
pg.array_agg(db.distinct(operator)).label("operators")
)
.join(service.patterns)
.join(JourneyPattern.links)
.join(JourneyPattern.local_operator)
.outerjoin(LocalOperator.operator)
.filter(JourneyLink.stop_point_ref == self.atco_code)
.group_by(service.id, JourneyPattern.direction)
.order_by(service.line, service.description,
JourneyPattern.direction)
)
services = query_services.all()
operators = {}
for sv in services:
operators.update(sv.operators)
return services, operators
def service_graph_stops(service_id, direction):
""" Creates graph and dictionary of stops from a service using pairs of
adjacent stops in journey patterns.
:param service_id: Service ID.
:param direction: Groups journey patterns by direction - False for
outbound and True for inbound.
:returns: Graph of service and dict of stop models with ATCO codes as
keys.
"""
stop_refs = (
db.session.query(
models.JourneyPattern.id.label("pattern_id"),
models.JourneyLink.sequence.label("sequence"),
models.JourneyLink.stop_point_ref.label("stop_ref"),
db.func.count(models.Journey.id).label("journeys")
)
.select_from(models.JourneyPattern)
.join(models.JourneyPattern.links)
.join(models.JourneyPattern.journeys)
.join(models.JourneyLink.stop_point)
.filter(models.JourneyPattern.service_ref == service_id,
models.JourneyPattern.direction.is_(direction),
models.StopPoint.active)
.group_by(models.JourneyPattern.id, models.JourneyLink.sequence,
models.JourneyLink.stop_point_ref)
.subquery()
)
stop_journeys = (
db.session.query(stop_refs.c.stop_ref,
db.func.max(stop_refs.c.journeys)
.label("max_journeys"))
.group_by(stop_refs.c.stop_ref)
.subquery()
)
pairs = (
db.session.query(
stop_refs.c.stop_ref.label("current"),
stop_refs.c.sequence,
db.func.lead(stop_refs.c.stop_ref)
.over(partition_by=stop_refs.c.pattern_id,
order_by=stop_refs.c.sequence).label("next")
)
.subquery()
)
adj_stops = (
db.session.query(
models.StopPoint,
db.func.max(stop_journeys.c.max_journeys).label("journeys"),
db.func.min(pairs.c.sequence).label("sequence"),
db.func.array_remove(db.func.array_agg(db.distinct(pairs.c.next)),
None).label("next_stops")
)
.options(db.contains_eager(models.StopPoint.locality))
.join(models.StopPoint.locality)
.join(pairs, pairs.c.current == models.StopPoint.atco_code)
.join(stop_journeys, pairs.c.current == stop_journeys.c.stop_ref)
.group_by(models.StopPoint.atco_code, models.Locality.code)
)
stops = {}
ranking = {}
edges = []
for s in adj_stops.all():
stop = s.StopPoint
stops[stop.atco_code] = stop
ranking[stop.atco_code] = -s.journeys, s.sequence, stop.atco_code
edges.extend((stop.atco_code, n) for n in s.next_stops)
return Graph(edges, sort=ranking.get), stops
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
]