CartoDB.js Charting with the SQL API

Generate a line chart of the area covered by basket teams during a NBA moment play.

by ricajess

HTML

<script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.14/cartodb.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.6/d3.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.8.1/nv.d3.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.8.1/nv.d3.css">
<div id="chart"></div>

JavaScript

// SQL to get the data
var sqlStatement = 'with positions as (select game_clock, team_id, ST_MakePoint(x_loc,y_loc) as position from nba_moment where team_id != -1 ) select st_area(st_convexhull(st_collect(position ))) area, game_clock, team_id from positions group by game_clock, team_id order by game_clock, team_id';
// Get the data using CartoDB SQL API
cartodb.SQL({user: 'jsanz'})
    .execute(sqlStatement).done(function (data) {
    // Get the first value of the game clock
    var minGameClock = Math.min.apply(null, 
            data.rows.map(function(row){return row.game_clock}));
    
	// Create the data object filtering the rows by team_id
    areaSeries = [{
        values: data.rows.filter(function (row) {
            return row.team_id == 1610612745}),
        key: 'LA Clippers',
        color: 'red'
    }, {
        values: data.rows.filter(function (row) {
            return row.team_id == 1610612746}),
        key: 'Houston Roquets',
        color: 'gray'
    }];
    
	// Create the chart using custom x/y functions for our data
    chart = nv.models.lineChart()
        .options({
            useInteractiveGuideline: true,
        	x: function(row){return row.game_clock - minGameClock},
        	y: function(row){return row.area.toFixed(1)}
        });
    // Set up domain and labels
    chart.yDomain([0, 1100]);
    chart.xAxis.axisLabel("Time (s)")
        .tickFormat(d3.format(',.1f'));
    chart.yAxis.axisLabel('Area covered (foot²)');
    // Generate the chart inside the div
    d3.select('#chart').append('svg')
        .datum(areaSeries)
        .call(chart);
    // Window resize handling
    nv.utils.windowResize(chart.update);
});