Visualizing data using the Google Calendar Chart
This example shows how to pull data from a Socrata Dataset (in this case, the City of Chicago crime records) with the Google “Calendar Chart” visualization. As a bonus, we will then embed that chart into a Socrata Perspectives page.
The Google Charts library provides a number of different chart types for visualization that can be leveraged using the SODA API. The “Calendar Chart” is useful when you have incident level data for which you would like to visualize by daily density over the course of a year.
Prerequisites
There are a number of prerequisites necessary before starting with this example:
- Most obviously, you’ll need to work with data in a Socrata dataset containing time series data that can be aggregated at a daily level. If you’re looking for a dataset to work with, we recommend you explore the Open Data Network, where you can find a full catalog of datasets from our awesome customers.
- You’ll need some basic familiarity with JavaScript before starting. If you’ve never worked with JavaScript before, we recommend this course from CodeAcademy.
- We’ll also be making use of jQuery to simplify some of our development tasks.
Check out all of the different chart types available through the Google Charts library.
Craft your SoQL query
The Calendar Chart requires at a minimum two fields - a date and a numeric value. So we’ll use the SoQL $group and $group parameters to aggregate our dataset to daily roll-ups, this results in a SoQL query that looks like the following:
The results will be aggregated like the following:
[
{
"count": "762",
"day": "2016-09-04T00:00:00.000"
},
{
"count": "842",
"day": "2014-07-20T00:00:00.000"
},
...
]Fetch data using jQuery
We’ll define a fetchValues function that uses the jQuery.get(...) function to fetch data from the SODA API, transform it into an array of JavaScript Date objects and counts, and returns it for handling:
var fetchValues = function() {
return $.get(
'https://data.cityofchicago.org/resource/6zsd-86xi.json',
{
'$select' : 'date_trunc_ymd(date) as day, count(*)',
'$where' : "date > '2014-01-01'",
'$group' : 'day'
}
).pipe(function(res) {
var ary = []
$.each(res, function(idx, rec) {
ary.push([new Date(rec.day.replace("T00:00:00", "T12:00:00")), parseInt(rec.count)]);
});
return ary;
});
};Visualize the data with Google Charts
Once we’ve got our data from the SODA API, we’ll plumb it into the Google Calendar Chart library to visualize the actual data. We do this in our drawChart function:
- First we initialize our
DataTableand add two columns - one for our date and another for our value. - Then we initialize our
Calendar, feeding it our target element by ID,calendar_basic. - Finally, we draw our chart, feeding it configuration via our
optionsobject.
var drawChart = function(ary) {
var data = new google.visualization.DataTable();
data.addColumn({ type: 'date', id: 'Date' });
data.addColumn({ type: 'number', id: 'count' });
data.addRows(ary);
var chart = new google.visualization.Calendar(document.getElementById('calendar_basic'));
var options = {
title: "City of Chicago Police Incidents Over Time",
height: 500,
};
chart.draw(data, options);
};Finally, we tie things all together by having the Google Charts library call our function when it loads:
google.charts.setOnLoadCallback(function() {
fetchValues().done(function(data) {
drawChart(data);
});
});BONUS: Embed your visualization in Socrata Perspectives
To get access to Socrata Perspectives page, you'll need to work for one of our awesome customers. Maybe your local government is hiring!
Once you’ve created your visualization, you can use the ability for Perspectives to include embedded content to embed your visualization into a new story. To do so, first you’ll need to craft a very simple HTML page like the following which loads your visualization. Make sure you include in that page the script tags to load your dependencies, in this case both jQuery and the Google Charts library.
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script
src="https://code.jquery.com/jquery-3.1.1.min.js"
integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8="
crossorigin="anonymous"></script>
</head>
<body>
<div id="calendar_basic" style="width: 1000px; height: 350px;"></div>
<script type="text/javascript">
(function() {
// Initialize the charting library
google.charts.load("current", { packages:["calendar"] });
var fetchValues = function() {
return $.get(
'https://data.cityofchicago.org/resource/6zsd-86xi.json',
{
'$select' : 'date_trunc_ymd(date) as day, count(*)',
'$where' : "date > '2014-01-01'",
'$group' : 'day'
}
).pipe(function(res) {
var ary = []
$.each(res, function(idx, rec) {
ary.push([new Date(rec.day.replace("T00:00:00", "T12:00:00")), parseInt(rec.count)]);
});
return ary;
});
};
var drawChart = function(ary) {
var data = new google.visualization.DataTable();
data.addColumn({ type: 'date', id: 'Date' });
data.addColumn({ type: 'number', id: 'count' });
data.addRows(ary);
var chart = new google.visualization.Calendar(document.getElementById('calendar_basic'));
var options = {
title: "City of Chicago Police Incidents Over Time",
height: 500,
};
chart.draw(data, options);
};
google.charts.setOnLoadCallback(function() {
fetchValues().done(function(data) {
drawChart(data);
});
});
})();
</script>
</body>
</html>Then, to add it as a content block in your story:
- When editing your story, click “Add Content” to bring up the palate, and drag in a new content block.
- Click “Insert” and then “HTML Embed”
- Where it says “Paste or type HTML code”, paste in the entire contents of your HTML snippet and click “Insert”
That’s it! Click below to see what this looks like.