How to Duplicate and Enhance a Demo

From Data-gov Wiki

Jump to: navigation, search
Infobox (How-To) edit with form
  • name: How to Duplicate and Enhance a Demo

  • description: A tutorial on how to duplicate a data-gov demo, with examples of how to enhance it.
  • creator(s): Johanna Flores
  • created: 2010/06/07
  • modified: 2010-6-10


Contents

Introduction

The goal of this tutorial is to expose you to the technologies used to build data-gov demos so that you can begin building some of your own immediately. This tutorial will walk you through reconstructing Demo: State Adjusted Gross Income Versus Medicare Claims (2007), which I suggest you view before beginning. No Semantic Web technology experience is assumed. The demo uses the Google Visualization API, which I will only detail briefly as it is explained extensively in How to use Google Visualization API. Knoweldge of HTML and Javascript will be useful in understanding the code but isn't necessary. The source code for the demo can be found here. The demo reproduced in this tutorial is exactly the same, except for some content and styling that has been removed from the HTML page.

After you have successfully duplicated the demo, I will walk you through two examples on how to enhance it. Each example will focus on one of the two major aspects of a data-gov demo: visualization and data. Hopefully running through them will give you some ideas (and practice) on how you can utilize these two aspects effectively for when you create a demo of your own.

Duplicating a Demo: State Adjusted Gross Income Versus Medicare Claims (2007)

Preliminaries

To begin, you will need an HTML editor and access to a webserver for deployment. A Google Maps API key will also be necessary, if you are deploying this demo locally. I also suggest you use either IE or Firefox for testing the demo in this tutorial (Google Visualizations don't always render in Chrome for some reason).

Step 1: Load Google Visualization API

Create a new HTML document and insert the following code between the head tags. This code will load the Google API.

<!-- <script src="http://maps.google.com/maps?file=api&v=2&key=YOUR_GOOGLE_MAP_API_KEY" type="text/javascript"></script> -->
<script type = "text/javascript">
	//load a specific visualization package, this one loads 3: geomap, table, columchart
	google.load("visualization", "1", {packages:["geomap","table","columnchart"]});
</script>

Note: If you are using the tw2.rpi.edu server for deployment, you may leave the first script tag commented out. Otherwise, you will need to uncomment it and replace YOUR_GOOGLE_MAP_API_KEY with your Google Maps API key.

Step 2: Send your SPARQL query

Copy this code and paste it right before the </head> tag.

	google.setOnLoadCallback(drawMap);
	
	function drawMap()
	{
		//SPARQL endpoint we will use to query the datasets
		var sparqlproxy = "http://data-gov.tw.rpi.edu/sparql?";
		//this is where the actual SPARQL query is stored
		var queryloc = "http://data-gov.tw.rpi.edu/demo/stable/demo-1356-1623-health-claim-vs-income.sparql";
		//url that will execute the query
		var queryurl = sparqlproxy + "output=gvds&query-uri=" + encodeURIComponent(queryloc);
		//construct the query
		var query = new google.visualization.Query(queryurl);
		
		//send query with callback function
		query.send(handleQueryResponse);
	};

The drawMap function takes a SPARQL query file (stored in the variable queryloc) constructs a query using the SPARQLProxy endpoint, and then sends it to be processed along with the callback function handleQueryResponse. An HTML representation of the result can be accessed from [1]

Step 3: Build the Google map

Now we shall draw the google map. Copy and paste this code right before the </head> tag.

//global variables
	var median_agi_per_claim;
	var median_agi;
	var median_claim;
	var median_population;
	
	//helper function to calculate percentage
	function percentage(value){
		return Math.round(value*100);
	}
	
	//check query for errors and handle accordingly
	function handleQueryResponse(response)
	{
		
		//check query response for errors
		if(response.isError())
		{
			alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
			return;
		}
		
		//gets the query result as a DataTable object
		var data = response.getDataTable();
		if(data == null)
			return;
		
		//construct a new data table with columns (i) states and (ii) agi/claim
		var newdata = new google.visualization.DataTable();
		newdata.addColumn('string', 'State');
		newdata.addColumn('number','AGI (Billion USD) Per Claim');
		
		var ary_agi = new Array();
		var ary_claims = new Array();
		var ary_population = new Array();
		
		//iterate through rows of data table
		var rows = data.getNumberOfRows();
		for(var i=0; i < rows; i++)
		{
			//getValue(rowIndex, columnIndex) from DataTable
			var agi = data.getValue(i,1);
			
			agi = agi * 1000;
			agi = agi/Math.pow(10,9);
			agi = Math.round(agi);
			ary_agi[i] = agi;
			
			var claims = data.getValue(i,2);
			ary_claims[i] = claims;
			
			var population = data.getValue(i,3);
			ary_population[i] = population;
			
			var agi_per_claim = Math.round(( agi / claims)*1000)/1000;
			newdata.addRow([data.getValue(i,0), agi_per_claim]);
		}
		
		//sort data
		ary_agi.sort();
		ary_claims.sort();
		ary_population.sort();
		
		var median_index = Math.floor(rows / 2);
		median_agi = ary_agi[median_index];
		median_claims = ary_claims[median_index];
		median_population = ary_population[median_index];
		
		media_agi_per_claim = median_agi/median_claims;
		
		//Customizing map
		var options = {};
		options['region'] = 'US';
		options['dataMode'] = 'regions';
		options['width'] = 690;
		options['height'] = 450;
		options['colors'] = [0xFFFFFF,
					0x00FF00,0x00CC00,0x00AA00,0x008800,0x006600];

		//create container and draw map
		var container = document.getElementById('map_canvas');
		var geomap = new google.visualization.GeoMap(container);
		geomap.draw(newdata,options);                           
};
    </script>
	</head>
		<body>
		
		</body>
</html>

Notice that handleQueryResponse takes one parameter: response. This variable holds the results of the query that we constructed in the previous step. The results are iterated through and assigned to the newdata variable, which is a specialized DataTable containing only the data we want to display on the map (in this case, the ratio AGI/Claims for each State)


In order to display the map, you will need to modify your HTML page with the following code. Copy and paste this between the body tags.

<center>
<h3>State Adjusted Gross Income Versus Medicare Claims (2007)</h3>
         
   <table>
        <tr>
            <td style="width: 690px;" ><div id='map_canvas'></div></td>
        </tr>
   </table>
   
</center>

Your page should look like the following now.

Step 4: Add the event listener

Now we need to add an event listener for the map. Copy and paste this code right before the </head> tag.

google.visualization.events.addListener(geomap,'select',function()
		{
			//get map selection
			var selection = geomap.getSelection();
			//get data of selected state on map
			var row = selection[0].row;
			//format data for a chart
			var state = data.getValue(row,0);
			var agi = data.getValue(row,1);
			agi = agi * 1000;
			agi = agi/Math.pow(10,9);
			agi = Math.round(agi);
			
			var claims = data.getValue(row,2);
			
			var population = data.getValue(row,3);
			});

This function will execute every time someone clicks on any of the states on the map. Currently it does nothing overt, but we will fix that in the next step.

Step 5: Add bar graph and chart to the listener

The code below will add some visualization functionality to the event listener. Insert this code before the closing curly brace of google.visualization.events.addListener

			{
				var options1 = {};
				options1['width'] = 300;
				options1['height'] = 250;
				options1['min'] = 0;
				options1['title'] = "["+ state +"] vs national median (=100%)";
				options1['titleY'] = "percentage(y%)";
				options1['legend'] = 'top';
				
				var chart1data = new google.visualization.DataTable();
				chart1data.addColumn('string','Measure');
				chart1data.addColumn('number', 'State value');
				chart1data.addColumn('number', 'National Median');
				
				chart1data.addRow(["AGI",percentage(agi/median_agi),100]);
				chart1data.addRow(["Claims",percentage(claims/median_claims),100]);
				chart1data.addRow(["Population", percentage(population/median_population),100]);
				
				//set chart canvas
				var container1 = document.getElementById('chart1_canvas');
				
				//Draw chart to canvas
				var chart1 = new google.visualization.ColumnChart(container1);
				chart1.draw(chart1data,options1);
			}
			{
			        // Set chart options                                            
                    var options1 = {};
                    options1['width'] = 300;
                    options1['height'] = 200;
                                                
                    // Add data to charts                                   
                    var chart1data = new google.visualization.DataTable();                                                  
                    chart1data.addColumn('string','Measure');
                    chart1data.addColumn('number','State Value');
					chart1data.addColumn('number','National Median');
                    chart1data.addColumn('number','State vs National (100%)');
                                                
                    chart1data.addRow([ "AGI (USD in Billions)", agi,  median_agi, percentage(agi/median_agi)]);
                    chart1data.addRow([ "Medicare Claims", claims, median_claims, percentage(claims/median_claims)]);
                    chart1data.addRow([ "Population", population,  median_population, percentage(population/median_population)]);

                    // Set chart canvas                                             
                    var container1 = document.getElementById('chart2_canvas');
                                                
                    // Draw chart to canvas                                         
                var chart1 = new google.visualization.Table(container1);
                chart1.draw(chart1data, options1);              
       		} 

In order to render the new visuals, you will need to alter the table in your HTML page to look like this.

<table >
        <tr>
                <td style="width: 690px;" ><div id='map_canvas'></div></td>
                <td style="width: 300px; background:lightblue">
                        <div id='chart1_canvas'><p>click a state on the map to show a column-chart here</p></div>
                        <div id='chart2_canvas'><p>click a state on the map to show a table here</p></div>
                </td>
        </tr>
</table>

You should now have something that looks like this, a recreation of the State Adjusted Gross Income Versus Medicare Claims (2007) demo.

  • Important: Be sure to save your code for this demo. This will form the base code used by the next two examples.

Now that we have successfully recreated the demo, we might want to add some features of our own. In the next two sections, I will provide a couple examples of ways to do this. Being the easier of the two, the first example will cover adding just another visualization to the demo. Once you are comfortable with doing that, you should move on to the second where we will modify the SPARQL query a little to acquire new data and build a visualization surrounding it.

  • Important: Be sure to save your code for these two examples in separate files from the demo code you wrote in the previous section so that you do not overwrite it. The base code will be used as a starting point for each of these examples.

Example 1: Change the visualization features

In this example we will focus on the number of claims and annual gross income (AGI) reported by each state. The demo already provides this information individually for each state, but it could be useful to visualize it collectively. Given the right visual, we might be able to discern whether there is a relationship or correlation between AGI and the number of claims.

To find out, let us build a scatter plot of AGI vs. Number of Claims, with each point representing a state in the US. The example built in this section uses the same demo code that we worked with earlier, so please load it before beginning.

Step 1: Load the Scatterchart package

The scatterplot visual is stored in a package called 'scatterchart'. Add the package to the Google Visualization API loader as shown below (refer to #Step 1: Load Google Visualization API if you need help).

  • Note: While 'corechart' contains functionality to create both column and scatter charts, the 'columnchart' and 'scatterchart' packages appear to be much richer in terms of features and customization, so it is recommended that you load them individually instead of deferring to 'corechart'.
google.load("visualization", "1", {packages:["geomap","table","columnchart","scatterchart"]});

Step 2: Construct a new DataTable

Every visual must have an underlying DataTable so that it knows what information to display. In this case, we want a DataTable to hold AGI and Number of Claims for each state.

Add the following code to the handleQueryResponse function to construct this new DataTable.

var chartdata = new google.visualization.DataTable();
chartdata.addColumn('number','AGI');
chartdata.addColumn('number','Claims');

Next, add the following line to the end of the for-loop you wrote in #Step 3: Build the Google Map, in order to populate your DataTable.

chartdata.addRow([claims,agi]);

Step 3: Build a scatterplot

Add the following to the handleQueryResponse function.

	options = {};
	options['width'] = 650;
	options['height'] = 500;
	options['title']='AGI vs. Claims';
	options['hAxis'] = {title: 'AGI (USD in Billions)'};
	options['vAxis'] = {title: 'Claims'};
	options['legend'] = 'none';
	
	container = document.getElementById('chart_canvas');
	var chart = new google.visualization.ScatterChart(container);
	chart.draw(chartdata,options);		

Also, be sure to add a div with the id 'chart_canvas' to your HTML body to render the scatterplot.

	<div id= 'chart_canvas'></div>

Once you've done that, load the html page containing the new code into your browser and check whether a new scatterplot graphic has appeared.

Your demo should now look something like this

Example 2: Change the data

This next example will focus on making changes to the actual data used by the demo. This involves modifying the SPARQL query file to specify what new data we want to access from the Datasets.

  • Please download the SPARQL file and save it to your development server as "healthcare-demo-duplication-example2-1.sparql". This is the same query file processed by the original demo. Open it using the text editor of your choice and examine the contents.
PREFIX dgp1356: <http://data-gov.tw.rpi.edu/vocab/p/1356/>
PREFIX dgp1623: <http://data-gov.tw.rpi.edu/vocab/p/1623/>
PREFIX dgp353:  <http://data-gov.tw.rpi.edu/vocab/p/353/>
PREFIX skos:  <http://www.w3.org/2004/02/skos/core#>
SELECT ?state_name1623 ?state_agi07 ?state_medicareclaims07 ?state_population
WHERE {
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_1356>
 {
   ?s dgp1356:county_code "000" .
   ?s dgp1356:year "2007" .
   ?s dgp1356:county_name ?state_name1356 .
   ?s dgp1356:agi ?state_agi07 .
   ?s dgp1356:state_code  ?state_fipscode .
 }
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_1623>
 {             
    ?s2 dgp1623:state ?state_name1623 .
    ?s2 dgp1623:fiscal_year_07 ?state_medicareclaims07 .
 }
 GRAPH <http://data-gov.tw.rpi.edu/wikidata/United_States_and_Territories>
 {
    ?s3  skos:altLabel  ?state_name1356 . 
    ?s3  skos:altLabel  ?state_name1623 .    
 }
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_353>
 {             
     ?entry dgp353:pub_fips ?state_fipscode .
     ?entry dgp353:popu_st ?state_population. 
 }
}
order by ?state_name1623

As you can see, Datasets 1623, 353, and 1356 are being queried for information about the number of claims, state population, and annual gross income(AGI), respectively, for each state in the year 2007. The data returned by this query is used to create the visualizations you have been building so far. However, what if one wants to gather more data from the existing Datasets, or incorporate new Datasets that might have relevant or related data?

Instead of focusing exclusively on AGI and number of claims, as is done in the base demo, perhaps one could concentrate on trend data from one year to the next for each state. For example, Medicare benefits are predominately claimed by U.S. citizens that are considered elderly (age 65 and above). If data on the number of elderly citizens per state, between two years, could be obtained one would be able to examine whether there was a trend between the number of elderly and the number of Medicare claims filed for each state within this time period (the question can be thought of as "If the number of elderly increases/decreases, does the number of Medicare claims filed increase/decrease as well from one year to the next?").

In this example, we will demonstrate this trend data for each state from 2006 to 2007. This example is meant to be built on top of the base code you wrote. If you need the demo base code, it can be found here. Likewise, the source code for this example can be found for download here

Step 1: Modify query

Open the SPARQL file you downloaded and replace it with the following:

PREFIX dgp1356: <http://data-gov.tw.rpi.edu/vocab/p/1356/>
PREFIX dgp1623: <http://data-gov.tw.rpi.edu/vocab/p/1623/>
PREFIX dgp353:  <http://data-gov.tw.rpi.edu/vocab/p/353/>
PREFIX skos:  <http://www.w3.org/2004/02/skos/core#>

SELECT ?state_name1623 ?state_agi06 ?state_agi07 ?state_medicare06 ?state_medicare07 ?state_population06 ?state_abbrev 

WHERE {
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_1356>
 {
   {
     ?s dgp1356:county_code "000" .
     ?s dgp1356:year "2007" .
     ?s1 dgp1356:county_code "000";
dgp1356:year "2006".

   }
	?s dgp1356:county_name ?state_name1356 .
   ?s dgp1356:agi ?state_agi07 .
   ?s dgp1356:state_code  ?state_fipscode ;
dgp1356:state_abbrv ?state_abbrev.
   ?s1 dgp1356:agi ?state_agi06 ;
   dgp1356:state_code  ?state_fipscode .
 }
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_1623>
 {             
    ?s2 dgp1623:state ?state_name1623 ;
    dgp1623:fiscal_year_06 ?state_medicare06 ;
    dgp1623:fiscal_year_07 ?state_medicare07 .
 }
  GRAPH <http://data-gov.tw.rpi.edu/wikidata/United_States_and_Territories>
 {
    ?s3  skos:altLabel  ?state_name1356 . 
    ?s3  skos:altLabel  ?state_name1623 .    
 }
 graph <http://data-gov.tw.rpi.edu/vocab/Dataset_353>
 {             
     ?entry dgp353:pub_fips ?state_fipscode ;
     dgp353:popu_st ?state_population06. 
 }
}

This returns the same data as in the original demo (agi, number of medicare claims, and population for 2006), as well as the data for each of these variables for 2007. However, state population for 2007, is not contained in any of these sets, nor is any information available that we might use to calculate the number of elderly for each state. For this, you need another query.

Create a new file, and name it "healthcare-demo-duplication-example2-2.sparql". Open it using your text editor and paste the following into it:

PREFIX dgp1356: <http://data-gov.tw.rpi.edu/vocab/p/1524/>
PREFIX dgp92:  <http://data-gov.tw.rpi.edu/vocab/p/353/>
SELECT ?pop7 ?child6 ?adult6 ?child7 ?adult7 ?state_fipscode 
From <http://data-gov.tw.rpi.edu/raw/354/data-354.rdf>
From <http://data-gov.tw.rpi.edu/raw/1524/data-1524.rdf>
WHERE { 

 ?entry dgp92:phys_st ?state_fipscode .
  ?entry dgp92:popu_st  ?pop7.
?e1 dgp1356:date "2006";
	dgp1356:state_code ?state_fipscode;
	dgp1356:population_under_age_18 ?child7;
	dgp1356:population_age_18_64 ?adult7.
?e2 dgp1356:date "2007".
?e2 dgp1356:state_code ?state_fipscode.
?e2 dgp1356:population_under_age_18 ?child6.
?e2 dgp1356:population_age_18_64 ?adult6.
}

This query returns the missing data we need to complete this demo: population for 2007, and adult (age 18-64) and child (18 and under) populations from 2006-2007 for each state, which we can subtract to get the elderly population. You may be wondering why it is necessary to have a second query completely, why it couldn't just be incorporated into the first. For the purpose of this demo, the simple answer is that this query requires a specific service to run, http://data-gov.tw.rpi.edu/joseki/sparql.html. The first query cannot be processed using this service, and therefore both queries must be executed individually.

Step 2: Process Queries

Beforehand, be sure to include the appropriate Google Visualization API packages.

google.load("visualization", "1", {packages:["geomap","table","linechart","columnchart","areachart"]});

In your drawMap function, you'll need to process each query separately. Rewrite your drawMap function as such:

function drawMap()
	{
		var sparqlproxy = "http://data-gov.tw.rpi.edu/sparql?";
	       var service = "http://data-gov.tw.rpi.edu/joseki/sparql";
		{	
			//this is where the query is stored
			var queryloc = "http://data-gov.tw.rpi.edu/demo/static/healthcare-demo-duplication-example2-1.sparql";
			//url that will execute the query
			var queryurl = sparqlproxy + "output=gvds&query-uri=" + encodeURIComponent(queryloc);
			//source of the data
			var query = new google.visualization.Query(queryurl);
		
			//send query
			query.send(h1);
		}
		{
			var queryloc = "http://data-gov.tw.rpi.edu/demo/static/healthcare-demo-duplication-example2-2.sparql";
			var queryurl = sparqlproxy + "output=gvds&query-uri=" + encodeURIComponent(queryloc) + "&service-uri=" + encodeURIComponent(service);
			var query = new google.visualization.Query(queryurl);
			query.send(h2);
		}
	};
  • Note: If you would like to use your files instead, replace the file url (i.e. http://data-gov.tw.rpi.edu/demo/static/healthcare-demo-duplication-example2-2.sparql) with the location of your file.

Also include your handlers for callback functions h1 and h2:

var q1_data = null;
	var q2_data = null;

	function h1(response) {
        if (response.isError()) {
          alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
          return;
        }
        q1_data = response.getDataTable();
        handleQueryResponse(response);
      }

	function h2(response) {
        if (response.isError()) {
          alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
          return;
        }
        q2_data= response.getDataTable();
        handleQueryResponse(response);
      }
	 
	 //helper function to calculate percentage
	function percentage(value){
		return Math.round(value*100);
	}

Step 3: Process the query responses

Replace your ENTIRE handleQueryResponse with the following:

function handleQueryResponse(response)
	{
		
		//check query response for errors
		if(response.isError())
		{
			alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
			return;
		}
		
		//google vis. api requires data be represented as a DataTable object
		if(q1_data == null || q2_data == null)
		{
			return;
		}
		
		//construct a new data table with columns (i) states and (ii) agi/claim
		
		var newdata = new google.visualization.DataTable();
		newdata.addColumn('string', 'State');
		newdata.addColumn('number','AGI (Billion USD) Per Claim');
		
		
		var ary_agi6 = new Array();
		var ary_claims6 = new Array();
		var ary_population6 = new Array();
		var ary_agi7 = new Array();
		var ary_claims7 = new Array();
		var ary_population7 = new Array();

		var q2ref = new Array();
	
		var trows = q2_data.getNumberOfRows();
		for(var j = 0; j < trows; j++){
		   q2ref[q2_data.getValue(j,5)] = j;
		}

		//iterate through rows of data table
		var rows = q1_data.getNumberOfRows();
		for(var i=0; i < rows; i++)
		{
			var agi06 = q1_data.getValue(i,1);
			agi06 = agi06 * 1000;
			agi06 = agi06/Math.pow(10,9);
			agi06 = Math.round(agi06);
			ary_agi6[i] = agi06;
			var agi07 = q1_data.getValue(i,2);
			agi07 = agi07 * 1000;
			agi07 = agi07/Math.pow(10,9);
			agi07 = Math.round(agi07);
			ary_agi7[i] = agi07;

			var claims06 = q1_data.getValue(i,3);
			ary_claims6[i] = claims06;
			var claims07 = q1_data.getValue(i,4);
			ary_claims7[i] = claims07;

			var population06 = q1_data.getValue(i,5);
			ary_population6[i] = population06;			
			var q2id = q2ref[q1_data.getValue(i,6)];
			var population07 = q2_data.getValue(q2id,0);
			ary_population7[i] = population07;
			
			var agi_per_claim07 = Math.round(( agi07 / claims07)*1000)/1000;
			newdata.addRow([q1_data.getValue(i,0), agi_per_claim07]);
		}
		
		//sort data
		ary_agi6.sort();
		ary_claims6.sort();
		ary_population6.sort();
		ary_agi7.sort();
		ary_claims7.sort();
		ary_population7.sort();

		var median_index = Math.floor(rows / 2);
		median_agi6 = ary_agi6[median_index];
		median_claims6 = ary_claims6[median_index];
		median_population6 = ary_population6[median_index];
		
		median_agi_per_claim6 = median_agi6/median_claims6;

		median_agi7 = ary_agi7[median_index];
		median_claims7 = ary_claims7[median_index];
		median_population7 = ary_population7[median_index];
		
		median_agi_per_claim7 = median_agi7/median_claims7;

		
		//Customizing map
		var options = {};
		options['region'] = 'US';
		options['dataMode'] = 'regions';
		options['width'] = 690;
		options['height'] = 450;
		options['colors'] = [0xFFFFFF,
					0x00FF00,0x00CC00,0x00AA00,0x008800,0x006600];


		//create container and draw map
		var container = document.getElementById('map_canvas');
		var geomap = new google.visualization.GeoMap(container);
		geomap.draw(newdata,options);

		//ADD LISTENER HERE          
	};

Aside from a few minor additions, this function doesn't do much different from what you've seen in the previous examples: it populates returned query data into a new DataTable object, manipulates the data for the purposes of the demo, and constructs a geomap to display it.

However, notice this block which does something new and very necessary:

var q2ref = new Array();
	
		var trows = q2_data.getNumberOfRows();
		for(var j = 0; j < trows; j++){
		   q2ref[q2_data.getValue(j,5)] = j;
		}

Since the two queries you saw earlier are discontiguous, there is no inherent link between them. That is, there is no matching between the rows of both query results. Therefore, this must be done manually via Javascript, as shown above. What the code does is associate state abbreviations (returned by the second query) and index for each row. This way, you can take a state abbreviation from the first query result, and find the associated row within the second query result. The reason I mention this is because it might be something for you to consider when working with multiple queries.

Step 4: Include the listener with visuals to be generated

From the previous step, find the comment in the handleQueryResponse function code that says "//ADD LISTENER HERE" (should be the last line). Replace the comment with this block of code:

		google.visualization.events.addListener(geomap,'select',function()
		{
			//get map selection
			var selection = geomap.getSelection();
			//get data of selected state on map
			var row = selection[0].row;
			//format data for a chart
			var state = q1_data.getValue(row,0);
			var agi6 = q1_data.getValue(row,1);
			agi6 = agi6 * 1000;
			agi6 = agi6/Math.pow(10,9);
			agi6 = Math.round(agi6);
			var agi7 = q1_data.getValue(row,2);
			agi7 = agi7 * 1000;
			agi7 = agi7/Math.pow(10,9);
			agi7 = Math.round(agi7);	
			var claims6 = q1_data.getValue(row,3);		
			var claims7 = q1_data.getValue(row,4);
			
			var population6 = q1_data.getValue(row,5);
			var q2id = q2ref[q1_data.getValue(row,6)];
			var population7 = q2_data.getValue(q2id,0);
			
			var elderpop6 = null;
			var elderpop7 = null;
			var adultpop = q2_data.getValue(q2id,2);

			var childpop = q2_data.getValue(q2id,1);
			var t65 = adultpop + childpop;
			elderpop6 = population6 - (t65);
			var pop6under65 = Math.round(t65/100000);
			elderpop6 = Math.round(elderpop6/100000);
			childpop = q2_data.getValue(q2id,3);
			adultpop = q2_data.getValue(q2id,4);
			t65 = adultpop + childpop;	
			var pop7under65 = Math.round(t65/100000);
			elderpop7 = population7 - (t65);
			elderpop7 = Math.round(elderpop7/100000);

			{
				var options1 = {};
				options1['width'] = 300;
				options1['height'] = 250;
				options1['min'] = 0;
				options1['title'] = "["+ state +"] Population distribution (2006-2007)";
				options1['titleY'] = "People (in hundred thousands)";
				options1['legend'] = 'top';
				var chart1data = new google.visualization.DataTable();
				chart1data.addColumn('string','Year');
				chart1data.addColumn('number', 'People under 65');
				chart1data.addColumn('number', 'People Age 65+');
				
				chart1data.addRow(["2006",pop6under65,elderpop6]);
				chart1data.addRow(["2007",pop7under65,elderpop7]);	
		
				var container1 = document.getElementById('chart1_canvas');		
				var chart1 = new google.visualization.AreaChart(container1);
				chart1.draw(chart1data,options1);
			}
			{
			    // Set chart options                                            
                var options1 = {};
                options1['width'] = 800;
                options1['height'] = 300;
                                                
                // Add data to charts                                  
				var chart1data = new google.visualization.DataTable();               
                chart1data.addColumn('string','Measure');
                chart1data.addColumn('number','State Value06');
                chart1data.addColumn('number','National Median06');
                chart1data.addColumn('number','State Value07');
                chart1data.addColumn('number','National Median07');
                chart1data.addColumn('number','State06 vs National06  (100%)');
                chart1data.addColumn('number','State07 vs National07 (100%)');
                                                
				chart1data.addRow([ "AGI (USD in Billions)", agi6,  median_agi6, agi7, median_agi7, percentage(agi6/median_agi6), percentage(agi7/median_agi7)]);
                chart1data.addRow([ "Medicare Claims", claims6, median_claims6, claims7, median_claims7, percentage(claims6/median_claims6),percentage(claims7/median_claims7)]);
                chart1data.addRow([ "Population", population6,  median_population6, population7,  median_population7, percentage(population6/median_population6), percentage(population7/median_population7)]);
	
                // Set chart canvas                                             
                var container1 = document.getElementById('chart2_canvas');                         
                // Draw chart to canvas                                         
                var chart1 = new google.visualization.Table(container1);
                chart1.draw(chart1data, options1);              
       		} 
			{
				
				var options1 = {};
				options1['width'] = 300;
				options1['height'] = 200;
				options1['title'] = "["+ state +"] Number of Medicare Claims  from 2006-2007";
				options1['vAxis'] = {title: "Number of Claims"};
				options1['hAxis'] = {title: "Year"};
				//options1['legend'] = 'top';
				var chart1data = new google.visualization.DataTable();
				chart1data.addColumn('string','Year');
				chart1data.addColumn('number', 'Claims');
				
				chart1data.addRow(["2006",claims6]);
				chart1data.addRow(["2007",claims7]);	
			
				var container1 = document.getElementById('chart3_canvas');
				//Draw chart to canvas
				var chart1 = new google.visualization.LineChart(container1);
				chart1.draw(chart1data,options1);
			}
	});         

The listener generates three graphics when you click a state on the map: an area chart, a line chart, and an expanded table similar to the one from the original demo. The area chart and line chart both display new data involving temporal data (2006-2007) for population demographics and number of claims. The expanded table also incorporates temporal data.

Step 5: Include divs for each visual

As always, a necessary final step is to include divs for each visual you want rendered in the body of your html document. Replace whatever is between the <body> tags with the following:

<center>
<!-- Demo Header -->            
<h3>State Adjusted Gross Income Versus Medicare Claims (2007)</h3>
                
<!-- Demo Content -->           
<table >
        <tr>
            <td style="width: 690px;" ><div id='map_canvas'></div></td>
			<td style = "width: 300px; background: lightblue">
			   <div id="chart1_canvas"><p>Column chart</p></div>
				<div id='chart3_canvas'><p>click a state on the map to show a table here</p></div>
			</td>
		</tr>
</table>
<div id='chart2_canvas'><p>click a state on the map to show a table here</p></div>	
</center>

Now load the html page containing the new code into your browser and check whether the new changes have appeared.

Your demo should now look something like this
Facts about How to Duplicate and Enhance a DemoRDF feed
Dcterms:created7 June 2010  +
Dcterms:creatorJohanna Flores  +
Dcterms:descriptionA tutorial on how to duplicate a data-gov demo, with examples of how to enhance it.
Dcterms:modified2010-6-10
Foaf:nameHow to Duplicate and Enhance a Demo
Skos:altLabelHow to Duplicate and Enhance a Demo  +, how to duplicate and enhance a demo  +, and HOW TO DUPLICATE AND ENHANCE A DEMO  +
Personal tools
internal pages