Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

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

richbyard
New Contributor III

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.
Attachments
Comments
flanfranco
New Contributor III

Great Post!!
Thanks!!!

borisman
Contributor III

this is a really great post and helped to fix US census zip KML file. Thanks Richard!

Not applicable

Thank you! Do you have qvd file too?

richbyard
New Contributor III

Hi, KML (similar to XML) is already optimized for reloading and reads in milliseconds. As such there would be no real benefit in going through the process of saving this as QVD. If you want to you can of course use the Store command to output it into a QVD yourself.

Cheers

Richard

Not applicable

Thanks Richard

rafael_costa
New Contributor

Thank you very much!!! This doc saved me!!

birchgold
New Contributor II

Thank you for the post!
Sadly, I followed the steps to the dot but I am getting the Hypercube too large error in Qlik. This is for US County. I simplified the geometry to .0005 like you did but I will try something even smaller now... hope it works. If there are other solutions, or things i should try, please let me know.

birchgold
New Contributor II

.00005 didnt work either...

rafael_costa
New Contributor

Henry this very problem you had happened to me in 2 different ways:

1) The first one is the easiest one to correct: The poligons are just too big. This happened to me in the Argentina's map. I had to do the simplyfly procedure 2 times in order to be able to visualize my map. In this I don't know the exact max size that the kml is accepted in QSense BUT I know this: at 17 mb I couldnt do it. At 10mb I could. So the limit is between 10 and 17 mb.

2) This one is a bit harder to see and correct. Nothing impossible, obviously. To link the kml file to the app I mad a simple xlsx sheet in wich they would join. The need to join the tables is so the app would show the name for the states, instead of the standard 'p1', 'p2' etc of the kml.

The Argentina example (CORRECT):

[ARGENTINA_ESTADOS/ARGENTINA_ESTADOS]:

LOAD

[ARGENTINA_ESTADOS.Name] AS [ARGENTINA_ESTADOS.Name-ESTADOID],

[ARGENTINA_ESTADOS.Point],

[ARGENTINA_ESTADOS.Area]

FROM [lib://LATAM BI SO/MARKET\PesquisaMercado\00_Data\ARG_adm\ARGENTINA_ESTADOS.kml]

(kml, Table is [ARGENTINA_ESTADOS/ARGENTINA_ESTADOS]);

[República Argentina]:

Join([ARGENTINA_ESTADOS/ARGENTINA_ESTADOS])

LOAD

[ESTADOID] AS [ARGENTINA_ESTADOS.Name-ESTADOID],

[ESTADO],

[DESC]

FROM [lib://LATAM BI SO/MARKET\PesquisaMercado\00_Data\ARG_adm\ESTADOS.xlsx]

(ooxml, embedded labels, table is [República Argentina]);

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

The Argentina example (INCORRECT***):

[ARGENTINA_ESTADOS/ARGENTINA_ESTADOS]:

LOAD

[ARGENTINA_ESTADOS.Name] <<<<FORGOT THE RENAME FOR THE JOIN***,

[ARGENTINA_ESTADOS.Point],

[ARGENTINA_ESTADOS.Area]

FROM [lib://LATAM BI SO/MARKET\PesquisaMercado\00_Data\ARG_adm\ARGENTINA_ESTADOS.kml]

(kml, Table is [ARGENTINA_ESTADOS/ARGENTINA_ESTADOS]);

[República Argentina]:

Join([ARGENTINA_ESTADOS/ARGENTINA_ESTADOS])

LOAD

[ESTADOID] AS [ARGENTINA_ESTADOS.Name-ESTADOID],

[ESTADO],

[DESC]

FROM [lib://LATAM BI SO/MARKET\PesquisaMercado\00_Data\ARG_adm\ESTADOS.xlsx]

(ooxml, embedded labels, table is [República Argentina]);

First is the kml. I didnt touch the file after reducing its quality and converting it to kml.

The second Table is the xlsx I created. The hypercube would get to big when I joined the tables but forgeting to rename the fields. It would just made the "cartesian product" (every possible combination of the 2 tables).

See, even with the file in an acceptable size, the error would happen because I forgot the rename on the join.

I hope this helps

birchgold
New Contributor II

I was actually simplifying the geometry in the opposite direction. I got the polygon to show up on Qlik but now I am having issues linking it to data...

I have a KML file that works great by itself. It has 3 fields (only 2 are being used). One is the KML polygon data and the other is the name. Sadly, the name is in numerical value so I made a excel sheet to link to the KML so that the name can be what I need it to be (County Names). I want to hover over the polygon and get that name rather than the numerical value.

I add data and use the "Countycustom7.AreaMap" as data and then it gives me the option to use the "KeyCounty_State" as the name, thats exactly what I want to do. But when I qlik it, all the data is null. It does not actually associate the data although i proply linked the two by the "County ID" number.

Can anyone help? Please view images.
Thanks in advance.map.JPGmap2.JPG

Version history
Revision #:
1 of 1
Last update:
‎09-13-2015 05:53 AM
Updated by: