Overlaying circles and heatmaps

Overlaying shapes on markers get it now

This article will cover 3 main topics
  • How to add a circle to a marker
  • how to work out a colorramp
  • how to use a different icon

Try it out first ?

If you just want a to try it, then you can download an example application (googleMapping.xlsm) and come back here later. First you should take a look at Adding markers to Google Maps from Excel since this is just an enhancement of that.

How to specify circle sizes and colors

As in the other mapping examples, javaScript procedures are created based on the Excel data, which are then executed to create a map. Adding circles is simply building on what has been already covered in Adding markers to Google Maps from Excel. 

Here is an example

In the VenueMaster tab of the googleMapping.xlsm workbook, you will see two columns - markerColor and markerSize. These are used to provide the data needed to plot the circles. You can use whatever mechanism you want to create the relative size data. 

Specifying the color

The color is specified as standard html color code. This is similar, but not the same as, RGB codes. See VBA rgb to htmlRGB for how to convert. Quite often using a color ramp - see Charts and color ramps, is an effective method of choosing overlay colors. You can use the Color ramp library to do this, and it can be entered as an Excel formula. In the example above, the markerColor column has this formula - with the color being calculated according to its position relative to the maximum and minimum size. 

=rgbToHTMLHex(rampLibraryRGB("heatmap",MIN($F$2:$F$8),MAX($F$2:$F$8),F2))

Activating circles

The module for generating this map is exactly the same as in Adding markers to Google Maps from Excel, but in the geoCoding Parameters Sheet we have introduced this, where the color and size parameter are given the names of the columns that hold the values to use in order to plot the circle.

Changing the properties of the circle

By default, the circle properties are

  if (sz) {
      var circle =  {
         strokeColor: color,
         strokeOpacity: 0.8,
         strokeWeight: 1,
         fillColor: color,
         fillOpacity: 0.20,
         map: gMap,
         center: p,
         radius: parseFloat(sz), 
      };

Changing these is just a matter of editing the above, which is in the functions row of the marker html parameter block in the geoCodingParamers worksheet. For advice on calculating sizes, see the size of circles on Google Maps.

Using squares instead of circle overlays

You can use a square instead of a circle by changing a parameter on the geocoding parameters sheet. Just change the shape parameter to 'square'


The radius for the circle is used as a 45 degree half diagonal for the square, so the apparent coverage would look the same as an equivalent sized circle. The same properties are applied here as for the circles and the colors you select on your sheet are passed through. 


Using alternative Markers

You can also specify alternative markers. In the same Markers parameter block, just specify an alterative image to use in the column that corresponds to the markerImage parameter.

You might want to check out http://mapicons.nicolasmollet.com/ or http://mabp.kiev.ua/2010/01/12/google-map-markers/ for ready made images to use.

See Creating a VizMap Application for how to do the same thing with more complex applications in both google maps and google earth.
In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available, or read more about Charts and color ramps, or Mapping and adding markers to maps

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.


Comments