I’ve been looking forward to using some of the features of spatial data in SQL Server 2012 but realised I hadn’t found a quick way to utilise UK Maps. Since it’s highly graphical and tangible in nature I believe this to be an excellent way to start with reporting services and spatial data. Thus this tutorial blog using UK Postcodes, Latitude and Longitude and the spatial geography data type was born.
The first thing we’ll do is get a list of postcodes to UK Latitude and Longitude co-ordinates. This is available from http://www.doogal.co.uk/UKPostcodes.phpand click on the link ‘Download CSVDownload the full list of postcodes in CSV format’ .
Import the .csv file into a database table called PostcodeUKAll
Then we need to add a column for our SQL spatial geography data type and populate it with the point data from the latitude and longitude co-ordinates.
ALTER TABLE [dbo].[PostcodeUKAll]
ADD [GeoLocation] geographyNULL
UPDATE[dbo].[PostcodeUKAll]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
Next we’re going to need to get the UK Map as a shapefile , this is available for download here http://www.statsilk.com/maps/download-free-shapefile-maps
Once downloaded, unzip the file and we’re ready to create a new project in SSDT.
Using the Toolbox drag a map onto your report. Next we want to get the ERSI shapefile option and browse to the map.shp file.
Click Next and then Leave the map embedded in the report. We’ll use the Basic Map for now so click Next. Single color map and generic settings are fine so click Finish.
Now we’ve should be able to see the map of the UK but it’s not telling us anything as we’ve no data points on it.
So create the connections and a dataset which for example may choose 10 postcodes with the co-ordinates randomising the selection with newid as below.
SELECT TOP 10 [Postcode], [GeoLocation]
FROM [dbo].[PostcodeUKAll]
ORDER BY NEWID()
Now we need to add this layer to our map. Right click on the Map and you should be able to Add Layer as below
Next select the SQL Server Spatial Query and choose your dataset and it should automatically make the Spatial Field: Geolocation and Layer Type: Point and then click Next. Keep the selected Basic Marker Map and click Next. Untick the single color map and click Finish.
Now we can deploy the map but we may get an error “There was an exception running the extensions specified in the config file. —> Maximum request length exceeded”
This is due to the size and time so we’ll need to edit the Report Servers’ web.config file. On the Report Server edit the reporting services config so we can load larger project files. Standard installation of SQL2012 may be at C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServer. Edit the web.config file and search for “<httpRuntime executionTimeout = “9000”>” and either edit or add it to <httpRuntime executionTimeout = “9000” maxRequestLength=”500000″ />. If inserting it goes immediately after </httpModules>
Now we can deploy our report to the report server and marvel at the output from the simplicity of the setup. Now we have the foundations for the maps we can reuse the tables and shapes.
Although our query doesn’t use spatial index we may later want to add a spatial index to support queries running on our spatial data type. Now since Spatial indexes require the table to have a clustered primary key on the table we’ll start by putting a one on the postcode as it’s most likely to be queried:
ALTER TABLE [dbo].[PostcodeUKAll] ADD CONSTRAINT [PK_PostcodeUKAll]PRIMARY KEY CLUSTERED ([Postcode] ASC)
Now we can create a spatial index on our geography column:
CREATE SPATIAL INDEX [ix_GeoLocation] ON [dbo].[PostcodeUKAll]([GeoLocation])USING GEOGRAPHY_GRID
Note if you use the UI to create a spatial index remember on the spatial section to set Tessellation Scheme to Geometry Grid.
Next we might want to calculate the distance between some postcodes and , for example, the Loch Ness Centre Exhibition Experience at IV63 6TU. Firstly we’ll translate the postcode to the co-ordinates as below. The POINT method takes Longitude then Latitude.
SELECT TOP 10
dbo.PostcodeUKAll.GeoLocation,
dbo.PostcodeUKAll.Postcode,
geolocation.STDistance(geography::STGeomFromText(‘POINT(-4.478758 57.337857)’, 4326))/1000 AS Kilometres
FROM [dbo].[PostcodeUKAll]
ORDER BY NEWID()
The simplicity of building these outstanding visual and informative reports is one of the great features of Reporting Services and spatial data.
Simple UK Mapping in SQL Server 2012 using spatial data types – geography