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).
“
[…] somehow. There are no end of ways this is possible in the SQL Server BI stack (here’s a good post by Alex Whittles about using maps in SSRS for example) but I think the most exciting thing about a tool like […]
[…] somehow. There are no end of ways this is possible in the SQL Server BI stack (here’s a good post by Alex Whittles about using maps in SSRS for example) but I think the most exciting thing about a tool like […]
[…] somehow. There are no end of ways this is possible in the SQL Server BI stack (here’s a good post by Alex Whittles about using maps in SSRS for example) but I think the most exciting thing about a tool like […]
[…] convert postcode (in UK) to latitude/longitude using a UK postcode dataset. Alex Whittles has a blog post which suggests a number of UK postcode datasets, and also introduces how to convert postcode to […]
[…] convert postcode (in UK) to latitude/longitude using a UK postcode dataset. Alex Whittles has a blog post which suggests a number of UK postcode datasets, and also introduces how to convert postcode to […]
Awesome! – Really usefull and informative. A great page and well explained.
I will add a link in my blog, if that is ok?
How do you keep the map to show if the report server does not have net access?
Hi Elliot,
By all means, link away.
As far as I’m aware there’s no way of caching the map data, so the client always needs to be online. If you have offline requirements then you could look at integrating into MapPoint or similar, but I’m not aware of any way to do that in SSRS.
Alex
Map Postcodes in SSRS Reporting Services post is very useful and its a lifesaver……
Looking at this I realised that using max latitude and max longtitude is probably not accurate since postcode regions are irregular. The combination of these two points could actually arrive at a point outside the real postcode region. Instead I got the spatial points for each postcode (the 1.7 million file) – using the technique above except for “max”. Then I created two new fields Sector_Postcode and Sector_Geog_Pt. I populated the sector postcode with left(postcode,len(postcode)-2).
Then I filled the Sector_Geog_Pt using:
;with SectorCTE AS (
SELECT [postcode]
,[latitude]
,[longitude]
,[postcode_sector]
,[GeogPt]
,[Sector_Geog_Pt]
,Row_Number () OVER (partition by [postcode_sector] order by [postcode_sector],[postcode]) as RowNum
FROM [postcodes].[dbo].[postcodelatlng]
)
–Uses an actual spatial point from inside the sector not a concatenated lat + long
update [postcodelatlng]
set [postcodelatlng].[Sector_Geog_Pt] =SectorCTE.[GeogPt]
FROM SectorCTE
where rownum=1
Nice work – thanks for posting the details
Alex
Sorry – slight correction:
,Row_Number () OVER (partition by [postcode_sector] order by [postcode_sector],[postcode]) as RowNum
Should be:
,Row_Number () OVER (partition by [postcode_sector] order by [postcode_sector]) as RowNum
Hello, Need some help with the above, is anyone able to contact me on the email used?
How to color bubbles from hex color value stored in the dataset?