Creating a network planning using hierarchical queries

Assume we have a table of locations like

Name                                      Null?    Type
----------------------------------------- -------- -----------------
ID                                        NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(50)

and a table of possible lines (or route parts) like

Name                                      Null?    Type
----------------------------------------- -------- --------
ID                                        NOT NULL NUMBER
FROM_LOC_ID                               NOT NULL NUMBER
TO_LOC_ID                                 NOT NULL NUMBER
DISTANCE                                  NOT NULL NUMBER

How can we calculate the best route (in this case: the route with the shortest distance) from a startpoint to an endpoint?
The answer is: By using an hierarchical query:

select dep_name
,      arr_name
,      route
,      distance
,      execute(distance) total
from
(
select connect_by_root dep.id dep_id
, connect_by_root dep.name dep_name
, arr.id arr_id
, arr.name arr_name
, level stops
, substr(sys_connect_by_path(dep.name||'-'||arr.name, '->'),3) route
, substr(sys_connect_by_path(lne.id, ','),2) lne_ids
, substr(sys_connect_by_path(lne.distance, '+'),2) distance
from lines lne
, locations dep
, locations arr
where lne.from_loc_id = dep.id
and lne.to_loc_id = arr.id
connect by nocycle prior arr.id = dep.id
)
where dep_name = 'MAASTRICHT'
and arr_name='AMSTERDAM'
ORDER BY 5 ASC

(Remark: The function 'execute' does nothing but returning the result of an execute immediate of a string value).
The outcome of this query is a list of all defined routes from Maastricht to Amsterdam with the shortest route shown first. The

"substr(sys_connect_by_path(dep.name||'-'||arr.name, '->'),3) route"

column returns the route like

"MAASTRICHT-EINDHOVEN->EINDHOVEN-UTRECHT->UTRECHT-AMSTERDAM"

The column

"substr(sys_connect_by_path(lne.distance, '+'),2)"

returns a string that represents the distances to sum:

"86,7+93,6+44,7"

This column is used for calculating the total distance.

So you can use hierarchical queries for a lot more purposes than the obvious who-is-the-manager query.
Ofcourse you won't use this kind of solution to calculate distances (there are far better webservices available for that). In 'real life' we've used this approach (but more elaborated and a lot more difficult) to get the cheapest supplier for the movement of goods from a to b.