Least distance problem

You can use recursive common table expressions to find desirable paths on a directed graph. Each row in a database table represents
a directed edge. Each row specifies an origin, a destination, and a cost of traveling from the origin to the destination.
Depending on the problem, the cost may represent distance, travel time, or some other measure. Recursion permits you to explore
possible routes through this graph. From the set of possible routes, you can then select the ones that interest you.

For example, consider the problem of finding a desirable way to drive between the cities of Kitchener and Pembroke. There
are quite a few possible routes, each of which takes you through a different set of intermediate cities. The goal is to find
the shortest routes, and to compare them to reasonable alternatives.

First, define a table to represent the edges of this graph and insert one row for each edge. Since all the edges of this graph
are bi-directional, the edges that represent the reverse directions must be inserted also. This is done by selecting the initial
set of rows, but interchanging the origin and destination. For example, one row must represent the trip from Kitchener to
Toronto, and another row the trip from Toronto back to Kitchener.

The next task is to write the recursive common table expression. Since the trip starts in Kitchener, the initial subquery
begins by selecting all the possible paths out of Kitchener, along with the distance of each.

The recursive subquery extends the paths. For each path, it adds segments that continue along from the destinations of the
previous segments, and adds the length of the new segments to maintain a running total cost of each route. For efficiency,
routes end if they meet either of the following conditions:

The path returns to the starting location.

The path returns to the previous location.

The path reaches the final destination.

In the current example, no path should return to Kitchener and all paths should end if they reach Pembroke.

When using recursive queries to explore cyclic graphs, it is important to verify that they finish properly. In this case,
the above conditions are insufficient, as a route may include an arbitrarily large number of trips back and forth between
two intermediate cities. The recursive query below guarantees an end by limiting the maximum number of segments in any given
route to seven.

Since the point of the example query is to select a practical route, the main query selects only those routes that are less
than 50 percent longer than the shortest route.