tl;dr. Look for the SQL files here, https://github.com/SQLAdrian/drawingWithSSMS
The premise is quite simple, to draw something in SSMS using the spatial results tab. The easiest way to do this is to pick an image and convert it into something that SQL Server can understand.
My intent was, and still is, to provide you with some guidance on how to create your own image in SQL Server Management Studio, and I provide this further in this article. You will find this article relatively technical.
First though I want to cover how much we need to know to make something like this work. You need to know:
What spatial data is
What Well-know Text is
What the differences is between vector and raster images
How to drive a vector editing tool, like Inkscape
How to handle layers
How to edit text files
How to write SQL code
That’s quite a bit and you will have to use all that the knowledge to bring it all together in a usable way. This set of instructions that follow is not exhaustive. You will need time though. For me, from start to finish I have invested roughly 100 hours just to get to a point where I can bring a reasonable set of instruction together in a way that most SQL professionals will be able to follow.
By showing you how I get to the solution, I want you to be inspired to find a better way to do something you are already busy with. Drawing in SQL server using SSMS is relatively easy and gives you new ways to express what you are doing within the context of SQL server and SSMS.
The use case I present is quite basic, copy an image into SSMS. The solution however is not bound to replication of image. Once you have a set of elements there is nothing limiting you from moving them around, or adding conditions to what gets presented in SSMS.
Note: Background information is at the end of the article, I'm sure most of you just want to jump straight in.
OK. Let’s do this. Drawing in SQL server using SSMS
So SSMS can render simple shapes, the feature has been around for more than a decade. All we have to do is break a complex image into component parts and reassemble them in SSMS, so it’s like Lego.
Lego is easy, just ask Emmet.
Note: Here is the link to the files I used to create this, along with the SQL file to render the
image.
Step 1. Choose an image.
Pick your champion. For the purpose of explanation, and sticking to the building block theme, I have picked a simple image, Emmet, from the LEGO Movie 2.
Note: More colors and detail adds complexity to the final result, so it would be advisable to keep complex images on the bench for now.
Step 2. Convert your image to SVG format.
I use Inkscape for this, it's easy to use. There are many great sources that talk about using Inkscape to convert a raster to a vector, so detailed instructions on how to carry this out falls outside of the scope of this article.
Note: If you cannot find good conversion tools, just start with an existing SVG.
Step 3. Clean up the SVG file.
This can be a labor intensive process, but this is the most important step.
Cleaning up the SVG requires all curves to be converted to straight lines. I have been unable to reliably get Curved Polygon shapes converted from SVG to something SQL can understand, which leads to this recommendation. Converting the SVG to SQL code works best when you only have polygons with straight lines. There is also a lot of work in removing small artefacts that you might not need in the final vector.
You will be surprised by the sheer amount of points that need cleaning up.
And you end up with an image that looks clean and has few color layers. I don't really want to work with more than 10 colors.
The time you spend cleaning up the SVG is invaluable. A lot of this effort goes into reducing extra points which does results in a loss of details, but makes the image easier to work with. For me this is about finding the right balance.
Step 4. Looking inside the SVG.
You now have a well-formed SVG file containing all the objects that need to be converted.
Crack open the SVG file using your favourite text editor. You will notice that SVG files are just XML files. The <path> elements are each an object within the final SVG render.
<g> are groups, ensure that you only have 1 group as the conversion only picks up the first group element. The d attribute inside the path defines a path to be drawn. So each path contains instructions on how to draw the element. Each <path> element will become a a polygon.
Inside the SVG:
<g
<path style="fill:#b2a9a7;stroke-width:0.83110172" d="m 41.." id="path6660" />
<path
style="fill:#fbd114;stroke-width:0.83110172" d="m 327.98'''"id="path6658" />
<path .../>
<path .../>
<path .../>
</g>
Look at the following snippet:
<path d="m 414.70534,1921.9386 0.32323,-4.3687 2.27689, ..”
Let me translate the snippet below. “To draw this path move the start point to <414.70534>,<1921.9386> . Now draw a line to coordinates <0.32323>,<-4.3687>. From this point draw a line to the next set of coordinates and so on.
Tip: Find the following character “ c “, with the spaces. If you find this in the document edit the SVG to remove the curve to elements. These point to curved lines and conversions of curved lines causes havoc.
Step 5. Converting to Well-Known Text
Up to now we have working with SVG data, now we need to turn it into something that SQL server can understand.
Enter Well Known Text (WKT). This is the format that SQL uses to describe geospatial objects, like Polygons. You can convert this using a custom method, however it is preferable to standardize the conversion for the purpose of this document.
I like using MyGeoData
The conversion process is quite simple. Upload your file and step through the conversion process. After processing you would be able to download a compressed file containing a CSV file with the WKT data.
Note: If you are using your own SVG file please note that the SVG file needs to be modified before uploading. This is a requirement of the online converter we will be using. Add the bit of code between </metadata>.. and ..<defs
</metadata>
<MetaInfo xmlns="http://www.prognoz.ru"><Geo><GeoItem X="-595.30" Y="-142.88" Latitude="37.375593" Longitude="-121.977795"/><GeoItem X="1388.66" Y="622.34" Latitude="37.369930" Longitude="-121.959404"/></Geo></MetaInfo>
<defs
Tip: Once you have your image finalized in SVG format, shrink down the width to somewhere between 80%-90% of the original, while keeping the same height. 80% works well for Emmet, 87% works well for Brent The conversion processing using MyGeodata widens the end result.
Step 6. CSV to SQL.
The CSV file is not going to help much on its own, you will have to extract the data into a SQL script. Open the CSV file. You will see each row contains an object, which contains each group of colors from the SVG. So each <path .../> will convert to a line in the final CSV file.
It helps to think of each POLYGON as distinct layer in the final image. In the output, each PATH element would translate to a color grouping and handling these as distinct color layers in SSMS will greatly simplify getting to a nice drawing in SSMS.
Step 7. Coloring in and Layering.
This is ridiculously hard in SSMS. There is no way to specify a color for a layer, a color get assigned based on how many layers have been rendered before. To get the color you need requires rendering a fixed color palette and then rendering points for every layer where you do not want to assign a color.
If you want the color that is present in layer 16, you have to render points for layer 1-15 and then render your polygon during the 16th render for SSMS to match the color in the palette.
Colors are opaque, so when you have overlapping polygons the color will interfere with one another.
You can increase intensity by rendering the same object more than once in the same polygon or by redrawing the same polygon in another color.
I use a palette of around 900 colors, rendering speed is not impacted by this, and I never quite get the color I’m looking for.
What Emmet looks like as geospatial data and colored in. Each color is separate layer in the spatial data that simply gets layered on top of each other.
Note: In the SQL script you will see that I create a simple loop and evaluate each layer based on the loop counter. This is not the best way to do this, a number table is ideal, but this works well for explaining the process.
I am interested in your thoughts on this, please share them.
Background: What is spatial data?
The spatial results tab is focused on, well, viewing spatial data type results. These data types are grouped into geometry (flat plane, like a paper) and geography (ellipsoidal, like the Earth).
We encounter spatial results all the time, just open a navigation app on a smartphone. Spatial data is used in rendering the map. All those maps and all the elements they include. Spatial data is used to render the roads and features. We’re talking continents, countries, cities, streets, buildings, lakes and the everything in between. Every object in these maps are representations of an object as it would exist on a surface, in this case the Earth. All of these representation are made using geometry and geography shapes and how they are positioned on a plane.
Maps are just 2D representations of shapes on a map. The coordinates, latitude and longitude, of each item links to a point on the map.
Spatial Data Types Overview
Polygons and lines are used to create representations of our world and we can use them to create some basic images. All we must do is manipulate these geo data building blocks into some shapes.
Enter the humble vector image.
But images are images right. Right? It turns out that how we see the world can be quite complex to visually capture and then recreate digitally. There are 2 main types of images used to represent images that we encounter every day. Raster and vector graphics.
Raster image are made from little pixels and each pixel has a position and a color associated with it. JPG, PNG and GIF image formats are raster formats.
Vector images are made up of shapes, and you’ll find them lurking inside SVG, EPS, PDF and AI files. Vectors are everywhere.
Take your mobile phone for example. Your selfies and pictures are raster pictures, your app icons on your phone are normally vector.
Vectors are like maps, but instead of mapping object based on latitude and longitude you have points on a canvas.
“Vector graphics are computer graphics images that are defined in terms of 2D points, which are connected by lines and curves to form polygons and other shapes”. - Wikipedia
If vector images are lines and polygons on a fixed size canvas all we have to do is to convert a vector image’s component shapes on the canvas into an equivalent representation of those components on a map using coordinates.
SSMS offers a framework for interpreting those spatial shape and presenting them on a map. It is pretty much up to our imagination to decide what type of shapes we want SSMS to render. There is no real limit to the type of shapes that can rendered, if they fall within the limitations of the framework.
What I will be describing below is a simple framework that can be used to convert images between formats and convert them to spatial data.
There is always a price to pay. This conversion is a trade-off and is done at the loss of quality to gain functionality. Complex images are hard and can take a tremendous amount of time to clean up and sanitize before converting them to spatial data. Colous are hard to reproduce in the current versions of SSMS. All of this is a compromise, and in my opinion totally worth it.
By now you should have a good idea of what spatial data is and how we plan to convert in image to a vector so we split out to object.
This sounds a lot easier than I’m making it out to be.
- This has been reposted from the original article on LinkedIn, by Adrian Sullivan