# tf\_graph\_shortest\_paths\_distances

Given a distance-weighted directed graph, consisting of a query`CURSOR` 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_list` `CURSOR`, 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**

<table><thead><tr><th width="268">Parameter</th><th>Description</th><th>Data Types</th></tr></thead><tbody><tr><td><code>node1</code></td><td>Origin node column in directed edge list CURSOR</td><td>Column&#x3C;INT | BIGINT | TEXT ENCODED DICT></td></tr><tr><td><code>node2</code></td><td>Destination node column in directed edge list CURSOR</td><td>Column&#x3C;INT | BIGINT | TEXT ENCODED DICT> (must be the same type as <code>node1</code>)</td></tr><tr><td><code>distance</code></td><td>Distance between origin and destination node in directed edge list CURSOR</td><td>Column INT | BIGINT | FLOAT | DOUBLE></td></tr><tr><td><code>origin_node</code></td><td>The origin node to start graph traversal from. If not a value present in <code>edge_list.node1</code>, will cause empty result set to be returned.</td><td>BIGINT | TEXT ENCODED DICT</td></tr></tbody></table>

**Output Columns**

| Name                  | Description                                                                                                                                                          | Data Types                                                                                        |
| --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------- |
| `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. | Column \<INT>                                                                                     |

**Example A**

<pre><code>/* 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. */

<strong>SELECT
</strong>  *
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
</code></pre>

**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
```

<figure><img src="https://875484548-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FgWRc88gdQeZ7mRBB46Rx%2Fuploads%2Fgit-blob-5ca153482a6ad3c5fc36879051ca8412fb8c621d%2Fall_paths_distance.png?alt=media" alt=""><figcaption><p>Rendered chart of the output of tf_graph_shortest_paths_distances along an Eastern US time-traversal weighted edge graph. The shortest travel destinations are rendered in blue, and the furthest travel destinations in yellow.</p></figcaption></figure>