create view FlightAirspaceConflicts (FlightID, FlightNumber, Departure, Arrival, Origin, Destination,
    Distance, AirspaceID, AirspaceName, AirspaceType, ConflictSegment, ConflictLengthKm) as
    select fr.FlightID, fr.FlightNumber, fr.Departure, fr.Arrival, fr.Origin, fr.Destination,
        fr.Distance, ra.ID,ra.Name,at.Name,
        ST_Intersection(fr.Route, ra.Boundary::geometry),
        ST_Length(ST_Intersection(fr.Route, ra.Boundary::geometry)::geography) / 1000.0
    from FlightRoutes as fr
    join RestrictedAirspace as ra on ST_Intersects(fr.Route, ra.Boundary::geometry)
    join AirspaceType as at on at.ID = ra.TypeID
    where (ra.ActiveFrom is null or fr.Departure >= ra.ActiveFrom) and (ra.ActiveTo is null or fr.Arrival <= ra.ActiveTo);