Pass each object in JSON array to query

Question

I'm running query that looks like this:

var ready_data= $.getJSON('URL HERE', function (data) {
var id_value= data.rows.map(function (row) {
  return [row[1]+","+ row[2]];
});

The var id_value comes back in my console log with something like this:

Array[1]]0: Array[1]0: "34.154321,-118.349126"length: 1__proto__: Array[0]1: Array[1]2: Array[1]3....

I'm only showing part of what's returned for clarity. But basically, I get back an array for each item that matches my query criteria from my getJSON (they're map coordinates).

What I want to do is put each of these arrays (one at a time) into a query and get back the results. Right now, I'm using this:

 $.each(id_value,function(data) {

  var key='MY API KEY'
          var table ='TABLE NUMBER'
           var sql = "SELECT COUNT() from " + table +
              " WHERE ST_INTERSECTS(coordinates,CIRCLE(LATLNG("+id_value+"), 16093 ))";
              url = "https://www.googleapis.com/fusiontables/v1/query?key=" +     encodeURIComponent(key) + "&sql=" + encodeURIComponent(sql) + '&callback=?';
 $.getJSON(url, function (data) {
              $('#info2').append((data.rows ? data.rows[0] : 0) );
          });
});

This doesn't work because it builds a query string with every single pair of coordinates in the same query instead of one query per array.

How should I be doing this so that I've got a functioning .each loop (or something totally different)?

That part: ... LATLNG("+id_value+"), ... looks like a genuine mistake to me. Ought to be ... LATLNG("+data+"), ..., no? — Tomalak
– Tomalak, Commented Dec 30, 2014 at 21:41
@Tomalak That didn't make a difference unfortunately. This is close though. It passes one object at a time, but it looks like it's passing the value from a different column instead of the combined object from the two columns (1,2). It's passing what's in column 4. — jonmrich
– jonmrich, Commented Dec 30, 2014 at 22:02

Tomalak · Accepted Answer · 2015-01-05 00:44:11Z

2

I'm pretty sure your code should look something like this:

$.getJSON('URL HERE').done(function (data) {
    $.each(data.rows, function (row, i) {
        var coords = [row[1], row[2]].join(",");

        $.getJSON("https://www.googleapis.com/fusiontables/v1/query?callback=?", {
            key: "MY API KEY",
            sql: "SELECT COUNT() from TABLE NUMBER WHERE ST_INTERSECTS(coordinates, CIRCLE(LATLNG(" + coords + "), 16093))"
        }).done(function (result) {
            console.log("received result " + i, result);
            // deal with the result (remember that they don't arrive in predictable order)
        }).fail(function (jqXHR, textStatus, errorThrown) {
            // don't forget to add error handling
        });
    });
}).fail(function (jqXHR, textStatus, errorThrown) {
     // don't forget to add error handling here, either
});

This sends N requests to the Google API, one for each row in your original array and runs a callback for each of the N results.

Note how you don't need to deal with encodeURIComponent() if you simply pass an object with your parameters to jQuery.

EDIT: To orchestrate multiple asynchronous HTTP requests and run a certain function when all of them are done, use $.when(). I strongly recommend reading into jQuery's Deferred semantics, they are an indispensable tool and you should take the time learning how they work.

$.getJSON('<QUERY URL HERE>').done(function (data) {
    // create an array of pending Ajax requests
    var xhrRequests = $.map(data.rows, function (row) {
        var latlng = [row[1], row[2]].join(",");
        return $.getJSON("https://www.googleapis.com/fusiontables/v1/query?callback=?", {
            key: "API KEY",
            sql: "SELECT COUNT() from TABLE NUMBER WHERE ST_INTERSECTS(coordinates, CIRCLE(LATLNG(" + latlng + "), 16093))"
        });
    });

    $.when.apply($, xhrRequests).done(function () {
        // this runs after *all* requests have completed successfully
        // arguments contains the results in original request order
        var counts = $.map(arguments, function (xhrResult) {
                // each of the results will be an array [data, status, jqXhr]
                var data = xhrResult[0];
                return data.rows ? data.rows[0] : 0;
            }),
            total = 0;

        // output to the screen, calculate a total, whatever you need
        $.each(counts, function (i, count) {
            total += count;
            $("<div>", {text: count}).appendTo("#info2");
        });

        $("<div>", {text: total}).appendTo("#info2");
    })
    .fail(function (jqXhr, status, textStatus) {
        // this runs as soon as *one* of the requests has failed
        // react to the HTTP error here
        console.log(jqXhr, status, textStatus);
    });
});

I've created a more advanced, more modularized, live example over here: http://jsfiddle.net/Tomalak/y54tkpz7/1/

edited Jan 5, 2015 at 0:44

answered Dec 30, 2014 at 21:34

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

jonmrich Over a year ago

Unfortunately, this didn't work. If I console.log coords, I get just a comma and that's it.

Tomalak Over a year ago

Without seeing what data looks like, exactly, I can't tell you what's wrong. And I'm sure if you look very closely at how data looks like, you could figure it out yourself.

jonmrich Over a year ago

Thanks. I was able to get it working based on your answer and comments. Unfortunately, I'm stuck again. I thought it would be trivial to add each of the counts as they came in and I was only appending to the div just to see if I could get the values. What I really want is to add them together to get a total, but can't figure that out. Any ideas? What I'm getting returned are just single numbers (not arrays).

Tomalak Over a year ago

@jonmrich See modified answer. Also have a look at the jsFiddle.

jonmrich Over a year ago

Thanks for the additional information. The issue I'm having now is an error for all the queries because it's adding "callback=%3F". The error says that there's a problem with that part of the query. If I remove the entire callback part, the query works, but then I get "Uncaught TypeError: Cannot read property 'rows' of undefined" for the line that comes after this: var data = xhrResult[0];. Not sure if this is because I removed the callback or if this would cause an error on its own (i.e., something else is wrong).

|

jonmrich · Accepted Answer · 2014-12-31 14:01:57Z

Thanks to the response from @Tomalak, I was able to figure it out (or at least something that's working for me).

$.getJSON('<QUERY URL HERE>').done(function (data) {
 var coors = data.rows.map(function (row) {
    return [row[1],row[2]];
});

$.each(coors, function (index,value) {
   var key='API KEY'
          var table ='TABLE NUMBER'
           var sql = "SELECT COUNT() from " + table +
              " WHERE ST_INTERSECTS(coordinates,CIRCLE(LATLNG("+value+"), 16093 ))";
              url = "https://www.googleapis.com/fusiontables/v1/query?key=" + encodeURIComponent(key) + "&sql=" + encodeURIComponent(sql) + '&callback=?';

$.getJSON(url, function (data) {
$('#info2').append(data.rows ? data.rows[0] : 0);
          });

This adds each of the counts (of stores within 10 miles) for each set of coordinates to my div "info2".

Collectives™ on Stack Overflow

Pass each object in JSON array to query

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related