1. Travel planning for flight data
We will now practice recursive querying techniques on a real-world example by planning a flight route.
2. Scoreboard of an airport
In every airport, we can find a scoreboard showing all possible flights in the near future. A flight is characterized by the departure airport, the destination airport, the flight number, the corresponding times of departure and arrival, and so on.
3. How is a flight data set structured?
A common table for flights can look like this. For each flight we can find a departure airport. Consider we are starting from London. From this airport, we can fly to Paris. This flight can be identified by a flight number and has other data fields with more information such as cost of the flight and the usual travel time in hours.
4. How to build a flight route?
Let's try to figure out possible flight routes between cities. We will use recursion to find all possible connections from a departure airport to all other destination airports. There are thousands of possible flight connections all over the world, so, in order to stick to realistic flight routes, we will have to limit the number of layovers when we are creating the recursive query.
5. Building a flight route - step 1
Recall that to build the recursive CTE we have to specify an anchor and a recursive query. The table we are using is called flightPlan.
First, we create the CTE table called flightRoute.
The anchor member is defined by selecting information from the flightPlan table.
The recursive member selects the same information and makes sure the departure airport from the anchor member is the same as the arrival airport of the recursive member.
We also introduce a new field called stops to track the number of layovers. The output of this query is the collection of all possible flights starting from Vienna. Let's take it one step further and find all routes to each destination airport.
6. Building a flight route - step 2
To get all possible routes implies getting routes with a high number of layovers, which we want to avoid. To prevent this, we will introduce a field to track the routes and limit the number of stops.
We introduce a new field route in the anchor query and keep track of it for each layover in the recursive query. Finally, we also limit the number of stops in the recursion step to 5. This is achieved by using a WHERE clause in the recursive query.
7. Building a flight route - result
As we can see, the result is the route from one airport to a destination airport together with the layovers in between. For example, we can fly from London to New York by stopping in Vienna and Chicago.
8. Querying for possible flight with limits
In the following exercises, we will use the CTE on this slide combined with our knowledge on defining routes to answer several questions. On the right, we see possible questions and on the left, the definition of the query.
For example, we can select the departure airport to be New York. Or, we might want to get all possible routes with less than n stops or routes that fulfill a defined condition like limiting total costs or getting the cheapest route to some destination.
9. Let's find possible flight routes!
Have fun finding all possible flight routes from Vienna!