Using Google Fusion Tables to Add Real-Time Feeds to MapBox Maps

January 28 2013 by Chris Herwig

Last Thursday the United Nations opened an inquiry into the “civilian impact of the use of drones and other forms of targeted killing.” Data - data about casualties, targets, frequency, and other topics - will play an important role in this investigation, led by UN special rapporteur for human rights and counterterrorism. Visualization can add important context to help the public follow this inquiry. Adding markers dynamically can be a serious gamechanger for visualizations using time-sensitive data, and something that you can do with MapBox.

In this post, I’ll walk through how to use MapBox.js to pull dynamic data from a Google Fusion table onto a MapBox map. Following these steps will let you turn tabular data into an interactive map like this:

U.S. Drone Strikes in Pakistan, 2004-2013

Our new MapBox.js library makes it easy to visualize complex and dynamic datasets in real-time. Over the past few months, we’ve been refining the mapbox.markers features of MapBox.js, which allow you to add markers to your MapBox maps using client-side JavaScript, rather than rendering them as separate map layers.

The Bureau of Investigative Journalism provides a live-updated database of U.S. covert drone strikes in Pakistan going back to 2004. There are other sources for this information, including New America Foundation and The Long War Journal, each of which has its own advantages and disadvantages.

The Google Fusion Tables API is slightly different from the Google Spreadsheet API, which we blogged about using with MapBox last year. In general, sticking to Google Spreadsheets has a lot of benefits, the most important of which being version tracking, which Fusion Tables lacks entirely. The two main things to know about Google Fusion Tables integration are that you’ll need to sign up for an API key, and that the Fusion Tables JSON response is formatted as rows. You can see a sample response below.

{"kind":"fusiontables#sqlresponse",
 "columns":["Location","Strike ID","Bureau ID","Date","Area","Target","Target Group","Minimum Total Killed","Number of deaths","Civilians Killed","Injured","Children Killed","Summary","Latitude","Longitude"],
 "rows":[
 ["Wana","","B1","17-Jun-04","South Waziristan","","","","6-8","2","At least 1","2","First known drone strike in Pakistan kills 7, including Taliban leader and 2 children. Wana, South Waziristan.","32.305125653338","69.57624435424805"],
 ["Toorikhel","","B2","8-May-05","North Waziristan","","","","2","","","","Two killed, including al Qaeda operative, near Mir Ali, North Waziristan.","32.986779893387755","70.26082992553711"],["Mosaki","","B3","5-Nov-05","North Waziristan","","","","8","4-8","1","3","Failed strike against Al Qaeda official kills 8 including 3 children. Mosaki, North Waziristan.","32.999881911897106","70.34082412719727"]
 ]
}

You can preview the Fusion Table from your browser using https://www.googleapis.com/fusiontables/v1/query?sql=SELECT%20*%20FROM%20' + fusion_table_id + ‘&key=’ + your_api_key. Next, identify the columns of interest for your map. For example, latitude is located at index position 13, and longitude is located at index position 14. I’m combining the column values with html and additional text for the marker description property.

I’m taking advantage of a few JavaScript libraries, which you’ll want to include in your page.

<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js'></script>
<script src='https://api.tiles.mapbox.com/mapbox.js/v0.6.6/mapbox.js'></script>
<script src='http://underscorejs.org/underscore-min.js'></script>
<link href='https://api.tiles.mapbox.com/mapbox.js/v0.6.6/mapbox.css' rel='stylesheet' />

In the body of the page, add a div with the id of ‘map’ <div id=‘map’></div>. Finally, include a script element with the following code, modified to include your values for YOUR_API_KEY, FUSION_TABLES_ID, and MAPBOX_MAP_ID.

function fusionTables(id, callback) {
    function response(x) {
        var features = [];
        if (!x || !x.rows) return features;
        for (var i = 0; i < x.rows.length; i++) {
            var entry = x.rows[i];
            //
            // modify the index values below based on the columns of your table
            //
            var feature = {
                geometry: {
                    type: 'Point',
                    coordinates: [parseFloat(entry[14]), parseFloat(entry[13])]
                },
                properties: {
                    "marker-color": "#ff0000",
                    "marker-size": "small",
                    "marker-symbol": "heliport",
                    "title": entry[0],
                    "description": "<p>Number of Deaths:" + entry[8] + "</p>"
                }
            };
            features.push(feature);
        }

        return callback(features);
    }
    // enter  enter your google fusion tables api key below
    var key = "YOUR_API_KEY";
    var url = 'https://www.googleapis.com/fusiontables/v1/query?sql=SELECT%20*%20FROM%20' + id + '&key=' + key + '&typed=false&callback=jsonp';
    $.ajax({
        url: url,
        dataType: 'jsonp',
        jsonpCallback: 'jsonp',
        success: response,
        error: response
    });
}

// enter the id of the fusion table you want to show on the map

fusionTables('FUSION_TABLES_ID', function(features) {
    features = _.map(features, function(f) {
        f.properties.title = f.properties.title;
        f.properties.description = f.properties.description;
        return f;
    });
    var map = mapbox.map('map');

    // enter the MapBox map id you want to display on the page

    mapbox.load('MAPBOX_MAP_ID', function(o) {
        map.addLayer(o.layer);
        map.setZoomRange(6, 12);
        map.ui.hash.add();
        map.zoom(8).center({
            lat: 32.604,
            lon: 69.791
        });
        var markerLayer = mapbox.markers.layer().features(features);
        map.addLayer(markerLayer);
        mapbox.markers.interaction(markerLayer).add();
    });
});

U.S. Drone Strikes in Pakistan | MapBox

Now that we have the data on the map, we can take advantage of the other MapBox.js functions for GeoJSON marker layers. My U.S. Drone Strikes in Pakistan page builds off of the timeline example map to visualize the Bureau of Investigative Journalism’s drone strike dataset.

U.S. Drone Strikes in Pakistan | MapBox

If you want to know more about the impact of drone strikes, check out the Living Under Drones project at Stanford Law School.