1

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?

3

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.

  • you can also perform the calculation directly on the Dashboard interface, at the DATA tab, find at the bottom a switch to change to the SQL view. – Jorge Sanz Apr 11 '18 at 9:47
1

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.

  • Hmmm, that looks interesting in order to produce a column on the fly. Maybe I need to experiment with what you suggest. At first glance it does not look to me like this would solve my original issue which is how to make that new column (total_crocs) persistent and visible to CartoDB visualisation so that it can be used to drive styling (eg number of buckets, symbol diameter, colour band, etc). – John-CapeYorkNRM Oct 10 '14 at 0:33
  • Your visualization in CartoDB should automatically be updated when you add data into CartoDB. So for example if your SQL for your visualization is 'SELECT * FROM croc_table', then once another line of data gets update, your visualization will include the new data because it would included in the results of the SQL query. – scud42 Oct 11 '14 at 0:07
  • Thanks: I will have to try this to make sure I understand it and to verify that CartoDB's styling tools can actually make use of a column populated in this way. – John-CapeYorkNRM Oct 12 '14 at 22:00
-1

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

  • 1
    Welcome to GIS SE! Please use code tags to make your posts more readable. – karpfen Jul 29 '16 at 11:49

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.