shortest-path

Try graph queries in 5 min.

• Using Graph Server with Database
• Using Graph Studio of Autonomous Database

Using Graph Server

Setup

Install JDK 11 on your Linux.

Download Oracle Graph Server.

Install Oracle Graph Server.

Try graph queries

Locate graph dataset files.

/<path_to_the_files>/route.csv
/<path_to_the_files>/route.json

Open JShell.

cd /opt/oracle/graph/bin
./opg-jshell

var graph = session.readGraphWithProperties("/<path_to_the_files>/route.json");

Run a shortest path query.

shortest-path.pgql

graph.queryPgql(
  " SELECT a.name AS a, b.name AS b, COUNT(e) AS path_length, SUM(e.cost) AS total_cost, ARRAY_AGG(n.name) AS nodes "
+ " FROM MATCH TOP 3 SHORTEST ( (a) (-[e]->(n))* (b) ) "
+ " WHERE a.name = 'A' AND b.name = 'H' "
).print();

+-------------------------------------------------+
| a | b | path_length | total_cost | nodes        |
+-------------------------------------------------+
| A | H | 3           | 12.0       | [B, E, H]    |
| A | H | 3           | 11.0       | [D, G, H]    |
| A | H | 4           | 15.0       | [B, C, E, H] |
+-------------------------------------------------+

Run a cheapest path query.

cheapest-path.pgql

graph.queryPgql(
  " SELECT a.name AS a, b.name AS b, COUNT(e) AS path_length, SUM(e.cost) AS total_cost, ARRAY_AGG(n.name) AS nodes "
+ " FROM MATCH TOP 3 CHEAPEST ( (a) (-[e]->(n) COST e.cost)* (b) ) "
+ " WHERE a.name = 'A' AND b.name = 'H' "
).print();

+----------------------------------------------------+
| a | b | path_length | total_cost | nodes           |
+----------------------------------------------------+
| A | H | 5           | 10.0       | [B, E, D, G, H] |
| A | H | 3           | 11.0       | [D, G, H]       |
| A | H | 3           | 12.0       | [B, E, H]       |
+----------------------------------------------------+

Exit JShell.

/exit

Using Graph Viz

Graph Viz can also return the result as a table.

SELECT a.name AS a, b.name AS b, COUNT(e) AS path_length, SUM(e.cost) AS total_cost, ARRAY_AGG(n.name) AS nodes, ARRAY_AGG(ID(e)) AS edges
FROM MATCH CHEAPEST ( (a) (-[e]->(n) COST e.cost)* (b) )
WHERE a.name = 'A' AND b.name = 'H'

However, the path detected is visualized as a dot-line only.

SELECT a, b
FROM MATCH CHEAPEST ( (a) (-[e]->(n) COST e.cost)* (b) )
WHERE a.name = 'A' AND b.name = 'H'

To show the intermediate nodes, visualize the list of edges.

SELECT *
FROM MATCH ()-[e]->()
WHERE ID(e) IN (0, 3, 8, 7, 13)

Using Graph Studio

Using Graph Studio Load data into ADW with route.csv and routesource.csv dataset files.

route.csv

SNAME,DNAME,COST
A,B,1
A,D,6
B,C,5
B,E,3
C,E,1
C,F,7
D,B,2
D,G,3
E,D,1
E,H,8
F,E,3
F,H,13
G,E,2
G,H,2

routesource.csv

NAME
A
B
C
D
E
F
G
H

Create Model with dataset files.

Generated PGQL statement.

CREATE PROPERTY GRAPH draft_1616572432113
  VERTEX TABLES (
    test01.routesource
      KEY ( name )
      PROPERTIES ( name )
  )
  EDGE TABLES (
    test01.route
      SOURCE KEY ( sname ) REFERENCES routesource
      DESTINATION KEY ( dname ) REFERENCES routesource
      PROPERTIES ( cost, dname, sname )
  )

Load graph into an in-memory representation.

The job of "Load graph into an in-memory" is proceeded successfully.

Run a shortest path query, Graph Studio return the result as a table.

%pgql-pgx
SELECT a.name AS a, b.name AS b, COUNT(e) AS path_length, SUM(e.cost) AS total_cost, ARRAY_AGG(n.name) AS nodes
FROM MATCH TOP 3 SHORTEST ((a)(-[e]->(n))*(b)) ON ROUTE01
WHERE a.name='A' AND b.name='H'

Run a cheapest path query. Graph Studio return the result as a table.

%pgql-pgx
SELECT a.name AS a, b.name AS b, COUNT(e) AS path_length, SUM(e.cost) AS total_cost, ARRAY_AGG(n.name) AS nodes, ARRAY_AGG(ID(e)) AS edges 
FROM MATCH top 5 CHEAPEST ((a) (-[e]->(n) COST e.cost)*(b)) ON ROUTE01 
WHERE a.name='A' AND b.name='H'