{"id":96,"date":"2011-12-20T02:26:54","date_gmt":"2011-12-20T02:26:54","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=96"},"modified":"2014-03-06T15:38:33","modified_gmt":"2014-03-06T15:38:33","slug":"creating-an-ssrs-map-report-with-data-pinpoints","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/creating-an-ssrs-map-report-with-data-pinpoints\/","title":{"rendered":"Creating an SSRS Map report with data pinpoints"},"content":{"rendered":"

SQL Server Reporting Services has some cool features such as displaying maps and data points on the maps.\u00a0 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.<\/p>\n

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.<\/p>\n

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\/<\/a> 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.<\/p>\n

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.<\/p>\n

\"SQL<\/a><\/p>\n

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.<\/p>\n

\n
ALTER TABLE ZipCodes ADD SpatialData geography<\/pre>\n<\/div>\n
We should now have a table that looks like below:<\/p>\n
\"SQL<\/a><\/p>\n
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.<\/p>\n
\n
UPDATE ZipCodes SET SpatialData = 'POINT(85.972173  31.809675)' WHERE ZipCode = 36081 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(88.053241  30.686394)' WHERE ZipCode = 36685\r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.602739  33.621385)' WHERE ZipCode = 35173\r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.265837  32.35351)' WHERE ZipCode = 36106 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(87.022234  32.41179)' WHERE ZipCode = 36701 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.102689  33.43451)' WHERE ZipCode = 35161 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(87.571005  33.209003)' WHERE ZipCode = 35402 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.584979  34.729135)' WHERE ZipCode = 35801 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.007172  34.014772)' WHERE ZipCode = 35901 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.809484  33.517467)' WHERE ZipCode = 35266 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.300629  32.38012)' WHERE ZipCode = 36124 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(86.977029  34.60946)' WHERE ZipCode = 35602 \r\nUPDATE ZipCodes SET SpatialData = 'POINT(85.239689  31.941565)' WHERE ZipCode = 36072<\/pre>\n<\/div>\n
Once you update your table with the spatial data and run SELECT * FROM ZipCodes you should see the following output:<\/p>\n
\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Reporting Services has some cool features such as displaying maps and data points on the maps.\u00a0 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,29],"tags":[35,106,59,33,34,32,31],"class_list":["post-96","post","type-post","status-publish","format-standard","hentry","category-business-intelligence","category-reporting-services-ssrs","tag-bi","tag-business-intelligence","tag-development","tag-maps","tag-spatial","tag-sql-server-reporting-services","tag-ssrs"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/96","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"predecessor-version":[{"id":100,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/96\/revisions\/100"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}