NoSQL Graph DB with Ignition

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.

8 Likes

This is very cool! And kudos for sharing it back.

I haven’t dived into all your code, but a quick thing jumps out at me:

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

This could be simplified to just

def unique(values):
    return list(set(values))

Since you’re sorting it right after.

Yea, I had done that at first. When it is a list of lists you get a TypeError: unhashable type: ‘list’. I suppose I could have turned them into tuples first…