0

I'm a journalist, and I would like to create a map to explain some things (about the countries that are playing the Euro 2016). I'm using CartoDB to create the map.

But for some reason, I can't find a way to display Wales, England and N.Ireland instead of UK. I have no idea how to fix this thing. Does anyone have an idea?

  • You are making this map from uploading your own data, or are you talking about the built-in backdrop tiles? If the former, please update with some information about the source. – John Powell Jul 4 '16 at 17:40
  • I'm using the default map of the website. I dont know how to do things differently. Here is the link to the map as it is at the moment, so that you can have a better idea (and hopefully you'll be able to see the little dataset as well) pirontimothee.cartodb.com/viz/… – Timothée Piron Jul 4 '16 at 23:58
  • edit : CartoDB has been able to locate every country from their name in english. This is how I managed to create this map. But it does not work for Wales, England and Northern Ireland – Timothée Piron Jul 5 '16 at 0:05
  • I downloaded your table, a9es_hooligans, as a shp file, and there is no geometry for England, Wales or Northern Ireland. I don't know what the source of the data is. If you could post the original data source, it would be easier to investigate. – John Powell Jul 5 '16 at 8:20
  • As i tried to explain, the original data are just the name of the countries and their translation in french. CartoDB has been able to create geometry and stuff for all the countries except for the United Kingdom. – Timothée Piron Jul 5 '16 at 10:41
2

Instead of creating a dataset from the ground up, and thus geocoding your data using the "By Admin. Regions" option. You can download a dataset which contains the info you are interested in. That's something not only for working with CartoDB. This is common for other webmapping application or GIS desktop tool (before you start to read about how to get the dataset and visualize it with CartoDB. I highly recommend you to go and ask (or maybe it is already posted) in Open Data Stack Exchange).

In this case, in order to get a dataset with Wales, Northern Ireland and England geometries you can go to Eurostat. Click on NUTS 2013 (Nomenclature Administrative Units for Statistics), and download the first shapefile of the list. CartoDB allows you to import the whole zip file. Once it is imported, you will see several new datasets in your dashboard. We are going to use nuts_rg_01m_2013. This is the map view:

enter image description here

As you can observe, you need to select and join the small units or NUTs for each of the countries/regions (England, Scotland, Nothern Ireland and Wales). Here you have a very useful resource that will help you to know which code you need to select. For instance, all the NUTs that start with UKN are from Northern Ireland. Using the SQL console:

SELECT 
  ST_Union(the_geom_webmercator) as the_geom_webmercator,
CASE 
  WHEN nuts_id ilike 'UKN%' THEN 'Northern Ireland'
  WHEN nuts_id ilike 'UKL%' THEN 'Wales'
  ELSE 'England'
END AS region
FROM 
  nuts_rg_01m_2013 
where
  nuts_id ~ 'UK[N|C-L].*'
group by
  region

I have used the_geom_webmercator instead of the_geom in order to see the changes. But you can use the later and create a new dataset from the query. This is the resulting map:

enter image description here

Finally, in order to join this dataset with yours, first you will need to add the same columns (with the same data type) and then run the following SQL command (substituting the name of the datasets):

SELECT
  *
FROM
  your_dataset
UNION ALL
SELECT
  *
FROM
  new_dataset
  • This is a good answer. The question still remains as to why CartoDB fails to geocode England, Wales and N. Ireland, though. – John Powell Jul 5 '16 at 12:02
  • Simple, CartoDB only recognizes a list of administrative regions names when geocoding "by Adm. Regions". These are basically countries (adm0). Wales, England and Northern Ireland are not in that list. – ramiroaznar Jul 5 '16 at 12:11
  • Hm, and Italy, France and Holland are in the list along with all the other countries that geocoded under admin areas are not also countries. Also, in these days of Solr/Elastic Search, or even Postgres full-text, and the like, it shouldn't be too difficult to widen the search. – John Powell Jul 5 '16 at 12:51
  • Well thank you for the effort, but I have an error when i try to import the ZIP file... – Timothée Piron Jul 5 '16 at 14:05
1

You could also use this dataset and a SQL query like:

SELECT the_geom_webmercator
FROM uk_administrative_regions
WHERE 
  geounit ILIKE 'England'
  OR geounit ILIKE 'Wales'
  OR geounit ILIKE 'Northern Ireland'

Once you have the geometries in a separate dataset, you could proceed with an UNION or a JOIN to attach your data.

  • That was easy! :-) – ramiroaznar Jul 5 '16 at 11:18
  • As @ramiroaznar said, we check against a geocoding database to assign polygons to each admin region name. That database is populated with open datasets coming from naturalearthdata.com You could check a list of the names we can directly geocode here: gist.github.com/ernesmb/a8397f76cf515ec31b92bcfb9c9075d6 – Ernesto Martínez Jul 5 '16 at 12:19
  • Sorry, but failing to recognize England and Wales, while recognizing all the other European counties, is not very impressive. – John Powell Jul 5 '16 at 12:55
  • i know basically nothing regarding SQL queries, how can I join the datasets ? Union doesnt work – Timothée Piron Jul 5 '16 at 14:07

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.