Different Representation of my data without much changes

Question

I Have a table that represents nodes in a network and their path. So it's basically a graph.

Something like this :

              Path (tablename)
Destination        Nodes           Edges

China                13              1
China                16              2
China                1               3
Australia            13              1
Australia            234             2

So it means that, to get to China, you will have to get through node 13,16 and 1. The path is represented by the edges. So

China : 13->16->1
Australia: 13->234

Now, graph softwares like gephi, allows a possibility of easily importing this data into it's application to automatically draw the graph. But how ever, my representation is not in accordance to what they have. To build a graph in grephi i need to have a table called

Nodes : contain the list of nodes (eg 13,16,1,13)

and

Edges : contain the connection between the nodes like :

             Edges
     From               To
eg    13                 16
      16                 1

So my question is, what better way can i get to this representation from what i already have ? Does it mean creating those two tables, and if so how may i automate the process with that pattern ? Or should i extract the data then parse it to gephi graph format ?

Answer 1

It depends on what for are you using that data. If there is other usage than for gephi, it is good to leave data in the current format and extract nodes and edges with SELECT statements:

-- All nodes
SELECT DISTINCT node FROM path;

-- All edges
SELECT DISTINCT a.node from_node, b.node to_node
FROM path a, path b
WHERE a.destination = b.destination
  AND a.edge = b.edge - 1;

If you use data only for a graphs, than you can create 2 tables and fill them by INSERT statement with same SELECT's. Like:

CREATE TABLE nodes (node INTEGER PRIMARY KEY);
INSERT INTO nodes SELECT DISTINCT node FROM path;

CREATE TABLE edges (from INTEGER NOT NULL, to INTEGER NOT NULL, UNIQUE(from,to));
INSERT INTO edges SELECT DISTINCT a.node, b.node ...