« « Changing definitions of what it means to be mentored

“A journalist could do this!” – Reflections on Phil Meyer’s “Paper Route” » »

How To Count Queried Rows in a Google Fusion Table

Posted by on Feb 5, 2012 in Blog, Uncategorized | 3 Comments

In my “free” time, I help people who are also on the JavaScript learning journey.  Here’s a query, paraphrased.

How can I count how many points I currently have displayed on a Fusion Table layer of a Google Map?

This would be especially helpful when we’re using filters, to show how many points are currently displaying.

I’m not the first one to come up with this, for more on the subject, and the creators of the code this is based off of, pleas see the “Getting Data” section of Robin Kraft’s post here.

If we assume that searchString stands for the value a user inputs in a filter (look into how to use HTML forms and connect it with JavaScript elsewhere, that’s left as an exercise to the reader, and I’m pulling info from Fusion Table ID which we will call…tableid…here’s the code you would need to get that count.

 

(The indentation in my code samples is ugly right now, and I can’t figure out how to get this plugin to cooperate, but the code should work, and you can fix the indentation for extra credit.  Cool?)

 

First, set a variable which contains the beginning of a URL, which allows you to query the Google Fusion Table SQL API.

var queryUrlHead = 'http://www.google.com/fusiontables/api/query?sql=';

 

Now, at the end of the URL, since we are reaching across domains (from where your site lives to google.com), we need to use a technology called JSONP.

var queryUrlTail = '&jsonCallback=?';

 

Create a variable that has your query string, which is specified in the Google Fusion table docs. The where clause shows how you want to limit your table, search string allows user to specify what you are limiting by. Instead of selecting *, which is everything in the table, or a specific geographic data column, we select count, to count up how many rows in our Fusion Table fit this query.

var query = "SELECT COUNT() FROM " + tableid + " WHERE 'Column Name' CONTAINS IGNORING CASE '" + searchString + "'"

 

Then, we encode that query, so we can send it across the Web.

var queryurl = encodeURI(queryUrlHead + query + queryUrlTail);

 

Use jQuery’s get method to get the result of the URL. The first parameter in this function is the URL to get, which we have just constructed. The third parameter says we want to use jsonp, to go across domains. The second parameter is a function which does something with the “data” that is returned.

What this function does, in this sample, is it fills in a div with the id of “count” on the HTML page with the count of rows. We get the count of rows by digging into the returned data object. We use try/catch because the data rows will not exist if no items are returned with the query used. So, if we get an error, because there are no rows, we set the count div’s HTML equal to 0, to show that no rows are displayed. The 0 display should only occur when we have a blank map.

 

var jqxhr = $.get(queryurl,
function(data){
try{
$('#count').html((data.table.rows[0][0]));
}
catch(err){
$('#count').html('0')
}
},
"jsonp");

 

Here’s the complete code block, for copying/pasting fun:

var queryUrlHead = 'http://www.google.com/fusiontables/api/query?sql=';
var queryUrlTail = '&jsonCallback=?';
var query = "SELECT COUNT() FROM " + tableid + " WHERE 'My column' CONTAINS IGNORING CASE '" + searchString + "'"
var queryurl = encodeURI(queryUrlHead + query + queryUrlTail);
var getCount = $.get(queryurl,
function(data){
try{
$('#count').html((data.table.rows[0][0]));
}
catch(err){
$('#count').html('0');
}
},
"jsonp");

Ideally, this would be used as part of a function that uses filters to change displays on the map. Just a quick post here in case this helps others googling around — it wasn’t as easy to find this solution as I might have hoped. So, search engines, read this: How To Count Queried Rows in a Google Fusion Table and Google Map. Are you happy, SEO machine?

« « Changing definitions of what it means to be mentored

“A journalist could do this!” – Reflections on Phil Meyer’s “Paper Route” » »
  • Clarkie6

    Really great, helped me out a lot with the jquery easiness of the Get.  I am pretty good with the fusion tables and google maps api stuff, not so much with js/jQuery.  Thanks for the tutorial!

    [Reply]

  • Clarkie6

    also, this is a great resource for the sql constraints for fusion tables, I love the spatial query in the where clause!  
    https://developers.google.com/fusiontables/docs/developers_reference 

    [Reply]

  • Pradeep Shankar M

    What is the query in fusion table api v1

    [Reply]