Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<html><head><title>Guardian Datastore Explorer - Gov Spending Data, Released November 2010</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages':['table', 'scatterchart', 'linechart','piechart', 'barchart','columnchart']});
<?php if ($_GET['run']) echo "google.setOnLoadCallback(preview2);"; ?>
function removeChildrenFromNode(node) {
if(node.hasChildNodes()) {
while(node.childNodes.length >= 1 ) {
node.removeChild(node.firstChild);
}
}
}
var deliObj;
var dps="<?php if($_GET['dps']!='') echo $_GET['dps'] ?>";
function deliList(o){
deliObj=o;
var ds=document.getElementById('datastore');
for (var i=0; i<o.length;i++) {
//o[i].d, o[i].u o[i].n.split('--')[1]
var opt=document.createElement('option');
opt.setAttribute('value',i);
opt.innerHTML=o[i].d;
ds.appendChild(opt);
}
}
function preview3(){
var sel=document.getElementById('datastore');
var selection=sel.selectedIndex;
sel=sel[selection].value;
//var key=deliObj[sel].n.split('--')[1];
var key=deliObj[sel].u;
if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];
document.getElementById('gsKey').value=key;
var txt='<a href="'+deliObj[sel].u+'">'+deliObj[sel].d+'</a>';
document.getElementById('deptsrc').innerHTML=deliObj[sel].d;
dps=deliObj[sel].d;
document.getElementById('statrep').innerHTML=txt;
preview();
}
function preview2(){
preview();
drawViz();
}
var key='rvWgEEGK9xuUQBR1EFcxHWA';
function preview() {
if (document.getElementById('gsKey').value!='') key=document.getElementById('gsKey').value;
if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];
var sheet=document.getElementById('gsSheet').value;
if (sheet!='') key+='&gid='+sheet;
var url='http://spreadsheets.google.com/tq?tq=select%20*where%20A=false&key='+key;
var query = new google.visualization.Query(url);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
visualization = new google.visualization.Table(document.getElementById('headings'));
visualization.draw(data, null);
columnList(data);
selTest(data)
}
function createCell(str){
var el=document.createElement('td');
el.innerHTML=str;
return el;
}
function columnList(data){
var t=document.getElementById('colLookup');
removeChildrenFromNode(t);
var labels=document.createElement('tr');
var headings=document.createElement('tr');
for (var i=0;i<data.getNumberOfColumns();i++){
var cell=createCell(data.getColumnId(i));
labels.appendChild(cell);
cell=createCell(data.getColumnLabel(i));
headings.appendChild(cell);
}
t.appendChild(labels);
t.appendChild(headings);
}
function createSelItem(str, col){
var el=document.createElement('option');
el.innerHTML=str;
el.setAttribute('value',col);
// el.setAttribute('label',col);
return el;
}
function optionsChange(){
var gqo; var flag=false;
var sel=document.getElementById('nameTest3');
for (var i = 0; i < sel.options.length; i++)
if (sel.options[ i ].selected) {
if (flag){gqo+=','+sel.options[i].value;}
else {gqo=sel.options[i].value;flag=true;}
}
document.getElementById('gqo').value=gqo;
}
function whereChange(){
var gqw='';
var where=document.getElementById('nameTest2');
var flag=-1;
for (var i = 0; i < where.options.length; i++)
if (where.options[ i ].selected) {
if (flag!=-1){if (flag==0) gqw='('+gqw;gqw+=' and|or '+where.options[i].value+' **contains|matches|<|>|=[0-9|\']**';flag++}
else {gqw+=where.options[i].value+' **contains|matches|<|>|=[0-9|\']**';flag++; }
}
if (flag>0) gqw+=')';
document.getElementById('gqw').value=gqw;
}
function whereSel(){
//iterate options in nameTest and clone selected ones into here
var sel=document.getElementById('nameTest');
var where=document.getElementById('nameTest2');
var order=document.getElementById('nameTest3');
while (where.childNodes[0]) { where.removeChild(where.childNodes[0]);}
while (order.childNodes[0]) { order.removeChild(order.childNodes[0]);}
var flag=false;
var gqc; var gqo;
for (var i = 0; i < sel.options.length; i++)
if (sel.options[ i ].selected) {
if (flag){gqo=gqc+=','+sel.options[i].value;}
else {gqo=gqc=sel.options[i].value;flag=true;}
where.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
order.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
}
document.getElementById('gqc').value=gqc;
// document.getElementById('gqo').value=gqo;
}
function childSelTest(id,fn,node){
var sl=document.createElement('select');
sl.setAttribute('id',id);//sl.setAttribute('id','nameTest2');
sl.setAttribute('multiple','');
sl.setAttribute('size','8');
sl.setAttribute('onchange',fn);//sl.setAttribute('onchange','whereChange()');
document.getElementById(node).appendChild(sl);//document.getElementById('boxes2').appendChild(sl);
}
function selTest(data){
var sl=document.createElement('select');
sl.setAttribute('id','nameTest');
sl.setAttribute('multiple','');
sl.setAttribute('size','8');
sl.setAttribute('onchange','whereSel()');
for (var i=0;i< data.getNumberOfColumns();i++){
var txt='('+data.getColumnId(i)+') '+data.getColumnLabel(i);
sl.appendChild(createSelItem(txt, data.getColumnId(i)));
}
removeChildrenFromNode(document.getElementById('boxes'));
removeChildrenFromNode(document.getElementById('boxes2'));
removeChildrenFromNode(document.getElementById('boxes3'));
document.getElementById('boxes').appendChild(sl);
childSelTest('nameTest2','whereChange()','boxes2');
childSelTest('nameTest3','optionsChange()','boxes3');
}
function drawViz() {
var gqc=document.getElementById('gqc').value
var gq='select '+gqc;
gq=encodeURIComponent(gq);
var gqw=document.getElementById('gqw').value;
if (gqw!='') gq+=' where '+escape(gqw);
var gqg=document.getElementById('gqg').value;
if (gqg!='') gq+=' group by '+encodeURIComponent(gqg);
var gqo=document.getElementById('gqo').value;
var sel=document.getElementById('gqad');
var selection=sel.selectedIndex;
gqad=sel[selection].value;
if (gqo!='') gq+=' order by '+encodeURIComponent(gqo)+' '+gqad;
var gql=document.getElementById('gql').value;
if (gql!='') gq+=' limit '+encodeURIComponent(gql);
document.getElementById('gqpreview').innerHTML=gq;
//gq=encodeURIComponent(gq);
var url='http://spreadsheets.google.com/tq?tq='+gq+'&key='+key;
var purl='http://spreadsheets.google.com/tq?tqx=out:html&tq='+gq+'&key='+key;
var l=document.getElementById('htmlout');
l.innerHTML="<a href='"+purl+"'>HTML preview URL</a>";
purl='http://spreadsheets.google.com/tq?tqx=out:csv&tq='+gq+'&key='+key;
l=document.getElementById('csvout');
l.innerHTML="<a href='"+purl+"'>CSV URL</a>";
var b=document.getElementById('bookmark');
var bookmark="govspending.php?run=true&gsKey="+key+"&gqc="+encodeURIComponent(gqc)+"&gqw="+escape(gqw)+"&gqo="+encodeURIComponent(gqo)+"&gql="+encodeURIComponent(gql)+"&gqg="+encodeURIComponent(gqg)+"&dps="+encodeURIComponent(dps);
b.innerHTML="<a href='"+bookmark+"'>bookmark</a>";
var query = new google.visualization.Query(url);
query.send(handleQueryResponse2);
}
function handleQueryResponse2(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
removeChildrenFromNode(document.getElementById('preview'));
var typ;
var sel=document.getElementsByName('opType');
for (var i = 0; i < sel.length; i++)
if (sel[ i ].checked) typ=sel[i].value;
switch (typ){
case 'table': tablechart(data);break;
case 'linechart': linechart(data);break;
case 'scatterchart': scatterchart(data);break;
case 'piechart': piechart(data);break;
case 'barchart': barchart(data);break;
case 'columnchart': columnchart(data);break;
default:;
}
//visualization = new google.visualization.Table(document.getElementById('preview'));
//visualization.draw(data, null);
//linechart(data);
//piechart(data);
//scatterchart(data);
}
function tablechart(data){
new google.visualization.Table(document.getElementById('preview')).draw(data, null);
}
function scatterchart(data){
new google.visualization.ScatterChart(document.getElementById('preview')).draw(data, {width: 800, height: 400}); 
}
function linechart(data){
new google.visualization.LineChart(document.getElementById('preview')).draw(data, {width: 800, height: 400}); 
}
function piechart(data){
new google.visualization.PieChart(document.getElementById('preview')).draw(data, {width: 800, height: 400}); 
}
function barchart(data){
new google.visualization.BarChart(document.getElementById('preview')).draw(data, {width: 800, height: 400}); 
}
function columnchart(data){
new google.visualization.ColumnChart(document.getElementById('preview')).draw(data, {width: 800, height: 400}); 
}
</script>
</head>
<body>
<h1>Guardian Datastore Explorer - UK Gov Spending Data, Released November 2010</h1>
<p> Explore the details of the UK Government Spending Data by using the departmental spreadsheets as individual databases (<a href="http://www.guardian.co.uk/news/datablog/2010/nov/19/government-spending-data?CMP=ouseful_gsdde">Guardian Data Blog: data details</a>). For information about the query syntax, see <a href="http://code.google.com/apis/visualization/documentation/querylanguage.html">Google Chart Tools: Query Language Reference (Version 0.7)</a>. For more details about this application, see <a href="http://blog.ouseful.info">OUseful.info</a>. <em>Tony Hirst, Dept of Communication and Systems, The Open University</em></p>
<form name="config">
<input type="hidden" size=30 id='gsKey' <?php if($_GET['gsKey']!='') echo "value=\"".$_GET['gsKey']."\"" ?> /> <input type="hidden" size=3 id='gsSheet' <?php if($_GET['gid']!='') echo "value=\"".$_GET['gid']."\"" ?> /> <br/>
<p>Grab a sheet from the Guardian Data store: <select name="dList" id="datastore"></select><input type="button" value="Preview" onclick="preview3()" />
<p>If you are prompted to sign in, wait a few seconds and then try again.</p>
<div id="statrep"></div>
</form>
<hr/>
<div id="headings"></div>
<br/><br/>
<p>Using data from spreadsheet:<p><h1 id='deptsrc'><?php if($_GET['dps']!='') echo $_GET['dps'] ?></h1>
<br/><br/><hr/>
<div id="preview"></div>
<div id="linechart"></div>
<div id="scatterchart"></div>
<div id="piechart"></div>
<br/>
<p>Try out some visualisation queries here...</p>
<table id='colLookup' border='1'></table>
<br/>
<p>Select the columns from the list box (ctrl+click for multiple selections in IE, command+click on a Mac) or type the elements directly into the appropriate text box.</p><br/>
<form>
<div><tt>select</tt> <span id='boxes'></span> <input type="text" id="gqc" size=20 <?php if($_GET['gqc']!='') echo "value=\"".$_GET['gqc']."\"" ?> /><br/>
<br/><tt>where</tt> <span id='boxes2'></span> <input type="text" id="gqw" size=30 <?php if($_GET['gqw']!='') echo "value=\"".stripslashes($_GET['gqw'])."\"" ?> /> <tt>group by</tt> <input type="text" id="gqg" size=30 <?php if($_GET['gqg']!='') echo "value=\"".$_GET['gqg']."\"" ?> />
<br/><br/><tt>order by</tt> <span id='boxes3'></span> <input type="text" id="gqo" size=30 <?php if($_GET['gqo']!='') echo "value=\"".$_GET['gqo']."\"" ?> /> <select id="gqad" name="gqad"><option value='asc' selected>asc</option><option value='desc'>desc</option>
</select> <tt>limit</tt> <input type="text" id="gql" size=10 <?php if($_GET['gql']!='') echo "value=\"".$_GET['gql']."\"" ?> /></div>
<div>Display as:
| Table <input type="radio" name="opType" value="table" checked />
| Scatter chart <input type="radio" name="opType" value="scatterchart"/>
| Line chart <input type="radio" name="opType" value="linechart"/>
| Pie chart <input type="radio" name="opType" value="piechart"/>
| Bar chart <input type="radio" name="opType" value="barchart"/>
| Column chart <input type="radio" name="opType" value="columnchart"/>
|
<input type="button" value='Go Fish' onclick="drawViz()"/>
</div>
<div>So you are asking: <em id='gqpreview'></em></div>
<div>Here is the URL for that query: <span id='htmlout'></span>, <span id='csvout'></span></div>
<div>here is a link to this page: <span id="bookmark"></span></div>
</form>
<h2>Go fish...</h2>
<form name='qform'>
<div>Now it's time to write your own query in two parts - the columns you want to select, and the conditions you want each returned result to satisfy. ***FOLLOWING EXAMPLES NEED TWEAKING FOR CURRENT CONTEXT</div>
<table border=1><tr><th>Department</th><th><tt>select</tt> input</th><th><tt>where</tt> input</th><th><tt>group by</tt> input</th><th><tt>order by</tt> input</th><th><tt>limit</tt> input</th><th>Comments</th></tr>
<tr><td>All</td><td>*</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>20</td><td>Display everything (dangerous! May take some time and/or kill your browser! Hence the limit term...) [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=*&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
<tr><td>Treasury</td><td>F,G,H</td><td>&nbsp;</td><td>Display columns F,G, H (Expense Type, Expense area and supplier)</td><td>&nbsp;</td><td>20</td><td>Use this approach to get a feel for what a column contains [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=F%2CG%2CH&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
<tr><td>Home Office</td><td>E,I,K</td><td>K > 20000000</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>Display columns E, I and K (Entity, Supplier, Amount) sums over 20 million [<a href="govspending.php?run=true&gsKey=tnBJa5GzHGs6BdHL2K-5N9w&gqc=E%2CI%2CK&gqw=K%20%3E20000000&gqo=&gql=&gqg=">try it</a>]</td></tr>
<tr><td>DFID</td><td>H,I,K</td><td>H contains 'Africa' and K > 10000000</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>View suppliers and amounts over a million for expense areas containing 'Africa'; (as well as the free text search 'contains', 'matches' must match exactly) [<a href="govspending.php?run=true&gsKey=0AonYZs4MzlZbdFdjaGVOVFAydm5sUUlTb09JUzFaNXc&gqc=H%2CI%2CK&gqw=H%20contains%20%27Africa%27%20and%20K%20%3E%2010000000&gqo=&gql=&gqg=&dps=International%20Development%20(DFID)">try it</a>]</td></tr>
<tr><td>Government Equalities Office</td><td>F,G,H,J</td><td>J > 100000</td><td>&nbsp;</td><td>J <em>desc</em></td><td>&nbsp;</td><td>View suppliers and amounts over a hundred thousand, sorted by amount (note that if you click on column headings in the table, you can sort the table by that column) [<a href="govspending.php?run=true&gsKey=tKUQuJiBekBQxlDcasQFFaQ&gqc=F%2CG%2CH%2CJ&gqw=J%20%3E%20100000&gqo=J&gql=&gqg=&dps=Government%20Equalities%20Office%20(GEO)">try it</a>]</td></tr>
<tr><td>Health</td><td>H,J</td><td>J < 0</td><td>&nbsp;</td><td>J <em>asc</em></td><td>&nbsp;</td><td>View suppliers and amounts where the amount is negative... [<a href="govspending.php?run=true&gsKey=tPdRIE1Dtovo5adgjt_D5rA&gqc=H%2CJ&gqw=J%20%3C%200&gqo=J&gql=&gqg=&dps=Health%20(DOH)">try it</a>]</td></tr>
<tr><td>DCMS</td><td>G,H,I,sum(K),count(K)</td><td>K >0 and H contains 'Museums'</td><td>G,H,I</td><td>&nbsp;</td><td>&nbsp;</td><td>Look at expense type, area and supplier where area contains 'Museums', then group by expense type, area and supplier, reporting total amount awarded to each supplier correspondingly, along with a count of how many payments were made[<a href="govspending.php?run=true&gsKey=tKlkQ_ocEHaUa3RDgIee5sQ&gqc=G%2CH%2CI%2Csum(K)%2Ccount(K)&gqw=K%20%3E0%20and%20H%20contains%20%27Museums%27&gqo=&gql=&gqg=G%2CH%2CI&dps=Culture%2C%20Media%20and%20Sport%20(DCMS)">try it</a>]</td></tr>
</table>
<script type="text/javascript" src="http://feeds.delicious.com/v2/json/psychemedia/gdatastore_11_2010?callback=deliList"></script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment