SQL Server Reporting Services has some cool features such as displaying maps and data points on the maps. In this post I’ll show how to take a list of addresses and display them as pinpoints on a map in an SSRS report.
With SSRS 2008R2 you have the capability of adding maps to your reports using spatial data. In this tip I’ll show you how to accomplish this.
If you have a table with addresses, zip codes, etc. we’ll need to find the latitude and longitude of each address. You can accomplish this by using a geocoding website. There are many out there but I use http://www.gpsvisualizer.com/geocoder/ because it’s fast and easy to copy and paste multiple addresses into their input box, click “start geocoding” and within seconds it will output latitude and longitude information.
Once you get the latitude and longitude information you can import it into your database. See below for an example of my table of Alabama cities and zip codes with their appropriate coordinates.
Once you have your coordinates we will need to create a new column with a geography data type that we will add our spatial data into. The following code will accomplish this.
ALTER TABLE ZipCodes ADD SpatialData geography
We should now have a table that looks like below:
Inserting the data into the SpatialData column can be time consuming if you have a lot of records. The best way I have figured out how to do this is to use a basic UPDATE statement. Below is my script I have created for my table for this example.
UPDATE ZipCodes SET SpatialData = 'POINT(85.972173 31.809675)' WHERE ZipCode = 36081 UPDATE ZipCodes SET SpatialData = 'POINT(88.053241 30.686394)' WHERE ZipCode = 36685 UPDATE ZipCodes SET SpatialData = 'POINT(86.602739 33.621385)' WHERE ZipCode = 35173 UPDATE ZipCodes SET SpatialData = 'POINT(86.265837 32.35351)' WHERE ZipCode = 36106 UPDATE ZipCodes SET SpatialData = 'POINT(87.022234 32.41179)' WHERE ZipCode = 36701 UPDATE ZipCodes SET SpatialData = 'POINT(86.102689 33.43451)' WHERE ZipCode = 35161 UPDATE ZipCodes SET SpatialData = 'POINT(87.571005 33.209003)' WHERE ZipCode = 35402 UPDATE ZipCodes SET SpatialData = 'POINT(86.584979 34.729135)' WHERE ZipCode = 35801 UPDATE ZipCodes SET SpatialData = 'POINT(86.007172 34.014772)' WHERE ZipCode = 35901 UPDATE ZipCodes SET SpatialData = 'POINT(86.809484 33.517467)' WHERE ZipCode = 35266 UPDATE ZipCodes SET SpatialData = 'POINT(86.300629 32.38012)' WHERE ZipCode = 36124 UPDATE ZipCodes SET SpatialData = 'POINT(86.977029 34.60946)' WHERE ZipCode = 35602 UPDATE ZipCodes SET SpatialData = 'POINT(85.239689 31.941565)' WHERE ZipCode = 36072
Once you update your table with the spatial data and run SELECT * FROM ZipCodes you should see the following output:
