SQL Server Spatial: let's draw something

Now it is the second post in my blog. In this post I will show you steps to draw a picture in SQL Server by utilizing its spatial function. The idea is coming from here but will be a bit different.

Firstly we need to find a source picturem, such as below one

 



Now we need to convert it to eps. I found this website provide quite good function to do so. Simply upload the image, select the output format, click button, save the result, then done.

My next step is to open the file in text editor, and copy all contents into Excel. As mentioned, Excel is quite good to do some batch jobs in this case.

So starts from here, we need to do some batch jobs.

Firstly, we can see there are quite a lot indicators in the file, such as /m { moveto } bd, /l { lineto } bd, etc. We only need to handle moveto, lineto, and curveto to draw the image. So use Ctrl+H in Excel to replace all "/l" to "/lineto", "/m" to "/moveto", and "/c" to "/curveto".

Next in column B I extract last few characters from column A(=TRIM(RIGHT(TRIM(A2), 7))) , because through Excel filter, we can remove rows we dont need. I name this column as direction, because it tells me what I need to do:

• moveto means jump to another point, I am not going to handle it;
• lineto means draw a LINESTRING
• curveto means a CIRCULARSTRING

Then in column C I use replace function to get points (=TRIM(REPLACE(A2, FIND(B2, A2, 1), LEN(B2), ""))). This column is named as position



So now we have a file with all point information and direction information.

Finally we can use excel function to generate statements, such as
select geometry::Parse('CIRCULARSTRING(158.356 181, 152.733 183.330, 155 189)')


One extra step you might want to take, is to optimize the spatial statements. In my case, the Santa image contains more than 3800 lines, after I combine them and order them, excution speed is much faster.

Try it and enjoy the image :P