Simple Graph Search Algorithm in SQL (PostgreSQL)

Question

I've implemented a graph of nodes in PostgreSQL (not a tree)

the structure of the table is in this format

id | node1  | node2 
--------------------
1  |   1    |   2
2  |   1    |   3
3  |   4    |   1
4  |   5    |   1
5  |   1    |   6

This shows the relationships between the node 1 and the nodes it is connected to.

My Problem

...is that i need a function or method to find a particular node path in sql.

I want to call a function like SELECT getGraphPath(startnode,targetnode) and this will display the path in any form(rows, or strings)

e.g. SELECT getGraphPath(1,18) gives:

[1]-->[3]-->[17]-->[18]
[1]-->[4]-->[10]-->[18]

or even rows:

Result  |
--------
1
3
17
18

I'd also like to know how to traverse the graph using breadth first search and depth first search.

I'm not sure how your sample output relates to your sample data. I can't see a *3 -> 17* or a *4 -> 10* in your sample data... — , Dec 03 '10 at 11:12

score 12 · Answer 1 · edited Oct 20 '12 at 15:05

12

Something like this:

with recursive graph_cte (node1, node2, start_id) 
as
( 
  select node1, node2, id as start_id
  from graphs
  where node1 = 1 -- alternatively elect the starting element using where id = xyz
  union all
  select nxt.node1, nxt.node2, prv.start_id
  from graphs nxt
    join graph_cte prv on nxt.node1 = prv.node2
)
select start_id, node1, node2
from graph_cte
order by start_id;

(requires PostgreSQL 8.4 or higher)

edited Oct 20 '12 at 15:05

Uwe L. Korn

8,080
1
30
42

answered Dec 03 '10 at 11:24

MySQL and MariaDB can do this now too. – MountainX Dec 12 '18 at 00:54

Tony · Answer 2 · 2010-11-02T12:41:31.670

6

SQL is not best suited to manipulating graphs and finding paths. You're probably better off loading the graph in to a procedural language and using the Floyd-Warshall algorithm or Johnson's algorithm to find a path between nodes.

However, if you really must use SQL then I suggest you pick up a copy of Joe Celko's SQL for Smarties which has an entire chapter devoted to graphs in SQL.

edited Nov 02 '10 at 12:41

answered Nov 02 '10 at 09:19

Tony

9,672
3
47
75

score 2 · Answer 3 · answered Jan 30 '11 at 00:36

2

You can use graph database directly to solve your problem: https://launchpad.net/oqgraph -> graph as mysql storage engine

answered Jan 30 '11 at 00:36

aris

21
1

score 0 · Answer 4 · answered Dec 30 '18 at 03:26

This is not exactly memory-optimal, but works with cyclic graphs too:

with recursive graph_cte (node1, node2, path) 
as
( 
  select node1, node2, ARRAY[node1] as path
  from graph
  where node1 = 4 -- start node
  union all
  select nxt.node1, nxt.node2, array_append(prv.path, nxt.node1)
  from graph nxt, graph_cte prv
  where nxt.node1 = prv.node2
  and nxt.node1 != ALL(prv.path)
)
select array_append(path, node2)
from graph_cte
where node2 = 6 -- goal node
limit 1;

Its based on a previously accepted answer, but keeps track of the path. It should stop searching immediately once it finds the goal node.

Simple Graph Search Algorithm in SQL (PostgreSQL)

My Problem

4 Answers4

Linked