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?
Best Answer
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.