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.php
and 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] geography
NULL
UPDATE
[dbo].[PostcodeUKAll]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
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 Files\Microsoft SQL
Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. 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.
Labels: geography, GIS, Mapping, Maps, spatial, SQL2012