StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I researched the leads in Cade Roux's answer and developed a solution using GraphViz.</p> <p>To understand GraphViz, first I read this <a href="http://www.linuxjournal.com/article/7275" rel="noreferrer">introductory article</a> and the <a href="http://www.graphviz.org/content/command-line-invocation" rel="noreferrer">Command-line Invocation</a> documentation. After successfully generating graphs from the example code listing in the article, I felt confident to work with my own data.</p> <p>As Cade suggested, the best way to learn GraphViz's DOT language is to write it out myself. I studied the article's examples (Listings 1, 2, and 6) and then came up with this <code>venues.gv</code> to describe my own data:</p> <pre><code>digraph Venues { N1[label = "Scotland"]; N2[label = "Glasgow"]; N3[label = "Edinburgh"]; N4[label = "St Andrews"]; N5[label = "The Barrowlands"]; N6[label = "The Cathouse"]; N7[label = "Carling Academy"]; N8[label = "SECC"]; N9[label = "King Tut's Wah-Wah Hut"]; N10[label = "Henry's Cellar Bar"]; N11[label = "The Bongo Club"]; N12[label = "Sneaky Pete's"]; N13[label = "The Picture House"]; N14[label = "Potterrow"]; N15[label = "Aikman's"]; N16[label = "The Union"]; N17[label = "Castle Sands"]; N1 -> N2; N1 -> N3; N1 -> N4; N2 -> N5; N2 -> N6; N2 -> N7; N2 -> N8; N2 -> N9; N3 -> N10; N3 -> N11; N3 -> N12; N3 -> N13; N3 -> N14; N4 -> N15; N4 -> N16; N4 -> N17; } </code></pre> <p>I fed this to <code>circo</code>, just one of the many graph-drawing commands that are part of GraphViz, and got pleasing output:</p> <p>Output of <code>circo -Tpng venues.gv -o venues.png</code>:</p> <p><img src="https://i.stack.imgur.com/fsQ1z.png" alt="Visualization of hierarchical venue data"></p> <p>The GraphViz file is structured in two blocks. One block declares a label for each node, and the other block declares the edges of the graph.</p> <p>To provide the data for each of these blocks, I created a view of <code>NodeHierarchy</code>.</p> <p>This view provides the data to declare labels for nodes:</p> <pre><code>CREATE VIEW NodeLabels ( Node, Label ) AS SELECT PK_NodeID AS Node, Name AS Label FROM NodeHierarchy; </code></pre> <p>This view provides the data to declare edges between nodes:</p> <pre><code>CREATE VIEW Edges ( Parent, Child ) AS SELECT FK_ParentNodeID AS Parent, PK_NodeID AS Child FROM NodeHierarchy WHERE FK_ParentNodeID IS NOT NULL; </code></pre> <p>This Powershell script called <code>generate-graph.ps1</code> selects the data from the views, transforms it into a GraphViz input, and pipes it to <code>circo</code> to produce a visualization of the full hierarchy like the one above:</p> <pre><code>"digraph Venues {" + ( Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.NodeLabels" | ForEach-Object {"N" + $_.Node + "[label = """ + $_.Label + """];"} ) + ( Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.Edges" | ForEach-Object {"N" + $_.Parent + " -> N" + $_.Child + ";"} ) + "}" | circo -Tpng -o venues.png </code></pre> <p>The script must be run in <code>sqlps</code> instead of <code>powershell</code> so that the <code>Invoke-Sqlcmd</code> cmdlet is available. The default working directory of <code>sqlps</code> is <code>SQLSERVER</code>, so I have to specify the drive when I run the script through <code>sqlps</code>.</p> <p>This is the command I use to generate a graph like the one above:</p> <pre><code>sqlps C:.\generate-graph.ps1 </code></pre> <p>This outputs a file called <code>venues.png</code> in the C working directory.</p> <p>This Powershell solution feels a little inelegant, but this does what I need it to do. A more experienced Powershell programmer might be able to come up with something cleaner.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload