Using Google Static Maps API to Facilitate Simple Mapping

We recently had a need to implement a basic map plotting the top 10 zip codes that sales leads originated from within a specified date range.

Here is how we implemented a quick and dirty solution using the Google Static Maps API.

Here is some sample data:

create table #Temp_MapZipCodes (Latitude float, Longitude float)
insert into #Temp_MapZipCodes (Latitude, Longitude)

select 40.1479,	-79.8301
UNION
select 36.5958,	-121.884
UNION
select 37.6637,	-121.873
UNION
select 38.6868,	-121.352
UNION
select 38.4428,	-122.751
UNION
select 28.6815,	-81.2786
UNION
select 25.4773,	-80.4832
UNION
select 30.3722,	-90.0563
UNION
select 30.2985,	-89.8175
UNION
select 41.3315,	-96.0513

During normal operation, this temp table is populated with the top 10 most common zip codes which had sales leads within a specified time period.

In the report, I have a dataset which generates the above dataset, and uses the following to return a pipe-delimited set of latitude/longitude values, which is the format required by the Google Static Maps API.

select Substring(
		(select cast(latitude as varchar) + ',' + cast(longitude as varchar) + '|' from #Temp_MapZipCodes for XML Path ('')) 
		,1, Len(
				(select cast(latitude as varchar) + ',' + cast(longitude as varchar) + '|' from #Temp_MapZipCodes for XML Path (''))
				) - 1
)
as 'Markers'

I am sure there are probably better ways to accomplish this, but I find the above solution easy to implement and provides me with exactly what I need.

In order to use these to generate a map, I added an External image to my report:

Access to the Google Static Maps API is done via a constructed URL string. In this case, we are going to put this value as the 'Use this image:' value. 

Per the API specs, the URL begins with: http://maps.google.com/maps/api/staticmap?

The first parameter we use is markers, which is a string of latitude <comma> longitude values separated by the pipe character |.

The additional parameters I used were:

size=640x640 <- 640x640 is the largest size available and worked for our needs.
sensor=false <- This is used to specify if your application is using a sensor, such as a GPS-locator to determine user location. Since we are providing a list of markers to chart the map, this doesn't necessarily apply to us, however the API states that a value of true or false must be specified.

There are additional parameters you can use to specify the type of map, zoom levels, centering options, image type returned, custom style information, etc. but for our purposes, the defaults work for everything except markers, size and sensor.

The end result for the 'Use this image:' value:

With these in place, running the report results in the following:

All the ZipCodes are represented and with the defaults in place, the map will determine the appropriate centering and zoom level for your specified markers.

More information on the Google Static Maps API

I hope someone might find this useful.