Original image by GotCredit/Flickr

Maps are a powerful tool to visualise data because they can explain stories with a high level of detail. You can show the data within countries, regions or even electoral wards. The only thing you need to know is how to work with KML files.

The open source data cleaning tool Open Refine can help you open, process and convert KML files into other formats. This conversion allows you, for example, to match two datasets (VLOOKUP) or to create a new map with the information of the KML file.

What is the difference between KML and XML?

In this post, you will learn how to convert a KML file into XML and to download it as a CSV file.

XML – Extensible Markup Language –  is a language designed to describe data which is used in RSS systems.

XML uses tags like HTML, but there is a big difference between both languages. XML defines the structure of the information, whereas HTML focuses on how the data looks like and is displayed.

KML – Keyhole Markup Language – are XML files specific for geographical annotations. KML files contain the parameters to add shapes to maps or three-dimensional Earth browsers like Google Earth.

The big advantage of KML files is that users can customise the maps according to their data and without knowing how to code.

Image of a KML map in Google Fusion Tables
Image of a KML map in Google Fusion Tables

Convert a KML file to XML

You can find KML files in this link. Just type what you are searching for and add the word geometry or KML, as the example shows.

Captura de pantalla 2016-02-28 a las 19.59.01

To convert the KML file, upload your KML in Open Refine (download it here) and click next.KML 1

In the blue box under your data, select “XML files”.


Now you can see the XML file with the structure of the information.

If you want to create a map with your own data and the shapes in the KML file, you need to match the KML with your data.

The example I have used contains the shapes of the local authorities in the UK. In this case, I want to match the shapes with the party that runs every council.

The element both datasets have in common is the name of the councils. I need to create a filter in Open Refine with a process similar to some scraping technics.

Have a look to the XML file and try to find the tags that contain the information you need, in this case, cities. In the example the tag is <name></name>.

Click on the yellow rectangle and wait until the process has finished.

Captura de pantalla 2016-02-28 a las 20.29.06

Now you should have a column or columns as the picture shows.

Captura de pantalla 2016-02-28 a las 20.33.07

On the right hand side of the page, change the name of your file and click on “Create a new project”. Now, click on “Export” and select the format you prefer.


You can download the information as a CSV file. In this tutorial, you can learn how to open a CSV file.

If you cannot open it with the traditional process, you can follow this tutorial for rebel CSV.

What originally was a KML file is now a filtered file with data ready to match.

Have you used Open Refine to change the format of your files? Leave a comment with your tips and technics or send it at @Carlapedret.