Build your own map using Fusion Tables and Javascript

10 August, 2011 - Robin Kraft

In developing the mobile app described in an earlier blogpost, I learned a lot about using Javascript and Google Fusion Tables, Google's handy data publishing and visualization tool. So in this post, I'm going to show how to use jQuery's $.get function to query a specific table and add the result to a map, as shown here.

[Update: Check out the comments for an explanation of why you might want to do this.]

Things to keep in mind:

I'll be using this query throughout this post:

SELECT p201012, lat, lon FROM 1043910 LIMIT 100

For the curious, this query retrieves the first hundred records from table 1043910, which contains data on forest clearing activity in Indonesia generated using satellite data and the FORMA algorithm. p201012 refers to the probability (0-100) of clearing as of December 2010 (we're up to June 2011 now, but it's not in the table).
One row returned by that query looks like this (probability, lat, lon): 68,-7.870833,129.6253
The Javascript code driving the map is available in ft_js_maps.js

Background on single origin policy and JSONP

For a Javascript newbie like me it was not entirely straightforward why I couldn't just use a standard Fusion Tables URL like this one to get data into a Javascript program. That query returns a comma-separated datafile, which you could easily parse using Javascript. However, for security reasons, a Javascript program can only retrieve documents or scripts - or data, in this case - stored on the same domain. The key exception to this rule is the mighty JSONP object.

No idea what JSONP means, or why this exception exists? No problem! All you have to know is that Fusion Tables provides an undocumented JSON API for getting a JSONP object back from a GET request. As far as I can tell, this is the only way to use Javascript to retrieve and manipulate raw data stored in Fusion Tables. But this means it's actually easy to query a table with Javascript - you just need to add &jsonCallback=? to the end of the URL used with the jQuery $.get function (see queryUrlTail below).

Getting data

Let's assume your data and query are all set, and you're ready to retrieve some data. Here's what your query function might look like:

function getData(table) {
    // Builds a Fusion Tables SQL query and hands the result to dataHandler()

    var queryUrlHead = 'http://www.google.com/fusiontables/api/query?sql=';
    var queryUrlTail = '&jsonCallback=?'; // ? could be a function name
    
    // write your SQL as normal, then encode it
    var query = "SELECT p201012, lat, lon FROM " + table + " LIMIT 100";
    var queryurl = encodeURI(queryUrlHead + query + queryUrlTail);

    var jqxhr = $.get(queryurl, dataHandler, "jsonp");
}

That last line is key. Without the third parameter, jQuery's $.get function will throw a cross-domain error:

XMLHttpRequest cannot load [YOUR QUERY URL HERE]. Origin [SOME ORIGIN] is not
allowed by Access-Control-Allow-Origin.

So be sure to include jsonp as that third parameter. If all goes well, you'll get something like this:

jQuery16109070935510098934_1312998050216({"table":{"cols":["p201012","lat","lon"],
"rows":[[32,-7.829167,131.2274],[25,-7.854167,131.3026],[29,-7.904167,131.2679]]}})

It may be helpful to know that Google recently changed how browsers interpret data from Fusion Tables. So although the returned data is actually JSON, your browser will think it's a CSV file. At the time of writing, you could still safely ignore this and assume you're getting JSON back from Fusion Tables.

Creating markers from data

Now that we've got some data, let's add it to a map using the dataHandler function below. The only detail I want to point out is that for a JSON data object d the rows of data are actually stored in d.table.rows, as you might expect given the structure of the JSON object above. You then loop through the rows array and create a new marker for each "row", using the standard Google Maps code you already know.

function dataHandler(d) {
    var data = d.table.rows;
    infoWindow = new google.maps.InfoWindow();
    
    for (var i = 0; i < data.length; i++) {
        var latlon = new google.maps.LatLng(data[i][1], data[i][2]);
        var probability = data[i][0];

        var marker = new google.maps.Marker({
            position: latlon,
            rowid: i,
            prob: probability,
            map: mymap
        });
        var fn = markerClickFunction(mymap, marker, infoWindow);
        google.maps.event.addListener(marker, 'click', fn);
    }
}

Note that I'm storing bits of data from the query inside the marker instance. Yes, you can add arbitrary attributes to Javascript objects! Am I the only one who didn't know that? Anyway, these custom attributes will be accessible to the infoWindow object activated by the listener. This comes in handy if you need to build an infoWindow from scratch using data from your query:

function markerClick(map, m, ifw) {
    return function() {
        // In case there's already an infoWindow open
        ifw.close(map)
        
        // Build html content, using data stored in the marker instance
        var infoHtml = '<strong>rowid: '+ m.rowid + ' prob: ' + m.prob
        infoHtml += '</strong><br />' + m.position.toString() + "</p>";

        // Standard infoWindow initialization steps
        infoWindow.setContent(infoHtml);
        infoWindow.setPosition(m.position);
        infoWindow.open(map);
    };
}

And with that, we're done!

Build your own map using Fusion Tables and Javascript

Background on single origin policy and JSONP

Getting data

Creating markers from data

Related Posts