Choropleth in Cartodb with lat and long of countries

Question

I have a dataset in Cartodb with the following columns:

Name,Count,Lat,Long

When I upload the dataset, Cartodb automatically update the column Geometry with the Lat and Long.

The Lat and Long in my datasets are the centre of countries. So, I have only once combination of Lat and Long per country.

I want to use the Count and create a Choropleth for the countries that have the Lat and Long in my dataset.

I did it in the past with another dataset, but unfortunately, I haven't kept the procedure to do it again.

Answer 1

You can use the merge dataset option in order to join your data with a countries dataset.

1. In data library, upload "World borders", which is basically a dataset of all countries in the world.

2. Inside the world_borders dataset, in the "Table view", select "merge dataset" option in the right toolbar.

3. Choose "spatial join".

4. In the column selector for the world_borders table, select the_geom (it is by default) and for example "name" or any other information you want to have in your new table about the countries.

5. In the second dataset selector, choose the table that you are specifying in this question. Choose your count column, select the SUM operation and click on merge. This will sum the "count" inside the countries and will give you as a result a dataset with the country shapes and your aggregated count column for each of them.

6. Now, you're able to perform a choropleth dataset on this new dataset.

If in your dataset you had also the names of the countries, you could have geocoder the dataset by this name. Assuming that you have a single row per country, the result would have been equivalent and more straightforward.