How do I set map dot colors based on a JavaScript variable?

Question

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?

Answer 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);
}