class District(db.Model):
""" NPTG district. """
__tablename__ = "district"
code = db.Column(db.VARCHAR(3), primary_key=True)
name = db.Column(db.Text, nullable=False, index=True)
admin_area_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("admin_area.code", ondelete="CASCADE"),
nullable=False, index=True
)
modified = db.deferred(db.Column(db.DateTime))
localities = db.relationship("Locality", backref="district",
order_by="Locality.name", lazy="raise")
postcodes = db.relationship("Postcode", backref="district",
order_by="Postcode.text", lazy="raise")
def __repr__(self):
return f"<District({self.code!r})>"
def list_localities(self):
""" Queries all localities containing active stops. """
query_local = (
Locality.query
.filter(Locality.district_ref == self.code,
Locality.stop_points.any(StopPoint.active))
.order_by(Locality.name)
)
return query_local.all()
class Postcode(db.Model):
""" Postcodes with coordinates, derived from the NSPL data. """
__tablename__ = "postcode"
index = db.Column(db.VARCHAR(7), primary_key=True)
text = db.Column(db.VARCHAR(8), index=True, unique=True, nullable=False)
admin_area_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("admin_area.code", ondelete="CASCADE"),
index=True
)
district_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("district.code", ondelete="CASCADE"),
index=True
)
latitude = db.Column(db.Float, nullable=False)
longitude = db.Column(db.Float, nullable=False)
easting = db.deferred(db.Column(db.Integer, nullable=False))
northing = db.deferred(db.Column(db.Integer, nullable=False))
def __repr__(self):
return f"<Postcode({self.text!r})>"
def stops_in_range(self, *options):
""" Returns a list of all stop points within range.
:param options: Options for loading model instances, eg load_only
:returns: List of StopPoint objects with distance attribute added
and sorted.
"""
return StopPoint.in_range(self.latitude, self.longitude, *options)
class JourneyLink(db.Model):
""" Stop with timing and journey info..
Each stop has the following fields:
- ATCO code for stop as foreign key
- Whether this is a timing info point (expected to be timetabled)
- Whether this is a principal point (services must stop here)
- Whether the bus stops or passes by
"""
__tablename__ = "journey_link"
id = db.Column(db.Integer, primary_key=True, autoincrement=False)
pattern_ref = db.Column(
db.Integer,
db.ForeignKey("journey_pattern.id", ondelete="CASCADE"),
nullable=False, index=True
)
stop_point_ref = db.Column(
db.VARCHAR(12),
db.ForeignKey("stop_point.atco_code", ondelete="CASCADE"),
nullable=True, index=True
)
run_time = db.Column(db.Interval, nullable=True)
wait_arrive = db.Column(db.Interval, nullable=True)
wait_leave = db.Column(db.Interval, nullable=True)
timing_point = db.Column(db.Boolean, nullable=False)
principal_point = db.Column(db.Boolean, nullable=False)
stopping = db.Column(db.Boolean, nullable=False)
sequence = db.Column(db.Integer, index=True)
__table_args__ = (
db.UniqueConstraint("pattern_ref", "sequence"),
db.CheckConstraint("run_time IS NOT NULL AND wait_arrive IS NOT NULL "
"OR wait_leave IS NOT NULL")
)
class AdminArea(db.Model):
""" NPTG administrative area. """
__tablename__ = "admin_area"
code = db.Column(db.VARCHAR(3), primary_key=True)
name = db.Column(db.Text, nullable=False, index=True)
atco_code = db.deferred(db.Column(db.VARCHAR(3), unique=True, nullable=False))
region_ref = db.Column(
db.VARCHAR(2),
db.ForeignKey("region.code", ondelete="CASCADE"),
nullable=False, index=True
)
is_live = db.deferred(db.Column(db.Boolean, default=True))
modified = db.deferred(db.Column(db.DateTime))
districts = db.relationship("District", backref="admin_area",
order_by="District.name", lazy="raise")
localities = db.relationship("Locality", backref="admin_area",
innerjoin=True, order_by="Locality.name",
lazy="raise")
postcodes = db.relationship("Postcode", backref="admin_area",
innerjoin=True, order_by="Postcode.text",
lazy="raise")
stop_points = db.relationship(
"StopPoint", backref="admin_area", innerjoin=True,
order_by="StopPoint.name, StopPoint.short_ind", lazy="raise"
)
stop_areas = db.relationship("StopArea", backref="admin_area",
innerjoin=True, order_by="StopArea.name",
lazy="raise")
def __repr__(self):
return f"<AdminArea({self.code!r})>"
def list_localities(self):
""" Queries all localities containing active stops. """
query_local = (
Locality.query
.filter(Locality.admin_area_ref == self.code,
Locality.stop_points.any(StopPoint.active))
.order_by(Locality.name)
)
return query_local.all()
class StopArea(db.Model):
""" NaPTAN stop areas, eg bus interchanges. """
__tablename__ = "stop_area"
code = db.Column(db.VARCHAR(12), primary_key=True)
name = db.Column(db.Text, nullable=False, index=True)
admin_area_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("admin_area.code", ondelete="CASCADE"),
nullable=False, index=True
)
locality_ref = db.Column(
db.VARCHAR(8),
db.ForeignKey("locality.code", ondelete="CASCADE"),
index=True
)
stop_area_type = db.Column(db.VARCHAR(4), nullable=False)
active = db.Column(db.Boolean, nullable=False, index=True)
latitude = db.Column(db.Float, nullable=False)
longitude = db.Column(db.Float, nullable=False)
easting = db.deferred(db.Column(db.Integer, nullable=False))
northing = db.deferred(db.Column(db.Integer, nullable=False))
modified = db.deferred(db.Column(db.DateTime))
# Number of stop points associated with this stop area
stop_count = db.deferred(
db.select([db.cast(db.func.count(), db.Text)])
.where((_stop_point.c.stop_area_ref == code) & _stop_point.c.active)
.scalar_subquery()
)
stop_points = db.relationship(
"StopPoint", backref="stop_area",
order_by="StopPoint.name, StopPoint.short_ind", lazy="raise"
)
def __repr__(self):
return f"<StopArea({self.code!r})>"
class Region(db.Model):
""" NPTG region. """
__tablename__ = "region"
code = db.Column(db.VARCHAR(2), primary_key=True)
name = db.Column(db.Text, nullable=False, index=True)
modified = db.deferred(db.Column(db.DateTime))
areas = db.relationship("AdminArea", backref="region", innerjoin=True,
order_by="AdminArea.name", lazy="raise")
patterns = db.relationship("JourneyPattern", backref="region",
innerjoin=True, lazy="raise")
def __repr__(self):
return f"<Region({self.code!r})>"
class Operator(db.Model):
""" Bus/metro service operator. """
__tablename__ = "operator"
SPLIT_ADDRESS = re.compile(r"\s*,\s*")
code = db.Column(db.Text, primary_key=True)
region_ref = db.Column(
db.VARCHAR(2),
db.ForeignKey("region.code", ondelete="CASCADE"),
nullable=False, index=True
)
name = db.Column(db.Text, nullable=False)
mode = db.Column(
db.Integer,
db.ForeignKey("service_mode.id"),
nullable=False, index=True
)
licence_name = db.deferred(db.Column(db.Text, nullable=True))
email = db.deferred(db.Column(db.Text), group="contacts")
address = db.deferred(db.Column(db.Text), group="contacts")
website = db.deferred(db.Column(db.Text), group="contacts")
twitter = db.deferred(db.Column(db.Text), group="contacts")
local_codes = db.relationship(
"LocalOperator",
backref=db.backref("operator", innerjoin=True, uselist=False),
order_by="LocalOperator.code",
lazy="raise"
)
patterns = db.relationship(
"JourneyPattern",
backref=db.backref("operator", innerjoin=True, viewonly=True,
uselist=False),
secondary="local_operator",
viewonly=True,
lazy="raise"
)
@property
def split_address(self):
if "address" not in db.inspect(self).unloaded:
return self.SPLIT_ADDRESS.split(self.address)
else:
return None
class JourneyPattern(db.Model):
""" Sequences of timing links. """
__tablename__ = "journey_pattern"
id = db.Column(db.Integer, primary_key=True, autoincrement=False)
origin = db.Column(db.Text)
destination = db.Column(db.Text)
service_ref = db.Column(
db.Integer,
db.ForeignKey("service.id", ondelete="CASCADE"),
nullable=False, index=True
)
local_operator_ref = db.Column(db.Text, nullable=False, index=True)
region_ref = db.Column(
db.VARCHAR(2),
db.ForeignKey("region.code", ondelete="CASCADE"),
nullable=False, index=True
)
direction = db.Column(db.Boolean, nullable=False, index=True)
date_start = db.Column(db.Date, nullable=False)
date_end = db.Column(db.Date)
__table_args__ = (
db.CheckConstraint("date_start <= date_end"),
db.ForeignKeyConstraint(
["local_operator_ref", "region_ref"],
["local_operator.code", "local_operator.region_ref"],
ondelete="CASCADE"
),
db.Index("ix_journey_pattern_local_operator_ref_region_ref",
"local_operator_ref", "region_ref")
)
links = db.relationship("JourneyLink", backref="pattern", innerjoin=True,
order_by="JourneyLink.sequence", lazy="raise")
journeys = db.relationship("Journey", backref="pattern", innerjoin=True,
lazy="raise")
class LocalOperator(db.Model):
""" Operator codes within regions for each operator. """
__tablename__ = "local_operator"
code = db.Column(db.Text, primary_key=True)
region_ref = db.Column(
db.VARCHAR(2),
db.ForeignKey("region.code", ondelete="CASCADE"),
primary_key=True, index=True
)
operator_ref = db.Column(
db.Text,
db.ForeignKey("operator.code", ondelete="CASCADE"),
index=True
)
name = db.Column(db.Text, nullable=True)
patterns = db.relationship(
"JourneyPattern",
backref=db.backref("local_operator", innerjoin=True, uselist=False,
viewonly=True),
viewonly=True,
lazy="raise"
)
class FTS(db.Model):
""" Materialized view for full text searching.
All rankings are done with weights 0.125, 0.25, 0.5 and 1.0 for ``D``
to ``A`` respectively.
"""
__tablename__ = "fts"
table_name = db.Column(db.Text, index=True, primary_key=True)
code = db.Column(db.Text, index=True, primary_key=True)
name = db.Column(db.Text, nullable=False)
indicator = db.Column(db.Text(collation="utf8_numeric"))
street = db.Column(db.Text)
stop_type = db.Column(db.Text)
stop_area_ref = db.Column(db.Text)
locality_name = db.Column(db.Text)
district_name = db.Column(db.Text)
admin_area_ref = db.Column(db.VARCHAR(3))
admin_area_name = db.Column(db.Text)
admin_areas = db.Column(pg.ARRAY(db.Text, dimensions=1), nullable=False)
vector = db.Column(pg.TSVECTOR, nullable=False)
# Unique index for table_name + code required for concurrent refresh
__table_args__ = (db.Index("ix_fts_unique",
"table_name",
"code",
unique=True),
db.Index("ix_fts_vector_gin",
"vector",
postgresql_using="gin"),
db.Index("ix_fts_areas_gin",
"admin_areas",
postgresql_using="gin"))
GROUP_NAMES = {
"area": "Areas",
"place": "Places",
"stop": "Stops",
"service": "Services"
}
GROUPS = {
"area": {"region", "admin_area", "district"},
"place": {"locality"},
"stop": {"stop_area", "stop_point"},
"service": {"service"}
}
DICTIONARY = "english"
WEIGHTS = "{0.125, 0.25, 0.5, 1.0}"
def __repr__(self):
return f"<FTS({self.table_name!r}, {self.code!r})>"
@classmethod
def match(cls, query):
""" Full text search expression with a tsquery.
:param query: Query as string.
:returns: Expression to be used in a query.
"""
dict_ = db.bindparam("dictionary", cls.DICTIONARY)
return cls.vector.op("@@")(db.func.websearch_to_tsquery(dict_, query))
@classmethod
def ts_rank(cls, query):
""" Full text search rank expression with a tsquery.
:param query: Query as string.
:returns: Expression to be used in a query.
"""
dict_ = db.bindparam("dictionary", cls.DICTIONARY)
tsquery = db.func.querytree(db.func.websearch_to_tsquery(dict_, query))
return db.func.ts_rank(db.bindparam("weights", cls.WEIGHTS),
cls.vector, db.cast(tsquery, TSQUERY), 1)
@classmethod
def _apply_filters(cls, match, groups=None, areas=None):
""" Apply filters to a search expression if they are specified.
:param match: The original query expression
:param groups: Groups, eg 'stop' or 'area'
:param areas: Administrative area codes to filter by
:returns: Query expression with added filters, if any
"""
if groups is not None:
if set(groups) - cls.GROUP_NAMES.keys():
raise ValueError(f"Groups {groups!r} contain invalid values.")
tables = []
for g in groups:
tables.extend(cls.GROUPS[g])
match = match.filter(cls.table_name.in_(tables))
if areas is not None:
match = match.filter(cls.admin_areas.overlap(areas))
return match
@classmethod
def search(cls, query, groups=None, admin_areas=None):
""" Creates an expression for searching queries, excluding stop points
within areas that already match.
:param query: web search query as string.
:param groups: Set with values 'area', 'place' or 'stop'.
:param admin_areas: List of administrative area codes to filter by.
:returns: Query expression to be executed.
"""
if not _test_query(query):
return None
fts_sa = db.aliased(cls)
rank = cls.ts_rank(query)
# Defer vector and admin areas, and order by rank, name and indicator
match = (
cls.query.options(db.defer(cls.vector), db.defer(
cls.admin_areas)).filter(
cls.match(query),
# Ignore stops whose stop areas already match
~db.exists().where(
fts_sa.match(query)
& (fts_sa.code == cls.stop_area_ref))).order_by(
db.desc(rank), cls.name, cls.indicator))
# Add filters for groups or admin area
match = cls._apply_filters(match, groups, admin_areas)
return match
@classmethod
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
class StopPoint(db.Model):
""" NaPTAN stop points, eg bus stops. """
__tablename__ = "stop_point"
atco_code = db.Column(db.VARCHAR(12), primary_key=True)
naptan_code = db.Column(db.VARCHAR(9), index=True, unique=True,
nullable=False)
name = db.Column(db.Text, nullable=False, index=True)
landmark = db.Column(db.Text)
street = db.Column(db.Text)
crossing = db.Column(db.Text)
indicator = db.Column(db.Text, default="", nullable=False)
short_ind = db.Column(
db.Text(collation="utf8_numeric"),
index=True,
default="",
nullable=False
)
locality_ref = db.Column(
db.VARCHAR(8),
db.ForeignKey("locality.code", ondelete="CASCADE"),
nullable=False, index=True
)
admin_area_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("admin_area.code", ondelete="CASCADE"),
nullable=False, index=True
)
stop_area_ref = db.Column(
db.VARCHAR(12),
db.ForeignKey("stop_area.code", ondelete="CASCADE"),
index=True
)
stop_type = db.Column(db.VARCHAR(3), nullable=False)
active = db.Column(db.Boolean, nullable=False, index=True)
bearing = db.Column(db.VARCHAR(2))
latitude = db.Column(db.Float, nullable=False, index=True)
longitude = db.Column(db.Float, nullable=False, index=True)
easting = db.deferred(db.Column(db.Integer, nullable=False))
northing = db.deferred(db.Column(db.Integer, nullable=False))
modified = db.deferred(db.Column(db.DateTime))
# Distinct list of lines serving this stop
lines = db.deferred(_array_lines(atco_code))
locality = db.relationship("Locality", uselist=False,
back_populates="stop_points", lazy="raise")
other_stops = db.relationship(
"StopPoint",
primaryjoin=(
db.foreign(stop_area_ref).isnot(None) &
(db.remote(stop_area_ref) == db.foreign(stop_area_ref)) &
(db.remote(atco_code) != db.foreign(atco_code)) &
db.remote(active)
),
uselist=True,
order_by="StopPoint.name, StopPoint.short_ind",
lazy="raise"
)
links = db.relationship("JourneyLink", backref="stop_point", lazy="raise")
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
# Declared in case it needs to be defined for stops near a point
distance = None
def __repr__(self):
if "atco_code" in self.__dict__:
repr_text = f"<StopPoint(atco_code={self.atco_code!r})>"
else:
repr_text = f"<StopPoint(naptan_code={self.naptan_code!r})>"
return repr_text
@property
def long_name(self):
if self.indicator:
return f"{self.name} ({self.indicator})"
else:
return self.name
@classmethod
def from_list(cls, list_naptan_codes):
""" Finds all stops from a list of NaPTAN codes, ordered using the
same list.
:param list_naptan_codes: List of NaPTAN/SMS codes.
:returns: Ordered list of StopPoint objects.
"""
if list_naptan_codes:
def _stop_index(stop):
return list_naptan_codes.index(stop.naptan_code)
stops = (
cls.query
.options(db.joinedload(cls.locality, innerjoin=True))
.filter(cls.naptan_code.in_(list_naptan_codes))
.all()
)
stops.sort(key=_stop_index)
else:
stops = []
return stops
@classmethod
def within_box(cls, box, *options, active_only=True):
""" Finds all stop points within a box with latitude and longitude
coordinates for each side.
:param box: BoundingBox object with north, east, south and west
attributes
:param options: Options for loading model instances, eg load_only
:param active_only: Active stops only
:returns: Unordered list of StopPoint objects
"""
query = cls.query
if options:
query = query.options(*options)
if active_only:
query = query.filter(cls.active)
try:
nearby_stops = query.filter(
db.between(cls.latitude, box.south, box.north),
db.between(cls.longitude, box.west, box.east)
)
except AttributeError:
raise TypeError(f"Box {box!r} is not a valid BoundingBox object.")
return nearby_stops.all()
@classmethod
def in_range(cls, latitude, longitude, *options, active_only=True):
""" Finds stop points in range of lat/long coordinates.
Returns an ordered list of stop points and their distances from
said coordinates.
:param latitude: Latitude of centre point
:param longitude: Longitude of centre point
:param options: Options for loading model instances, eg load_only
:param active_only: Active stops only
:returns: List of StopPoint objects with distance attribute added
and sorted.
"""
box = location.bounding_box(latitude, longitude, MAX_DIST)
nearby_stops = cls.within_box(box, *options, active_only=active_only)
stops = []
for stop in nearby_stops:
dist = location.get_distance((latitude, longitude),
(stop.latitude, stop.longitude))
if dist < MAX_DIST:
stop.distance = dist
stops.append(stop)
return sorted(stops, key=lambda s: s.distance)
def to_geojson(self):
""" Outputs stop point data in GeoJSON format.
:returns: JSON-serializable dict.
"""
geojson = {
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [self.longitude, self.latitude]
},
"properties": {
"atcoCode": self.atco_code,
"smsCode": self.naptan_code,
"title": self.long_name,
"name": self.name,
"indicator": self.short_ind,
"street": self.street,
"bearing": self.bearing,
"stopType": self.stop_type,
"locality": self.locality.name,
"adminAreaRef": self.admin_area_ref,
}
}
return geojson
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 to_full_json(self):
""" Produces full data for stop point in JSON format, including services
and locality data.
"""
services, operators = self.get_services()
json = {
"atcoCode": self.atco_code,
"smsCode": self.naptan_code,
"title": self.long_name,
"name": self.name,
"indicator": self.short_ind,
"street": self.street,
"crossing": self.crossing,
"landmark": self.landmark,
"bearing": self.bearing,
"stopType": self.stop_type,
"adminAreaRef": self.admin_area_ref,
"latitude": self.latitude,
"longitude": self.longitude,
"active": self.active,
"adminArea": {
"code": self.admin_area.code,
"name": self.admin_area.name,
},
"district": {
"code": self.locality.district.code,
"name": self.locality.district.name,
} if self.locality.district is not None else None,
"locality": {
"code": self.locality.code,
"name": self.locality.name,
},
"services": [{
"code": s.service.code,
"shortDescription": s.service.short_description,
"line": s.service.line,
"direction": "inbound" if s.direction else "outbound",
"reverse": s.direction,
"origin": s.origin,
"destination": s.destination,
"terminates": s.terminates,
"operatorCodes": list(operators)
} for s in services],
"operators": [{
"code": code, "name": name
} for code, name in operators.items()]
}
return json
class Locality(db.Model):
""" NPTG locality. """
__tablename__ = "locality"
code = db.Column(db.VARCHAR(8), primary_key=True)
name = db.Column(db.Text, nullable=False, index=True)
parent_ref = db.deferred(db.Column(db.VARCHAR(8), index=True))
admin_area_ref = db.Column(
db.VARCHAR(3),
db.ForeignKey("admin_area.code", ondelete="CASCADE"),
nullable=False, index=True
)
district_ref = db.Column(db.VARCHAR(3),
db.ForeignKey("district.code", ondelete="CASCADE"),
index=True)
latitude = db.deferred(db.Column(db.Float, nullable=False),
group="coordinates")
longitude = db.deferred(db.Column(db.Float, nullable=False),
group="coordinates")
easting = db.deferred(db.Column(db.Integer, nullable=False))
northing = db.deferred(db.Column(db.Integer, nullable=False))
modified = db.deferred(db.Column(db.DateTime))
stop_points = db.relationship(
"StopPoint", order_by="StopPoint.name, StopPoint.short_ind",
back_populates="locality", lazy="raise"
)
stop_areas = db.relationship("StopArea", backref="locality",
order_by="StopArea.name", lazy="raise")
def __repr__(self):
return f"<Locality({self.code!r})>"
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()