create view FlightRoutes (FLightID, FlightNumber, Departure, Arrival,
    Origin, Destination, Route, Distance) as
    select flight.id, flight.flightnumber,
           flight.departure, flight.arrival,
           ad.code, aa.code,
           ST_MakeLine(ad.location::geography, aa.location::geography),
           scheduledflight.distance
    from flight
    join gate as ga on flight.actualgatearrivalid = ga.id
    join gate as gd on flight.actualgatedepartureid = gd.id
    join terminal as ta on ta.id = ga.terminalid
    join terminal as td on td.id = gd.terminalid
    join airport as aa on aa.id = ta.airportid
    join airport as ad on ad.id = td.airportid
    join scheduledflight on flight.scheduleid = scheduledflight.id
    where aa.location is not null and ad.location is not null;