| 1 | create view FlightRoutes (FLightID, FlightNumber, Departure, Arrival,
|
|---|
| 2 | Origin, Destination, Route, Distance) as
|
|---|
| 3 | select flight.id, flight.flightnumber,
|
|---|
| 4 | flight.departure, flight.arrival,
|
|---|
| 5 | ad.code, aa.code,
|
|---|
| 6 | ST_MakeLine(ad.location::geography, aa.location::geography),
|
|---|
| 7 | scheduledflight.distance
|
|---|
| 8 | from flight
|
|---|
| 9 | join gate as ga on flight.actualgatearrivalid = ga.id
|
|---|
| 10 | join gate as gd on flight.actualgatedepartureid = gd.id
|
|---|
| 11 | join terminal as ta on ta.id = ga.terminalid
|
|---|
| 12 | join terminal as td on td.id = gd.terminalid
|
|---|
| 13 | join airport as aa on aa.id = ta.airportid
|
|---|
| 14 | join airport as ad on ad.id = td.airportid
|
|---|
| 15 | join scheduledflight on flight.scheduleid = scheduledflight.id
|
|---|
| 16 | where aa.location is not null and ad.location is not null; |
|---|
