Skip to content

Open Infrastructure Map, a view of infrastructure data in OpenStreetMap

7.5 KB
Raw Blame
from typing import Any

from pydantic import BaseModel
from shapely import from_wkt
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.sql import text
from starlette.exceptions import HTTPException
from starlette.routing import Route

from .. import Request, get_db, path_param
from ..openapi import APIRoute


def extract_local_names(tags: dict[str, str]) -> dict[str, str]:
    result = {}
    for key in tags:
        if key.startswith("name:"):
            _, code = key.split(":", 1)
            result[code] = tags[key]
    return result


class Circuit(BaseModel):
    """A circuit relation"""

    id: int
    name: str | None
    local_names: dict[str, str]
    voltage: int | None
    frequency: float | None


class CircuitMembership(BaseModel):
    """Membership of a substation or line in a circuit relation"""

    circuit: Circuit
    role: str | None


class SubstationResponse(BaseModel):
    circuits: list[CircuitMembership]


async def substation(request: Request) -> SubstationResponse:
    """Fetch circuit metadata for a substation."""
    database = get_db(request)
    # Imposm uses negative IDs for OSM relations in geometry tables, while
    # relation member references retain the original positive OSM ID.
    substation_id = abs(path_param(request, "substation_id", int))
    res = await database.execute(
        text(
            """SELECT relation.osm_id AS relation_id,
            relation.tags -> 'name' AS name,
            relation.tags -> 'frequency' AS frequency,
            relation.tags,
            convert_voltage(relation.tags -> 'voltage') AS voltage,
            member.role
            FROM osm_power_circuit_relation_member member, osm_power_circuit_relation relation
            WHERE relation.osm_id = member.osm_id AND member.member = :member_id"""
        ).columns(tags=HSTORE),
        {"member_id": substation_id},
    )

    circuits = [
        CircuitMembership(
            circuit=Circuit(
                id=r._mapping["relation_id"],
                name=r._mapping["name"],
                local_names=extract_local_names(r._mapping["tags"]),
                voltage=r._mapping["voltage"] / 1000 if r._mapping["voltage"] else None,
                frequency=r._mapping["frequency"],
            ),
            role=r._mapping["role"],
        )
        for r in res
    ]

    return SubstationResponse(circuits=circuits)


class CircuitSubstation(BaseModel):
    id: int
    name: str | None
    local_names: dict[str, str]
    location: tuple[float, float] | None


class CircuitResponse(Circuit):
    length: float
    type: str
    wikidata: str | None

    substations: list[CircuitSubstation]

    geometry: dict[str, Any]  # GeoJSON


async def circuit(request: Request) -> CircuitResponse:
    """Fetch data for a circuit relation"""
    database = get_db(request)
    circuit_id = path_param(request, "circuit_id", int)
    relation = (
        await database.execute(
            text("""
                SELECT relation.osm_id AS relation_id,
                relation.tags -> 'name' AS name,
                relation.tags -> 'frequency' AS frequency,
                relation.tags -> 'type' AS type,
                relation.tags -> 'wikidata' AS wikidata,
                relation.tags,
                convert_voltage(relation.tags -> 'voltage') AS voltage,
                (SELECT json_build_object('type', 'FeatureCollection', 'features',
                    json_agg(json_build_object('type', 'Feature',
                        'geometry', ST_AsGeoJSON(ST_Transform(ST_Simplify(geometry, 1), 4326),
                                    maxdecimaldigits=>6)::json)))
                    FROM osm_power_circuit_relation_member WHERE osm_id = relation.osm_id) AS geojson,
                (SELECT sum(st_length(ST_Transform(geometry, 4326)::geography))
                    from osm_power_circuit_relation_member WHERE role = 'section' AND
                    osm_id = relation.osm_id) AS length,
                (select ST_AsText(st_envelope(st_collect(st_transform(geometry, 4326)))) from
                    osm_power_circuit_relation_member where osm_id = relation.osm_id) AS bbox

                FROM osm_power_circuit_relation relation
                WHERE relation.osm_id = :relation_id
                AND relation.tags -> 'type' IN ('power', 'route')
            """).columns(tags=HSTORE),
            {"relation_id": circuit_id},
        )
    ).fetchone()
    if not relation:
        raise HTTPException(404)

    geojson = relation._mapping["geojson"]

    bbox = from_wkt(relation._mapping["bbox"])
    geojson["bbox"] = bbox.bounds

    substations = await database.execute(
        text("""
            SELECT id, tags -> 'name' AS name, tags,
                ST_AsText(ST_Transform(ST_Centroid(geometry),4326)) AS location
            FROM osm_power_circuit_relation_member
            WHERE osm_id = :relation_id AND role = 'substation'
        """).columns(tags=HSTORE),
        {"relation_id": circuit_id},
    )

    circuit_substations = []
    for s in substations:
        location = from_wkt(s._mapping["location"])
        circuit_substations.append(
            CircuitSubstation(
                id=s._mapping["id"],
                name=s._mapping["name"],
                local_names=extract_local_names(s._mapping["tags"]),
                location=location.coords[0] if len(location.coords) > 0 else None,
            )
        )

    return CircuitResponse(
        id=relation._mapping["relation_id"],
        name=relation._mapping["name"],
        local_names=extract_local_names(relation._mapping["tags"]),
        voltage=relation._mapping["voltage"] / 1000 if relation._mapping["voltage"] else None,
        frequency=relation._mapping["frequency"],
        geometry=geojson,
        length=round((relation._mapping["length"] or 0) / 1000, 2),
        substations=circuit_substations,
        type=relation._mapping["type"],
        wikidata=relation._mapping["wikidata"],
    )


class LineResponse(BaseModel):
    circuits: list[CircuitMembership]


async def line(request: Request) -> LineResponse:
    """Fetch circuits for a power line"""
    database = get_db(request)
    line_id = path_param(request, "line_id", int)

    res = await database.execute(
        text(
            """
                SELECT relation.osm_id AS relation_id,
                    relation.tags->'name' AS name,
                    relation.tags -> 'frequency' AS frequency,
                    relation.tags AS tags,
                    convert_voltage(relation.tags -> 'voltage') AS voltage,
                    member.role
                FROM osm_power_circuit_relation_member member,
                     osm_power_circuit_relation relation
                WHERE member = :line_id
                AND member.osm_id = relation.osm_id
            """
        ).columns(tags=HSTORE),
        {"line_id": line_id},
    )

    circuits = [
        CircuitMembership(
            circuit=Circuit(
                id=r._mapping["relation_id"],
                name=r._mapping["name"],
                local_names=extract_local_names(r._mapping["tags"]),
                voltage=r._mapping["voltage"] / 1000 if r._mapping["voltage"] else None,
                frequency=r._mapping["frequency"],
            ),
            role=r._mapping["role"],
        )
        for r in res
    ]

    return LineResponse(circuits=circuits)


routes: list[Route] = [
    APIRoute("/api/substation/{substation_id}", endpoint=substation),
    APIRoute("/api/circuit/{circuit_id}", endpoint=circuit),
    APIRoute("/api/line/{line_id}", endpoint=line),
]