def _retrieve_nearest_vertex(db: Database, uic_ref: int) -> Optional[int]:
transaction = db.transaction()
row = db.query("""SELECT nearest_vertex_id FROM stop_vertex_mapping
WHERE stop_uic_ref = :uic_ref;""",
uic_ref=uic_ref).first()
transaction.commit()
if row:
return row['nearest_vertex_id']
return None
def optimize_stop_vertex_mapping(db: Database):
logger.info("Locate public transport stops on the routing graph")
transaction = db.transaction()
db.query("""DROP TABLE IF EXISTS edge_preselection""")
db.query("""CREATE UNLOGGED TABLE edge_preselection (
id INTEGER,
source INTEGER,
target INTEGER,
cost DOUBLE PRECISION
)""")
db.query("SELECT optimize_stop_vertex_mapping()")
db.query("DROP TABLE edge_preselection;")
transaction.commit()
def calc_isochrones(db: Database, uic_ref: int, boundaries: List[int]) -> List[Isochrone]:
logger.info(f"Calculate isochrones for {uic_ref}")
nearest_vertex_id = _retrieve_nearest_vertex(db, uic_ref)
if not nearest_vertex_id:
return list()
transaction = db.transaction()
rows = db.query("""SELECT distance, polygon FROM isochrones(:node_id, :boundaries)""",
node_id=nearest_vertex_id,
boundaries=boundaries
).all()
isochrones = _map_isochrones(rows)
transaction.commit()
return isochrones
def mark_relevant_roads(db: Database, max_relevant_distance: float):
logger.info(f"Mark nodes that are reachable in {max_relevant_distance} metres")
transaction = db.transaction()
db.query("""SELECT mark_relevant_ways(:max_relevant_distance);""", max_relevant_distance=max_relevant_distance)
transaction.commit()