In part 1 of this 2 part blog I introduced the SQL 2008 R2 Map report control, and ran through a quick example of one of the Map types. In this post we will take a look at another of the Map types, the Bubble marker map which is used to display point data.

To illustrate this map type I will need some point data, so I decided to record the locations of some of the traffic lights (signals) I spend time waiting at on my way to work. I recorded the time spent at the lights and my level of frustration on a high-tech notepad and the GPS coordinate on my iPhone using GPSLight (obviously while stopped).  I then loaded the data into a simple SQL table with a geography field which stores the latitude and longitude of the intersections, using a straight forward SQL script. Obviously working with SQL spatial data is a massive topic to cover in it’s own right so I will just point out the function you need to load the lat, long data to the geography field:

INSERT INTO TrafficLight
(WaitTime, Location)
SELECT 47, geography::Point(-27.587788, 153.085521, 4326)

The geography point function takes Lat Long and the second parameter is the Spatial Reference IDentifier (you can get a full definition here) but 4326 is the SRID for mapping WGS 84 the standard used for most standard GPS devices (like the iPhone) and GPS mapping apps including Bing and Google maps.

Enough chitty-chat cut to the code (well the series of screen shots in this case).

Bubbles and Bing

So to get started we need to  fire up a new report into to our reports project and add a new Map to the detail section.

This time select the SQL Server spatial query as the datasource, this will form the base spatial information as well as the two analytical data points for the report.

 

Next follow the wizard to add a new data source and specify your SQL query to retrieve the dataset, notice the POINT data specified in the last column.

Now we have our data we can see our boring little points floating on a white map portal, that is until we hit the magic Bing Maps layer, and select the hybrid or map view. See told you it was awesome, now we see our points have some context.

So now pick the map type (Bubble marker) and select your dataset.

     

Now to configure the analytic data points, the Bubble marker map can show 2 points of analytical data by changing the size and colour of the bubbles. For this example I will base the bubble size on the wait time spent at the lights and the colour will represent how frustrated I was at the time.  Then nothing left to do other than hit the finish button and we get a sweet new map on our report.

 

So that about wraps it up

There is a lot more to this control and the R2 release than just it’s initial bucket full of Radness, like the ability to apply layers on top of each other or the fact that the Bing maps layer renders out to export formats like PDF. I for one think this is the kind of stuff that will really start to change the way people report on their data.

As more and more mobile devices include some kind of location service like GPS, cell triangulation or alien lasers, the availability of spatial data will increase at light speed. How to use that data in a useful way for business is the next main challenge and maybe a topic for another blog another day.

 As this is my first contribution of bloggy-ness to the interwebs I would appreciate your comments and to a much lesser extent your criticisms :)