Why won't Carto read my data?

Question

I'm new to Carto, and using any of the file types Carto relies on, so treat me as a complete beginner. I'm trying to create a heat map for the number of road casualties in 2015 in England.

I used a shapefile from the ONS which includes the geometrical data of all the different authorities in England, and uploaded that to Carto. This seemed to work, because the map is displaying the borders between each authority

However it wouldn't work with my CSV file, which included the data for the casualties in each authority. So, I inserted the geometrical polygon data from the shapefile for each authority on my CSV file manually, and deleted the shapefile. The above photo is displaying from just using the CSV file with the added polygon data.

Below is a photo of the spreadsheet in Carto.

While the polygon geometry data is working, it's not properly reading the Casualties column, and isn't allowing me to create any analysis from this to make a heatmap. I'm not sure if I'm just not understanding the interface properly, because it is reading the casualties to some extent, shown by the numbers in the right locations below.

What I'm asking, is how do I get these numbers into something more like a heatmap, as shown in the first photo, but without the values between each colour making the majority of the map purple. I can't seem to find a way to change how Carto quantifies the numbers.

Answer 1

Heatmaps (now is called aggregation by pixel) work in CARTO aggregating dot density per pixel. It does not take into account any numeric field when displaying those "heat areas". So what I would use instead is making a bubble map:

1-First, we have to transform our polygons into points. On the main menu, click on "ADD ANALYSIS". Select "Find centroid of geometries" and click on "ADD ANALYSIS". Set the parameters as follows: * CATEGORIZE...: cartodb_id * AGGREGATE...: sum(casualties) Click on apply.

2-Secondly, we have to style our bubbles based upon casualties. As explained in this guide, click on "STYLE". Click on the marker size (it is fixed in 7 as default). Select "BY VALUE" and choose casualties from the dropdown menu. You can also repeat these steps with marker color.

You could also make a choropleth map instead of a bubble map. But if you chose this second option, you would need to normalize your data. The easiest way to do this is dividing the number of casualties by area or population. I would choose the former, thus using PostGIS ST_Area() function and adding a new field:

1-First, on the main menu, click on the layer. From the "DATA" tab, click on "SQL" (at the bottom of the sidebar menu). The (black) SQL panel would open. You would need to type and apply a query like this one:

SELECT *, casualties/ST_Area(the_geom) as casual_norm FROM casualties_by_region_no_wales

2-Secondly, go to "STYLE". Click on the polygon color, select "BY VALUE" and choose the new field we have just created, casual_norm. And select a nice color palette, the number of buckets and a type of breaking method.

Here you have more information about styling choropleth maps using Turbo CARTO, a more advanced method to fine tune your visualizations.