Put addresses on a map as markers get it now
We've already looked at how to geoCode addresses using various APIs. Now lets look at using Google Maps to put markers on a Map using an Excel worksheet as input. As usual you can find the completed example in the googleMapping.xlsm download. Before running this please check the parameter worksheet and change the filename value for the place that the application will generate a web page to something relevant for your machine. Look for the filename in the Marker html section of the Parameter Worksheet.
Putting markers on a map for our 'Customer Sheet'
If you've been following along with the geoCoding examples you will already be familiar with our test data, which we've now already geocoded from input addresses of various levels of accuracy. You can also customize the markers and add heatmaps and circles - see Overlaying circles and heatmaps
The objective of this section is to plot this sheet on a map, with a marker for each row. In addition we are also going to have a popup box that will show the customer name and the full address. The final map using our test data looks like this.
Parameters
Like many of the examples on this site, we are using parameters to drive the behavior of the code. Many of the parameters used in the geoCoding examples are used, but we have a couple of new sections in the parameter worksheet.
4 pieces of data are needed for placing markers on the map are given under the 'Markers' parameter block. This instructs the application which columns in the table contain these values.
Preparing the Content
The title Marker Parameter specifies the column containing the value you want shown in reaction to a mouse hover over the map marker. In this case, we want to show the customer name. The content marker parameter specifies the column containing the value you want to show in the pop-up box in reaction to a click on the map marker. In this case, we want to create a description of the customer and his address, and we can use html to construct it if necessary. You cab see that I've added a content field in our example application that looks like the below, but you can of course put whatever you want in there. A blank content field will mean that you don't need a pop up box for that marker.
="<b>" & B2 & "</b><br>" & SUBSTITUTE(L2,",","<br>") & "<br>"which translates to
<b>Acme</b><br>1600 Amphitheatre Pkwy<br> Mountain View<br> CA 94043<br> USA<br>Customizing the generated web page
The application creates , and displays, a web page in the place you specify in the filename parameter of the Marker Html section of the Parameter WorkSheet.
The other parameters in this section contain the Code needed to generate this web page. Header, main and functions are largely javaScript and are responsible for interpreting the Excel data and plotting it on the map. You can change these if you want of course, but only if you want to change the functionality of the application.
The body section however is likely going to need to be customized to design whatever web page you want to create. Right now it just looks like this
<body onload="initialize()">
<div id="comboquicklink" class = "mcquicklink">Quick Links</div>
<div id="combocategory" class = "mccategory"></div>
<div id="heading" class ="mcheading">Excel and Google Mapping Integration</b>
<a href="http://rambings.mcpher.com">
ramblings.mcpher.com</a></div>
<div id="map_canvas" style="width: 100%; height: 90%"></div>
</body>
As long as you retain the
<body onload="initialize()"> (load up the map when the page loads) and <div id="map_canvas"></div> (the place on the web page to contain the map), you can tailor this body parameter as as much as you like. Compare Google Maps to others
I've implemented this exact same thing on other APIs as below. Take a look to compare. My recommendation is to stick with Google Maps.
The generated html file
When you execute the code for marking up the customer worksheet, an html file is generated in the location you have specified and IE is kicked off to display it. (You may get a security warning about running local content on IE depending on your settings). The generated file from our example application looks like this. It is just the head, main, body and functions code from the Parameter sheet, plus the function mcpherDataPopulate(), which is generated at run time and contains the Excel data that will be displayed on the map. By default, the map will be displayed at zoom level 2, but you can change that with a Url parameter, for example (we use file:// instead of http:// because the file has been generated locally, but you may want to host it on a web site, or use localhost if you are running a server on your pc)
Finally, the map will be centered around the first row in your data and will be a standard google Map. If you wanted to change that default (for example to be centered around your head office, with a satellite view), then you would need to change the code section below of the main parameter
var myOptions = { center: new google.maps.LatLng(mcpherData.cJobject[0].lat, mcpherData.cJobject[0].lng), mapTypeId: google.maps.MapTypeId.ROADMAPGenerated html file contents
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<style type="text/css">
html { height: 100% }
body {
height: 100%;
margin: 0;
padding: 0 ;
color: Gray ;
background-color: WhiteSmoke;
}
#map_canvas { height: 100%}
.mcquicklink {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mccategory {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mcheading {
display: inline;
font-family: sans-serif;
}
</style>
<script type="text/javascript"
src="http://maps.googleapis.com/maps/api/js?sensor=false">
</script>
<script type="text/javascript">
function mcpherDataPopulate() {
var mcpherData = {
"framework":{ "control": { "zoomlevelonselect":10, "resizeboundingbox": true}},
"cJobject":[ {
"title":"Rosemount theatre",
"content":"\<b\>Rosemount theatre\</b\>\<br\>5400 North River Road\<br\> Rosemont\<br\> IL 60018\<br\> USA\<br\>",
"lat":"41.9788038",
"lng":"-87.8624106",
"color":"#0000FF",
"size":"74848.2063701911",
"category":[ {
"city":"Rosemont"
},
{
"country":"United States"
}
],
"image":""
},
{
"title":"UNO Lakefront Arena",
"content":"\<b\>UNO Lakefront Arena\</b\>\<br\>6801 Franklin Avenue\<br\> University of New Orleans-East Campus\<br\> New Orleans\<br\> LA 70122\<br\> USA\<br\>",
"lat":"30.030512",
"lng":"-90.04931",
"color":"#00728D",
"size":"112837.916709551",
"category":[ {
"city":"New Orleans"
},
{
"country":"United States"
}
],
"image":""
},
{
"title":"Intex",
"content":"\<b\>Intex\</b\>\<br\>Nankokita\<br\> Suminoe Ward\<br\> Osaka\<br\> Osaka Prefecture 559-0034\<br\> Japan\<br\>",
"lat":"34.6381228",
"lng":"135.4253848",
"color":"#00728D",
"size":"112837.916709551",
"category":[ {
"city":"Suminoe Ward"
},
{
"country":"Japan"
}
],
"image":""
},
{
"title":"olympic park",
"content":"\<b\>olympic park\</b\>\<br\>Olympic Park\<br\> Bangi-dong\<br\> Seoul\<br\> South Korea\<br\>",
"lat":"37.5198209",
"lng":"127.1227901",
"color":"#FF5C00",
"size":"298541.066072092",
"category":[ {
"city":"Seoul"
},
{
"country":"South Korea"
}
],
"image":""
},
{
"title":"aktiv square",
"content":"\<b\>aktiv square\</b\>\<br\>Bang Yai\<br\> Nonthaburi\<br\> Thailand\<br\>",
"lat":"13.8973058",
"lng":"100.3910926",
"color":"#00936C",
"size":"123607.744647421",
"category":[ {
"city":""
},
{
"country":"Thailand"
}
],
"image":"http://maps.google.com/mapfiles/marker_orange.png"
},
{
"title":"red square",
"content":"\<b\>red square\</b\>\<br\>Moscow\<br\> Russia\<br\>",
"lat":"55.7512419",
"lng":"37.6184217",
"color":"#FF0000",
"size":"328976.232123977",
"category":[ {
"city":"Moscow"
},
{
"country":"Russia"
}
],
"image":"http://maps.google.com/mapfiles/marker_orange.png"
},
{
"title":"heineken hall",
"content":"\<b\>heineken hall\</b\>\<br\>ArenA Boulevard 590\<br\> Bullewijk\<br\> 1101 DS Amsterdam-Zuidoost\<br\> The Netherlands\<br\>",
"lat":"52.3123574",
"lng":"4.9441509",
"color":"#001BE4",
"size":"83682.8387188401",
"category":[ {
"city":"Amsterdam-Zuidoost"
},
{
"country":"The Netherlands"
}
],
"image":""
}
]};
return mcpherData; };
function initialize() {
mcpherData = mcpherDataPopulate();
if (mcpherData.cJobject.length > 0) {
mcpherData.cJobject.sort(function(a, b) {
return a.title.toLowerCase() < b.title.toLowerCase() ? -1 : (a.title.toLowerCase() > b.title.toLowerCase() ? 1 : 0);
});
var myOptions = {
center: new google.maps.LatLng(mcpherData.cJobject[0].lat, mcpherData.cJobject[0].lng),
mapTypeId: google.maps.MapTypeId.ROADMAP
};
// get parameters if any
var qparams = mcpherGetqparams();
var cj = mcpherData.cJobject;
var bounds = new google.maps.LatLngBounds();
for (var i = 0; i < cj.length; i++) {
bounds.extend(new google.maps.LatLng(cj[i].lat, cj[i].lng));
}
if (!qparams['zoom']) qparams['zoom'] = 2;
myOptions['zoom'] = parseInt(qparams['zoom']);
// create the map
gMap = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
gMap.fitBounds(bounds);
// add the excel data
for (var i = 0; i < cj.length; i++) {
mcpherAddMarker(cj[i]);
}
// Set up combox
category();
quickLink(-1);
makeFlightGroups();
initialCategory();
}
}
// flightpath stuff
function toType(obj) {
return ({}).toString.call(obj).match(/\s([a-zA-Z]+)/)[1].toLowerCase()
}
function isArray(arg) {
return toType(arg) == 'array';
}
function createFlightGroups() {
var flightGroups = [];
var cj = mcpherData.cJobject;
var fg;
for (var i = 0; i < cj.length; i++) {
if (fg = cj[i].flightGroup) {
if (flightGroups.indexOf(fg) == -1) {
flightGroups.push(fg);
}
cj[i].flightGroupIndex = flightGroups.indexOf(fg);
} else cj[i].flightGroupIndex = -1;
}
return flightGroups;
}
function initialCategory() {
// sets initial category using the first select element
var cj = mcpherData.cJobject;
var c, p;
var combos = getAllCombos();
if (combos.length > 0) {
var cats = createCategories(combos[0]);
for (var i = 0; i < cj.length; i++) {
if (c = cj[i].startCategory) {
if ((p = cats.indexOf(c)) != -1) {
var elem = document.getElementById('selectcategory0');
elem.value = p;
dealWithCategories(elem);
return p;
}
}
}
}
return -1;
}
// this is because IE doesnt have indexOf...
if (!Array.indexOf) {
Array.prototype.indexOf = function(obj, start) {
for (var i = (start || 0); i < this.length; i++) {
if (this[i] == obj) {
return i;
}
}
return -1;
}
}
function resetBoundingBox(force) {
var z = mcpherData.framework && mcpherData.framework.control
&& mcpherData.framework.control.resizeboundingbox ?
mcpherData.framework.control.resizeboundingbox : false;
if((z || force) && !vMap) {
// dont do for vizmap apps - reset bounding box to visible items
var cj = mcpherData.cJobject;
var bounds = new google.maps.LatLngBounds();
for (var i = 0; i < cj.length; i++) {
if (cj[i].marker.visible)bounds.extend(new google.maps.LatLng(cj[i].lat, cj[i].lng));
}
gMap.fitBounds(bounds);
}
}
function makeFlightGroups() {
var flightGroups = createFlightGroups();
var cj = mcpherData.cJobject;
for (var j = 0; j < flightGroups.length; j++) {
var flightCoords = [];
var color = "#FF0000";
for (var i = 0; i < cj.length; i++) {
if (j == cj[i].flightGroupIndex) {
flightCoords.push(cj[i].marker.position);
if (cj[i].flightColor) color = cj[i].flightColor;
}
}
if (flightCoords.length) {
flightGroups[j].flightPath = new google.maps.Polyline({
path: flightCoords,
strokeColor: color,
strokeOpacity: 1.0,
strokeWeight: 2,
map: gMap
});
}
}
return flightGroups;
}
function mcpherAddMarker(cj) {
var p = new google.maps.LatLng(cj.lat, cj.lng);
cj.circle = null;
if (cj.size) {
var circle = {
strokeColor: cj.color,
strokeOpacity: 0.8,
strokeWeight: 1,
fillColor: cj.color,
fillOpacity: 0.20,
map: gMap,
center: p,
radius: parseFloat(cj.size),
};
var drawCirle = new google.maps.Circle(circle);
cj.circle = drawCirle;
}
var marker = new google.maps.Marker({
position: p,
map: gMap,
title: cj.title
});
cj.marker = marker;
cj.infowindow = null;
if (cj.image) marker.setIcon(cj.image);
if (cj.content) {
cj.infowindow = new google.maps.InfoWindow({
content: cj.content
});
google.maps.event.addListener(cj.marker, 'click', function() {
cj.infowindow.open(gMap, cj.marker);
adjustZoom(cj);
});
}
return cj.marker;
}
function adjustZoom(cj){
var z = mcpherData.framework && mcpherData.framework.control
&& mcpherData.framework.control.zoomlevelonselect ?
parseInt(mcpherData.framework.control.zoomlevelonselect) : null;
if (z) {
if (gMap.getZoom() == z) {
resetBoundingBox(true);
}
else {
gMap.setZoom(z);
gMap.setCenter(cj.marker.getPosition());
}
}
}
function mcpherGetqparams() {
var qparams = new Array();
var htmlquery = window.location.search.substring(1);
var htmlparams = htmlquery.split('&');
for (var i = 0; i < htmlparams.length; i++) {
var k = htmlparams[i].indexOf('=');
if (k > 0) qparams[htmlparams[i].substring(0, k)] = decodeURI(htmlparams[i].substring(k + 1));
return qparams;
}
}
function dealWithQuickLink(selValue) {
if (vMap) {
var nextSpot = vMap.spots[selValue];
vMap.gotoAnotherSpot(vMap.currentSpot,nextSpot);
if (vMap.provider=='maps') nextSpot.createInfoWindow(0);
}
else {
var cj = mcpherData.cJobject[selValue];
if (cj.infowindow) {
cj.infowindow.open(gMap, cj.marker);
}
adjustZoom(cj);
return cj.infowindow;
}
}
function findMySpot(cj) {
// return the spot to which this cj belongs
if(vMap) {
for (var i=0; i < vMap.spots.length ; i++ ) {
if ( cj.SpotID === vMap.spots[i].spotId) return (vMap.spots[i]);
}
return null;
}
else
return cj;
}
function dealWithCategories() {
var combos = getAllCombos();
// start by not showing anything, except where theres no categories
var cj = mcpherData.cJobject;
for (var j = 0; j < cj.length; j++) {
var mySpot = findMySpot(cj[j]);
mySpot.marker.setVisible(combos.length == 0);
if (mySpot.circle) {
mySpot.circle.setVisible(combos.length == 0);
}
}
// need appear in each category
for (var j = 0; j < cj.length && combos.length > 0; j++) {
var show = [];
for (var i = 0; i < combos.length ; i++) {
var selElem = document.getElementById('selectcategory' + i);
if (selElem.value != 0) {
// we have a filter operating
var cats = createCategories(combos[i]);
var target = cats[selElem.value];
if (vMap) {
var c = cj[j];
if (c.hasOwnProperty(combos[i])) {
if(c[combos[i]] == target) show[i]=true ;
}
}
else {
for (var k = 0; k < cj[j].category.length ; k++) {
for (m in cj[j].category[k]) {
if (m == combos[i]) {
if(cj[j].category[k][m] == target)show[i]=true;
}
}
}
}
}
else {
show[i]= true;
}
}
// show it?
var x=0;
for (var k=0; k < combos.length; k++ ) {
if (show[k] === true) x++;
}
if (x == combos.length) {
var mySpot = findMySpot(cj[j]);
mySpot.marker.setVisible(true);
if (mySpot.circle) {
mySpot.circle.setVisible(true);
}
}
}
// reset to show only filtered spots
quickLink();
}
function quickLink(selCategory) {
var comboElem = document.getElementById('comboquicklink');
if (comboElem) {
if (selCategory == -1) { //first time in
var selElem = document.createElement('select');
selElem.id = "quickLinks";
} else {
var selElem = document.getElementById('quickLinks');
selElem.options.length = 0;
}
// depends on type of app
var cj = vMap ? vMap.spots : mcpherData.cJobject ;
mcpherAddEvent(selElem, "change", function() {
dealWithQuickLink(selElem.value);
}, false, true);
for (var i = 0; i < cj.length; i++) {
// only show visible spots
var workit = true;
if (cj[i].marker) workit = cj[i].marker.visible;
if (workit) {
var o = document.createElement('option');
o.text = cj[i].title;
o.value = i;
selElem.value = o.value;
try {
selElem.add(o, null);
} catch (error) {
selElem.add(o);
}
}
}
comboElem.appendChild(selElem);
}
resetBoundingBox();
}
function createCategories(categoryName) {
var cats = [];
var cj = mcpherData.cJobject;
for (var i = 0; i < cj.length; i++) {
// find the matching object
if (vMap) {
if(cj[i].hasOwnProperty(categoryName)) {
if (cats.indexOf(cj[i][categoryName]) == -1) cats.push(cj[i][categoryName]);
}
}
else {
for (var j = 0; j < cj[i].category.length; j++) {
if (cj[i].category[j].hasOwnProperty(categoryName)) {
if (cats.indexOf(cj[i].category[j][categoryName]) == -1) cats.push(cj[i].category[j][categoryName]);
}
}
}
}
cats.sort().splice(0, 0, 'all categories');
return cats;
}
function category() {
var comboElem = document.getElementById('combocategory');
if (comboElem) {
var combos = getAllCombos();
for (var j = 0; j < combos.length; j++) {
var selElem = document.createElement('select');
selElem.id = "selectcategory" + j;
var cats = createCategories(combos[j]);
mcpherAddEvent(selElem, "change", function() {
dealWithCategories(selElem);
}, false, true);
for (var i = 0; i < cats.length; i++) {
var o = document.createElement('option');
o.text = cats[i];
o.value = i;
try {
selElem.add(o, null);
} catch (error) {
selElem.add(o);
}
}
var d = document.createElement('span');
var t = document.createTextNode(combos[j]);
d.appendChild(t);
d.appendChild(selElem);
comboElem.appendChild(d);
}
}
}
function getAllCombos() {
var combos = [];
if (vMap) {
for (var i = 0; vMap.framework.spots.categories && i < vMap.framework.spots.categories.length; i++)
combos.push(vMap.framework.spots.categories[i]);
}
else {
var cj = mcpherData.cJobject;
for (var i = 0; i < cj.length; i++) {
if (isArray(cj[i].category)) {
for (var j = 0; j < cj[i].category.length; j++) {
for (k in cj[i].category[j])
if (combos.indexOf(k) == -1) {
combos.push(k);
}
}
}
}
}
return combos;
}
function mcpherAddEvent(o, e, f, b, complain) {
// because IE is different
if (o.addEventListener) return (o.addEventListener(e, f, b));
else if (o.attachEvent) return (o.attachEvent('on' + e, f));
else if (complain) alert('browser doesnt support events');
return (null);
}
var mcpherData;
var gMap;
var vMap;
</script>
</head>
<body onload="initialize()">
<div id="comboquicklink" class = "mcquicklink">Quick Links</div>
<div id="combocategory" class = "mccategory"></div>
<div id="heading" class ="mcheading">Excel and Google Mapping Integration</b>
<a href="http://rambings.mcpher.com">
ramblings.mcpher.com</a></div>
<div id="map_canvas" style="width: 100%; height: 90%"></div>
</body>
The Vba code
Most of the code used here is already covered in Creating Google Maps Layers from Excel Data, json, Data Manipulation Classes, and Google Visualization but here is the specific code for creating the html file from the Customer Worksheet.
' This will take the geocoded data and mark it
Option Explicit
'for more about this
' http://ramblings.mcpher.com/Home/excelquirks/classeslink/data-manipulation-classes
'to contact me
' http://groups.google.com/group/excel-ramblings
'reuse of code
' http://ramblings.mcpher.com/Home/excelquirks/codeuse
Public Sub genericMarking(paramName As String, markerHtml As String, _
Optional eOutput As eOutputMarkers = eOutputHtml)
Dim dSets As cDataSets, dc As cCell, job As cJobject, fName As String
Dim dr As cDataRow, vc As cCell, a As Variant, i As Long
Set dSets = dSetsSetup(paramName)
If dSets Is Nothing Then Exit Sub
' check that we have the required marker fields
With dSets
For Each dc In .dataSet(cMarkers).column("Column Name").rows
If .dataSet(cMarkers).isCellTrue(dc.row, "required") Then
With .dataSet(cMaster).headingRow
If Not .validate(True, dc.toString) Then Exit Sub
End With
End If
Next dc
End With
' we have it all now create a job
Set job = New cJobject
With job.init(Nothing)
' add framework/control parameters
With .add("framework").add("control")
For Each dr In dSets.dataSet(cControl).rows
.add dr.value(cControl), dr.value(cControlValue)
Next dr
End With
With .add("cJobject").addArray
For Each dr In dSets.dataSet(cMaster).rows
With .add
For Each dc In dSets.dataSet(cMarkers).column(cMarkers).rows
If Not dSets.dataSet(cMarkers).isCellTrue(dc.row, "array") Then
Set vc = dr.cell(dc.parent.cell("Column Name").toString)
If Not vc Is Nothing Then
.add dc.toString, vc.toString
End If
Else
a = Split(dc.parent.cell("Column Name").toString, ",")
With .add(dc.toString).addArray
For i = LBound(a) To UBound(a)
Set vc = dr.cell(CStr(a(i)))
If Not vc Is Nothing Then
.add.add CStr(a(i)), vc.toString
End If
Next i
End With
End If
Next dc
End With
Next dr
End With
End With
' now create the html file and browse to it
fName = dSets.dataSet(markerHtml).cell("filename", "code").toString
Select Case eOutput
Case eOutputHtml
If openNewHtml(fName, generateHtml(job, dSets, markerHtml)) Then
pickABrowser dSets, fName, True
End If
Case eOutputKML
If openNewHtml(fName, generateKML(job, dSets)) Then
pickABrowser dSets, fName, True
End If
Case Else
Debug.Assert False
End Select
dSets.tearDown
Set dSets = Nothing
End Sub
Private Function generateHtml(job As cJobject, dSets As cDataSets, mHtml As String) As String
Dim s1 As String
' the deserialized data
s1 = "function mcpherDataPopulate() { " & vbCrLf & _
"var mcpherData = " & job.serialize(True) & ";" & vbCrLf & _
"return mcpherData; };"
With dSets.dataSet(mHtml)
generateHtml = _
.cell("header", "code").toString & vbCrLf _
& s1 & vbCrLf _
& .cell("main", "code").toString & vbCrLf _
& .cell("catfunctions", "code").toString & vbCrLf _
& .cell("functions", "code").toString & vbCrLf _
& .cell("body", "code").toString & vbCrLf
End With
End Function
Private Function generateKML(job As cJobject, dSets As cDataSets) As String
Dim s1 As String
Dim jo As cJobject
s1 = vbNullString
For Each jo In job.children
s1 = s1 & generatePlaceMark(jo)
Next jo
generateKML = _
"<?xml version='1.0' encoding='UTF-8'?>" & vbLf & _
"<kml xmlns='http://www.opengis.net/kml/2.2'>" & vbLf & _
tag("Document", s1) & "</kml>"
End Function
Private Function generatePlaceMark(job As cJobject) As String
' convert jSon item to KML
With job
generatePlaceMark = _
tag("Placemark", _
tag("name", .child("title").toString) & _
tag("description", "<![CDATA[" & .child("content").toString & "]]>") & _
tag("Point", _
tag("coordinates", .child("lng").toString & "," & .child("lat").toString) & ",0"))
End With
End Function
Private Function tag(tagName As String, Optional item As String = vbNullString) As String
tag = "<" & tagName & ">" & vbLf & item & vbLf & "</" & tagName & ">"
End Function