This example shows all paths from root nodes.
Input
The vertices (nodes) are bus stops in a small town. The Vertices table lists each bus stop and the boarding fare at that stop.
nodeid | nodestring | value |
---|---|---|
1 | Park St | 2.25 |
2 | Main St | 2.25 |
3 | Walnut St | 2.25 |
4 | Water St | 3.5 |
5 | High St | 2.25 |
The Edges table represents the bus route. The columns nodeid and nodestring identify the source vertices (where the bus starts) and the columns endnodeid and endnodestring identify the target vertices (where the bus stops).
nodeid | nodestring | endnodeid | endnodestring |
---|---|---|---|
1 | Park St | 3 | Walnut St |
2 | Main St | 3 | Walnut St |
3 | Walnut St | 4 | Water St |
4 | Water St | 1 | Park St |
4 | Water St | 5 | High St |
The Root table defines the set of root vertices from which the function starts traversing the graph.
nodeid | nodestring | value |
---|---|---|
1 | Park St | 2.25 |
2 | Main St | 2.25 |
SQL Call
SELECT * FROM GTree ( ON gtree_vertices AS Vertices PARTITION BY nodeid, nodestring ON gtree_edges AS Edges PARTITION BY nodeid, nodestring ON gtree_root AS Root PARTITION BY nodeid, nodestring USING TargetKey ('endnodeid', 'endnodestring') AllowCycles ('t') MaxDepth (10) OutputType ('all') Results ( 'Propagate (nodeid) AS start_vertex', 'Current (nodeid) AS end_vertex', 'Path (nodestring)', 'Sum (value1)', 'Cycle()', 'Leaf()' ) EdgeResults ('PATH(nodestring, endnodestring) AS edgepath') ) AS dt ORDER BY 1,2;
Output
The output table has one column for each function that the Results or EdgeResults syntax element specifies. The edgepath column shows the links that comprise the path, the cycle column shows whether the path is a cycle, and the sum column shows the total fare for the path (the sum of the boarding fares at each node in the path).
start_vertex end_vertex path(nodestring) sum(value1) cycle() leaf() edgepath ------------ ---------- ---------------- ----------- ------- ------ ---------------- 1 1 park st-> 2.25 false false .-park stwalnut st->. 1 3 park st->walnut st-> 4.5 false false .-park stwalnut st->.-walnut stwater st->. 1 4 park st->walnut st->water st-> 8.0 true false .-park stwalnut st->.-walnut stwater st->.-water stpark st-> 1 4 park st->walnut st->water st-> 8.0 false false .-park stwalnut st->.-walnut stwater st->.-water sthigh st->. 1 5 park st->walnut st->water st->high st 10.25 false true .-park stwalnut st->.-walnut stwater st->.-water sthigh st->. 2 1 main st->walnut st->water st->park st-> 10.25 true false .-main stwalnut st->.-walnut stwater st->.-water stpark st->.-park stwalnut st-> 2 2 main st-> 2.25 false false .-main stwalnut st->. 2 3 main st->walnut st-> 4.5 false false .-main stwalnut st->.-walnut stwater st->. 2 4 main st->walnut st->water st-> 8.0 false false .-main stwalnut st->.-walnut stwater st->.-water stpark st->. 2 4 main st->walnut st->water st-> 8.0 false false .-main stwalnut st->.-walnut stwater st->.-water sthigh st->. 2 5 main st->walnut st->water st->high st 10.25 false true .-main stwalnut st->.-walnut stwater st->.-water sthigh st->.
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.