Geographic datatypes to create custom visualisations in SQL 08

I recently stumbled across a blog by Teo Lachev describing how to use the geometric datatypes in SQL Server 08 to produce squarified heat maps in SSRS. (http://prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx). In a previous role I looked into creating a heat map of this type for Reporting Services 05. To do this would have involved creating a custom component / tool for SSRS to draw the visualisation. We ultimately decided that this represented too much effort to peruse. (Which is why this entry caught my eye).

Much has been written on the subject of the geospatial capabilities of SQL Server but what this blog hints at is that because you can now store and manipulate geometric polygons natively in SQL Server, almost any visualisation is possible! Naturally this also opens a huge number of potential holes and increases the scope to produce truly awful visualisations. With this in mind you should have read and understood at least one of Stephen Few’s books before attempting to produce any graphical representation of information.  J  -  Stephen’s blog and details of his books and courses can be found at http://www.perceptualedge.com/)

For example if we look at the pie chart devised by Florence Nightingale to show the monthly breakdown of causes of death during 1854/55, we can see that this is made up of segments where the number of deaths is measured from the centre of the graph giving a ‘ragged’ pie chart. This is not easy using the native visualisations in SSRS, though it may be possible to coerce a radar chart to produce something similar.

 However each of these ‘segments’ can be drawn using the geometric data types and methods geometry::STPolyFromText(). I won’t labour the over the technical details again here as Teo has already covered them very well in his blog.

As a footnote, I should add that this chart does not represent good practice in terms of visualisation. As with any pie chart it is intended to distort the information in order to emphasise or hide something. In this case because the segments are arcs the outer edge holds more area than the inner. As a result the audience is given a greater impression of the importance of the higher values here and in this case make them more  likely to support Florence Nightingale’s argument that greater effort should be spent on disease prevention.

 A fairer representation of the data would have been a line graph.