I noticed that many of the NoSQL posts on the forum did not lead to any implementations that I could find. So I thought I would share a little of what I have done with MSSQL Graph DBs. I am also looking for some feedback on better ways of generating these visuals. Maybe a graph visualization tool is something Inductive can build for a future release?
First off, we did this all in MSSQL Server. MSSQL 2017 and forward has Hybrid NoSQL capabilities built-in for both document and graph DBs. Below is some SQL for generating simple Node and Edge tables. I use this in the following examples.
CREATE TABLE test_node (
test_node_ID INT IDENTITY(1,1) PRIMARY KEY,
label NVARCHAR(256) NOT NULL,
) AS NODE;
INSERT INTO test_node VALUES ('ABCD');
INSERT INTO test_node VALUES ('DFGH');
INSERT INTO test_node VALUES ('ZAQ1');
INSERT INTO test_node VALUES ('VFR4');
INSERT INTO test_node VALUES ('HJKL');
CREATE TABLE test_edge (
test_edge_ID INT IDENTITY(1,1) PRIMARY KEY,
CONSTRAINT NodeToNode CONNECTION (test_node TO test_node)
) AS EDGE;
INSERT INTO test_edge VALUES ((SELECT $node_id FROM test_node WHERE label = 'ABCD'),
(SELECT $node_id FROM test_node WHERE label = 'DFGH'));
INSERT INTO test_edge VALUES ((SELECT $node_id FROM test_node WHERE label = 'ABCD'),
(SELECT $node_id FROM test_node WHERE label = 'ZAQ1'));
INSERT INTO test_edge VALUES ((SELECT $node_id FROM test_node WHERE label = 'DFGH'),
(SELECT $node_id FROM test_node WHERE label = 'VFR4'));
INSERT INTO test_edge VALUES ((SELECT $node_id FROM test_node WHERE label = 'ZAQ1'),
(SELECT $node_id FROM test_node WHERE label = 'HJKL'));
INSERT INTO test_edge VALUES ((SELECT $node_id FROM test_node WHERE label = 'VFR4'),
(SELECT $node_id FROM test_node WHERE label = 'HJKL'));
The queries to pull this into ignition are as follows:
nodeSQL='SELECT test_node_ID AS ID, label FROM test_node'
Tbl=system.db.runPrepQuery(nodeSQL,[],'GraphDBName')
event.source.parent.nodeQRY=Tbl
edgeSQL='''SELECT test_node1.test_node_ID AS ID1, test_node1.label AS label1,
test_node2.test_node_ID AS ID2, test_node2.label AS label2
FROM test_node test_node1, test_edge, test_node test_node2
WHERE MATCH (test_node1-(test_edge)->test_node2)'''
Tbl=system.db.runPrepQuery(edgeSQL,[],'GraphDBName')
event.source.parent.edgeQRY=Tbl
I then wrote the following quick and dirty python code to implement Force-Directed-Graph Drawings. This is by no means optimized or elegantā¦
import random
import math
import copy
def unique(list1):
unique_list = []
# traverse for all elements
for x in list1:
# check if exists in unique_list or not
if x not in unique_list:
unique_list.append(x)
return unique_list
def main(nodes, edges, bounds, size, k, ke, Fstep):
#This script determines the location of nodes to optimize visualization of a graph
#It randomly initializes node locations according to a uniform distribtuion within the display bounds
#It then iteratively searches for an equilibrium point where attractive and repelent forces balance out
#The edges have attractive forces according to Hooke's law F=kx, where x is the euclidean distance
#The nodes have repelent forces according to Coulomb's law K=ke(q1q2/x^2). here we assume q1=q2=1. This may change in a later revision
#nodes is an Nx1 list of lists of node IDs as integers
#edges is an Mx2 lsits of lists of node IDs, from index 0 to index 1
#bounds is a 2-element list or tuple indicating the height and width of where the graph will be displayed in pixels
#size is a 2-element list or tuple indicating the height and width of the graph node displayed in pixels
#Fstep si the amoutn of force to move 1 pixel,
#The function returns an Nx2 list of node coordinates in pixels
nodes=unique(nodes)# get rid of duplicate edges. Later,we will account for multiple connections in the strength of the edge
nodes.sort()
N = len(nodes) # Number of nodes
edges=unique(edges)#get rid of duplicate edges. Later,we will account for multiple connections in the strength of the edge
edges.sort()#sort on first column
E = len(edges) # Number of edges
#Create new edges list based on node index
edges_idx=[[0,0]for i in range(E)]
for i in range(E):
edges_idx[i][0]=nodes.index(edges[i][0])
edges_idx[i][1] = nodes.index(edges[i][1])
#Shrink bounds according to size of the node. This represents where the midpoint of the node can be placed.
bounds[0]=bounds[0]-size[0]-2
bounds[1]=bounds[1]-size[1]-2
#Initialize node locations
Loc=[[0.0,0.0]for i in range(N)] #float to compute forces and distances
Locp1 = [[0.0, 0.0] for i in range(N)] # float to compute forces and distances
LocINT=[[0,0]for i in range(N)] #for pixel representation
LocINTp1 = [[0,0]for i in range(N)] #For next position
for i in range(N):
Locp1[i]=[random.uniform(1,bounds[0]),random.uniform(1,bounds[1])]
LocINT[i][0]=math.floor(Locp1[i][0])+1 #pixel equivalent. add one to facilitate while loop
LocINT[i][1] = math.floor(Locp1[i][1])+1
LocINTp1[i][0] = math.floor(Locp1[i][0]) #Next position
LocINTp1[i][1] = math.floor(Locp1[i][1])
cntr=0
LocHistory=[]#To keep track of position Changes
while LocINT!=LocINTp1 or cntr>=1000:
LocINT=copy.deepcopy(LocINTp1)
Temp=[]
for i in range(N):
Temp.append(LocINT[i][0])
Temp.append(LocINT[i][1])
LocHistory.append(Temp)
Loc = copy.deepcopy(Locp1)
#Intialize Cumulative forces on each node and temporary variables
Force=[[0.0,0.0]for i in range(N)] #float to compute forces and distances
Direction_First=[0.0,0.0]
Direction_Second = [0.0, 0.0]
r2=0.0
# attractive forces
for i in range(E):
#attractive forces
First=edges_idx[i][0]
Second=edges_idx[i][1]
Direction_First[0]=Loc[Second][0]-Loc[First][0]
Direction_First[1] = Loc[Second][1] - Loc[First][1]
Direction_Second[0] = Loc[First][0] - Loc[Second][0]
Direction_Second[1] = Loc[First][1] - Loc[Second][1]
Force[First][0] += Direction_First[0]*k
Force[First][1] += Direction_First[1]*k
Force[Second][0] += Direction_Second[0] * k
Force[Second][1] += Direction_Second[1] * k
#repellant forces
for i in range(N):
for j in range(N):
if i!=j:
Direction_First[0] = Loc[i][0] - Loc[j][0]
Direction_First[1] = Loc[i][1] - Loc[j][1]
r2=(Direction_First[0]**2+Direction_First[1]**2)
Force[i][0] += Direction_First[0] * ke/r2
Force[i][1] += Direction_First[1] * ke/r2
#update locations
for i in range(N):
Locp1[i][0]=Loc[i][0]+Force[i][0]/Fstep
#Constrain to bounds
if Locp1[i][0]>bounds[0]:
Locp1[i][0] =copy.copy(bounds[0])
elif Locp1[i][0]<1:
Locp1[i][0] =1
Locp1[i][1]=Loc[i][1]+Force[i][1]/Fstep
if Locp1[i][1]>bounds[1]:
Locp1[i][1] =copy.copy(bounds[1])
elif Locp1[i][1]<1:
Locp1[i][1] =1
LocINTp1[i][0] = math.floor(Locp1[i][0]) # Next position
LocINTp1[i][1] = math.floor(Locp1[i][1])
cntr+=1
return LocINT
I used the output of this code to place templates in a template canvas and draw connectors using the paintable canvas, as shown. As you will see, the values put in the guages are randomly generated for this demonstration:
import random
NodeTbl=event.source.parent.nodeQRY
EdgeTbl=event.source.parent.edgeQRY
nodes=[]
labels=[]
for i in range(NodeTbl.getRowCount()):
nodes.append(NodeTbl.getValueAt(i,0))
labels.append(NodeTbl.getValueAt(i,1))
edges=[]
for i in range(EdgeTbl.getRowCount()):
Temp=[]
Temp.append(EdgeTbl.getValueAt(i,0))
Temp.append(EdgeTbl.getValueAt(i,2))
edges.append(Temp)
bounds=[800,800]
size=[100,100]
k=.05
ke=10
Fstep=100
X=project.ForceDirectedGraph.main(nodes, edges, bounds, size, k, ke,Fstep)
T=event.source.parent.getComponent('Template Canvas').templates
for i in range(T.getRowCount()):
T = system.dataset.setValue(T, i, 3, X[i][0])
T = system.dataset.setValue(T, i, 4, X[i][1])
strTemp='{"Label":"'+labels[i]+'","OEE":"'+str(random.randint(1,100))+'"}'
T = system.dataset.setValue(T, i, 7, strTemp)
event.source.parent.getComponent('Template Canvas').templates=T
edges.sort()#sort on first column
E = len(edges) # Number of edges
#Create new edges list based on node index
edges_idx=[[0,0]for i in range(E)]
edges_coor=[[0,0,0,0]for i in range(E)]
for i in range(E):
edges_idx[i][0]=nodes.index(edges[i][0])
edges_idx[i][1] = nodes.index(edges[i][1])
for i in range(E):
edges_coor[i][0]=X[edges_idx[i][0]][0]
edges_coor[i][1]=X[edges_idx[i][0]][1]
edges_coor[i][2]=X[edges_idx[i][1]][0]
edges_coor[i][3]=X[edges_idx[i][1]][1]
Hdr=['X','Y']
Coordinates=system.dataset.toDataSet(Hdr,X)
event.source.parent.Nodes=Coordinates
Hdr=['X','Y','X2','Y2']
Coordinates=system.dataset.toDataSet(Hdr,edges_coor)
event.source.parent.Edges=Coordinates
The paintable canvas code looks like this:
from java.awt import Color
from java.awt.geom import GeneralPath
coordinates=event.source.parent.Edges
for i in range(coordinates.getRowCount()):
shape=GeneralPath()
shape.moveTo(coordinates.getValueAt(i, "X")+50, coordinates.getValueAt(i, "Y")+50)
shape.lineTo(coordinates.getValueAt(i, "X2")+50, coordinates.getValueAt(i, "Y2")+50)
shape.closePath()
g = event.graphics
g.draw(shape)
The output looks like this. In the future we can do other cool things like add arrows or weight the connections or change the size of the nodes, etc. etc.
The algorithm works by first randomly initializing the locations of the nodes, it then treats the nodes as charged particles and the connections as springs. It then iterates to find a solution where the forces ābalanceā within some tolerance. You will get a different result for the image every time, but it solves the problem of trying to hard code a pattern for the location of all your nodes, which can get busy and convoluted fast when there are lots of nodes.
There are lots of applications for Graph DB in manufacturing. Your machines and work centers are connected via routing steps. It makes more sense to store this relationship as a graph than a bunch of flat tables. Similarly, material and software BOMs are another application.