Deprecated
Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap applications I had created. I didn’t remove the entire topic as I thought some of the code may still be useful with some modifications.
Creating a VizMap application straight out of Excel
The components of VizMap Application
The GeoCodingParameters worksheet – common to all applications in the same work book. You should be able to use the one from the example with little or no modification. Its main purpose is to define how to handle geoCoding withvarious mapping providers. The xxxParameters worksheet – Specific to your application. Customize one of the application parameter worksheets in the example. You need one of these for each application. The Mapping data – The required columns of the input data should have been described in the VizMap Dictionary in your parameters worksheet. It is assumed that there will be one record for every reportable item. There will be a marker created for every unique SpotID encountered. There may be more than one item per Map marker. The data will already have been geoCoded. The input data Worksheet to the VizMap generation process is defined as the ‘Join’ parameter of the ‘Name’ Parameter block in you parameter sheet as per the example below. We will look into the purpose of other parameter values in Joining transactions to master data
A VBA module to generate the app – you will probably assign this to a command button in your workbook somewhere. The only thing needed is an argument with the name of the parameter sheet. It is also possible to create an End to End application which executes geoCoding and matching transaction data to master data before generating the application in one operation – but more of that later.
Public Sub vizVenues()
googleMarkingViz "VenuesParameters"
End Sub
All this together generates an html file which is a self contained vizMap application. It also includes two javaScript functions which return the jSon framework (mcpherGetFramework) describing the treatment of the data and (mcpherGetData) which contains the data values.
In the organization example I had created (deprecated now), the framework module looked like this.
//---Excel generated framework
function mcpherGetFramework ()
{ return (
{
"framework":{
"dictionary":{
"Latitude":"number",
"Longtitude":"number",
"Title":"string",
"SpotID":"number",
"Content":"string",
"Country Code":"string",
"Country":"string",
"State":"string",
"City":"string",
"Clean Address":"string",
"Office ID":"number",
"Office":"string",
"Office Photo":"string",
"Employee ID":"number",
"Manager ID":"number",
"Employee":"string",
"Photo":"string",
"Span":"number",
"Manager":"string",
"Function":"string",
"Function ID":"number",
"Manager Photo":"string"
},
"measures":{
"Span":"count"
},
"control":{
"browser":"default",
"provider":"google earth",
"tilt":"60",
"range":"500",
"flyspeed":"0.9",
"flyrange":"200",
"flyincrement":"1",
"imagewidth":"80",
"placemarkscale":"4",
"adjustlightfortimeofday":"yes",
"twitterradius":"20mi",
"twitterlanguage":"en",
"twittermaxtweets":"10"
},
"tabs":{
"Employee":{
"filter":[
"Employee ID"
],
"chart":[
"Employee",
"Manager",
"Function"
],
"table":[
"Employee",
"Office",
"Function",
"Manager"
],
"twitter":[
"Employee"
],
"image":"Photo",
"charttype":"orgchart"
},
"Office":{
"filter":[
"Office ID"
],
"chart":[
"Employee",
"Manager",
"Function"
],
"table":[
"Office",
"Employee",
"Function",
"Manager"
],
"twitter":[
"Title"
],
"image":"Office Photo",
"charttype":"orgchart"
},
"Function":{
"filter":[
"Function ID"
],
"chart":[
"Employee",
"Manager",
"Function"
],
"table":[
"Function",
"Office",
"Employee",
"Manager"
],
"twitter":[
"Manager"
],
"image":"Photo",
"charttype":"orgchart"
},
"Manager":{
"filter":[
"Manager ID"
],
"chart":[
"Employee",
"Manager",
"Function"
],
"table":[
"Manager",
"Office",
"Function",
"Employee"
],
"twitter":[
"Manager"
],
"image":"Manager Photo",
"charttype":"orgchart"
},
"Enterprise":{
"chart":[
"Employee",
"Manager",
"Function"
],
"table":[
"Country",
"City",
"Office",
"Function",
"Manager",
"Employee"
],
"twitter":[
"Title"
],
"image":"Office Photo",
"charttype":"orgchart"
}
},
"elements":{
"content":{
"position":"2",
"show":"yes"
},
"filters":{
"position":"0",
"show":"no"
},
"image":{
"position":"3",
"show":"yes"
},
"checks":{
"position":"8",
"show":"yes"
},
"table":{
"position":"0",
"show":"no"
},
"summary":{
"position":"0",
"show":"no"
},
"chart":{
"position":"7",
"show":"yes"
},
"title":{
"position":"1",
"show":"yes"
},
"navigate":{
"position":"11",
"show":"yes"
},
"twitter":{
"position":"9",
"show":"yes"
}
},
"spots":{
"earth marker":"Photo",
"maps marker":""
}
}
}
) ; }
and the data function looks like this
//---Excel generated data
function mcpherGetData ()
{ return (
{
"data":{
"cJobject":[ {
"Latitude":"40.755612",
"Longtitude":"-73.982025",
"Title":"Sofitel New York",
"SpotID":"1",
"Content":"<b>Sofitel New York</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"45 W 44th St, New York, NY 10036, USA",
"Office ID":"1",
"Office":"Sofitel New York",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"1",
"Manager ID":"10",
"Employee":"Dominic Strauss Khan",
"Photo":"http://4.bp.blogspot.com/-dTBHZ_UaiwQ/TdJm57L2NCI/AAAAAAAABvU/NGMHtfZXkxw/s320/IMF+chief+Dominique+Strauss-Kahn+will+next+appear+in+court.jpg",
"Span":"1",
"Manager":"Vikram Pandit",
"Function":"Employee Relations",
"Function ID":"3",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"11",
"Manager ID":"10",
"Employee":"Ebenezer Scrooge",
"Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/50286_36742018673_6402975_q.jpg",
"Span":"11",
"Manager":"Vikram Pandit",
"Function":"Mortgages and Loans",
"Function ID":"2",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"36.1552098",
"Longtitude":"-78.7750734",
"Title":"Butner Federal Correction Complex",
"SpotID":"7",
"Content":"<b>Butner Federal Correction Complex</b><br>Butner<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NC",
"City":"Butner",
"Clean Address":"1000 Old 75 Hwy, Butner, NC 27509, USA",
"Office ID":"7",
"Office":"Butner Federal Correction Complex",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"4",
"Manager ID":"9",
"Employee":"Bernie Madhoff",
"Photo":"http://media.advisorone.com/advisorone/files/article/Madoff_Bernie_Dec-08_TN-crop-96x96.jpg",
"Span":"4",
"Manager":"Charles Ponzi",
"Function":"Investment",
"Function ID":"5",
"Manager Photo":"http://media.advisorone.com/advisorone/files/article/Ponzi_TN-crop-96x96.JPG"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"5",
"Manager ID":"11",
"Employee":"Fannie Mae",
"Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/50289_79437483433_3527813_q.jpg",
"Span":"5",
"Manager":"Ebenezer Scrooge",
"Function":"Mortgages and Loans",
"Function ID":"2",
"Manager Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/50286_36742018673_6402975_q.jpg"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"6",
"Manager ID":"11",
"Employee":"Freddie Mack",
"Photo":"http://www.dollarsandsense.org/freddiemaclogotiny.jpg",
"Span":"6",
"Manager":"Ebenezer Scrooge",
"Function":"Mortgages and Loans",
"Function ID":"2",
"Manager Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/50286_36742018673_6402975_q.jpg"
},
{
"Latitude":"48.8707517",
"Longtitude":"2.3169228",
"Title":"Elysee Palace",
"SpotID":"2",
"Content":"<b>Elysee Palace</b><br>Paris<br>France",
"Country Code":"FR",
"Country":"France",
"State":"3/15/1900",
"City":"Paris",
"Clean Address":"55 Rue du Faubourg Saint-Honoré, 75008 Paris, France",
"Office ID":"2",
"Office":"Elysee Palace",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"2",
"Manager ID":"3",
"Employee":"Nicholas Sarkozy",
"Photo":"http://a2.twimg.com/profile_images/1267957845/SARKOZY_reasonably_small.JPG",
"Span":"2",
"Manager":"Angela Merkel",
"Function":"Foreign Exchange",
"Function ID":"7",
"Manager Photo":"http://static.guim.co.uk/sys-images/Guardian/Pix/pictures/2007/01/18/merkel128ready.jpg"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"7",
"Manager ID":"10",
"Employee":"Fred Goodwin",
"Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/188090_188711347831031_686876_q.jpg",
"Span":"7",
"Manager":"Vikram Pandit",
"Function":"Pensions",
"Function ID":"1",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"52.5201978",
"Longtitude":"13.3709925",
"Title":"Bundeskanzleramt",
"SpotID":"3",
"Content":"<b>Bundeskanzleramt</b><br>Berlin<br>Germany",
"Country Code":"DE",
"Country":"Germany",
"State":"Berlin",
"City":"Berlin",
"Clean Address":"Bundeskanzleramt, Willy-Brandt-Straße 1, 10557 Berlin, Germany",
"Office ID":"3",
"Office":"Bundeskanzleramt",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"3",
"Manager ID":"10",
"Employee":"Angela Merkel",
"Photo":"http://static.guim.co.uk/sys-images/Guardian/Pix/pictures/2007/01/18/merkel128ready.jpg",
"Span":"3",
"Manager":"Vikram Pandit",
"Function":"Foreign Exchange",
"Function ID":"7",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"9",
"Manager ID":"10",
"Employee":"Charles Ponzi",
"Photo":"http://media.advisorone.com/advisorone/files/article/Ponzi_TN-crop-96x96.JPG",
"Span":"9",
"Manager":"Vikram Pandit",
"Function":"Investment",
"Function ID":"5",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"10",
"Manager ID":"0",
"Employee":"Vikram Pandit",
"Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg",
"Span":"10",
"Manager":"",
"Function":"Shitibank administration",
"Function ID":"6",
"Manager Photo":""
},
{
"Latitude":"40.759277",
"Longtitude":"-73.972165",
"Title":"ShitiBank HQ",
"SpotID":"4",
"Content":"<b>ShitiBank HQ</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"399 Park Ave, New York, NY 10022, USA",
"Office ID":"4",
"Office":"ShitiBank HQ",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"8",
"Manager ID":"10",
"Employee":"Richard Fuld",
"Photo":"http://www.businessweek.com/bw50/2007/image/LEH.jpg",
"Span":"8",
"Manager":"Vikram Pandit",
"Function":"Wealth Management",
"Function ID":"4",
"Manager Photo":"http://www4.pictures.zimbio.com/gi/FORTUNE+Breakfast+Conversation+Vikram+Pandit+-l6QMTh1yApt.jpg"
},
{
"Latitude":"40.755612",
"Longtitude":"-73.982025",
"Title":"Sofitel New York",
"SpotID":"1",
"Content":"<b>Sofitel New York</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"45 W 44th St, New York, NY 10036, USA",
"Office ID":"1",
"Office":"Sofitel New York",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"12",
"Manager ID":"1",
"Employee":"Nafissatou Diallo",
"Photo":"http://static-cdn.guyanapublications.com/images/2011/08/20110809NafissatouDiallo-50x45.jpg",
"Span":"12",
"Manager":"Dominic Strauss Khan",
"Function":"Employee Relations",
"Function ID":"3",
"Manager Photo":"http://4.bp.blogspot.com/-dTBHZ_UaiwQ/TdJm57L2NCI/AAAAAAAABvU/NGMHtfZXkxw/s320/IMF+chief+Dominique+Strauss-Kahn+will+next+appear+in+court.jpg"
},
{
"Latitude":"40.755612",
"Longtitude":"-73.982025",
"Title":"Sofitel New York",
"SpotID":"1",
"Content":"<b>Sofitel New York</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"45 W 44th St, New York, NY 10036, USA",
"Office ID":"1",
"Office":"Sofitel New York",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"13",
"Manager ID":"1",
"Employee":"Tristane Banon",
"Photo":"http://imgs.sfgate.com/c/pictures/2011/07/05_t/mn-strauss06_ph1_0503732902_t.gif",
"Span":"13",
"Manager":"Dominic Strauss Khan",
"Function":"Employee Relations",
"Function ID":"3",
"Manager Photo":"http://4.bp.blogspot.com/-dTBHZ_UaiwQ/TdJm57L2NCI/AAAAAAAABvU/NGMHtfZXkxw/s320/IMF+chief+Dominique+Strauss-Kahn+will+next+appear+in+court.jpg"
},
{
"Latitude":"30.8321752",
"Longtitude":"-92.6372801",
"Title":"Oakdale Federal Correction Complex",
"SpotID":"5",
"Content":"<b>Oakdale Federal Correction Complex</b><br>Oakdale<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"LA",
"City":"Oakdale",
"Clean Address":"1507 E Whatley Rd, Oakdale, LA 71463, USA",
"Office ID":"5",
"Office":"Oakdale Federal Correction Complex",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"14",
"Manager ID":"8",
"Employee":"Berbie Ebbers",
"Photo":"http://imgs.sfgate.com/c/pictures/2003/08/28_t/bu_worldcom_t.gif",
"Span":"14",
"Manager":"Richard Fuld",
"Function":"Wealth Management",
"Function ID":"4",
"Manager Photo":"http://www.businessweek.com/bw50/2007/image/LEH.jpg"
},
{
"Latitude":"40.760716",
"Longtitude":"-73.979809",
"Title":"Equitable Life",
"SpotID":"6",
"Content":"<b>Equitable Life</b><br>New York<br>United States",
"Country Code":"US",
"Country":"United States",
"State":"NY",
"City":"New York",
"Clean Address":"1290 Avenue of the Americas, New York, NY 10104, USA",
"Office ID":"6",
"Office":"Equitable Life",
"Office Photo":"http://blogs.villagevoice.com/pressclips/shitibanknu-400.jpg",
"Employee ID":"15",
"Manager ID":"7",
"Employee":"David Thomas",
"Photo":"http://i.telegraph.co.uk/multimedia/archive/00851/money-graphics-2002_851877a.jpg",
"Span":"15",
"Manager":"Fred Goodwin",
"Function":"Pensions",
"Function ID":"1",
"Manager Photo":"http://profile.ak.fbcdn.net/hprofile-ak-snc4/188090_188711347831031_686876_q.jpg"
}
]
}
}
) ; }
Let’s take a look at the VizMap Dictionary which identifies the data fields that will be required by the application. Any questions, comments or feedback can be left at our forum.