Wednesday, December 9, 2015

DataBase Driven Blender: Creating Objects in Blender from DB Data

Introduction

Currently I am working in a project team installing a particle accelerator. Like with every other complex machinery this involves a large amount of cabling works to connect all kinds of devices with all kinds of cables and cable types. These cable types can roughly be grouped according to their function: power cables, Ethernet cables, interlock cables, etc. Quite often these different groups of cables are laid by different teams or even different companies along the same cable paths in a short period of time. Hence, it is crucial that these teams work coordinately and according to a transparen plan so they do not interfere with each other during their works.
As I am a big fan of 3D graphics I wanted to create 3D visualizations of the cable paths assigned to the various cable types and because I am a big fan of Blender, I wanted to create the required images there. The output of my efforts look like this:


What you see here is a look into the "cave" (a room created by large concrete blocks shielding radiation) where the accelerator will be installed. The 3D-Model data of the concrete blocks and the cable support structures is coming from CATIA data provided by our mechanical engineering deprtments - another article of mine describes the export from CATIA and import into Blender. The grating-like structures carried by orange support structures are cable trays where the cables are to be laid, the colored tube structures are the cable paths for the different able groups. The dimension along the horizontal axis is roughly 20m.

Environment

Initially, the data on cable trays and routes came from 2D Autocad drawings provided by engineers. Based on these drawings I created a MS SQL Server database model holding data on cable routes, including the x,y,z node coordinates of the cable paths and the definition of path segments which can be added to a end-to-end cable path from start device to an end device.
The database model has the following structure:


This database structure can be accessed via a view combining the data:

SELECT        dbo.CableRoutePathIDs.RoutePathID, dbo.CableRoutePaths.SegmentSortOrder, dbo.CableRoutePaths.RouteSegmentID, dbo.CableRoutePathIDs.RoutePathUser,                        dbo.CableRoutePathIDs.Description, dbo.CableRouteSegments.Start_NodeID,
                         dbo.CableRouteNodes.GsiQuadrantX * 720 + dbo.CableRouteNodes.DistToGsiQuadrantX_cm AS StartX,
                         dbo.CableRouteNodes.GsiQuadrantY * 720 + dbo.CableRouteNodes.DistToGsiQuadrantY_cm AS StartY, dbo.CableRouteNodes.HeightOverFloor_cm AS StartZ,
                         dbo.CableRouteSegments.End_NodeID, CableRouteNodes_1.GsiQuadrantX * 720 + CableRouteNodes_1.DistToGsiQuadrantX_cm AS EndX,
                         CableRouteNodes_1.GsiQuadrantY * 720 + CableRouteNodes_1.DistToGsiQuadrantY_cm AS EndY, CableRouteNodes_1.HeightOverFloor_cm AS EndZ,
                         dbo.CableRouteNodes.GSICoordX_m AS StartX2, dbo.CableRouteNodes.GSICoordY_m AS StartY2, CableRouteNodes_1.GSICoordX_m AS EndX2,
                         CableRouteNodes_1.GSICoordY_m AS EndY2
FROM            dbo.CableRouteNodes INNER JOIN
                         dbo.CableRouteSegments ON dbo.CableRouteNodes.NodeID = dbo.CableRouteSegments.Start_NodeID INNER JOIN
                         dbo.CableRouteNodes AS CableRouteNodes_1 ON dbo.CableRouteSegments.End_NodeID = CableRouteNodes_1.NodeID RIGHT OUTER JOIN
                         dbo.CableRoutePathIDs INNER JOIN
                         dbo.CableRoutePaths ON dbo.CableRoutePathIDs.RoutePathID = dbo.CableRoutePaths.RoutePathID ON
                         dbo.CableRouteSegments.PathSegmentID = dbo.CableRoutePaths.RouteSegmentID
Last but not least I needed a python script accessing the database and creating the tube-shaped objects along the paths given by the definitions of the cable paths. The view provides the following data:

  • [0] RoutePathID: the identifier of the cable route path (1 per cable group)
  • [1] SegmentSortOrder: the cable follows the path along a certain chain of path segments
  • [2] RouteSegmentID: identifier of current path segment
  • [3] RoutePathUser: as described above, each of the path is assigned to different cable groups which are under the supervision of a team
  • [4] Description: long text description of the path segment
  • [5] StartNodeID: Start node of path segment
  • [6,7;10,11] Start/End GSIQuadrantX/Y: the accelerator facility area is segmented in to squares of 720 x 720 cm providing a coordinate system with specific 0-point
  • [9] EndNodeID: End node of path segment
  • [8;12] Start/End HeightOverFloor_cm: z-coordinate
  • [13-16]Start/End  GSICoordX/Y_m: absolute distance to 0-point in x-direction


Blender Script

One challenge I had to overcome during the development of the script was the access to the python module PYODBC which provides an easy to use API to access databases, among which the MS SQL Server. I documented the solution I found - and which works pretty well in my case - in another blog post.
The Blender script implementation looks as follows:
import sys
pyodbcPath = 'E:\\Progs\\Anaconda\\envs\\python342\\Lib\\site-packages'
systemPaths = sys.path
checkResult = [s for s in systemPaths if pyodbcPath in s]
#check if path to pyodbc exists in sys and add if needed
if (checkResult == []):
    sys.path.append(pyodbcPath)
   
import pyodbc
import bpy
from mathutils import Vector
import bmesh
def createBevelObject(parentID):
    bpy.ops.curve.primitive_nurbs_circle_add(radius=0.05,layers=(False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,True))
    bevelObj = bpy.context.selected_objects[0]
    return (bevelObj)
dbConnection = pyodbc.connect('DRIVER={SQL Server};SERVER=<DBSERVERNAME>;DATABASE=>DBNAME>;UID=<DBUSERNAME>;PWD=<DBPASSWORD>')
dbCursor = dbConnection.cursor()
queryStatement = "SELECT * FROM dbo.CablesPathsDefinitionsWithOwners ORDER BY RoutePathID, SegmentSortOrder"
splineCounter = 0
oldPathID = ""
vertexList = []
scaleFactor = 100.0
outText = ""
bevelObject = createBevelObject(oldPathID)
for dbRow in dbCursor.execute(queryStatement):
    currentPathID = dbRow[0]
    if (oldPathID != currentPathID):
        if (len(vertexList) != 0): #vertexList must not be empty
            # Finalize curve creation with given list of vertices
            bpy.context.scene.objects.link(objectData)
            polyline = curveData.splines.new('POLY')
            polyline.points.add(len(vertexList)-1)
            for vertexCounter in range(len(vertexList)):
                x,y,z = vertexList[vertexCounter]
                outText = outText + oldPathID + ";" + str(vertexCounter) + ";x:" + str(x) + ";y:" + str(y) + ";z:" + str(z) + ";" + "\n"
                polyline.points[vertexCounter].co = (x,y,z,1)          
        vertexList = []
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # if oldPath != newPath create new curve object and fill with vertices afterwards
            # Prepare curve creation
            curveData = bpy.data.curves.new(currentPathID, type='CURVE')
            curveData.dimensions = '3D'          
            #curveData.bevel_depth = 1 / scaleFactor
            # Create new curve
            objectData = bpy.data.objects.new(currentPathID, curveData)
            curveData.bevel_object = bevelObject
            objectData.location = (0,0,0)
            oldPathID = currentPathID
            # add coordinates to vertexList
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            vertexList.append(Vector((x,y,z)))
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)          
            vertexList.append(Vector((x,y,z)))
    else:
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # add coordinates to vertexList - coordinates are not neccesarily well aligned in polyline
            # First set of coordinates from DB
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList): # check if entry already exists in list
                vertexList.append(theVector)
            # Second set of coordinates from DB
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList):
                vertexList.append(theVector)
dbConnection.close()
This is more or less it. Despite the fact that the xyz-coordinate data needs to be very correct and aligned with the other 3D model data coming from CATIA (following the s**t-in s**t-out paradigm), the script works pretty well and creates the desired bundles of Bezier curves in blender beveled with corresponding objects.

I hope you this post helps you with your own project, keep on geekin'!
Your WolfiG