Creating Spatial Map reports with SQL Server 2008 R2 and Bing Maps

By | September 7, 2009

By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.

The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:

update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)

There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx

Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world).  I created a view of the data so i can change its source on a later stage:

spatial

Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:

analytical

Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.

In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:

analytical2

Resulting in the following map in reporting services, so no programming necessary (except the Query of course):

nlanalytical

It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:

mapppoint

So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)

Using the same report for master and detail, this results in the following map when clicked:

adam

Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.

  • Kasper,
    I just started with the maps on SSRS of SQL 2008 R2. I want to drill down but did not get it working. Can you provide me the Stored procedure to use data awareness of the reporting control?

    Thanks for your time

    Gr. André

  • Kasper de Jonge

    @André van de Graaf
    Hi André,

    What you should do is create one SP that you use as datasource but that returns locations per ID. The first time you open a report and call the SP (with a empty parameter) you should return the top level of locations (top 50 citys in NL) with a ID of a city. When you click on the ID you can return the top 50 locations per City. The reporting control zooms in based on the data it gets. This gives you one zoom level. You can when you like create multiple levels by using a case and adding and extra parameter type.

    sample SP:

    create proc locdata
    @locId int,
    as

    if @locId <= 0 then BEGIN select top 50 location, id , sum(amount) from demotable group by location, id END ELSE BEGIN select top 50 location, id , sum(amount) from demotable where id = @locId group by location, id END I hope this clear things up for you ? Greetings, Kasper

  • Thanks Kasper for your time. I got this working and now i’m looking for more zoom levels. WorldCountryState

  • Daniel Reyes

    how to resolv this error:

    estado HTTP 407:Proxy Autentication Required(The ISA Server requires autorization to fulfill the request. Acces to the Web Proxy filter is Denied.)

    • Kasper de Jonge

      Hi Daniel,

      I guess you haven’t got access to the bing maps from your machine, you should contact your proxy admin to let them make contact from your IP to Bing maps.

      Kasper

  • YAM

    Hi Kasper and all,

    I just started with SSRS and am exited about it. At the same time am working on a demo including maps and all the possible fancy stuff I could add to it.

    I am now wondering where could I download the map for the Netherlands, could you pls give me some pointers.

    I would also appreciate if you guys give me some good hints to start with it.

    Hope to hear from you soon and a nice weekend in advance,

    Yesso.

  • Kasper de Jonge

    @YAM
    Check out this website for all kinds of maps:
    http://www.cdc.gov/epiinfo/europe.htm

    Just download it, and make sure your dataset contains identical geographical entities as the map does. Just use the map wizard in SSRS.

    Kasper

  • YAM

    Hi Kasper, thanks a lot for your answer. I have downloaded the Dutch map and will start doing some stuff with it. I will keep you in the loop as soon as I get something ready for my demo. Cheers,
    YAM.
    @Kasper de Jonge

  • venkatesh g.r

    Hi Kasper,

    Can i implement a Bing like Map using SSRS 2008 R2 Map control.For example when i select India as country.I get the list of states in India.When i select particular state i get more state map in more detail.

  • Arun

    Hi Kasper,

    Can i implement a Bing like Map using SSRS 2008 R2 Map control.For example when i select India as country.I get the list of states in India.When i select particular state i get more state map in more detail.

  • any body have .shp file having all city points
    of india map…. i have .shp with city only for north india ,i all the city