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:

Here’s Alex’s render:

And here’s my render :

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;

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;

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:

Google Maps –vs- Bing Maps –vs- OSM – Responsiveness of Data Corrections

Yesterday, Glenn Letham published an article on the GISuser website explaining how to report map errors on Google Maps, Bing Maps, Open Street Maps etc.

Seeing as I’ve already made comparisons in some of my own recent blog posts concerning the accuracy and completeness of various online mapping providers, the content in Glenn’s article gave me an idea for another comparison…

Firstly, I would find an error somewhere in each of Google Maps, Bing Maps, and Open Street Maps. Then, using the official feedback channels described by Glenn, I would report those errors to the relevant providers. Points would be awarded to each provider based on the ease with which problems could be reported, how long it took for the error to be acknowledged, and how long it took for the error to be corrected on the map.

So, I set off to find some errors:

Google Maps Error – “Abberbury Avenue” in the Heart of Oxford

I was born and brought up in Oxford, England. When I returned there recently, I was surprised when driving down Longwall Street in the city centre that my (Google Android-based) car navigation told me to “turn left onto Abberbury Avenue”. In 18 years, I had never heard of Abberbury Avenue, and was certain that the road in front of me was the High Street.

On returning home, I checked on the main Google Maps website. Here’s a screenshot of the offending road in question, right next to Magdalen College in the heart of the city:

And here’s how that same section appears correctly in OSM – it’s definitely the High Street (also notice, once again, at the awesome level of detail in the OSM map – the college quads being individually and correctly labelled etc.):

There is an Abberbury Avenue in Oxford, incidentally, but it is found several miles away, near Rose Hill. The journey from Google’s “Abberbury Avenue” to the true location is shown as follows:

So, I set about following Glenn’s instructions to report the error. And then I stumbled across my second problem. The steps described to report a problem in Google Maps, which are also stated by Google on their own help pages here , say:

You can access Report a Problem in a couple of places:

• Right click on the map and select “Report a Problem” 
• Click the “Report a Problem” link at the bottom right of the map

Look closely at the following screenshot of what I see when I right click on “Adderbury Avenue” on the Google Maps website, and see if you can spot the problem:

That’s right – Right-clicking on the map does not give an option for “Report a Problem”, and neither is there a “Report a Problem” link at the bottom right of the map. From a quick search of the internet, it seems that the ability to report errors on Google Maps is limited to users in the USA, so that’s points lost immediately for Google…

So how exactly am I meant to report this problem? Fortunately, there is a copyright notice at the bottom of the map crediting Tele Atlas as the source for Google’s map data, and Glenn’s article also gives instructions on how to feedback directly to them. Fortunately, this is a very user-friendly step-by-step process, and I completed the details of the error  and submitted it as shown below:

I was given a tracking reference number and a link to track the progress of the error online. In theory, if the error is corrected in Tele Atlas’ own dataset will eventually feed back into Google Maps…. let’s see if it does.

Bing Maps – Driving down a Pedestrian-only way

The nature of the error I found in the Bing Maps website was slightly different. I couldn’t find any labelling errors on the map, but when trying to use the directions feature to plot a driving route between Norvic Drive and Peckover Road in Norwich, I noticed an interesting instruction:

2. Turn right onto Chalfont Walk

This immediately alerted my attention – pretty much any road named Chalfont Walk is, well, designed for walking, not for driving. Sure enough, as shown in Open Street Maps, it’s pedestrian access only:

Fortunately, the Bing Maps website does have a feedback link at the bottom right corner,  with different options to report problems with e.g. imagery, points of interest on the map, directions, etc. I filled in the feedback form with details of the error as follows:

I was given a “Thank you for taking the time to tell us about your experience” message, but no tracking reference and I didn’t have the opportunity to provide an email address, so Bing Maps also loses points here. I guess I’ll just have to keep checking back to see when (if) the problem is solved….

Open Street Maps

As noted in a previous post of mine, OSM has incredibly high quality data – higher, I believe, than that of the Ordnance Survey. Finding an error on the OSM map was therefore going to prove tricky. In fact, the only “error” I could find was not really an error – more an omission (and a pretty minor one at that). Jubilee Park, a park near my house in Norwich, wasn’t shown on the map:

So, you know what? I just logged into http://osm.org and, using the online map editor, added the missing feature. 5 minutes later, and here’s the correct OSM map:

So, that’s full marks for OSM. But who will come in second place out of Bing and Google? I’ll let you know as soon as I hear anything from either of them.

SQL Server Spatial – Coordinate Storage Precision

SQL Server stores geography and geometry coordinates as binary data, adhering to the IEEE-754 standard for binary floating-point arithmetic. Based on this standard, each coordinate is stored as a double-precision floating-point number that is 64 bits (8 bytes) long.

However, although SQL Server stores coordinates as binary floating point values, Well-Known Text (WKT), the format from which you are most likely to create SQL Server spatial data, is a character string that represents coordinates as decimal values. Whenever you use a static method to create an instance of geography or geometry data from WKT (or any other static method that accepts decimal input), the supplied coordinates are implicitly converted to the closest possible value that can be represented in binary floating-point format…. { each coordinate value is effectively CAST AS binary(8) }

An 8-byte binary value is roughly equivalent to 15 digits of decimal precision, but the geography and geometry static methods allow you to create instances from coordinate values with greater precision than this; the Point() method, for example, will accept decimal coordinate values with up to 38 digits of precision. However, all created geometry or geography instances will ultimately be stored with the same 64-bit precision, and supplying coordinates with greater precision than this to a static method will not lead to any greater precision of the stored geography or geometry value.

To demonstrate this, consider the following code listing:

DECLARE @Precise geometry;

SET @Precise = geometry::Point(10.23456789012345, 0, 0);

DECLARE @SuperPrecise geometry;

SET @SuperPrecise = geometry::Point(10.234567890123456789012345678901234567, 0, 0);

SELECT @Precise.STEquals(@SuperPrecise);

The additional decimal places of precision supplied for the @SuperPrecise point cannot be represented in an 8-byte binary value. As a result, the stored coordinate values of both @Precise and @SuperPrecise are the same (0x3C8B514819782440).

The STEquals() method returns the value 1, which confirms that the coordinates of each created instance are the same, even though the supplied coordinates differed.

It is also worth remembering that coordinates supplied with less decimal precision will still occupy 8 bytes when converted to binary and saved to the database. Thus in the following example, even though @HighPrecision is stated with greater precision than @LowPrecision, the two sets of coordinates occupy exactly the same amount of space.

DECLARE @LowPrecision geometry;

SET @LowPrecision = geometry::STPointFromText('POINT(1 2)', 0);

DECLARE @HighPrecision geometry;

SET @HighPrecision = geometry::STPointFromText('POINT(1.2345678901234567890123456789 2.3456789012345678)', 0);

SELECT

DATALENGTH(@LowPrecision), --22

DATALENGTH(@HighPrecision); --22

The result demonstrates that you do not save storage space in the DB by supplying less precise coordinates; both points in this example require exactly 22 bytes of storage.

Alpha Shapes and Concave Hulls

One question that gets raised relatively frequently over at the MSDN Spatial forums is how to create a concave hull around a set of points. The answer given is normally the same – while SQL Server provides the STConvexHull() method to determine the convex hull of a geometry, there is no inbuilt nor easy way to determine the concave hull.

One reason for this is that, unlike the convex hull, there isn’t a single concave hull for a given set of points. Consider the following diagram – the orange dotted line clearly represents the convex hull of the set of points, but which of the three yellow lines would you expect to represent the concave hull? (or, would you expect yet a further different possible polygon hull?)

Anyway, considering that this is a relatively common requirement, I thought I’d set about looking at the methods available to determine concave hulls, and came across the concept of alpha shapes.

I’ve yet to find a really good definition of exactly what an alpha shape is, or who first created the concept, but from researching various sources, I can tell you that alpha shapes are created from algorithms that use a single parameter (α)  to create a geometric object representing the broad “shape” of a set of points. As α approaches 0, the alpha shape approaches the original point set. As α increases towards infinity, the alpha shape approaches the convex hull of that set of points. Using an appropriate value of alpha between these limits creates a shape that is not necessarily convex, and may contain one or more holes, but broadly resembles the “shape” implied by the distribution of the set of points.

To start with, I created a MultiPoint set of test data, in which the underlying distribution of points resembled a recognisable shape. When viewed in SQL Server Management Studio (with a buffer of 0.025, to be able to actually see each individual point), it looks like this:

Using SQL Server’s built-in functions, the closest approximation you can get of generalising the overall distribution of this set of points is creating a convex hull using STConvexHull(), which gives you the following (not very accurate) representation:

To create an alpha shape, you first triangulate all the points in the original geometry, so that, rather than having a set of points, you now have a set of tessellating, non-overlapping triangular polygons. The Delauney triangulation of my original MultiPoint looks something a bit like this:

This may look quite chaotic, but triangles are the basis of creating an alpha shape. Once you’ve triangulated the underlying set of points, you define an alpha shape by simply creating a union of all those triangles whose circumradius is less than the stated alpha value.

I wrapped the triangulation and the unioning process into a UDF to try it out with different alpha values. Choosing a small alpha value (in this example, 0.1) will lead to a degenerate geometry consisting of many unconnected islands, as follows:

Choosing too large an alpha value means that the result will instead approach the convex hull of the geometry – effectively joining too much of the point set together. Here’s what my dataset looks like with an alpha value of 1:

By adjusting the value, you can get the most aesthetically, or functionally pleasing outcome you require. For my dataset, an alpha value of 0.24 creates a MultiPolygon that resembles the broad underlying shape of the original pointset, as follows: