SQL Server Graph Database of US Capitals

SQL Server 2017 introduced graph database functionality. Graphs are a core concept in computer science and I was excited to hear of this new feature.

Robert Sheldon has a great series on SQL Server’s graph database here. If you’re interested in learning about graphs in SQL Server, it’s a great place to start.

While there’s countless relational databases out there for practice, there’s not much in the way of graph databases. It is my intent to share my graph databases with the world in hopes that it removes the friction associated with your learning.

US Capitals is a popular data set for working with graphs. Nodes identify a state capital. An edge connects a capital in one state with the capital of a neighboring state. Only the lower 48 states are present. While the data is readily available, I was unable to find TSQL scripts to create the graph using SQL Server 2017 graph database. I created those scripts and have made them readily available on GitHub.

Create a SQL Server database and execute these scripts in order:

1_CreateGraphNode_Capitals.sql

2_CreateGraphEdge_Distances.sql

3_InsertNode_Capitals.sql

4_InsertEdge_Distances.sql

There are two sets of INSERT statements in order to create a bi-directional graph. Without both edges, the graph could only be traversed in one direction.

Once you have a usable graph database, you can begin programming against it. In my next post, I’ll go over my PowerShell implementation of Dijkstra’s algorithm, which is a classic shortest path algorithm.

-Cheers

3 thoughts on “SQL Server Graph Database of US Capitals”

Leave a Reply to Oded Dror Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.