More Drawing Fun with SQL Server… this time in Technicolor

It seems I’m not the only one with a penchant for expressing my artistic side with SQL Server. Earlier today, Alex Whittles posted a screenshot to twitter showing his version of the SQLBits logo rendered in SQL Server Management Studio, rivalling that of the image from my blog post here.

Here’s the original logo:

image

Here’s Alex’s render:

image

And here’s my render :

image

This prompted Simon Sabin to ask whether it would be possible to recreate the correct colours for the image. The Spatial Results tab doesn’t let you dictate the colours in which features are rendered – it’s only meant to be a simplistic visualiser tool, so it assigns unique colours to each element from a fixed colour palette. However, there are a few tricks you can use….

Firstly, colours are assigned to each element in a fixed sequential order. Compare Alex’s and my images above – although each letter is coloured differently, the image itself is comprised of the same colour palette – the reason why the individual letters differ is because they’ve been selected in a different order in each of our result sets. The first result from the output plotted on the spatial results tab is always coloured light blue, the next orange, etc. etc.

To determine the sequence in which colours are assigned to items in the Spatial Results tab, you can run the following query:

DECLARE @SSMSColourPalette table (id int, geom geometry)
 DECLARE @x int = 0, @y int = 0;
 WHILE @y < 10
 BEGIN
 WHILE @x < 10
 BEGIN
 INSERT INTO @SSMSColourPalette VALUES(
 @y*10 + @x,
 'POLYGON((' + cast(@x AS varchar(32)) + ' ' + cast(@y AS varchar(32)) + ','
 + cast(@x + 1 AS varchar(32)) + ' ' + cast(@y AS varchar(32)) + ','
 + cast(@x + 1 AS varchar(32)) + ' ' + cast(@y + 1 AS varchar(32)) + ','
 + cast(@x AS varchar(32)) + ' ' + cast(@y + 1 AS varchar(32)) + ','
 + cast(@x AS varchar(32)) + ' ' + cast(@y AS varchar(32)) + '))'
 )
 SET @x = @x + 1;
 END
 SET @x = 0;
 SET @y = @y + 1;
 END

SELECT * FROM @SSMSColourPalette ORDER BY id;

image

The second useful piece of information is that each colour in the palette is a semi-transparent pastel shade. It is therefore possible to create new regions of colour by overlapping two shapes, as shown in the following example:

DECLARE @j geometry = 'POLYGON((0 0, 2 0, 2 10, 0 10, 0 0))';
 DECLARE @k geometry = 'POLYGON((-2 4, 4 4, 4 6,-2 6, -2 4))';

SELECT @j
 UNION ALL SELECT @k;

image

Now, to get the SQLBits logo coloured correctly, I simply had to scan through the table of colours in the palette shown above to determine at which position in the results each letter had to be placed. I wanted all the letters to be the same orange colour, so I turned them into a MultiGeometry. This MultiGeometry needed to be the second row in the output (box 1 in the chart above). The logo is a kind of bluey-grey, similar to that in box 3.

To get the logo and letters assigned the correct colours, I needed to create a single “dummy” row that would be selected first in the output, which would be coloured blue, and that would ensure the other elements were assigned the correct colours.

So, here’s my revised, colour-corrected code:

DECLARE @SqlBitsLogo table (
 id int,
 geom geometry
 );

INSERT INTO @SqlBitsLogo(id, geom) VALUES
 (1, 'POLYGON ((0 0, 6 0, 6 0.00000001, 0 0.00000001, 0 0))'),
 (2, 'GEOMETRYCOLLECTION(
 POLYGON ((1.5197357088327408 0.45103654265403748, 1.7697358727455139 0.45103654265403748, 1.7697358727455139 1.5482594966888428, 1.5197357088327408 1.5482594966888428, 1.5180402845144272 1.5360659956932068, 1.5217091739177704 1.4768347442150116, 1.51995949447155 1.4646955132484436, 1.5150224268436432 1.4557437002658844, 1.5058467984199524 1.45103719830513, 1.4811682403087616 1.4896928369998932, 1.4522375613451004 1.5198442041873932, 1.4200041890144348 1.5412336587905884, 1.3853972405195236 1.553549200296402, 1.3493593484163284 1.55653315782547, 1.3128128200769424 1.549887090921402, 1.2767071127891541 1.5333397686481476, 1.2419577389955521 1.5065927803516388, 1.2030444145202637 1.4375416338443756, 1.1787049323320389 1.3284378349781036, 1.1669522672891617 1.1933059096336365, 1.1658264994621277 1.0461568236351013, 1.1733541786670685 0.90100157260894775, 1.1875482499599457 0.77185109257698059, 1.2064488232135773 0.67275705933570862, 1.2280688434839249 0.61770331859588623, 1.2661480307579041 0.58193668723106384, 1.3070212602615356 0.56404659152030945, 1.3490541726350784 0.56046587228775024, 1.390625923871994 0.56758663058280945, 1.430108904838562 0.58184173703193665, 1.4658687561750412 0.59965047240257263, 1.5197357088327408 0.63159221410751343, 1.5197357088327408 0.45103654265403748), (1.4919579029083252 0.7288145124912262, 1.4592837244272232 0.732449471950531, 1.4385521113872528 0.746419757604599, 1.4274708330631256 0.76875865459442139, 1.423734113574028 0.79749944806098938, 1.4250565469264984 0.83067545294761658, 1.4364023208618164 0.93714797496795654, 1.4371686428785324 0.99443966150283813, 1.4344898760318756 1.1754564940929413, 1.4347001165151596 1.2337926030158997, 1.436849907040596 1.288602203130722, 1.4417734146118164 1.3383798003196716, 1.4502912163734436 1.381592720746994, 1.478069007396698 1.381592720746994, 1.5010318011045456 1.3590503633022308, 1.5156938135623932 1.3285463452339172, 1.5236622989177704 1.2915590107440948, 1.5265648663043976 1.2495939135551453, 1.5260019898414612 1.2041701674461365, 1.5197357088327408 1.0621480643749237, 1.5239267796278 0.921604335308075, 1.5243065655231476 0.87578725814819336, 1.5223805606365204 0.83243870735168457, 1.5170705020427704 0.79271155595779419, 1.5072845220565796 0.75778588652610779, 1.4919579029083252 0.7288145124912262)),
 POLYGON ((3.9264891371130943 0.65814930200576782, 3.9641817584633827 0.65937000513076782, 3.9894503280520439 0.722561776638031, 4.0106634497642517 0.81659667193889618, 4.0255153477191925 0.92991869151592255, 4.0316459983587265 1.0509853959083557, 4.0267360508441925 1.1682407855987549, 4.0084661841392517 1.2701288759708405, 3.9745034873485565 1.3450936675071716, 3.9225150644779205 1.3815927058458328, 3.8932588621973991 1.3841155022382736, 3.8680445402860641 1.3793818801641464, 3.8460583090782166 1.3693992346525192, 3.8264727890491486 1.3562291860580444, 3.7912487015128136 1.3283971399068832, 3.7739825248718262 1.3178177028894424, 3.7558482885360718 1.3121482133865356, 3.7540036663413048 1.3346091657876968, 3.7580455541610718 1.4358597844839096, 3.7563501298427582 1.45848348736763, 3.7513588070869446 1.4777977466583252, 3.7419593930244446 1.4927038997411728, 3.72126168012619 1.480415478348732, 3.699031300842762 1.4696597158908844, 3.6047387048602104 1.4319942146539688, 3.561403714120388 1.4093704968690872, 3.561403714120388 0.77048119902610779, 3.5887746140360832 0.76486594974994659, 3.614124558866024 0.75722977519035339, 3.7067624256014824 0.717923104763031, 3.7558482885360718 0.70103670656681061, 3.7576793432235718 0.74259489774703979, 3.7599444314837456 0.749010369181633, 3.7638235539197922 0.75381180644035339, 3.769737184047699 0.7565922886133194, 3.7813338711857796 0.7317306250333786, 3.7963756620883942 0.71031405031681061, 3.8149574846029282 0.69242395460605621, 3.837160736322403 0.678168848156929, 3.8630939126014709 0.667643666267395, 3.8928383961319923 0.660929799079895, 3.9264891371130943 0.65814930200576782), (3.8114038780331612 0.79825899004936218, 3.777454748749733 0.82671494781970978, 3.7575572729110718 0.87358999252319336, 3.7487817704677582 0.93381138145923615, 3.748212106525898 1.0022657364606857, 3.7529321610927582 1.07388037443161, 3.7666176110506058 1.2061368674039841, 3.769737184047699 1.2565926313400269, 3.8114038780331612 1.2565926313400269, 3.8187823593616486 1.2070320397615433, 3.8241534531116486 1.1508254110813141, 3.827476479113102 1.0901835560798645, 3.8286700621247292 1.0273444205522537, 3.827720619738102 0.96450529992580414, 3.824560359120369 0.903904139995575, 3.8191350027918816 0.84775175154209137, 3.8114038780331612 0.79825899004936218)),
 POLYGON ((2.1586250129029949 1.74270405720262, 2.1586250129029949 0.56214772313574857, 1.8947359499939289 0.56214772313574857, 1.8947359499939289 1.74270405720262, 2.1586250129029949 1.74270405720262)),
 POLYGON ((0.53362394130506574 1.256592625528026, 0.54649558607792259 1.167115027460021, 0.59734468645035721 1.0975755935817939, 0.6703088730210458 1.04135539591742, 0.74951221301166493 0.99186263324496993, 0.81909233702089124 0.942478377588519, 0.86318009395890094 0.88658370097214068, 0.86590633273587059 0.81757323879690813, 0.81140190226197673 0.7288144997098952, 0.78751001367674245 0.73442973778783271, 0.771450975308921 0.747898171148683, 0.761739597377029 0.76768713819146284, 0.75687034503408313 0.79233179420018884, 0.75534446512160014 0.82031304095087765, 0.75584631007059455 0.90937017433188727, 0.54751283935291128 0.90937017433188727, 0.56005896307777181 0.74035693353676679, 0.62850654510751058 0.6291779322690032, 0.73253764669635379 0.57230669250863564, 0.85183432909852708 0.56620317285870347, 0.96607187187975674 0.607340895299246, 1.054939117982769 0.69217981843330256, 1.098118128661788 0.81717990086391246, 1.0752909651710421 0.978814664571115, 1.0425082829624071 1.03503486223549, 0.99362587225495025 1.07995676685899, 0.87767934397173963 1.155111438815154, 0.8256570114888182 1.1949877671947109, 0.78762530238124118 1.2428122344961789, 0.77110510919542485 1.303413402931505, 0.78362410616628564 1.3815927079586361, 0.80066648936659612 1.387845424755566, 0.81592528849142643 1.3848614818156, 0.82910210924677985 1.3741328506087189, 0.83988499396165994 1.3571515026049079, 0.84797554834207 1.33540940927415, 0.85307537809401335 1.310412105463427, 0.85487252554649329 1.283637999265725, 0.85306859640551336 1.256592625528026, 1.0752909651710421 1.256592625528026, 1.077142366131522 1.39021901573054, 1.0283616807515641 1.4861663446274731, 0.94432977854899847 1.5456688795258109, 0.840427529041654 1.569974451109541, 0.73202902005885939 1.5603037633086489, 0.63450833942994389 1.51791821018412, 0.56324635667273637 1.4440520590429411, 0.53362394130506574 1.339926013815099, 0.53362394130506574 1.256592625528026)),
 POLYGON ((4.3808487005582828 1.5343705864849371, 4.3881593607612013 1.4875769358354569, 4.3823135452742656 1.44418769281294, 4.3688451119134157 1.4039722800083869, 4.3532879184945887 1.366727246766801, 4.3411758228337236 1.3322084523021851, 4.3380426827467584 1.3001717558285411, 4.3494223560496321 1.2704137066908721, 4.3808487005582828 1.24270372748018, 4.3844565588402427 1.1647143097310479, 4.3833036717952547 1.0819641466549681, 4.3771458986373233 0.9126118214348512, 4.3773629126693212 0.83123155943575622, 4.3832494182872557 0.75552078902259812, 4.3974367106290977 0.68809724195634792, 4.4225153947018194 0.63159221337497629, 4.4559626823834471 0.62939494630100079, 4.4869278720741024 0.62967977721799762, 4.5160755692467784 0.63178210065297424, 4.59950390117285 0.64209026717285955, 4.6586266615151937 0.64548111142282183, 4.6586266615151937 0.78437009190127738, 4.6455380027103388 0.78696069690824855, 4.633005442362478 0.78479055658827268, 4.5998022954668478 0.76978946162643946, 4.5905249455989514 0.7686772647124519, 4.5823191025140417 0.77286834820540529, 4.57529327322812 0.78437009190127738, 4.57529327322812 1.24270372748018, 4.6447377634673481 1.24270372748018, 4.6447377634673481 1.353814911862945, 4.62116461424161 1.359755670988879, 4.6028133651608139 1.370904766882755, 4.5891821712759659 1.386787481349578, 4.5798234411460692 1.4069426595713539, 4.5742353298221321 1.4308684565990879, 4.5719295557321571 1.4580901542377851, 4.5724449640581506 1.488119470915451, 4.57529327322812 1.5204816884370911, 4.5559112074953356 1.5271548699210169, 4.5341691141645777 1.5314408970529689, 4.51052814805384 1.5338416147819429, 4.3808487005582828 1.5343705864849371)),
 POLYGON ((5.1169602970940966 0.9510368684754239, 5.0957743022203319 0.99469737903793842, 5.0587327196337437 1.030436877432541, 5.0130512658982518 1.061442757254196, 4.9659863477087756 1.090875285343869, 4.9247672450062341 1.1219354186735231, 4.8966503644855459 1.1577834240841249, 4.8888514227106334 1.201620258547637, 4.9086268263764126 1.256592625528026, 4.9263541601152161 1.253486612195061, 4.93946994567407 1.2457690506821459, 4.9487337321649667 1.2341994901012749, 4.9549186320768994 1.219537479564438, 4.9587841945218551 1.202583258314627, 4.9641824185677956 1.1454814411452621, 5.0033805780973593 1.14648513104325, 5.0400423861279524 1.144952469442267, 5.0735032371865794 1.14020528749232, 5.1030713990462511 1.1315925430974161, 5.0897114727013992 1.2302932375253179, 5.0404221606839474 1.3039559380114989, 4.9686447696007461 1.3507495886609791, 4.8877799159276449 1.3688566969557781, 4.811255342894496 1.356432643623916, 4.7524987937311494 1.311646372770414, 4.724924448290456 1.2326803918772919, 4.7419600498022669 1.117703645049571, 4.7661842411239972 1.075806373497036, 4.8039582460685768 1.0408942410994251, 4.893096759711586 0.98118825554608857, 4.9309385815411648 0.95095548821342479, 4.9552848432558942 0.91681646830480445, 4.9593538563558486 0.87608564717425741, 4.9364046224721054 0.826036786044814, 4.9150016135663419 0.82373101195483966, 4.8996885609335123 0.82751519413779751, 4.8894889014296261 0.83641276944969856, 4.8834260719106926 0.84944717474655362, 4.8805099458557262 0.86562828350737375, 4.8797639601207354 0.88397953258816964, 4.8808490302807224 0.92325907237973281, 4.7558489478501116 0.92325907237973281, 4.7449575561192336 0.90173399308097213, 4.7397085292202918 0.87695370330224776, 4.7390710505012983 0.85016603372754562, 4.7420549934412657 0.82259168828685225, 4.7476159780112033 0.7954378075331543, 4.754777441067124 0.76995222215043768, 4.769737845897958 0.7288144997098952, 4.8295387750902927 0.7085236877181208, 4.8869525499306539 0.70513284346815852, 4.9408127199970551 0.71629550273903442, 4.9899935249985079 0.73969232806377416, 5.0333420778900262 0.77301754535240363, 5.069705491626622 0.8139518171379484, 5.0979715692943079 0.86017580595343435, 5.1169602970940966 0.90937017433188727, 5.1169602970940966 0.9510368684754239)),
 MULTIPOLYGON (((4.0752929449081421 1.4232594072818756, 4.0961127281188965 1.4302174150943756, 4.1195095516741276 1.4345848262310028, 4.1449544467031956 1.4369177222251892, 4.283626414835453 1.4371483027935028, 4.283626414835453 1.5899261832237244, 4.2537056058645248 1.5903330743312836, 4.1988959982991219 1.5861148685216904, 4.1484944894909859 1.5774749964475632, 4.0752929449081421 1.5621483772993088, 4.0752929449081421 1.4232594072818756)), ((4.2697375155985355 0.61770331859588623, 4.2697375155985355 1.3399260193109512, 4.2419190295040607 1.3417028188705444, 4.2171116136014462 1.3404549807310104, 4.1279188469052315 1.3254945874214172, 4.1031114310026169 1.3242603093385696, 4.0752929449081421 1.3260371088981628, 4.0752929449081421 0.63159221410751343, 4.1284478195011616 0.632650151848793, 4.1788086369633675 0.630941167473793, 4.2260227538645267 0.62607191503047943, 4.2697375155985355 0.61770331859588623)))
 )'),
 (3, 'POLYGON ((2.8591598570346832 0.3754885196685791, 3.1508267223834991 0.3754885196685791, 3.2155782878398895 0.38712590932846069, 3.4238168299198151 0.40805420279502869, 3.4879716038703918 0.42027479410171509, 3.5445579886436462 0.44010445475578308, 3.5905649662017822 0.47045928239822388, 3.623049259185791 0.51437750458717346, 3.5910261273384094 0.52054885029792786, 3.5636823773384094 0.53139954805374146, 3.54100438952446 0.54690247774124146, 3.5230057835578918 0.56709834933280945, 3.5097001194953918 0.59198716282844543, 3.5011009275913239 0.62158244848251343, 3.4972082376480103 0.65589779615402222, 3.4980491697788239 0.69493317604064941, 3.4486920535564423 0.7033696174621582, 3.4130882024765015 0.726888507604599, 3.3901931941509247 0.76169213652610779, 3.3789084851741791 0.80400985479354858, 3.3781760632991791 0.85005751252174377, 3.3869380056858063 0.89605093002319336, 3.4040956795215607 0.93819233775138855, 3.4286046922206879 0.97271114587783813, 3.3928787708282471 0.99838662147521973, 3.3689800798892975 1.0328104794025421, 3.3558507263660431 1.0734056532382965, 3.3524327576160431 1.1175951361656189, 3.3576682209968567 1.1627611815929413, 3.3704991936683655 1.2063267529010773, 3.3898676931858063 1.2456876635551453, 3.4147157967090607 1.2782669067382813, 3.3866938650608063 1.3136537671089172, 3.3713129758834839 1.3545202016830444, 3.3677458167076111 1.3980315327644348, 3.3751649856567383 1.4413529336452484, 3.3927431106567383 1.4816497266292572, 3.4196257293224335 1.516087144613266, 3.4550125896930695 1.541830450296402, 3.4980491697788239 1.55604487657547, 3.4980491697788239 1.6671560406684875, 3.509252518415451 1.6906613707542419, 3.5280784964561462 1.7065712213516235, 3.5758351385593414 1.7282590568065643, 3.59843173623085 1.7403847277164459, 3.6159420609474182 1.7575830817222595, 3.6252058446407318 1.7830415368080139, 3.623049259185791 1.8199339210987091, 3.5871334373950958 1.8517264723777771, 3.5420487821102142 1.8743501901626587, 3.4898297786712646 1.8898395597934723, 3.4324838221073151 1.9002019762992859, 3.2500428259372711 1.9208861291408539, 3.1924934387207031 1.9310451149940491, 2.7619443535804749 1.9310451149940491, 2.7079282104969025 1.9190822243690491, 2.6486494839191437 1.9123954772949219, 2.5259280502796173 1.9031723737716675, 2.4683040380477905 1.8948308825492859, 2.4170480370521545 1.8801146149635315, 2.3750693798065186 1.8561210036277771, 2.3452774286270142 1.8199339210987091, 2.3968182504177094 1.8037663698196411, 2.4516278505325317 1.7908675968647003, 2.5088992118835449 1.7804238200187683, 2.7462583184242249 1.7469629645347595, 2.8036042749881744 1.7366005480289459, 2.7423927485942841 1.7278657257556915, 2.6787669658660889 1.7254785597324371, 2.6140086054801941 1.7284896373748779, 2.5493791103363037 1.7359766364097595, 2.4861398637294769 1.7470172047615051, 2.4255726039409637 1.7606755197048187, 2.3689319491386414 1.7760292887687683, 2.3174996376037598 1.7921561300754547, 2.3174996376037598 1.5004892647266388, 2.3500992059707642 1.4827347993850708, 2.3853775560855865 1.4676659107208252, 2.4230430424213409 1.4549841284751892, 2.4628040790557861 1.4444047212600708, 2.5043690502643585 1.4356291890144348, 2.5917307734489441 1.4222828447818756, 2.63694429397583 1.4171558916568756, 2.6647220849990845 1.4171558916568756, 2.7220476865768433 1.408502459526062, 2.7850224673748016 1.4055049419403076, 2.984512597322464 1.4070918560028076, 3.0467549264431 1.4033619165420532, 3.1029751300811768 1.3935962915420532, 3.1508267223834991 1.3754891753196716, 3.0976718664169312 1.3765606880187988, 3.0473110377788544 1.3748381435871124, 3.0000969171524048 1.3699688911437988, 2.956382155418396 1.3616002798080444, 2.956382155418396 1.0838223397731781, 2.9039190113544464 1.07961767911911, 2.8520526587963104 1.0806485116481781, 2.7025571167469025 1.0949714183807373, 2.6556006968021393 1.0967617928981781, 2.6104956865310669 1.0937235951423645, 2.5674997866153717 1.0838223397731781, 2.6542850732803345 1.0837951898574829, 2.6693607568740845 1.0815165638923645, 2.6822934448719025 1.0770948827266693, 2.6924998760223389 1.0699334442615509, 2.6924998760223389 1.0560445189476013, 2.6658003628253937 1.0503479242324829, 2.6248050630092621 1.0499003231525421, 2.5737728476524353 1.0538744032382965, 2.5169762074947357 1.0614698827266693, 2.4586808085441589 1.0718458592891693, 2.4031591415405273 1.0842021107673645, 2.3546768426895142 1.0977247953414917, 2.3174996376037598 1.1116001307964325, 2.3174996376037598 0.80604436993598938, 2.4135893881320953 0.771918922662735, 2.4800160229206085 0.751546710729599, 2.5157555043697357 0.7438834011554718, 2.5539771020412445 0.7387022078037262, 2.6230553984642029 0.73659989237785339, 2.6288944184780121 0.7250710129737854, 2.6420576870441437 0.72087991237640381, 2.6979048848152161 0.72465053200721741, 2.7127635478973389 0.7221413254737854, 2.7211389541625977 0.713162362575531, 2.7202776670455933 0.69493317604064941, 2.5094553232192993 0.7202153205871582, 2.4580162167549133 0.7277972400188446, 2.4084285199642181 0.7372509241104126, 2.3613703846931458 0.7492138147354126, 2.3174996376037598 0.76437768340110779, 2.3174996376037598 0.47271081805229187, 2.3517539501190186 0.45315241813659668, 2.3894329965114594 0.43701201677322388, 2.4301299154758453 0.42388266324996948, 2.4734445512294769 0.41338461637496948, 2.5189632475376129 0.40508380532264709, 2.5662926435470581 0.39858695864677429, 2.6647220849990845 0.3893774151802063, 2.7202776670455933 0.3893774151802063, 2.7375777661800385 0.38758707046508789, 2.8178865015506744 0.39150688052177429, 2.8352747559547424 0.38979789614677429, 2.8493942320346832 0.38482013344764709, 2.8591598570346832 0.3754885196685791))');
 SELECT geom FROM @SqlBitsLogo ORDER BY id;

The colours are a little bit muted, but I think that’s unavoidable… and here’s the finished result:

image

About these ads
This entry was posted in Spatial, SQL Server and tagged , , . Bookmark the permalink.

One Response to More Drawing Fun with SQL Server… this time in Technicolor

  1. Pingback: Sunday Funday « SQL Swampland

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s