Creating an SSRS Map report with data pinpoints

By:   |   Updated: 2011-12-20   |   Comments (19)   |   Related: 1 | 2 | > Reporting Services Development


Problem

SQL Server Reporting Services has some cool features such as displaying maps and data points on the maps.  In this tip I show how to take a list of addresses and display them as pinpoints on a map in an SSRS report.

Solution

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.

list of addresses with latitude and longitude data

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:

addresses with spatial data column

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:

sql server table with spatial data for addresses

If you want to convert the SpatialData from binary back into the text you can use the CONVERT function: 

SELECT City, State, ZipCode, CONVERT(varchar(255), SpatialData) AS Coordinates 
FROM ZipCodes

sql spatial data coordinates

Now that we have all of our data in our table it's time to create our map.

Open Business Intelligence Development Studio and create a new project by going to File, New Project. Under Visual Studio installed templates choose Report Server Project Wizard and choose a Name and Location for your project.

bids project to build map with data points

After clicking OK, the Report Wizard will appear. Click Next at the main screen and then you'll need to configure the data source. Since my table was created on SQL2008, I will use this server and the database DBReports as my data source. To configure, choose Edit... and fill in the connection properties and click Test Connection. Once the connection is successful click OK. Rename the data source and click Next.

bids connetion manager properties

bids connection manager for sql 2008 r2

On the design query screen use the following query and click Next: 

SELECT * FROM ZipCodes

Select the Tabular Report Type and click Next, Next, Next.

On the Deployment Location screen I'll leave the defaults and click Next.

choosing a deployment location for a ssrs project

Once we are at the Summary Screen, name the report and click Finish. This should bring up the design screen. Once in the design screen go to View, Toolbox to display the Toolbox.

In the toolbox, click the Map icon and click anywhere on the report design screen and the New Map Layer box should appear.

choose a source for spatial data for new map layer

In the Map Layer Box there are a few options including Map gallery, ESRI shapefile, or SQL Server spatial query. The first layer we'll create will be the basic map layer. I'll choose Map gallery and drilldown states by county and select Alabama.

ssrs choosing a map layer from map gallery

Click Next where it will display the state of Alabama and it's outlined counties. Here you can add a Bing Map layer if needed and select a Map resolution. Click Next.

choose spatial field and layer type and configure map in ssrs

The next screen will display map visualizations. Here you can select Basic Map, Color Analytical Map, or Bubble Map. For this example, I'll choose Basic Map.

Next screen will let you choose a theme, single color map, and give you the option to display labels such as county names. Once you are finished customizing your map click Finish.

Next I usually click on the map and scale it to the size I want for display and change the Map Title at the top to an appropriate title.

ssrs map display showing address data

Once you have the map the way you want it you will need to add another layer for the zip codes. To do this right click on the map and select Add Layer. Our New Map Layer box should appear again. This time we'll choose SQL Server spatial query and click Next.

adding a map layer to show map pinpoints

Choose the existing dataset and click Next

choose a data source in bids

Here you will you see your pinpoints that were specified in the database table. You can also change the layer type if needed. Click Next to choose a map visualization.

chose spatial data and map options for an ssrs report

Once on the map visualization screen you can choose a Basic MarkerMap, Bubble Map, or Analytical Map. For this example I'll choose Basic Marker Map because I just want to mark my zip codes.

select tyupe of map visualization for ssrs report

The next screen will allow you to choose a Theme, Marker, specify a single/multicolor map and display labels based on your dataset. I'll choose Generic Theme, PushPin marker, and display labels based on City. Click Finish.

choosing a color theme and data visualization

Once back, you will be in design view, click Preview to preview the report.

display final ssrs report with map and showing pinpoiints on the map

Once you're map is the way you like it, right click the .rdl in Solution Explorer and Deploy.

Next Steps
  • To find Spatial data storage tips click here
  • To find out more regarding the geography data type check out this tip by Tim Cullen
  • Thank you to GPSVisualizer for there help in gathering geocodes


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-12-20

Comments For This Article




Tuesday, July 31, 2018 - 9:26:44 AM - Rodrigo Back To Top (76912)

Only usefull when you live in US,

How do you do it in UK or Ireland, when maps are not created by default?

REgards

Wednesday, January 18, 2017 - 1:06:55 PM - Anthony Sims Back To Top (45404)

Mr. Upton,

Thank you for the tutorial. It helped immensely, however I am working with a table consisting of over 40,000 rows, so updating each one individually wasn't really an option for me. To work around this, I used the following query:

UPDATE us_loc_data SET GeoLocation = geography::Point(b.latitude, b.longitude,4326)
FROM    us_loc_data2 AS A
JOIN    us_loc_data B on (A.zip = b.zip)

This updated all of my rows simultaneously.

Wednesday, March 16, 2016 - 5:46:08 PM - Anil Back To Top (40963)

Very helpful, however I want to do something like this.
For every coordinates (spatialData) I have seperate images stored in the DB. So instead of using some inbuilt markers like pushpin, how can I use these images from database to visualize each coordinates in the map. Or in your example above how can I replace pushpins with unique images to represent those zipcodes ?

Thanks a lot and any help/ideas would be really appreciated. 

Tuesday, May 12, 2015 - 9:13:53 PM - jon Back To Top (37158)

Great article. This is the article I am looking for.

I have a question to ask.

I have spare parts usage data which is aggregated by ZIP code. How could I show the parts usage on the map by ZIP code.

Is to add a layer and to use query to do so? I am not quite sure,

For example, SELECT ZIP, usage FROM usage_data

Thanks

--------------------------------------------------

Http://www.computerzero.com

One-stop online community and store to beautify your digital life. Computers & Electronics products. Quality and affordable

 

Thursday, May 7, 2015 - 9:20:05 AM - Caroline Back To Top (37120)

I am trying to follow this and this is the error message that I am getting. 

 Unable to determine the spatial data type in the specified data set.

This is what the data looks like when I  execute it in SSMS


50001 IA ACKWORTH      41.364     93.4245    0xE6100000010C3BDF4F8D97AE4440BA490C022B5B5740
50002 IA ADAIR             41.5092    94.6468    0xE6100000010CC58F31772DC1444061C3D32B65A95740
50003 IA ADEL               41.6119     94.026    0xE6100000010C11363CBD52CE44408B6CE7FBA9815740
50005 IA ALBION           42.1277     93.0148    0xE6100000010C7FD93D7958104540C5FEB27BF2405740

 

When I run the query in SSRS  this is what the data looks like

50001 IA ACKWORTH 41.364 93.4245 POINT (93.4245 41.364)
50002 IA ADAIR 41.5092 94.6468 POINT (94.6468 41.5092)
50003 IA ADEL 41.6119 94.026 POINT (94.026 41.6119)
50005 IA ALBION 42.1277 93.0148 POINT (93.0148 42.1277)

 

Can you tell me what I am doing wrong?  I am new to SSRS and wld really like to get this working.

Thank You

Wednesday, February 18, 2015 - 10:41:29 AM - Jatin Back To Top (36275)

Thank you :)

Wednesday, September 17, 2014 - 1:08:52 AM - Tim Back To Top (34554)

Using CONVERT will only retain 3 digits to the right of the decimal.  Instead use STR(, 15, 10)

update ZipCodes SET SpatialData = 'POINT(' + STR([Longitude], 15, 10) + ' ' + STR([Latitude], 15, 10) + ')' from ZipCodes

When you are dealing with house addresses you really need the extra precision.

Monday, August 25, 2014 - 4:16:19 PM - Amit Back To Top (34252)

Great Article. Thank you!!!!

 

 

Thursday, July 31, 2014 - 4:21:09 PM - Diego Back To Top (33964)

hello, unfortunately in my datawarehose work with oracle database (which is impossible to change to meet standards), please could show an example loading data (longitude and latitude, POINT (-64.447090448866433 -31.731954802667566)) from an oracle database . 

From already thank you very much

Tuesday, March 11, 2014 - 11:29:39 AM - Jeremy Kadlec Back To Top (29716)

Ron,

Thank you.  This has been updated.

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader

Tuesday, March 11, 2014 - 10:51:07 AM - Ron Back To Top (29714)

Helpful example.  FYI, the UPDATE statement above is missing the negative sign on the longitudes.  So the pinpoints wind up in Tibet. :)

Friday, February 28, 2014 - 4:20:57 PM - Tom Back To Top (29617)

Question: I have a Map of the US with many several points that have 2 or 3 points very close together. I would like to use LABELS for the points to provide the store numbers but right now the labels all stack up on each other. Is there a way to display the label - like on a Chart - where a thin black line connects to the actual point? Thanks

Tuesday, September 10, 2013 - 1:50:23 PM - Maiyana Back To Top (26712)

HI there, I have been working on the report map for few day. my ticket is to create report map for the Store location in Florida .

the report will show florida map in the map will have counties and in each counties have different number of store, example in Leon county has Store 5 store, i want my map report to have pin point 5 pinpoints to the location on the map. Here is query

Select 

A.[id] as StoreID, A.businessName as AgentName,

C.[description] as County, A.physicalAddressLatitude,A.physicalAddressLongitude,

Sum(OI.itemAmount) as OrderValue 

from dbo.Orders O

INNER JOIN dbo.Store A on A.[id] = O.[storeID]

INNER JOIN dbo.Counties C on C.[id] = A.countyID

INNER JOIN dbo.OrderItems OI on OI.orderID = O.[id]

Where O.orderDate >= '2013-01-01' and O.orderDate <='2013-09-01'

Group by A.[id], A.businessName, C.[description],A.physicalAddressLatitude,A.physicalAddressLongitude

Order by Sum(OI.itemAmount) DESC

here is the result of the query 

AgentID AgentName County physicalAddressLatitude physicalAddressLongitude OrderValue
1001 Internet License Sales leon 30.44745 -84.2831 8804477
1002 Phone License Sales Leon  30.44745 -84.2831 1850246
1003 Wal Mart #1874 Leon  26.93453 -82.3112 155814
1004 Half Hitch Tackle Leon  30.16158 -85.7545 135972.5
1005 Wal Mart #814 Leon  27.22664 -80.8323 125991.5


Thank you 

Monday, August 12, 2013 - 12:47:27 PM - Rena Back To Top (26253)

Is there a third party tool that can be used on zooming a SSRS bing maps. I need a bar thta can zoom in or zoom out a bing map just like google maps.

 

Thanks,

Rena

Friday, March 1, 2013 - 1:07:17 AM - Graham Angell Back To Top (22497)

Or you could use:

update ZipCodes SET SpatialData = 'POINT(' + convert(varchar(26), [Longitude]) + ' ' + convert(varchar(26), [Latitude]) + ')' from ZipCodes

...And check the result with:

select convert(varchar(8000), SpatialData) as SpatialData from ZipCodes

Thursday, October 11, 2012 - 3:44:07 PM - Jeff Allen Back To Top (19882)

Even easier: Configure SpatialData as a computed (non-persistent) column, and let SQL do the work:

[SpatialData]  AS (CONVERT([geography],((('POINT('+CONVERT([varchar],[Longitude],0))+'  ')+CONVERT([varchar],[Latitude],0))+')',0))

Thursday, August 2, 2012 - 9:24:39 AM - Randy Volters Back To Top (18886)

Apologies; remove the ABS function from my previous post, then it will work

Thursday, August 2, 2012 - 9:19:38 AM - Randy Volters Back To Top (18884)

In order to 'automate' the spatialdata creation on a larger table, try this (my table name is tbl_mapdinvoices).

I populate that table (tbl_mapdinvoices) and in the creation of the table's contents I include a column, 'rowid', which is an 

IDENTITY(int, 1,1) column.  Thus when the table is populated rowid is a running sequence of: 1,2,3, etc.

To populate spatialdata's values try this...

declare @counter int

set @counter = 1

declare @sql nvarchar(max)

declare @lat decimal(10,6)

declare @lng decimal(10,6)

 

while @counter < (select MAX(rowid)+1 from tbl_mapdinvoices)

begin

set @lat = (select ABS([num-lat]) from tbl_mapdinvoices where rowid = @counter)

set @lng = (select ABS([num-lng]) from tbl_mapdinvoices where rowid = @counter)

set @sql = 'update tbl_mapdinvoices set spatialdata ='+''''+'POINT('+(select cast(@lng as varchar(16)) )+' '+(select cast(@lat as varchar(16)) )+')'+''''

set @sql = @sql+' where rowid ='+(select cast(@counter as nvarchar(10)) )+';'

--print @sql

        exec sp_executesql @sql

set @counter = @counter+1

end

Tuesday, December 20, 2011 - 8:27:47 PM - JT Back To Top (15404)

Great article.














get free sql tips
agree to terms