Map Postcodes in SSRS Reporting Services

I was asked the other day for some help on how to plot data by postcode on an SSRS spatial map. I’ve done this a few times, initially for a presentation I gave back in 2010 and most recently a couple of weeks ago when I wanted to analyse the SQLMidlands membership. It occurred to me that despite posting a number of blogs using spatial data, I’ve not done one on the basics of plotting postcode data. So here we go…

Scenario

You have a database containing customers, members, visitors, contacts, etc., each of which has a postcode. You want a simple map which shows where these are. Something like this…

How do we do it?

Firstly, the Reporting Services map component doesn’t understand postcodes, so we have to convert postcodes to latitude and longitude.

There are a number of ways of doing this, some more costly and/or time consuming than others. There are datasets that you can buy, or the Ordnance Survey now provide a free download of postcode and other mapping data. Read Jamie Thompson’s blog (No, not the SSIS-Junkie, another one!) for details on how to use it. [Edit: Thanks to Martin Bell for pointing out this link is now invalid, sorry!] These datasets provide full 7 digit postcode resolution, which means you can plot at the street level (approx 2 million different locations).

For a number of purposes this is excessive, and using a simpler 4 digit postcode is more than adequate, which breaks the UK down into about 3000 areas. For this you can download the details here.

We can then use the spatial functionality introduced in SQL Server 2008 (and R2) to convert the lat/long into a geography data type, which can be displayed natively in an SSRS report.

Convert Postcode to Latitude Longitude

Assuming you’ve loaded the dataset above into a database table, we can join this to our existing table of customers. As our customer table has a full 7 digit postcode but our postcode table only has 4 digit, we need to simplify our customer postcode down to take only those characters before the space (convert SY1 1AA into SY1). We can do this using a LEFT and CHARINDEX, as per the code below.

   CASE WHEN
         --If the postcode doesn't contain a space, it isn't valid
         --So ignore it and return an empty string
      CHARINDEX(' ', Postcode)=0 THEN ''
         --If it does contain a space, only take the characters
         --up to (and not including) the space
      ELSE LEFT(Postcode, CHARINDEX(' ', Postcode)-1)
   END

We can then join this data to the postcode table to link the lat/long to each customer. I’ve saved the postcode list in a table called PostcodeOuter.

   SELECT *
   FROM Customer c
      INNER JOIN PostcodeOuter p ON p.outcode = 
         CASE WHEN 
            CHARINDEX(' ', c.Postcode)=0 THEN ''
            ELSE LEFT(c.Postcode, CHARINDEX(' ', c.Postcode)-1)
           END

Latitude and Longitude aren’t quite enough for Reporting Services to be able to display. We need to convert them into the internal SQL Server Geography data type. We do this with the geography::STPointFromText function.

   geography::STPointFromText('POINT(' + 
      CAST(longitude AS VARCHAR(20)) + ' ' + 
      CAST(latitude AS VARCHAR(20)) + ')', 4326)

Note that the 4326 is the Spatial Reference Identifier (SRID), 4326 is the default – you can just leave this as it is.

We now want to join all of this together, and group the results by the post code outer code, so we can count the number of customers in each code.

   SELECT 
       p.outcode
      ,geography::STPointFromText('POINT(' + 
         CAST(MAX(p.lng) AS VARCHAR(20)) + ' ' + 
         CAST(MAX(p.lat) AS VARCHAR(20)) + ')', 4326) AS Geog
      ,COUNT(*) AS CustomerCount 
   FROM Customer c
      INNER JOIN PostcodeOuter p ON p.outcode = 
         CASE WHEN 
            CHARINDEX(' ', c.Postcode)=0 THEN ''
            ELSE LEFT(c.Postcode, CHARINDEX(' ', c.Postcode)-1)
           END
   GROUP BY p.outcode

Note that as we’re grouping the results, we have to aggregate the lat and long using max. We could aggregate the resulting geography data type, but doing it at the lat/long level reduces the number of geography calculations that have to be performed, improving performance. You can run this in Management Studio. Notice that you should now have an extra results tab, Spatial Results. Clicking on this will show you a preview of your data. The dots can be hard to see, so you can add .STBuffer(5000) to make the dots larger. You should end up with geography:STPointFromText(….., 4326).STBuffer(5000) AS Geog

We can now put this query directly into SSRS and show the data on a map.

Displaying Spatial Data in SSRS

Firstly load up BIDS, load or create a Report Server Project, and then add a new report (don’t bother using the wizard).
Add a data source to your database. Use the query above as the dataset (obviously with the fields and tables modified to suit your database!).
From the Toolbox, drag a Map report item onto your report. This will start the Map wizard. Use the following details for each stage:

• Choose ‘SQL Server spatial query’
• Select the dataset that we just created
• Select the Geog field in the first box, and Point in the second. Also make sure that you tick the box at the bottom – ‘Add a Bing Maps layer’, Selecting whether you want the map layer to show Road, Aerial or Hybrid
• We want the size of each marker to change depending on the number of customers in that postcode, so select the Bubble Map
• Select the same dataset that we created earlier
• Tick the ‘Use bubble sizes to vizualize data’ option, and select [Sum(CustomerCount)]
• Lets also change the colour of the markers to make it even clearer. Tick the ‘Use bubble colors to visualize data’ box and select [Sum(CustomerCount] and Red-Green as the color rule

And there you have it, you can run the report and see your map. Not that tricky huh?
You can then play around with the settings to change the look and feel, a good one is to set the Map layer transparency to 50, which makes the markers stand out more. But this is up to you.

There’s plenty more information on the internet about spatial data, both on the Frog-Blog and elsewhere. I’d recommend taking a look at Alistair Aitchison’s Spatial blog for some really interesting stuff to do using spatial data.

Thanks to Stephen Bennett for inspiring this post.

Frog-Blog-Out

[Edit 03/03/2014] This post is now over two years old, so some of the links are out of date, have changed or are invalid, and SQL Server has also moved on to new versions. Please take this into consideration when following this guide. Thanks again to Martin Bell for adding the following:

“You may want to add the link http://enterprisegeospatial.blogspot.co.uk/2011/01/using-powershell-to-load-ordnance.html as a means of loading and converting the coordinate files. One thing to be aware of is that it appears the header has changed since post was written and the code should be updated using the header detailed in the documents (Code-Point_Open_Column_Headers.csv) and making sure the attributes used in the code match those in the header (the header can be changed to fit the code)

I believe that a postcode will always have a 3 digit outbound part, so rather than checking there is a space you can take all but the last 3 characters (when trimmed).

“

Tags: Map, Spatial, SSRS