I have to admit that I’m really excited about presenting a session at SQLBits 8 in Brighton next week. I’ve been an avid supporter of SQLBits since the first conference that I attended (SQLBits 2), and am thoroughly looking forward to finally getting a chance to be a part of the event and presenting my own session. If you’re going, I hope to see you there!
My session is about using SSRS, SQL spatial data and DMVs to visualise SSAS OLAP cube structures and generate real-time automated cube documentation (blog post here if you want to know more…).
This shows an unusual use for spatial data, drawing diagrams instead of the usual demonstrations which are pretty much always displaying sales by region on a map etc. Whilst writing my demos, it got me thinking – why not use spatial data to draw even more complex pictures, diagrams or logos…
So, I set to work trying to write a query to draw the SQLBits logo…
The first step is to define the coordinates of the image edges. For this I found a great website designed to help you create HTML image maps (www.image-maps.com). You can upload the image then just click on every corner. It turns this into an HTML image map, which without too much work can be converted into a SQL spatial query.
I’ve made it so that each object (or letter) is one query so all 8 queries (s, q, l, b, i, t, s, and the database in the middle) are union’d together to create the entire image.
Simple letters (s, l, t & s) are a single polygon, so we can use
SELECT geometry::STPolyFromText(‘POLYGON ((x y, x y, x y))’,0) AS Drawing
Where each xy pairing is a point around the image, and the last point must be the same as the first, to create a closed polygon.
Complex letters such as q however need a multi polygon. These allow us to create one polygon for the outline, and then another to remove the hole in the middle. i.e.
SELECT geometry::STMPolyFromText(‘MULTIPOLYGON (((x y, x y, x y)),((x y, x y, x y)))’,0) AS Drawing
With each coordinate group following the same rules as above.
We end up with this
SELECT geometry::STPolyFromText(‘POLYGON ((104 -222, 173 -222, 174 -174, 171 -160, 163 -147, 150 -137, 136 -128, 123 -123, 110 -117, 82 -116, 61 -122, 41 -134, 17 -150, 6 -173, 1 -194, 0 -232, 9 -259, 21 -276, 32 -289, 52 -302, 69 -312, 88 -320, 105 -335, 110 -375, 102 -390, 84 -395, 75 -385, 76 -330, 5 -333, 7 -390, 11 -411, 25 -428, 42 -442, 67 -451, 105 -453, 126 -446, 144 -439, 162 -424, 173 -404, 180 -382, 182 -337, 178 -311, 167 -296, 153 -279, 138 -268, 89 -234, 75 -222, 71 -208, 73 -188, 88 -178, 100 -190, 105 -220, 104 -222))’,0) AS Drawing
UNION ALL
SELECT geometry::STMPolyFromText(‘MULTIPOLYGON (((324 -127, 404 -127, 405 -488, 322 -490, 322 -421, 311 -432, 291 -446, 277 -452, 259 -453, 248 -446, 239 -440, 228 -429, 221 -419, 215 -402, 215 -386, 213 -188, 216 -174, 219 -159, 226 -148, 235 -140, 245 -132, 261 -127, 278 -127, 294 -134, 306 -143, 322 -158, 324 -127)),((296 -191, 300 -186, 308 -182, 319 -188, 324 -196, 322 -384, 317 -391, 311 -395, 305 -395, 300 -395, 293 -388, 296 -191)))’,0) AS Drawing
UNION ALL
SELECT geometry::STPolyFromText(‘POLYGON ((447 -62, 532 -65, 532 -450, 447 -450, 447 -62))’,0) AS Drawing
UNION ALL
SELECT geometry::STMPolyFromText(‘MULTIPOLYGON (((991 -170, 1053 -146, 1055 -209, 1065 -201, 1072 -190, 1089 -183, 1108 -181, 1122 -191, 1134 -199, 1139 -217, 1140 -386, 1133 -399, 1129 -408, 1116 -418, 1104 -422, 1090 -419, 1078 -413, 1073 -405, 1066 -397, 1055 -386, 1054 -405, 991 -381, 991 -170)),((1053 -233, 1057 -226, 1067 -224, 1078 -235, 1078 -366, 1074 -373, 1063 -375, 1054 -367, 1053 -233)))’,0) AS Drawing
UNION ALL
SELECT geometry::STMPolyFromText(‘MULTIPOLYGON (((1159 -199, 1226 -198, 1227 -431, 1160 -428, 1159 -199)),((1161 -121, 1227 -111, 1228 -162, 1162 -169, 1161 -121)))’,0) AS Drawing
UNION ALL
SELECT geometry::STPolyFromText(‘POLYGON ((1260 -132, 1322 -133, 1324 -183, 1348 -184, 1350 -227, 1323 -227, 1323 -378, 1354 -377, 1354 -421, 1297 -433, 1283 -432, 1274 -426, 1267 -420, 1260 -407, 1261 -224, 1243 -225, 1241 -179, 1260 -181, 1260 -132))’,0) AS Drawing
UNION ALL
SELECT geometry::STPolyFromText(‘POLYGON ((1445 -259, 1447 -233, 1445 -228, 1438 -224, 1427 -225, 1424 -236, 1426 -252, 1435 -266, 1451 -275, 1465 -286, 1479 -294, 1491 -307, 1499 -319, 1498 -341, 1493 -354, 1485 -369, 1476 -382, 1459 -393, 1440 -401, 1421 -404, 1404 -404, 1393 -398, 1379 -386, 1376 -370, 1373 -364, 1373 -334, 1423 -330, 1424 -359, 1432 -366, 1440 -364, 1448 -358, 1449 -340, 1447 -328, 1440 -319, 1426 -314, 1416 -307, 1406 -300, 1393 -294, 1385 -283, 1379 -270, 1376 -258, 1371 -245, 1371 -232, 1375 -219, 1382 -204, 1390 -189, 1405 -182, 1428 -182, 1442 -192, 1458 -201, 1473 -214, 1489 -231, 1494 -260, 1445 -259))’,0) AS Drawing
UNION ALL
SELECT geometry::STMPolyFromText(‘MULTIPOLYGON (((579 -40, 589 -29, 602 -22, 621 -15, 639 -13, 656 -9, 676 -7, 698 -4, 722 -2, 749 -1, 853 -0, 886 -4, 915 -7, 937 -12, 967 -16, 984 -25, 1000 -32, 1006 -59, 999 -61, 986 -65, 976 -75, 970 -88, 968 -102, 971 -121, 956 -127, 945 -135, 931 -149, 921 -166, 921 -183, 928 -199, 939 -209, 945 -216, 937 -224, 927 -234, 918 -246, 915 -260, 915 -278, 923 -293, 928 -308, 944 -317, 936 -328, 927 -341, 924 -354, 923 -374, 933 -389, 943 -400, 957 -404, 968 -407, 967 -420, 967 -437, 976 -449, 988 -459, 1008 -467, 1000 -476, 991 -483, 971 -492, 957 -494, 943 -500, 926 -503, 906 -507, 888 -507, 709 -508, 692 -506, 674 -505, 656 -501, 642 -498, 624 -496, 606 -491, 591 -485, 577 -473, 579 -40)), ‘
+ ‘((579 -136, 591 -144, 606 -150, 623 -154, 641 -159, 664 -163, 684 -165, 702 -169, 732 -170, 758 -171, 845 -173, 873 -170, 925 -162, 922 -172, 901 -177, 862 -183, 818 -186, 759 -185, 714 -183, 681 -182, 647 -174, 613 -168, 588 -161, 580 -151, 579 -136)),’
+ ‘((578 -246, 593 -257, 613 -265, 636 -271, 664 -276, 694 -277, 724 -281, 789 -283, 833 -283, 873 -281, 916 -273, 919 -285, 884 -293, 840 -295, 809 -299, 768 -299, 731 -298, 703 -295, 672 -293, 647 -289, 624 -281, 605 -276, 593 -271, 580 -262, 579 -262, 578 -246)),’
+ ‘((578 -360, 593 -369, 615 -377, 635 -382, 664 -388, 689 -390, 716 -394, 751 -395, 857 -394, 881 -391, 905 -389, 932 -383, 939 -392, 917 -399, 880 -405, 839 -409, 786 -411, 739 -411, 701 -409, 667 -405, 635 -399, 611 -392, 591 -383, 580 -377, 578 -360)))’,0) AS Drawing
Which, when we run it in SQL 2008 Management Studio, returns the results as
When you run a query which includes a spatial data type as a column, SSMS gives us a new tab, ‘Spatial results’. Clicking on this gives us a visual representation of the spatial results.
Note that I’ve had some trouble viewing multi polygons in SQL 2008 Management Studio, and can only get them to work in R2. Basic polygons seem to be fine in SQL 2008 though.
We can put this directly in a SQL Server Reporting Services map component (set to planar coordinates) and see it in a report.
Frog-Blog out
Great post, never knew could get something like this so easily.
Excellent post!!!
Are you available for family portraits 😉
Absolutely! I reckon that could be the next Purple Frog division 🙂
In fact I’ll be posting a new blog later this week with a step by step guide to doing portraits… Only line sketches at the moment, and not photo realistic, but hey, it’s SQL Server not Photoshop!
Hey! It’s great to see other SQL “Artists”
Here’s my own self portrait
http://michaeljswart.com/2010/05/sql-self-portrait/
And another one of a famous image.
http://michaeljswart.com/2010/02/more-images-from-the-spatial-results-tab/
I like to focus on the colours that are available (even though they use a terrible pallet)
Good job!
Great work Michael, that’s awesome!
Good to see there’s more timewasters out there apart from me and Alistair 🙂
Dear Alex,
How are you? I hope fine!
Could you help me understand how you convert the HTML code into SQL spatial query?
“It turns this into an HTML image map, which without too much work can be converted into a SQL spatial query.”
Thank you my friend!
Pedro
Hi Pedro, great to hear from you – hope all is well over in Portugal?
Good question, I jumped a bit in the post.
Using the image-maps.com tool you can easily trace the outline of an image into the html image map, which will have a structure similar to the following (for a simple triangle)
shape=”poly” coords=”582,161,742,158,661,271″
The coords property is a set of x,y coordinates. In this case 3 sets, each of which indicates the position of a corner. i.e. x,y, x,y, x,y
The format of a SQL spatial polygon is very similar; x y, x y, x y (the same, just without the comma between the x & y)
SELECT geometry::STPolyFromText(‘POLYGON ((x y, x y, x y))’,0) AS Drawing
So you just map each x,y from the html image map coords into the x y of the spatial query.
The only thing we need to change is that we need to close off the object, by ending up at the same position as we started. So we just need to copy the first xy coordinate to the end.
So you end up with
SELECT geometry::STPolyFromText(‘POLYGON ((582 161, 742 158, 661 271, 582 161))’,0) AS Drawing
Which gives you a nice simple triangle.
Hope this makes more sense!
Al
Dear Alex,
I made as you suggest and it works. But there is a problema… the polygon are well designed with SQL Spatial query, but in the wrong position in the map SQL viewer…
DO you imagine what could be?
thank you!
Pedro
Hi Pedro
To move the polygon you just need to add or subtract a fixed value from every x or y coordinate.
E.g. Take 10 away from every x value to shift the object to the left.
Or, if you’re in SSRS maps, make sure you’re using geometry and not a geography map, otherwise it will try and plot your polygon in the sea off the coast of Nigeria 🙂
Great Article and really helpful. I tried drawing a few shapes using the image map tool and got them into SQL – I’ve got multiple shapes I’m unioning together and it looks like the overall set is mirrored horizontally – In the imagemap tool the x axis runs along the top and in sql the x axis runs along the bottom, if that makes sense? To make this work I simply made the y coordinate (second of each pair of coordinates) the inverse – putting a minus in front of it. Is there a better approach?
Hi Paul
Each tool you use can determine its own origin, whether top left, bottom left, top right, etc. And all shapes will be relative to that. If you switch to a tool that uses a different origin then yes you’re right, you would need to invert/flip the x or y component, or add/subtract a set value to each co-ordinate to shift the image around on the canvas.
This is a perfectly valid approach, so if it works, then I’d stick to what you’re doing.
Alex
Dears,
I have question, i need to get the image that created from spatial result.
ex: i need to send mail with the (ex. “PIE” chart) from sql statement…
Is this Possible to save image or send email
I don’t believe that it is possible directly from SSMS or within SQL.
You could however do this using Reporting Services.
Alternatively if you’re in SQL 2016 then there are ways of saving a R plot to a file.
This is really amazing. May I know why the color of each character is different. How did you manage that to change the colors?
Hi Shubham
The colours are purely SSMS’s way of highlighting the different geospatial data points. There’s nothing in the code that handles colour, if you use a different query tool it would show differently.
Alex