Country Administrative Boundary KML shape / area files from open source GIS data.

    I wrote an earlier document on creating custom KML polygon files for specific use cases where the file you would like is not publically available, or at least not easy to find. To follow on from this I have outlined a step by step guide to sourcing GIS information from online sources and a process to convert them into useable KML files.


     

    What is a KML Polygon shape / area file

    KML (keyhole markup language) allows us to create map overlays using geo coordinates (longitude, latitude) for set of many points which create a shape and a contained area. These files are used when showing countries on a world map or states on a map of the US but any shapes can be created using geo coordinates. These shapes can then be color coded, showing heat maps etc, and are fully selectable in Qlik Sense. This example will walk you through this process.

     

     

    Source the GIS data for administrative boundaries online

    DIVA-GIS – http://diva-gis.org/gdata – is a reasonably easy source for different levels of administrative area GIS datasets by country. Select your country and choose the ‘Administrative areas’ subject. There are other datasets available but I have not yet explored these for their suitability for use in visualisations in Qlik Sense.

     

    They also have a countries of the world dataset you can download at http://biogeo.ucdavis.edu/data/world/countries_shp.zip.

     

    GADM.org (database of Global Administrative Areas) have a similar set of open source shapefiles with administrative areas by country.

    http://www.gadm.org/country

     


    Convert this GIS data into a KML file which can be read into Qlik Sense (and QlikView)

    The file you download will be very detailed which results in a large set of data which takes some time to render. This detail is unnecessary for our use cases as we require simple shapes on a map which indicate the boundary without being 100% exact at each point. As such it is sensible to reduce the density of the file.

     

    1. Download and install Q-GIS (open source software) at http://www.qgis.org/en/site/

     

    2. Once installed, Open Q-GIS desktop.

     

    3. Unzip the folder of GIS data you have downloaded in section above (be sure to unzip to a folder of the same name as you will need to drag it as a folder in the next step)

     

    4. Drag the folder into the Q-GIS window, it will ask you which layers you want to extract so click ‘Select All’ as we will be able to browse which boundaries we would like. Then click OK.

     

    5. In the window on the right you will see multiple layers, all of which will be ticked. Unselect all and select each one in turn to see what the layers represent. For example for the UK I have 3 layers;  0 is full territory, 2 is the countries and 3 is councils. These layers will be different in each country depending on the availability of data and the local government structures. For example New Zealand has 4 layers available down to electoral boundaries.

    5.png

     

    6. Select one Layer (2 in my example), and ensure that layer is highlighted in the window on the left hand side (as well as showing with an X in the checkbox).

     

    7. The detail in this layer is very precise and the data is therefore large and takes time to render. To make this object perform quicker in Qlik Sense we should reduce the number of points. Navigate to Vector > Geometry Tools > Simplify Geometries

    7.png

     

    8. In the window that appears ensure the layer you wish to simplify is selected. This is not automatic. Then reduce the ‘Simplify Tolerance’ value. For example, changing from 0.0001 to 0.0005 will in theory keep one in every 5 points although this is not an exact calculation.

    8.png

    When you click OK the process will commence, there may be a warning message but continue, and it will then show the output of the process. In my UK county example 428,056 points have been reduced to 83,234.

    8.1.png

     

    9. Identify the attribute value that you wish to assign to each polygon shape. In this example we wish to assign County name.

      • Navigate to Layer > Open Attribute Table to view the fields that are stored in the dataset you have downloaded.
      • Review the table to identify the field name of the column with the data you wish to assign to the shape, make a manual note of this as you will need to manually type this in for step 10.
      • NOTE: there may be multiple fields where the shape name may be written in different language character sets (e.g. Arabic and English or Turkish and English).
      • In this example I will use ‘NAME_2’ as this is the County name.

    9.png

     

     

    10. Now save this adjusted file. Navigate to Layer > Save As… on the top menu bar. A menu window will appear.

      • Ensure the format is Keyhole Markup Language [KML], change this if it is not.
      • Browse to the folder and enter the file name you will save the file as

    >>>>> EDIT for QGIS version 2.18 as of 8 December 2016 <<<<<

    (please avoid spaces in the file name as this seems to cause issues with the saving of the file, the layer name will                  default to the file name)

      • A new step is required where you need to check the fields from the Attribute table that you will use for reference. This is in addition to the steps below as before. You could also click 'Select All'.
      • In the [NameField] option of Datasource Options enter the name of the field identified in step 9, in this example NAME_2.
      • Click OK to save your KML file.

    QGIS 2.18 new step.png



    Check out your file in Qlik Sense

    Create a test application in Qlik Sense, drag in the KML file you have just created. You’ll notice that the County Code in this file will need some mapping to your regions but that should be a simple task in your data load. Once the data is loaded, create a map object and drag in the name field and you’ll have a selectable map chart like the one below (if you activate the map background).

    b.png

     

    You can then apply colours based on measures and expressions to start to highlight performance metrics on this area chart. For a step through guide on how to do this see the following post. https://community.qlik.com/docs/DOC-9136

     

     

    Identified Limitations in this process

    1. It is a little fiddly but this guide should get you there step by step.
    2. Some countries have territories that are geographically distant and as all the points are geo points it makes the map very small. An example is NZ where the Chatham Islands show to the West and not to the East as the longitude and latitude points dictate its position (with the dateline causing an issue in this case). To resolve this issue you would need to exclude the polygons related to the Chatham Islands when loading the data.