Adding values in two columns to new column on sync table in CARTO?

Question

We have field data coming in from an app platform and we want to drive a CartoDB visualisation to help with planning.

A bunch of rangers go out surveying sites to count crocodiles. For each site, the app collects: - Number of Male crocodiles (num_croc_male) - Number of Female crocodiles (num_croc_female)

We don't actually want our people standing there in the dark doing sums, so they upload the data in this format straight to the cloud, and this is then POSTed over to our CartoDB.

In the CartoDB visualisation, we want to show the two cols above, and we also want: Total Number of Crocodiles (num_croc_sitetotal).

Since this integer will also be used for styling the display of each site, I want the value stored in a column.

And since the data is being refreshed after each patrol, I need this to be a stable transform that persists through new rows being added.

It seems like I can't find the tutorial or walk through for how this might be done...and my SQL is nearly non-existent. I was sort of hoping CartoDB had some sort of formula-building interface that would shield me from the full blast of SQL syntax.

Could anyone provide an outline of the solution steps I should be following here?

Answer 1

You can do that summation in the SQL statement behind the table in CartoDB. When you are in the Data View for the table, click on the SQL sidebar and you will see something like:

SELECT * FROM crocs_table

To include your summation, you would modify that statement to:

SELECT *, (num_croc_male+num_croc_female) as total_crocs FROM crocs_table

You need to click the "Apply query" button and then the table will refresh with the calculated column showing up. When you're in the map view, the calculated field is available for the purposes you describe.

Answer 2

If you had the 3 different columns in your CartoDB, you could update it with the SQL API.

So if you wanted your CartoDB table (we'll call it crocs_table) looked like this:

       num_croc_male   num_croc_female  total_crocs
Row1:        5               10             15

You could update it it using the SQL API like so:

 http://{account}.cartodb.com/api/v2/sql?q={SQL statement}&api_key={Your API key}

Account = the name of your CartoDB account

Your API Key = The API key of your CartoDB account

SQL statement =

INSERT INTO croc_table (num_croc_male, num_croc_female, total_crocs) VALUES (5, 10, 15);

The male crocs have good odds here... The total URL would be something like this:

 http://{account}.cartodb.com/api/v2/sql?q=INSERT INTO croc_table (num_croc_male, num_croc_female, total_crocs) VALUES (5, 10, 15)&api_key={Your API key}

Visiting the above URL would insert your data in CartoDB.

The key to how the total (15) is getting in the SQL statement is based on how you have your gator wranglers entering the data. If you're going to do it through a web form, you could use javascript to add the values together to create the URL and then redirect the user to that URL.

Answer 3

Don't use sum. follow the below syntax select (num_croc_male+num_croc_female) as total_crocs from ...