One of the features of Qlik Sense is the ability to load your own mapping layers into the tool. This allows you to navigate your data spatially and provides another visualisation to display represented data that might have a geographic aspect. All Qlik needs is a KML file and it will take care of the rest. I was given the challenge of finding and loading an NZ map into Qlik. The task sounded simple enough to start with but I discovered quite a few challenges in the process.
Stats NZ provides a range of Open Data Maps that can be used for this purpose. They have maps that break up the country into useful areas such as Regional Council, Territorial Authorities, and Meshblock.
The first challenge is the formats they provide this in data are only in ESRI shapefiles and MapInfo TAB files. Both widely used, but commercial products. So I had to find a tool that would allow me to convert the file. QGIS is open source and met this need and solved some other issues I had as well. I downloaded the 2016 Clipped ESRI Shape file for this Proof of Concept. There is a separate download which explains the different mapping types included in the ESRI shape file.
After you have downloaded and install QGIS. Use the following steps to create KML file.
- Run QGIS Desktop application.
- From the menu bar Select Layer – Add Layer – Add Vector Layer (Ctrl-Shift-V keyboard shortcut)
- Browse to the unzip ESRI file location make sure the file type is set to ESRI Shapefile and pick the desired shape file. In my case, this is the REGC2016_GV_Clipped.shp file which breaks the country into Regional Councils.
- At this point, we need to fix a good problem. The maps are at a very detailed level and NZ coastlines are complex to draw. We need to simplify the map so it’s smaller and easier to deal with. The default KML file that would be created at this point will cause an error to be generated in Qlik as there’s too much detail.
- From the menu bar Select Vector – Geometry Tools – Simplify Geometries
- Set the Simplify Tolerance to something high, I used 100. The higher the value the lower the detail
- Click OK
- Click Yes on the warning dialogue
- Click OK on the summary dialogue. If you look at the numbers here you will get an idea of the degree of simplification on the map.
- Click OK on the next dialogue
- Click Close on the original dialogue window
- This step is optional and you shouldn’t do it if you are going to use a background map behind you layer as it will look funny! The Chatham Islands are part of NZ and by scale is the correct distance from the rest of the country but causes the map to be zoomed out a long way to fit everything in by default in Qlik.
- From the menu bar Select View – Select – Select Feature(s)
- Zoom in on the Chatham Islands
- Click on it. It should be filled with a yellow colour
- From the menu bar Select Edit – Move Feature(s)
- Drag it closer to the main islands
- Right click on the layer REGC2016_GV_Clipped in the Layers Panel
- Click on Save As
- Set Format to Keyhole Markup Language [KML]
- Click on Browse and specify a file name and location then click Save
- Click OK
- Exit out of QGIS, discarding all changes when prompted
In Qlik Sense to load and display the data.
- Create a new app
- Add Data
- Select Attach Files and drop the KML file into a dialogue box. Qlik automatically knows how to deal with KML files. There are only three fields that will be loaded, but only Area is provided in the KML file.
- Name – Key for each spatial object
- Point – coordinates of a point
- Area – polygon shape coordinates
- Click Load data and Finish
- Click Edit the sheet
- Select the Chart type Map and drag and drop into the sheet
- Drag the field name onto the map.
- Select Add as area layer
- Click Done
As I mentioned above the Area Attribute is the only automatically from the KML file. The appropriate Name attribute is available in the KML but stored in a buried XML Object ExtendedData. An example of this is below.
<ExtendedData><SchemaData schemaUrl="#REGC2016_GV_Clipped"> <SimpleData name="REGC2016">01</SimpleData> <SimpleData name="REGC2016_N">Northland Region</SimpleData> <SimpleData name="AREA_SQ_KM">12510.35034480000</SimpleData> <SimpleData name="LAND_SQ_KM">12497.92090900000</SimpleData> </SchemaData></ExtendedData>
In the case of this example the field you want is <SimpleData name=”REGC2016_N”>Northland Region</SimpleData>
I will cover this in next week’s post on how to extract and join this to the spatial data in Qlik to make it fully usable.
Other things to be aware of is that Qlik has a limit of 3,333 spatial objects for a map. So, loading a map based on meshblock will be too large as there will be over 40000 objects. However, you should be able to roll up Meshblocks different grain such as Area Units or Territorial Authorities for your visualisations.
All the code, all the fun – Ben
Ben writes blogs about the technical side of BI the code, all the code and not much other than the code.
You can read all of Ben’s blog here.
Don’t forget, we can train your team in the art of agile business intelligence at any time!