Given a distance-weighted directed graph, consisting of a queryCURSOR input consisting of the starting and ending node for each edge and a distance, and a specified origin node, tf_graph_shortest_paths_distances computes the shortest distance-weighted path distance between the origin_node and every other node in the graph. It returns a row for each node in the graph, with output columns consisting of the input origin_node, the given destination_node, the distance for the shortest path between the two nodes, and the number of edges or graph "hops" between the two nodes. If origin_node does not exist in the node1 column of the edge_listCURSOR, an error is returned.
SELECT * FROM TABLE(
tf_graph_shortest_paths_distances(
edge_list => CURSOR(
SELECT node1, node2, distance FROM table
),
origin_node => <origin node>
)
Input Arguments
Output Columns
Example A
/* Compute the 10 furthest destination airports as measured by average travel-time
when departing origin airport 'RDU' (Raleigh-Durham, NC) on United Airlines for the
year 2008, adding 60 minutes for each leg to account forboarding/plane change time
costs. */
SELECT
*
FROM
TABLE(
tf_graph_shortest_paths_distances(
edge_list => CURSOR(
SELECT
origin,
dest,
/* Add 60 minutes to each leg to account for boarding/plane change costs */
AVG(airtime) + 60 as avg_airtime
FROM
flights_2008
WHERE
carrier_name = 'United Air Lines'
GROUP by
origin,
dest
),
origin_node => 'RDU'
)
)
ORDER BY
distance DESC
LIMIT
10;
origin_node|destination_node|distance|num_edges_traversed
RDU|JFK|803|3
RDU|LIH|757|2
RDU|KOA|746|2
RDU|HNL|735|2
RDU|OGG|728|2
RDU|EUG|595|3
RDU|ANC|586|2
RDU|SJC|468|2
RDU|SFO|468|2
RDU|OAK|468|2
Example B
/* Compute the all-destinations path distances along a time-traversal weighted
edge graph of roads in the Eastern United States from a location in North Carolina joining to a node locations table to output the lon/lat pairs
of each destination node. */
select
destination_node,
lon,
lat
distance,
num_edges_traversed
from
table(
tf_graph_shortest_paths_distances(
cursor(
select
node1,
node2,
traversal_time
from
usa_roads_east_time
),
1561955
)
),
USA_roads_east_coords
where
destination_node = node_id
order by
distance desc
limit
20;
destination_node|lon|lat|distance|num_edges_traversed
2228153|-69.74701|46.941648|22021532|5387
324156|-69.67822799999999|46.990543|21916494|5386
324151|-69.687833|46.933106|21906798|5386
1372661|-69.64962799999999|46.942144|21830101|5385
320610|-69.47672399999999|46.967413|21807384|5379
324152|-69.637714|46.958516|21798959|5385
1372667|-69.633437|46.95189999999999|21793379|5385
1372662|-69.63483099999999|46.954334|21786119|5384
2228156|-69.622767|46.949534|21768541|5383
1372670|-69.58720599999999|46.942504|21759257|5382
1372663|-69.62387099999999|46.968569|21741445|5383
2226724|-69.557773|46.969276|21714682|5381
324159|-69.607209|46.967823|21709789|5382
324160|-69.59385999999999|46.967445|21691648|5382
2228155|-69.59575599999999|46.967461|21688053|5381
320578|-69.57176699999999|47.067628|21683322|5377
1372669|-69.58906999999999|46.977104|21675010|5382
2226740|-69.582106|46.991048|21673764|5379
320609|-69.55000199999999|46.966089|21668411|5378
324158|-69.585776|46.973521|21663260|5381
Destination node column in directed edge list CURSOR
Column<INT | BIGINT | TEXT ENCODED DICT> (must be the same type as node1)
distance
Distance between origin and destination node in directed edge list CURSOR
Column INT | BIGINT | FLOAT | DOUBLE>
origin_node
The origin node to start graph traversal from. If not a value present in edge_list.node1, will cause empty result set to be returned.
BIGINT | TEXT ENCODED DICT
origin_node
Starting node in graph traversal. Always equal to input origin_node.
Column <INT | BIGINT | TEXT ENCODED DICT> (same type as the node1 and node2 input columns)
destination_node
Final node in graph traversal. Will be equal to one of values of node2 input column.
Column <INT | BIGINT | TEXT ENCODED DICT> (same type as the node1 and node2 input columns)
distance
Cumulative distance between origin and destination node for shortest path graph traversal.
Column<INT | BIGINT | FLOAT | DOUBLE> (same type as the distance input column)
num_edges_traversed
Number of edges (or "hops") traversed in the graph to arrive at destination_node from origin_node for the shortest path graph traversal between these two nodes.