Do You Want to Build a Snowman?

I’ll be teaching a session on Spatial Data coming up this weekend at SQL Saturday, Washington DC.  In the recent past, at the end of my presentation we have a little fun and use spatial methods to render a drawing of Hillary and Donald using only T-SQL. Honestly, I’m getting rather tired of that story, and I’m ready to move on to something much more lighthearted.

So – I decided to replace that section of my presentation with a drawing of Olaf.

JPG that I started out with.
JPG that I started out with.

I used the technique created and outlined clearly by Alex Whittles in this blog post: http://www.purplefrogsystems.com/blog/2011/05/sql-server-art-using-spatial-data/

  1. Convert a lineart image to a bitmap
  2. Convert bitmap to a vector (vectormagic.com)
  3. Use Excel workbook for formatting
  4. Paste in SQL

I took the JPG above and opened it up in Adobe Photoshop and then converted and saved it as a BMP file. The result of that transformation left me with an image that looked like this (pretty much looks exactly the same…)

olaf
Bitmap Olaf

I used the vectormagic site to convert the BMP to an EPS. For some reason WordPress will not let me upload and share an EPS, so I changed the extension to TXT. I have attached that here so that you can see what it looks like. You’re going to open the EPS file in Notepad anyway in order to work with the contents.

Olaf EPS file contents: Olaf EPS File Contents

I took the contents of the EPS and dropped it into the excel workbook that Alex Whittles created. You can see how that looks here: Olaf Excel Workbook

I added an additional step of blending all of the vector lines together using the UnionAggregate spatial method. Then I combined the result with a Union of itself with a little buffering to make the drawing darker and more clear.  The resulting SQL statement that draws Olaf in SSMS is here (just rename to an .SQL extension):  Draw Olaf Using TSQL

The result should look something like this:

Spatial Olaf
Spatial Olaf

Now, as one person recently pointed out in a session I taught – “what is the business case for this?”. You know, I can’t think of one other than having fun while learning. I have used this technique to prank a few folks, emailing them a query to run that draws their own face. Nothing wrong with making learning fun if you ask me.  Happy coding.

Sources of Free Spatial Data

Some of the websites I’ve found that offer free Shapefiles. I’m going to be categorizing this further and adding to this collection over time.