Whilst trying to create a means of mapping my population data, I created a SQL statement to pull all the people out of the system grouped by outbound postcode.

But what I needed was to plot a circle bitmap dependant on the density.  I found some functions in T-SQl I’d not heard of before  rank() and dense_rank().

I used dense_rank() so rather than trying to deal with sets of numbers ranging from 1 to 109 I now had nicel grouped results from 1-34, the number of different types of density ranked in order.  This I used to calculate my ranges.   It helped when the top most range of density was massively different to all other values.  not much use in real populution density maps but really useful for what I needed which was a rough idea of where people where in general numbers.

Without the ranking I had lots of single pixel dots and 3 large 7 pixel blobs, not really representative of the data I wanted to portray, and there is the trick isn’t it, when plotting data like this you know what you think it should look like in your own mind, this is a sql tool that can help you quickly organise that data.

Technorati tags: , , ,