Skip to content

Instantly share code, notes, and snippets.

@flother
Last active February 18, 2016 13:49
Show Gist options
  • Save flother/57e88bc920ac1b12cf95 to your computer and use it in GitHub Desktop.
Save flother/57e88bc920ac1b12cf95 to your computer and use it in GitHub Desktop.
Publish a chart of CSV data queried in real-time using SQL
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>Liberal Democrat polls and quarterly polling averages</title>
<script src="//vega.github.io/vega-editor/vendor/d3.min.js"></script>
<script src="//vega.github.io/vega/vega.min.js"></script>
</head>
<body>
<div id="vis"></div>
<script type="text/javascript">
function parse(spec) {
vg.parse.spec(spec,
function (error, chart) {
chart({el:"#vis"}).update();
});
}
parse("spec.json");
</script>
</body>
</html>
{
"width": 800,
"height": 265,
"data": [
{
"name": "quarterly_averages",
"url": "http://data.flother.is/ukpolls?q=SELECT+TO_CHAR(date%2BINTERVAL+%2745+days%27,%27YYYY-MM-DD%27)+AS+date,+AVG(pollster_avg_share)+AS+avg_share+FROM+(SELECT+DATE_TRUNC(%27quarter%27,date)+AS+date,pollster,AVG(share)+AS+pollster_avg_share+FROM+ukpolls+WHERE+date%3E=CURRENT_DATE-INTERVAL+%275+years%27+AND+party=%27Liberal+Democrats%27+GROUP+BY+date,pollster)+AS+polls+GROUP+BY+date+ORDER+BY+date",
"format": {
"type": "csv",
"parse": {
"date": "date",
"avg_share": "number"
}
}
},
{
"name": "polls",
"url": "http://data.flother.is/ukpolls?q=SELECT+date,share+FROM+ukpolls+WHERE+date%3E=CURRENT_DATE-INTERVAL+%275+years%27+AND+party=%27Liberal+Democrats%27+ORDER+BY+date",
"format": {
"type": "csv",
"parse": {
"date": "date",
"share": "number"
}
}
}
],
"scales": [
{
"name": "xscale",
"type": "time",
"range": "width",
"domain": {
"data": "quarterly_averages",
"field": "date"
}
},
{
"name": "yscale",
"range": "height",
"domain": {
"data": "polls",
"field": "share"
}
}
],
"axes": [
{
"type": "x",
"scale": "xscale"
},
{
"type": "y",
"scale": "yscale",
"title": "%"
}
],
"marks": [
{
"type": "symbol",
"from": {
"data": "polls"
},
"properties": {
"enter": {
"x": {
"field": "date",
"scale": "xscale"
},
"y": {
"field": "share",
"scale": "yscale"
},
"size": {"value": 8},
"fill": {"value": "#fdbb30"},
"opacity": {"value": "0.2"},
"strokeOpacity": {"value": "1"}
}
}
},
{
"type": "line",
"from": {
"data": "quarterly_averages"
},
"properties": {
"enter": {
"x": {
"field": "date",
"scale": "xscale"
},
"y": {
"field": "avg_share",
"scale": "yscale"
},
"strokeWidth": {"value": 3},
"stroke": {"value": "#fdbb30"}
}
}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment