The purpose of this document is to introduce a set of tools to generate maps for the UK to be used in QlikView through an extension. There are already other solutions that address this but I couldn’t find any free; now google has also changed the access to their api so also that isn’t free anymore.
Through this document I’ll explain how to generate KML maps at various levels of detail to be then used with the quickmap extension created by Brian Munz (https://github.com/brianwmunz/QuickMap-QV11); this extension uses openstreetmap so it is free. Also, I’m providing an example to show how to implement it.
High level overview
There is a wealth of data available in the UK; I started this project when analysing the house price paid data available from the Land Registry. The data is very well structured and every transaction has an associated postcode, so that is the starting point for the visual representation: reference lookup data is available with postcode coordinates as well as the details of which geographical areas it belongs to.
There are multiple geographical levels in the UK and KML maps are available to download; the challenge with these maps is that they are extremely large to be loaded in one single instance by the extension. What I’ve done is to break down these maps in smaller areas and to establish two drilldown paths:
1. European Electoral Regions (EERCode) > Local authority district (DistrictCode) > Middle layer super output areas (MSOACode) > Lower layer super output areas (LSOACode) > Output areas (OACode)
2. Local authority district (DistrictCode) [lower resolution map] > Middle layer super output areas (MSOACode) > Lower layer super output areas (LSOACode) > Output areas (OACode)
Different drilldown paths can be set up in the application and the relevant maps generated.
I’ve created 4 QlikView applications:
UKMaps_Coordinates_Generator: takes the KML maps and saves them into QVD files
UKPostcodesLookup_generator: loads postcodes reference and saves them into QVD files
UKMaps_Creator: loads the QVD files created by the generator and creates the map files required by the quickmap extension
UKMaps_VisualizationExample: example implementation
I’ve included within the applications all the relevant instructions on how to use them.
The whole package is included in the zip file attached; I’ve also included an amended version of the quickmap extension: I’ve added some extra functionalities (ability to define max and min values and possibility to use a color gradient with smooth transitions) so please use this version. Within the qar file I’ve also already embedded the maps required by the example application.
Sample drilldown screenshots
In this example you can see that, as you select the data and drill down, different sets of maps are used for each level that contain progressively more details.
England and Wales district overview:
St Albans District:
MSOA Level - St Albans 002
LSOA level - St Albans 002C
OA level (coordinates) - OA E00120299
I hope someone might find this helpful. I would appreciate any comments or feedback.