SQL query as part of featureClick in CartoDB.js

Question

I am a complete novice at both Javascript and SQL so am learning as I'm going with this!

I have made a webpage that shows a map with a layer of points on the left hand side of the page. What I want is to click on a point and display the nearest five points to the clicked point (and associated information about these points) on the right hand side of the page. I have managed to do this so that information about the clicked point shows up but can't get it to show the info for the other five points.

This is the code I am using that doesn't do what I want:

        cartodb.createLayer(map, {
        user_name: 'XXX',
        type: 'cartodb',
        sublayers: [{
            sql: 'select * from branch_points',
            cartocss: '#branch_points{marker-fill: #109DCD; marker-width: 8; marker-line-color: white; marker-line-width: 0;}',
        }]
    })
        .addTo(map)
        .done(function (layer) {
        cdb.vis.Vis.addInfowindow(map, layer.getSubLayer(0), ['address', 'number_of_members', 'lat', 'long']);

        var sublayer = layer.getSubLayer(0);
        sublayer.on('featureClick', function (e, pos, latlng, data) {
            sql.execute("SELECT * FROM branch_points WHERE address='" + data.address + "' ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(" + data.long + "," + data.lat + "), 4326) LIMIT 5")

                .done(function (data) {
                console.log(data)
                for (var i = 0; i < data.total_rows; i++) {
                    $('#content').append("<div><span>" + data.rows[i].address +
                        "</span><span>&nbsp;&nbsp;&nbsp;&nbsp;" + data.rows[i].number_of_members +
                        "</span></div></br>");
                }
            })

        });

    });

It shows the information about the clicked point if I use the SQL query SELECT * FROM branch_points WHERE address='" + data.address + "'"

I've also just realised that, if I then click on another point, this gets added to the list on the right.

However, when I try to add the part to find the five nearest rows (based on the tutorial here), it doesn't work and I can see from the Javascript console that only one object in 'data' is returned.

Where am I going wrong here? Should I be doing all this code at the same time as adding the layer to the map? How can I get the information about the nearest points to display when a point is clicked? And how do I get this to refresh every time I click a different point?

Answer 1

After reviewing your code and playing with some examples myself I think that the problem is that the WHERE clause of your query is a bit too restrictive, cause WHERE address='" + data.address + " will discard any rows not matching that criterion. I'd need to check your table to be sure about that, though.

What I think you need would look like this:

    sublayer.on('featureClick', function (e, latlng, pos, data, layerIndex) {
        var sql = new cartodb.SQL({ user: 'your_username' }); // if not already defined

        var query = "SELECT * FROM branch_points ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(" + latlng[0] + "," + latlng[1] + "), 4326) LIMIT 5";
        console.log(query); // you can try this same query in the sql panel of the editor

        sql.execute(query)
            .done(function (data) {
              console.log(data); // useful to check that you're getting what you expect
              for (var i = 0; i < data.total_rows; i++) {
                  $('#content').append("<div><span>" + data.rows[i].address +
                      "</span><span>&nbsp;&nbsp;&nbsp;&nbsp;" + data.rows[i].number_of_members +
                      "</span></div></br>");
              }
        })
    });

Here's the documentation about featureClick. Please note the order of the params is relevant in javascript.

Just wondering: are you using version 2 or 3 of cartodb.js? You can find the most up-to-date documentation here.