Inspired by Itzik Ben Gan’s Geekiest Sig Challenge and Michael Cole’s virtual Christmas Card. I came up with a query that forces SQL Server’s spatial results tab to display something pretty. In recognition of Valentine’s Day, I chose a detail of Botticelli’s Birth of Venus.
The Query
The query isn’t too large, but it’s 120 KB, you can download it here:
The Results
Not quite as pretty as the original
The Process
- First reduce the color depth to a given color pallet (SQL Server’s icky pastels). I personally use a custom technique for this.
- Use a program like Adobe Illustrator to turn into an svg file.
- From there use your C# and VI savvy to convert into a SQL query.
Cheers guys
Michael, this is simply amazing! That’s way beyond what I expected when posting the challenge. Kudos!
BTW, did you need to do anything special to get the graphical results in SSMS? When I run your query and look at the Spatial results tab it just waits for the graphical depiction forever. Works fine with mine and with Will’s queries. Any clue?
Comment by Itzik Ben-Gan — February 19, 2010 @ 11:24 am
That is pretty cool
Does is really run in o seconds on your box? tales me 7 seconds on mine
Comment by SQLDenis — February 19, 2010 @ 2:39 pm
Hey! Thanks for the feedback!
It does take 0 seconds on my box and I didn’t have to do anything special with the query I provided in the post. Even retesting just now: 0 seconds.
I could try digging into finding the reasons for the discrepancy between running times on my computer and others. For some reason (maybe cause it’s Friday) but the curiosity and the motivation just aren’t there.
One thing I did learn in this experiment: Is that overlapping areas of polygons in the same geometrycollection show up darker. Which explains Microsoft’s motivation for starting out with pastel colors.
Comment by Michael J. Swart — February 19, 2010 @ 3:07 pm
Yes, it is also 0 seconds when it run it again since it is probably cached
Comment by SQLDenis — February 19, 2010 @ 3:19 pm
Of course you’re right Denis, I get two seconds after a server restart. Afterwards, switching to the spatial results tab shows the image immediately.
Comment by Michael J. Swart — February 19, 2010 @ 3:26 pm
[…] More images from the Spatial Results tab – Who said working with SQL Server is not art….. […]
Pingback by Something for the Weekend: SQL Server Links for the week | John Sansom - SQL Server DBA in the UK — February 21, 2010 @ 6:00 am
Wow, this is absolutely amazing! Who woulda thunk: Creation of art using SQL Server! Brilliant!
Comment by Colin — February 25, 2010 @ 8:13 am
Sorry, but you’ve got me curious now. How in the world did you get the detail in the picture the way you did?!? Itzik Ben-Gan said that he sat there for a good few hours drawing his picture, so I can’t imagine that you did the same manually?
Comment by Colin — February 25, 2010 @ 8:31 am
Nothing here was manual.
The hardest part was to get an svg image with the right colors.
For example, take a look at this one. http://upload.wikimedia.org/wikipedia/commons/c/cf/Flag_of_Canada.svg
I mean open it up in a text editor. Once you realize paths are polygons the rest is easy:
Comment by Michael J. Swart — February 25, 2010 @ 10:42 am
Actually the image in the last comment was cheating a bit. After I realized that shape colors are displayed with a certain amount of transparency. I realized I could get more colors by overlapping polygons. The image in the last comment is composed of only 6 multi-polygons. That took some C#, but again, nothing manual.
Comment by Michael J. Swart — February 25, 2010 @ 10:45 am
Ah Ok, brilliant! Thanks for sharing Michael 😀
Comment by Colin — February 26, 2010 @ 12:39 am
[…] what I wanted to say, some people are just talented: check out this submission by Michael J. Swart! No further words […]
Pingback by A Developer's Blog · Drawing In SQL Server 2008 — February 26, 2010 @ 4:18 pm
Bob Beauchemin explains why the query doesn’t display properly when using a 64-bit edition SQL Server. (See first comment above)
Comment by Michael J. Swart — March 1, 2010 @ 9:03 am
VERY very cool, thanks for sharing. Absolutely amazing.
Comment by Jorge Segarra — March 5, 2010 @ 3:14 pm
[…] of story to tell in the session, and I will be opening this session with the beautiful script of Botticelli’s Birth of Venus created by Michael J. […]
Pingback by SQL SERVER – What is Spatial Database? – Developing with SQL Server Spatial and Deep Dive into Spatial Indexing « Journey to SQL Authority with Pinal Dave — April 13, 2010 @ 9:31 pm
[…] kicked off this session with Michael J Swart‘s beautiful spatial image. This session was the last one for the day but, to my surprise, I […]
Pingback by SQLAuthority News – TechEd India – April 12-14, 2010 Bangalore – An Unforgettable Experience – An Oppertunity of Lifetime « Journey to SQL Authority with Pinal Dave — April 29, 2010 @ 9:32 pm
I am in awe of this. It is wonderful.
Comment by Phil Factor — July 13, 2010 @ 2:52 pm
Thanks Phil… It’s one of my favorite aesthetic things I’ve produced with a computer.
Comment by Michael J. Swart — July 16, 2010 @ 3:36 pm
[…] database presentation, I always start with his script where he has drawn the wonderful image of Botticelli’s Birth of Venus. I often think he is more of a creative artist than IT professional. However, if you read his blog […]
Pingback by SQLAuthority News – Pinal Dave: Blogger, MVP and now Interviewee by Michael J Swart « Journey to SQLAuthority — February 28, 2011 @ 8:31 pm
This is really awesome… a real and live example to Spatial geometry.
@Michael, you really are an artist first, then a techie.
Comment by Manoj Pandey — February 28, 2011 @ 9:14 pm
Thanks Manoj, That’s a big compliment.
Comment by Michael J. Swart — March 1, 2011 @ 9:10 am
This is truely Speechless !!!
Simply superb !!
Comment by Dattatrey Sindol (Datta) — May 10, 2011 @ 1:47 pm
Hey Michael,
This is awesome and I would really like to apply this technique, but whilst I have a fair bit of experience in SQL and can draw using vector programs, etc – I have been struggling to shed light on the 3rd bullet in your process above: “From there use your C# and VI savvy to convert into a SQL query.”
Are you able to point me in the right direction where I could find out about this part of the process? The only possible thing that I have found involved converting the vector graphic to EPS format and using some hacky techniques in Excel to generate linestrings, but really for my purposes I need polygons and I am wondering if there is a better way than using Excel.
Once again, this is marvellous stuff! Thanks in advance for your time.
Comment by Dan — August 15, 2011 @ 7:45 am
Hi Dan, When I talk about C# and vi savvy. That’s just the text editing and substitution skills that I’ve built up over the years, I threw away the program after using it, so I don’t have it. Sorry.
But I remember it was slightly less hacky then the Excel method you mentioned.
The thing to recognize is that if you crack open an svg file and look at it in a text editor, you’ll see that it’s made up of line segments:
For example, here is a square in svg format:
and here is the same square as a sql query:
SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION( POLYGON((0 0, 100 0, 100 100, 0 100, 0 0)) )', 0)I don’t think I know how to explain getting from one to the other. It’s just a programming exercise I guess.
Comment by Michael J. Swart — August 15, 2011 @ 8:32 am
In every human there is an artist hidden. You have unleashed the artist in you using SQL Server.
Comment by Kashif — October 29, 2011 @ 10:27 pm
Thanks Kashif,
I can’t take credit for the content though… The image is a detail of Sandro Botticelli’s The Birth of Venus.
I would call this creative, but maybe not art. (I do try to create art when I can though, but that doesn’t often make it on my blog)
Comment by Michael J. Swart — October 30, 2011 @ 9:51 am
Its really wonderful. Thanks for sharing the possibilities of sql. 🙂
Comment by haryik — April 10, 2012 @ 12:30 am
[…] J Swart posted a SQL query on his blog that produced a rendition of Botticelli’s Birth of Venus, in the spatial results tab of SQL […]
Pingback by Trouble seeing “Birth of Venus” image in the SSMS spatial results tab? | Bob Beauchemin — January 4, 2013 @ 3:03 am
Can I just say WOW!!!!
Comment by Wrqnhrd — April 3, 2013 @ 7:13 pm
[…] A segunda parte foi baseada aqui. […]
Pingback by SSRS – RDL maps – converter jpg em reports | SQLPT — August 5, 2014 @ 7:06 am
[…] Here’s a great example by Michael J Swart: https://michaeljswart.com/2010/02/more-images-from-the-spatial-results-tab/ […]
Pingback by SQL Server Spatial Art | Clint Huijbers' Blog — September 10, 2014 @ 11:13 am
[…] You have my permission to skip this post. This one’s just for me. So I’ve been drawing again with SQL Server’s spatial results tab, the first time I posted something was with Botticelli’s Birth of Venus in More images from the Spatial Results Tab. […]
Pingback by Drawing (again) with SQL Server | Michael J. Swart — May 5, 2017 @ 10:41 am
Awesome, Michael. This is not just mere esoterica: it is yet another important avenue to pursue for a retired database professional!
Comment by Terry Grignon — May 8, 2017 @ 9:09 am
[…] The spatial results tab can be used to create art. […]
Pingback by Hidden Features of SQL Server | IZZIDB — January 1, 2021 @ 7:22 am