0

I'm trying to dynamically set my Carto map's dot colors based on a JavaScript variable.

var queryAllRows=   "SELECT+" + colNames.join(',') + "+FROM+MY_TABLE_NAME";
queryAllRows=       queryAllRows.replace(' ','+')
var queryAllRowsURL="https://ACCOUNT_NAME.carto.com/api/v2/sql?q=" + queryAllRows + "&api_key=" + apiKey;
var sums=           [];
$.getJSON(queryAllRowsURL, function(queryResult){
    // The point of this loop is to find the highest number in `rowObj`.
    // Each `rowObj` elements looks something like this... 
    // {city: "New York", score1: 10, score2: 20}
    for (var i=0; i < queryResult.rows.length; i++) {
        var rowObj= queryResult.rows[i];
        sums.push(sumOfObjElements(rowObj)); // This line sums up the numbers in the object, pushes the sum into an array
    }
    var highestScore=   Math.max.apply(Math, sums); 
});

Each dot on my map is a city. I want to sum get the sum of each city's score, divide it by highestScore, and set the dot color based on that result.

How do I do this?

1

As far as I understand your question and code, I think you are overcomplicating things. Instead, I recommend following these steps:

-First, convert your json file into a CARTO supported geospatial format (you can use this free resource), and rename it as scores.csv and import it into your CARTO account.

-Secondly, you can get the highest score running this simple SQL query:

SELECT max(score1 + score2) as highest_score FROM scores

-Third, join your city dot dataset with the scores dataset using a PostGIS JOIN query and create a new field as a result of adding those two scores divided by the highest score:

SELECT
  a.*,
  b.score1,
  b.score2,
  (b.score1 + b.score2)/{{highest_score}} as total_norm
FROM
  cities a,
  scores b
WHERE
  a.city = b.city

-Fourth, then you can go back to your CARTO.js application setting your layer SQL source to the query above and CartoCSS to something like this:

#layer{
  marker-fill: ramp([total_norm], cartocolor(Sunset), jenks);
}
  • Hi, I just saw this. The problem I'm having is with (b.score1 + b.score2)/{{highest_score}} as total_norm. This gives me 0 in each row except for the one where total_norm is the same number as highest_score. In that case, it's 1. – Username Aug 18 '17 at 5:52
  • Could you share the scores dataset with us? It looks like there is just one city. – ramiroaznar Aug 18 '17 at 9:34
  • I use this dataset. pastebin.com/pMvFkhUc. It contains all the cities and their scores. I run this query. pastebin.com/axEPi3C8 – Username Aug 18 '17 at 17:17
  • After having a look at your dataset and query, IMHO it does not make sense what you are doing. You are mixing very different thematic indexes with a very different range of values. Also, I do not understand why the query is working on your end, because max("field1" + "field2" + ... ) should not work. I will recommend using another approach for this use case. – ramiroaznar Aug 22 '17 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.