Update jqGrid table with the results of Fusion Tables query in a Google Maps v3 page

Question

I am looking to understand how to update a jqGrid table from Fusion Tables (FT) -

at the moment I can search or scroll on a Google Map, send an event listener that compiles a FT query of the spatial bounds of the viewport/map, to get a new set of results. I want to use the new FT query string (or could use the Google code to retrieve the data - query.send(getData);) to update the jqGrid table with the new values.

Before I started using jqGrid, I tried/suceeded with the Google Visualisation API, and some of that code is below. Could anyone suggest how to move from table.draw, to loading/reloading a jqGrid table? Thanks a lot in advance.

function tilesLoaded() {
        google.maps.event.clearListeners(map, 'tilesloaded');
        google.maps.event.addListener(map, 'zoom_changed', getSpatialQuery);
        google.maps.event.addListener(map, 'dragend', getSpatialQuery);
        getSpatialQuery();  
    }   

    function getSpatialQuery() {
      sw = map.getBounds().getSouthWest();
      ne = map.getBounds().getNorthEast();
      var spatialQuery = "ST_INTERSECTS(latitude, RECTANGLE(LATLNG(" + sw.lat() + "," + sw.lng() + "), LATLNG(" + ne.lat() + "," + ne.lng() + ")))";

      changeDataTable(spatialQuery);
    }

function changeDataTable(spatialQuery) {
  var whereClause = "";
  if(spatialQuery) {
    whereClause =  " WHERE " + spatialQuery;
  }
  var queryText = encodeURIComponent("SELECT 'latitude', 'longitude', 'name' FROM xxxxxxxx" + whereClause + " LIMIT 50");
  var query = new google.visualization.Query('http://www.google.com/fusiontables/gvizdata?tq='  + queryText);
  query.send(getData);
}

function getData(response) {
  var table = new google.visualization.Table(document.getElementById('visualization'));
  table.draw(response.getDataTable(), {showRowNumber: true});
}

Oh, and I used Oleg's code jqGrid returns blank cells as a basis for just seeing if I could get a simple multi-select table to pull data from my FT - that worked fine with the simple mod of

url: 'http://www.google.com/fusiontables/api/query?sql=' +

In which web browser you have blank cells in [the demo](http://www.ok-soft-gmbh.com/jqGrid/google-tables.htm) from [the answer](http://stackoverflow.com/questions/4317646/jqgrid-returns-blank-cells/4326986#4326986)? I had the problem some time before in IE9 because of security problems, but now all work correctly. — Oleg, Oct 10 '11 at 08:29
ah sorry - perhaps I wasn't clear in referencing your great example. If I just want a "static" table from a Fusion Table source your example worked fine. What I want to do is use your example of how to make a table from a FT source, but make the table update every time a user moves/updates the Google Map. I was trying in both Chrome, Firefox and IE9 on a local file — JPMox, Oct 10 '11 at 11:59
Sorry, I don't know Fusion Table good enough. Is there an API which uses URL with parameters to get the information which you need in the JSON/JSONP format? — Oleg, Oct 10 '11 at 12:34
http://groups.google.com/group/fusion-tables-users-group/browse_thread/thread/caef336c62ee8c0e/d55662e2aea37b99?pli=1 — JPMox, Oct 10 '11 at 14:20
Sorry, that timed out (was trying to edit my comment, doh me). Doing some more searching on a jsoncallback, I found this thread: http://www.reddmetrics.com/2011/08/10/fusion-tables-javascript-query-maps.html so I guess I could try that out, although I am not too sure how to use the example in that URL to update a jqGrid table. Thanks for replying btw, really appreciate it! — JPMox, Oct 10 '11 at 14:32
In my understanding all is so: You create a fusion table and use the table as input for the Google map. If you want to display additionally the same information in jqGrid table you can do this exactly like in my demo which you referenced. — Oleg, Oct 10 '11 at 14:40

score 0 · Accepted Answer · answered Oct 27 '11 at 07:18

In case this helps someone, I've taken some of the code I came up with and pasted it below:

// You can get the map bounds via then pass it via a function (below is hacked from several functions
sw = map.getBounds().getSouthWest();
ne = map.getBounds().getNorthEast();
var whereClause = "ST_INTERSECTS(latitude, RECTANGLE(LATLNG(" + sw.lat() + "," + sw.lng() + "), LATLNG(" + ne.lat() + "," + ne.lng() + ")))";

//construct the URL to get the JSON
var queryUrlHead = 'http://www.google.com/fusiontables/api/query?sql=';
var queryUrlTail = '&jsonCallback=?'; // 
var queryOrderBy = ' ORDER BY \'name\' ASC';
var queryMain = "SELECT * FROM " + tableid + whereClause + queryOrderBy + " LIMIT 100";
var queryurl = encodeURI(queryUrlHead + queryMain + queryUrlTail);

//use the constructed URL to update the jqGrid table - this is the part that I didn't know in my above question
$("#gridTable").setGridParam({url:queryurl});
$("#gridTable").jqGrid('setGridParam',{datatype:'jsonp'}).trigger('reloadGrid');

Update jqGrid table with the results of Fusion Tables query in a Google Maps v3 page

1 Answers1