Table and map in same dashboard

[Sikker Trafik]

Hi

I am trying to embed a table and a map in my website. I have found out how to make the table with filters. But I have problems making the map work as well. 

When I'm trying to do this, it shows an error saying: 
The columns type does not match the supported data format. See documentation for supported formats.×

 

I don't know if the kind of map is not working anymore, or it is because of my lack of skills (wouldn't suprise).

I really hope someone can help me.

And here is my script:

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

      google.load('visualization', '1.0', {'packages':['controls', 'table', 'map']});

      google.setOnLoadCallback(drawDashboard);

      function drawDashboard() {

       var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?key=0Am7E06r_2i5edE80a0MxSUtTUURSaHdicDBNYUdYVXc&transpose=0&headers=1&range=A1%3AL165&gid=0&pub=1');

       query.setQuery('SELECT A,B,C,D,E,F,G,H,I,J,K,L');

       query.send(handleQueryResponse);

   }

 

   function handleQueryResponse(response) {

     if (response.isError()) {

       alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

      return;

     }

     var data = response.getDataTable();

        var dashboard = new google.visualization.Dashboard(

            document.getElementById('dashboard_div'));

        var filter = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control1',

          'options': {

            'filterColumnLabel': 'År',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg et år'

          }

          }

        });

      

        var filter2 = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control2',

          'options': {

            'filterColumnLabel': 'Køn',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg køn'

          }

          }

        });

        var filter3 = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control3',

          'options': {

            'filterColumnLabel': 'Element',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg element'

          }

          }

        });

        var filter4 = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control4',

          'options': {

            'filterColumnLabel': 'Modpart',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg modpart'

          }

          }

        });

  var table = new google.visualization.ChartWrapper({

    'chartType': 'Table',

    'containerId': 'chart2',

    'options': {

      'width': '900px',

      'height': '1200px'

    }

  });

     var map1 = new google.visualization.ChartWrapper({

        'chartType': 'Map',

        'containerId': 'map1',

        'view': { 'columns': [8, 9]}

    });

        dashboard.bind([filter, filter2, filter3, filter4], [table, map1]);

        dashboard.draw(data);

      }

    </script>

<p> </p>

<!--Div that will hold the dashboard-->

    <div id="dashboard_div">

      <!--Divs that will hold each control and chart-->

      <div id="control1"></div>

      <div id="control2"></div>

      <div id="control3"></div>

      <div id="control4"></div>

      <div id="chart2"></div>

      <div id="map1"></div>

    </div>

And if you need the link to my spreadsheet: 
https://docs.google.com/spreadsheet/ccc?key=0Am7E06r_2i5edE80a0MxSUtTUURSaHdicDBNYUdYVXc&usp=sharing

Cheers

Nikolaj

[asgallant]

I think you want to use columns 7 and 8 in the map's view, not 8 and 9 (columns are 0-indexed, so "X" is column 7 and "Y" is column 8).  See here: http://jsfiddle.net/asgallant/U43xx/

[sikkertrafik2011]

Hi
Sorry for my late reply. You're absolutely right - it's working now. 

Thank you so much for helping me.

Nikolaj

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/7mb7__D7bf0/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[asgallant]

You're welcome.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.

[sikkertrafik2011]

I've got another question in the same script. 

I try to change options for the map, but I can't figure out, why it doesn't work. The part of the script is here: 

    var map1 = new google.visualization.ChartWrapper({

        'chartType': 'Map',

        'containerId': 'map1',

        'options': {

            'width': 900,

            'height': 1200

        },

        'view': { 'columns': [7, 8]}

    });

It doesn't work, also when I' trying: 

            'width': '900px',

            'height': '1200px'

or 

            'width': '900',

            'height': '1200'

I've been trying to find out what different options I can do in the script, find some sort of list or something about what will work, but without luck.

I hope you can help again.
Thank you

Best
Nikolaj

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

Maps don't support the height and width options - you need to set those on the map's container div in HTML or CSS.  The full list of options available to Maps is listed here.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsubscr...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.

Visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[sikkertrafik2011]

Thank you asgallant. 

When I would like to import larger data sets from my spreadsheet into the table and map, it throw an error: 
One or more participants failed to draw()×

And on the map it says: 
Some of the data rows were truncated    ×

How can I find out what the limits on map are?
It looks as though it's a pretty limited number of data it is able to do. 
One asked about something similar, but I suppose there isn't the limit on 400 coordinates, when it's not about transforming adresses into coordinates? (The coordinates are given in the spreadsheet)
https://groups.google.com/forum/?fromgroups=#!searchin/google-visualization-api/some$20of$20the$20data$20rows$20were$20truncated/google-visualization-api/JEiY89OH5Gk/k9PB1oZFdHkJ

Hope you can help.

Thank you

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

The note in the Data Format section of the Map documentation says that Maps accept a maximum of 400 rows of data.

[sikkertrafik2011]

All right. Thank you so much for the help asgallant!

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Sikker Trafik]

Now I've got another question.

If I add showTip, it doesn't open the infoWindow. Like this:

    dashboard.draw(data, {showTip: true});

I don't what the problem is. I have looked through a lot of examples on this matter, but the only thing I can think of is, that I in some way have to add data do be shown on the map. Right now the map views column 7 and 8. Can I in some way add more columns and still be able to draw the map or am I looking for something else?

Thank you

Nikolaj

To post to this group, send email to google-visualization-api@googlegroups.com.

[asgallant]

If you are using lat/long pairs, then you need to add a third column to the map, which contains a string describing the location.  This string is what pops up in the tooltip.

[sikkertrafik2011]

I tried that, but nothing pops up. It only turn blue instead of red.

My code is now: 

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

      // Load the Visualization API and the controls package.

      google.load('visualization', '1.0', {'packages':['controls', 'table', 'map']});

      // Set a callback to run when the Google Visualization API is loaded.

      google.setOnLoadCallback(drawDashboard);

      // Callback that creates and populates a data table,

      // instantiates a dashboard, a range slider and a pie chart,

      // passes in the data and draws it.

      function drawDashboard() {

        // Create our data table.

       var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?key=0Am7E06r_2i5edGFrOXQ3dFBwZ1IwbmZBTlRwXzFCRFE&transpose=0&headers=1&range=A1%3AK2099&gid=0&pub=1');

       query.setQuery('SELECT A,B,C,D,E,F,G,H,I,J,K');

       query.send(handleQueryResponse);

   }

 

   function handleQueryResponse(response) {

     if (response.isError()) {

       alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

      return;

     }

     var data = response.getDataTable();

        var dashboard = new google.visualization.Dashboard(

            document.getElementById('dashboard_div'));

        // Create a range slider, passing some options

        var donutRangeSlider = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control1',

          'options': {

            'filterColumnLabel': 'År',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg et år'

          }

          },

    'state': {'selectedValues': ['2013']}

  });      

  // Create a name filter, passing some options

        var atomMagter = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control2',

          'options': {

            'filterColumnLabel': 'Køn',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg køn'

          }

          }

        });

  // Create a name filter, passing some options

        var elementArt= new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control3',

          'options': {

            'filterColumnLabel': 'Element',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg element'

          }

          }

        });

  // Create a name filter, passing some options

        var modpartArt= new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control4',

          'options': {

            'filterColumnLabel': 'Modpart',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg modpart'

          }

          }

        });

  // Define a table

  var table = new google.visualization.ChartWrapper({

    'chartType': 'Table',

    'containerId': 'chart2',

    'options': {

      'width': '900px'

    },

     'view': { 'columns': [ 2, 3, 4, 5, 8, 10]}

  });

  // Define a map

     var map1 = new google.visualization.ChartWrapper({

        'chartType': 'Map',

        'containerId': 'map1',

        'view': { 'columns': [6, 7]},

        'options': {

            'enableScrollWheel': false,

            'mapType': 'hybrid',

            'useMapTypeControl': true           

            }

    });

        // Establish dependencies, declaring that 'filter' drives 'table',

        // so that the table will only display entries that are let through

        // given the chosen slider range.

        dashboard.bind([donutRangeSlider, atomMagter, elementArt, modpartArt], [map1, table]);

        // Draw the dashboard.

   dashboard.draw(data, {showTip: true});

}

    </script>

<p> </p>

<!--Div that will hold the dashboard-->

    <div id="dashboard_div">

      <!--Divs that will hold each control and chart-->

    <div id="map1" style="zoom: 1; width: 900px; height: 700px;"></div>

<p> </p>

    <div id="control1"></div>

    <div id="control2"></div>

    <div id="control3"></div>

    <div id="control4"></div>

<p> </p>

    <div id="chart2" style="width: 900px;"></div>

</div>

<p> </p>

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

You need to set the map's "showTip" option to true.

[sikkertrafik2011]

Sure. I thought I had done that. I have a few questions, which I hope you can help me with:

1. Is it possible to get the showTip to show more than one column?
2. Is it possible to have different colours on the markers?
3. Can I set a start position for my map? It doesn't quite capture all of Denmark when it loads.
4. It is possible to have a zoom-limitation for the map? So visitors of a website can only zoom a little?
5. My table will only show the first five out of six columns. How do I fix it so the columns are more narrow so all six columns fit in? 

My code is this now: 

        'allowMultiple': false,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg et år'

          }

          },

    'state': {'selectedValues': ['2013']}

  });      

  // Create a name filter, passing some options

        var atomMagter = new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control2',

          'options': {

            'filterColumnLabel': 'Køn',

      'ui': {

        'allowTyping': false,

        'allowMultiple': false,

        'view': { 'columns': [6, 7, 5]},

        'options': {

            'enableScrollWheel': false,

            'mapType': 'hybrid',

            'useMapTypeControl': true,

            'showTip': true           

            }

    });

        // Establish dependencies, declaring that 'filter' drives 'table',

        // so that the table will only display entries that are let through

        // given the chosen slider range.

        dashboard.bind([donutRangeSlider, atomMagter, elementArt, modpartArt], [map1, table]);

        // Draw the dashboard.

   dashboard.draw(data, {showTip: true});

}

    </script>

<p> </p>

<!--Div that will hold the dashboard-->

    <div id="dashboard_div">

      <!--Divs that will hold each control and chart-->

    <div id="map1" style="zoom: 1; width: 900px; height: 700px;"></div>

<p> </p>

    <div id="control1"></div>

    <div id="control2"></div>

    <div id="control3"></div>

    <div id="control4"></div>

<p> </p>

    <div id="chart2" style="width: 900px;"></div>

</div>

<p> </p>

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

In order to get the tooltips to display data from multiple columns, you will have to define a calculated column in the map's ChartWrapper's view.columns parameter:

view: {

    columns: [6, 7, {

        type: 'string',

        calc: function (dt, row) {

            // access data in your DataTable via dt.getValue(row, <column index>) or dt.getFormatterValue(row, <column Index>)

            var tooltip = dt.getValue(row, 5);

            return tooltip;

        }

    }]

}

To the best of my knowledge, you can't have multiple colors of markers on the map, nor can you set the start position, nor can you set a max or min zoom level.  This visualization is a highly limited wrapper for Google Maps; if you need that functionality, you should switch to using the Maps API directly (I am given to understand that you can still feed that via the Visualization API, so you wouldn't have to change much in your code outside the map).

I tested your code (here: http://jsfiddle.net/asgallant/X2CuR/) and the table columns all show up when I run it - what is the problem you are experiencing?

[sikkertrafik2011]

Thank you for the answers.

The problem I'm facing, you can see on this link: 
http://www.sikkertrafik.dk/Raad-og-viden/I-bil/2006til2013

The column "Modpart" is shown in your example but not in mine. Can I set some kind of "fit into window" so the columns are not that wide? 

What will I not be able to do in Maps API? I don't have any knowledge at all about that.


I tried to embed that part of the code to show multiple columns in the showtip, but it won't work. Can you figure out what the problem is?

  // Define a map

     var map1 = new google.visualization.ChartWrapper({

        'chartType': 'Map',

        'containerId': 'map1',

        'view': {

            'columns': [6, 7, {

        'type': 'string',

                'calc': function (dt, row) {

            var tooltip = dt.getValue(row, 5);

            return tooltip;

        }

    }]

}

        'options': {

            'enableScrollWheel': false,

            'mapType': 'hybrid',

            'useMapTypeControl': true,

            'showTip': true           

            }

    });

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

The problem is your container divs (both the map and table) - they have their widths set to 900px, but the div they are in only has a width of ~700px, so the rightmost 200px are hidden.  Set the widths to something else (like 100%) and they should draw properly.

[sikkertrafik2011]

As always - you're right! :)  Thank you!

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Sikker Trafik]

Hi asgallant

I don't know if you're still able to help with my map. I have some questions to the code below.

1. When I try to use the float:left function (or right) for the filters, the drop-downs won't work. 

2. When I use filters where no results come up in the table and on the map, it shows an error: One or more participants failed to draw() above the map and in the map itself it says "No data points to show on map". It makes sense that it shows this error, but is there any way to cancel the troubleshooting so the error boxes are not shown?

3. Can I set the zoom level of the map to a level of my choosing?

I really hope you can help me with these things. 

Best, 

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

      // Load the Visualization API and the controls package.

      google.load('visualization', '1.0', {'packages':['controls', 'table', 'map']});

      // Set a callback to run when the Google Visualization API is loaded.

      google.setOnLoadCallback(drawDashboard);

      // Callback that creates and populates a data table,

      // instantiates a dashboard, a range slider and a pie chart,

      // passes in the data and draws it.

      function drawDashboard() {

        // Create our data table.

       var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?key=0Am7E06r_2i5edGFrOXQ3dFBwZ1IwbmZBTlRwXzFCRFE&transpose=0&headers=1&range=A1%3AL1404&gid=0&pub=1');

       query.setQuery('SELECT A,B,C,D,E,F,G,H,I,J,K,L');

       query.send(handleQueryResponse);

   }

 

   function handleQueryResponse(response) {

     if (response.isError()) {

       alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

      return;

     }

     var data = response.getDataTable();

        var dashboard = new google.visualization.Dashboard(

            document.getElementById('dashboard_div'));

            'values': ['Fodgænger', 'Cykel', 'Lille knallert', 'Stor knallert', 'MC', 'Bil', 'Varebil', 'Lastbil'],

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg element'

          }

          }

        });

  // Create a name filter, passing some options

        var hovedsituationArt= new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control4',

          'options': {

            'filterColumnLabel': 'Hovedsituation',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg hovedsituation'

          }

          }

        });

  // Create a name filter, passing some options

        var modpartArt= new google.visualization.ControlWrapper({

          'controlType': 'CategoryFilter',

          'containerId': 'control5',

          'options': {

            'filterColumnLabel': 'Modpart',

      'ui': {

        'allowTyping': false,

        'allowMultiple': true,

        'selectedValuesLayout': 'belowWrapping',

        'labelStacking': 'vertical',

        'caption': 'Vælg modpart'

          }

          }

        });

  // Define a table

  var table = new google.visualization.ChartWrapper({

    'chartType': 'Table',

    'containerId': 'chart2',

    'options': {

      'width': '100%'

    },

     'view': { 'columns': [ 2, 3, 4, 5, 8, 10]}

  });

  // Define a map

     var map1 = new google.visualization.ChartWrapper({

        'chartType': 'Map',

        'containerId': 'map1',

        'view': { 'columns': [6, 7, 5]},

        'options': {

            'enableScrollWheel': false,

            'mapType': 'hybrid',

            'useMapTypeControl': true,

            'showTip': true           

            }

    });

        // Establish dependencies, declaring that 'filter' drives 'table',

        // so that the table will only display entries that are let through

        // given the chosen slider range.

        dashboard.bind([donutRangeSlider, atomMagter, elementArt, hovedsituationArt, modpartArt], [map1, table]);

        // Draw the dashboard.

   dashboard.draw(data, {showTip: true});

}

    </script>

<p> </p>

<!--Div that will hold the dashboard-->

    <div id="dashboard_div">

      <!--Divs that will hold each control and chart-->

    <div id="map1" style="zoom: 1; width: 695px; height: 695px;"></div>

<p> </p>

    <div id="control1"></div>

    <div id="control2"></div>

    <div id="control3"></div>

    <div id="control4"></div>

    <div id="control5"></div>

<p> </p>

    <div id="chart2" style="width: 695px;"></div>

</div>

<p> </p>

[Andrew Gallant]

When you float the controls, you need to clear the float afterwards (otherwise, the controls end up layered underneath the table's container div, which is why they don't work).  See example: http://jsfiddle.net/asgallant/t2vw2hrq/

You can clear the errors by setting up "error" event handlers for the map and dashboard that remove the error message: http://jsfiddle.net/asgallant/t2vw2hrq/1/.  You still get a blank map, but there are no error messages.

You should be able to set the default zoom level by setting the "zoomLevel" option, which takes an integer from 0-19 (0 being the farthest out (whole world) and 19 being the closest in), but it isn't working on your map for some reason (though it works fine on other maps).  I'll look into this to see if I can figure out what is going on.

[Sikker Trafik]

Thank you!

The error handling is exactly as I wanted it and it works - thank you. 

The floating is still a problem. Forgive me, but where am I supposed to type in the CSS part (the part below)? 

#control1, #control2, #control3, #control4, #control5 {

    float: left;

}

#clearFloat {

    clear: both;

}

The program I use only read an html in one script. 

It would be great if you can figure out why it doesn't work! 

Thanks again!
Best, 

Nikolaj 

To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/7mb7__D7bf0/unsubscribe.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.

Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

[Andrew Gallant]

Add those styles to a style sheet or in a <style> tag.  You need to have a div with the class "clearFloat" after your controls for it to work (see the example HTML).

...

[Sikker Trafik]

I still can't figure out how to make it work. The HTML now looks like this. The controls are floated, but the drop-down still doesn't work. I've tried different ways of making the clearFloat work, but so far without luck. Can you help me again?

<!--Div that will hold the dashboard-->

    <div id="dashboard_div">

      <!--Divs that will hold each control and chart-->

    <div id="map1" style="zoom: 1; width: 695px; height: 695px;"></div>

<p> </p>

    <div id="control1"></div>

    <div id="control2"></div>

    <div id="control3"></div>

    <div id="control4"></div>

    <div id="control5"></div>

    <div id="clearFloat"></div>

<style type="text/css">

#control1, #control2, #control3, #control4, #control5 {

    float: left;

}

​#clearFloat {

    clear: both;

}​ 

 </style>

<p> </p>

    <div id="chart2" style="width: 695px;"></div>

</div>

<p> </p>

--

[Andrew Gallant]

I think you have some invalid zero-width characters before and after the #clearFloat css declaration.  Overwrite your style block with this and see if it works:

<style type="text/css">

#control1, #control2, #control3, #control4, #control5 {

    float: left;

}

#clearFloat {

    clear: both;

}

</style>

<blockquote style="margin:0 0 0 .8ex;border-left:1px

...

[Sikker Trafik]

Yep, you're right! 

That works - thank you very much. 

Did you find out about the zoomLevel issue?

--