Mapping the Data just like Heat Map on Google

[NITIN]

Hi,

I am looking for mapping on Google map, using Excel data, which have its own parameters in terms of Data Slabs, as the Data slab in the site http://geocommons.com/ have some limitations, e.g.

1. The Data slabs, like 0-100, 101-200, 201-300, 301 and above, or of any other kind can not be obtained on the map, as it is an auto generated which does lead to choice of slabs
2. The Size of the shape on the map can not be varied with the colour also, which may give a broad spectrum in differentiating the data.

Please to have such kind of excel vba coding, which shall suffice the desired needs with more modified options in it can be added.

Thanks!

[Bruce McPherson]

Nitin

This is a JavaScript problem rather than an excel one, even though the source data comes from excel. If you take a look at 

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications

You will see that the vba role is to generate JavaScript and format it's data to create an executable application.

Anything that you can generate using google charts and visualisations can be embedded on a google map, but first you should mock up what the visualization needs to look like. Getting the data from excel is the trivial part.

Bruce
Sent from my iPad

--
You received this message because you are subscribed to the Google Groups "Excel Ramblings" group.
To view this discussion on the web visit https://groups.google.com/d/msg/excel-ramblings/-/6G0O6nHtZH4J.
To post to this group, send email to excel-r...@googlegroups.com.
To unsubscribe from this group, send email to excel-ramblin...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/excel-ramblings?hl=en.

[Nitin D Sharma]

Dear Bruce,

Thanks for your response, i got it, that this is because of the JavaScript, which has been assigned to make slabs of the data depending upon the code which has been put up in the script.

But i am looking for the map, in which we can design our slabs based on the data and can have data mapped as per requirement as was stated in my earlier mail.

For the reference, i am enclosing the map which is generated from the website  http://geocommons.com/, which denotes the mapping of data, but the volume slabs come up as per the predefined calculation of JavaScript, and moreover the color of the brand for different data can not be changed as per this website, which limits the visualization desired on the map.

Request to make it convenient to develop something inline with this, which shall prove to have an edge over this data mapping technique developed by  http://geocommons.com/

Rgds

Nitin

[Bruce McPherson]

Nitin

Ok I think I got it.. I'm assuming by slabs you mean the bars of a bar chart.  And you are saying that the site you reference doesn't allow you to configure the bar chart the way you want. 

So the question is whether a google visualization can, and whether we can write something to embed that visualization  in a map and feed all that directly from excel.

Are you able to create the chart you want in excel... If so can you do it and send me the workbook at ex...@mcpher.com and I will try to find some time to take a look at it.

Bruce

Sent from my iPad

<UIO Mapping-Nitin.jpg>

[Bruce McPherson]

Nitin

I just noticed your attachment. This looks like all you need is to vary  the markers used for each plotted point so it should not be so hard. I will take a look at it over the next few days.

Bruce

Sent from my iPad

On 2 Mar 2012, at 05:29, NITIN <nitinsh...@gmail.com> wrote:

--

[bruce]

Nitin

I have updated the googlemapping.xlsm file on ramblings.mcpher.com

If you go to the VenueMaster tab, you will see 2 columns, markerColor
and markerSize. If you ask it to generate markers with google maps,
you will see it plots varying size and colored circles.

These are defined in the geoCoding parameters sheet like this - in
other words these parameters define the column name to use for size
and color.

color markerColor
size markerSize

If you arrange through excel formulas to have relative values for size
in the markerSize column, a circle will be plotted at each marker of
the size given.

Similarily in the markerColor column, put an html color code - for
example as below.
markerColor markerSize
#20FF00 500000
#0000FF 200000
#00609F 300000
#80FF00 600000
#FF0000 1000000
#0000FF 200000
#00609F 300000

Now, the second piece, for how to modify the colors automatically
instead of entering them manually. You can use all the heatmap
formulas in your sheet, so the formula you can use in the markercolor
column is simply this.
=rgbToHTMLHex(rampLibraryRGB("heatmap",MIN($F$2:$F$8),MAX($F$2:$F
$8),F2))

for other color scales, or to create your own, take a look at
http://ramblings.mcpher.com/Home/excelquirks/snippets/heatmap/lib

The javascript being generated is as follows..( it is easy to adapt
the opacity etc if you want by editing this code in the marker html
section of the geoCodingParametes parameter sheet).

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

I hope this gets you on your way.

regards
bruce

On Mar 3, 6:31 pm, Bruce McPherson <br...@mcpher.com> wrote:
> Nitin
>
> I just noticed your attachment. This looks like all you need is to vary  the markers used for each plotted point so it should not be so hard. I will take a look at it over the next few days.
>
> Bruce
>
> Sent from my iPad
>

> On 2 Mar 2012, at 05:29, NITIN <nitinsharma5...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Hi,
>

> > I am looking for mapping on Google map, using Excel data, which have its own parameters in terms of Data Slabs, as the Data slab in the sitehttp://geocommons.com/have some limitations, e.g.

>
> > The Data slabs, like 0-100, 101-200, 201-300, 301 and above, or of any other kind can not be obtained on the map, as it is an auto generated which does lead to choice of slabs
> > The Size of the shape on the map can not be varied with the colour also, which may give a broad spectrum in differentiating the data.
> > Please to have such kind of excel vba coding, which shall suffice the desired needs with more modified options in it can be added.
>
> > Thanks!
> > --
> > You received this message because you are subscribed to the Google Groups "Excel Ramblings" group.

> > To view this discussion on the web visithttps://groups.google.com/d/msg/excel-ramblings/-/6G0O6nHtZH4J.

[bruce]

I've updated the site to include a description of how to do all of
this.




http://ramblings.mcpher.com/Home/excelquirks/getmaps/mapping/circles
Bruce

> for other color scales, or to create your own, take a look athttp://ramblings.mcpher.com/Home/excelquirks/snippets/heatmap/lib

>
> The javascript being generated is as follows..( it is easy to adapt
> the opacity etc if you want by editing this code in the marker html
> section of the geoCodingParametes parameter sheet).
>
>  var circle =  {
>          strokeColor: color,
>          strokeOpacity: 0.8,
>          strokeWeight: 1,
>          fillColor: color,
>          fillOpacity: 0.20,
>          map: gMap,
>          center: p,
>          radius: parseFloat(sz),
>       };
>
> I hope this gets you on your way.
>
> regards
> bruce
>
> On Mar 3, 6:31 pm, Bruce McPherson <br...@mcpher.com> wrote:
>
>
>
>
>
>
>
> > Nitin
>
> > I just noticed your attachment. This looks like all you need is to vary  the markers used for each plotted point so it should not be so hard. I will take a look at it over the next few days.
>
> > Bruce
>
> > Sent from my iPad
>
> > On 2 Mar 2012, at 05:29, NITIN <nitinsharma5...@gmail.com> wrote:
>
> > > Hi,
>

> > > I am looking for mapping on Google map, using Excel data, which have its own parameters in terms of Data Slabs, as the Data slab in the sitehttp://geocommons.com/havesome limitations, e.g.

[Nitin D Sharma]

Hi Bruce,

Thanks for the work which is really commendable. 

In line with this and adding some other features on the same visualization map, i have attached an excel file with some dummy Data, for (http://www.box.com/s/zogaxhb3kzjs688zir6y) for the reference. Different locations are mapped for different products sale. It would be quite beneficial in visualizing the following on a single map.

1. If the location is clicked on the map, then a pop up / label with different products sale with graph should come, as done in previous coding earlier by you.
2. Different products button on the map, if these are clicked one by one, then shape ( as just done in your coding) should appear with different sizes on these locations, moreover the colour of the shape put up on the location should vary with the button clicked relating to the product.
3. As sizes of the shapes can be varied based on the sales data for each product data, now the slabs need to be shown as legend (e.g. 0-100, 101-200,201-300,301-400,>400 and so on) on the google map for quick reference for the viewer.
4. A button should on google map should be there to see the sales data for locations like 3-D charts (could be cylinder or some shape projecting above from these locations) or something like topographic chart. 

Probably this would add more to visualizations, if performed for the good of everyone.

I am very thankful to you for taking so much pain in resolving my earlier problem for which i was struggling for the last few days. 

Rgds

Nitin D Sharma

[Bruce McPherson]

Nitin

Im not sure what you want me to do with this. I don't have the time to develop your application for you I'm afraid, although I'm very happy to help with specific questions.

I would think you have all the tools you need now to get going and achieve most of what you describe.

You can set the content in the source sheet to whatever you want to pop up, and working out the sizes according to your test data is just straightforward formulas.

Let me know if you have something specific and I'll do my best to help you.

Bruce

Sent from my iPad

<mcpher.xlsm>

[Nitin D Sharma]

Dear Bruce,

Thanks for your prompt reply, i apologize that i put up every of the nascent ideas that has come up for visualization, and all thoughts move around on the same single map file with the use of command buttons.

The foremost anxiousness comes my way, which is, can we put up buttons like "command button" as it is there on the Excel userform) on the map, which are coded with the different products sale, e.g. clicking on Product A BUTTON, shall show the data on the map for various locations, similarly Clicking on Product B BUTTON on the same map shall reflect the respective Sales Data on the same map, and so on, and finally All products button shall show data for all locations for all products with different shapes (different shapes to categorize different products on the locations which shall ease out in recognizing the product sale for the location.

Probably this would help in using buttons on the same map for visualizing different products data, and legend to show permanently / regulated with the button clicked on the map.

Hope, you would help me in getting through it, though i had asked a little bit extra.

Again, i am very thankful to you for resolving my problems and investing your precious time.

Regards
Nitin

[Bruce Mcpherson]

Nitin

You are going to need pretty good knowledge of javaScript,css and google visualization to be able to accomplish this, so I suggest you start with developing your app in javaScript, and worry about the Excel connection and data later since this is easily accomplished. 

All that you ask for is very doable. Take a look at this, which has all the elements you ask about, including forms, tables, and visualizations that change according to a button push. Its likely that you could use a generated application from here or adapt it to get close to what you want. 

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications 

Each one these items is covered in some detail here, including how to do it in either maps or earth. You'll find the complete source code there, or you can generate it from Excel.

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications/javascript/howto

Getting started with maps

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications/javascript/howto/using-google-maps

Creating buttons is pretty straightforward. Here's the section to create a button to go to the next marker.  

What's happening below is -

• check to see if we need a navigate button by checking if there is a DOM element with that name
• create a button type DOM element, and use a css class to style it if you need to
• add some text on the button
• add the button to the DOM so it becomes part of the web page.
• Add a click  event which will execute the function shown. In this case, it moves to the next map marker, and creates a popup window. That window will get setup with the google visualization that shows the data associated with it.

  if (ie['navigate']){

   var bx = document.createElement('button');

   bx.className = 'mcnextbutton';

   bx.appendChild(document.createTextNode('Next Spot'));

   ie['navigate'].appendChild(bx);

   mcpherAddEvent (bx,"click",function(){ 

    var nextSpot =vm.nextSpot(mm.spot)

    vm.gotoAnotherSpot(mm.spot,nextSpot);

    if (vm.provider=='maps') nextSpot.createInfoWindow(0);

   },false ,true) ;

In particular you should look at how to embed charts in popups and how to create google visualization tables

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications/javascript/howto/viz 

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications/javascript/howto/charts

As I mentioned, if you have a go at this, I'm happy to point you in the right direction when you get stuck.

Bruce