1

The data I have is a list of projects for which I have the following geographic information:

  • postcode
  • longitude and latitude
  • local authority
  • region (or country, eg Wales)

as well as other details for each project.

Dataset:

https://saira.carto.com/tables/fww_data_for_mapload1

I would like to create bubble map in Carto summing together project numbers according to UK local authorities and UK regions and countries.

map:

https://saira.carto.com/tables/fww_data_for_mapload1/map

So there should be one bubble for each local authority or region. For example using region: Northern Ireland would have the smallest bubble representing 71 projects and South East (England) would have the largest with 165 projects.

I tried to georeference the local authority and region but I get an error.

When I select the Bubble option from the wizard, the column drop down has only numerical values relating to each project. I can see no way of showing project numbers per "Admin Region".

1

1-Geocoding. In theory, if you have longitude and latitude data, you do not need to georeference your table using other geo-related fields.

2-Styling. If I understand you correctly, you want to show bubbles based upon the number of projects on the one hand. And on the other, display dot colors based upon admin. region. In order to do so, you would need to play with Wizards and CartoCSS:

2.1-Select CATEGORY from Wizards tab and choose the field that represents admin. regions in your table.

2.2-Go to CartoCSS tab. Copy the code (you can save it in a text editor).

2.3-Go back to Wizards tab. Select BUBBLES and choose the numeric field that represents the number of projects in your table.

2.4-Finally, go to CartoCSS tab and paste the "category" code at the end. Apply. Boom!

Here you have the code and below you could have a look at the result:

bubbles and categories with CartoCSS

UPDATE:

In order to aggregate your data based upon admin. regions you need to follow these steps:

1-Go to SQL tab and apply a query like this one:

SELECT
  count(*) as num_projs,
  region
FROM
  fww_data_for_mapload1
GROUP BY
  region

2-Create a new dataset from query (you will see the yellow banner on the top of your map).

3-Georeference your data using admin. region (in your case, region). If the dataset is not correctly geocoded, you could join your this table with an already UK admin dataset from CARTO Data Library.

4-In order to make a bubble map you need to follow 2.3 steps using num_projs as the numeric field you want to represent.

  • Thank you Ramiro for looking at my request. I get to 2.3 of your instructions: the numeric field that represents the number of projects in your table - however there is no numeric field representing the number of projects. Each line in the data represents a project. So for example if I based the Admin Region on Region(in my dataset) there should be only twelve bubbles representing twelve UK regions (and countries). With the bubble representing a sum of all the projects in that region. I haven't been able to follow the rest of your instructions. – Saira Nov 30 '16 at 14:01
  • Ok. Let me update my answer. I did not quite understand you in the 1st place. – ramiroaznar Nov 30 '16 at 14:13
  • Thanks for the update Ramiro, I tried the extra steps. Step1 works perfectly, also I created a new dataset from the query. However I am unable to geocode. I tried again with regions but it seems carto does not recognise these. I tried to join with another dataset but the only ones available are my own where the admin region could not be coded. Is there a way to have carto recognise the UK region? – Saira Nov 30 '16 at 14:32
  • If you go to the DATASETS main menu and select DATA LIBRARY. Look for the uk_administrative_regions dataset, select and connect it to your account. Then you can merge both datasets using a common field (region and admXname where X is the level in which your data is located). – ramiroaznar Nov 30 '16 at 14:45
  • I've been trying to do this. However the values in the region column of UK_Administrative_Regions does not correspond to values in the region column in my dataset. I made a copy of the dataset and thought I could overwrite the value, for example there is a South Western (which is in Scotland) I need it to read Scotland. I edit and save it but when I look at it again it has not saved. I've been able to follow your additional instructions but got stuck with 2.3 of your original. – Saira Nov 30 '16 at 15:35

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.