Mapping Spatial Data in SQL Server

Spatial data types were first introduced in SQL Server 2008 and allow points, lines and shapes to be stored inside a column inside a database table. There are two spatial data types:
·         Geometry: Stores spatial data on a flat surface
·         Geography: Stores spatial data on a sphere, or to be more exact the earth

Data can be inserted into a geography column in a table using references to spatial data objects such as POINT, LINESTRING, POLYGON etc. Some example inserts into a table that has a Location column of type geography are:

INSERT INTO Site (ID, Location)
VALUES (1, 'POINT(51.508 -0.128)')--London

INSERT INTO Site (ID, Location)
VALUES (2, 'POINT(48.857 2.352)') --Paris

INSERT INTO Site (ID, Location)
VALUES (2, 'LINESTRING(1 1,2 3,4 8, -6 3)')

The spatial data types also have built in methods allowing spatial computations to be carried out as part of an SQL query. There are many such methods including: STDistance(), STIntersection(), STUnion() and STDifference(). The first of these STDistance() calculates the distance between two points or shapes, in metres when using the default coordinate system, as shown in the example below:

DECLARE @p1 geography

SELECT @p1 = Location FROM Site WHERE ID = 1
SELECT Location.STDistance(@p1) FROM Site WHERE ID = 2

The answer is just over 402793 metres which when divided by 1609.344 gives 250 miles which is correct given that the first point is in London and the second one is in Paris.

So spatial shapes can be stored in a database and calculations can be carried out on them in SQL queries. But how do we visualise spatial data? Well one way is to the use the mapping facility in SQL Server Reporting Services (SSRS).

Conveniently the AdventureWorks sample database has a geography column called SpatialLocation in the Address table. So to demonstrate the combined capabilities of SQL spatial data types and maps; I have devised an example report in SSRS that allows a user to select an address in New York together with a distance in miles and then display on a map all the addresses that are within the distance.

I first wrote a query to return all the addresses in New York. This query is used to populate the dropdown list for the user to select a starting address:
SELECT A.[AddressID], A.[AddressLine1] + ' '
       + ISNULL(A.[AddressLine2], '')
       + ' ' + A.[City]
       + ' ' + SP.StateProvinceCode
       + A.[PostalCode] AS [Address]
FROM [AdventureWorks].[Person].[Address] AS A
INNER JOIN [AdventureWorks].[Person].[StateProvince] AS SP
ON A.StateProvinceID = SP.StateProvinceID
WHERE SP.CountryRegionCode = 'US'
AND SP.StateProvinceCode = 'NY'
ORDER BY A.[PostalCode]

I then wrote a query to return all the addresses within @Distance of the selected property. Note the used of the STDistance() spatial method to calculate the distance between the addresses. This query will be used to plot the addresses on the map:

DECLARE @Distance int = 100 -- Parameter
DECLARE @NYAddress int = 770 – Parameter
SELECT A.[AddressID], A.[AddressLine1] + ' '
       + A.[City]
       + ' ' + SP.StateProvinceCode +
       A.[PostalCode] AS [Address],
       CONVERT(int, A.[SpatialLocation].STDistance(
              A2.[SpatialLocation])/1609.344)
       AS [Distance], A.[SpatialLocation]
FROM [AdventureWorks].[Person].[Address] AS A
INNER JOIN [AdventureWorks].[Person].[StateProvince] AS SP
ON A.StateProvinceID = SP.StateProvinceID
INNER JOIN [AdventureWorks].[Person].[Address] AS A2
ON CONVERT(int, A.[SpatialLocation].STDistance(A2.[SpatialLocation])/1609.344)
       < @Distance
WHERE A2.AddressID = @NYAddress
ORDER BY [Distance]

Next step was to create a new blank SSRS report in SQL Server Data Tools and add:

·         A SQL Server data source connecting to AdventureWorks
·         Two data sets one for each of the SQL queries shown above
·         Two parameters, one for each of the parameters used in the second query
·         I dragged a Table item from the toolbox onto the report surface and configured it to display the Address and Distance columns for each for the selected addresses
·         I then dragged a Map item from the toolbox onto the report surface and configured it to plot the position of each of the selected addresses on a Bing map. To do this I went through wizard screens as shown below:

 

On this screen I selected “Layer Type” as Point and I checked the “Add a Bing Maps layer” option. I ignored the message about the @Display variable because I have defined it as a parameter and so I knew it would be OK.

 

For the rest of the Wizard screens I just selected the default options.

I then opened the Point Properties dialog for the Point Layer on the map and changed the fill colour to Red to make it easier to see the points on the map.

The result is below with 8 matching addresses shown for the 50 mile radius. Change the New York Address and the distance and click View Report and the map is rescaled and redrawn as required.

This is a simple example, but it shows how easy it is to create a spatially aware application using SQL Server. The database engine stores the locations and calculates the distances, while SSRS renders them nicely as a familiar and easy to use map.

 

This article is based on SQL Server 2012.