Examples for QlikView with Qlik GeoAnalytics.

Binning
This app shows an example of how the Binning feature can be used to create rectangular or hexagonal bins over Sweden. It also shows a drill-down hierarchy with two levels of rectangular bins and a point level. 

Chart Layer example
An example of how the Chart Layer can be used to plot data points on graphs (both bars and pies) using two dimensions. This example shows how the average house prices in Sweden have developed over time.

Polylines and Symbols example
An example showing how to use the Line Layer to create Polylines and the Bubble Layer to create Symbols from image URLs. 

Line Layer example – Storms
Example of Line Layer using Storm data to visualize historical storms. 

EU statistics example
Uses: Bubble Layer with latitude and longitude measures, bubble size and color by measure, Area Layer with location ID for country codes, area color by measure, transparency.

Drill down example
A drill down example with three different ways of using drill down data to visualize data. Sheet 1: Drill down based on State - County - Zip Sheet 2: Drill down based on Zip. 

Heatmap example
Uses: Heatmap Layer, drill down with layers active in different levels, Bubble Layer with visibility restricted to resolution limits. 

US flight delay example
This example shows how Line Layer, among others, can be used to show routes between different positions and include average departure/arrival delays in the appearance of the lines.

The examples requires v 12 or later of QlikView.

Sense Map chart

Example adding a WMS background

1. Add in Content Security Policy (Qlik SaaS)
Origin: nowcoast.noaa.gov Directive : connect-src and img-src
2. Add Map chart
3. Add background layer WMS
url: https://nowcoast.noaa.gov/arcgis/services/nowcoast/radar_meteo_imagery_nexrad_time/MapServer/WMSServer
version:1.3.0
crs: EPSG:3857, transparent, png, layer: Image

Note, use CRS 3857 if the base map has the Mercator projection (default).
Pick CRS 4326 if the base map has the Adaptive projection.

Link to documentation and example.

(QGA Extension map, old kept for reference)

Start by figure out the correct parameters. In this case the source is a WMS with data from the Dutch city Zaanstad.

Note, the urls below has chnaged and not working anymore, principles still applies)

1. Easiest way to pick up the WMS source and at the same time verify that it works is through a demo page,
   for example http://geo.zaanstad.nl/mapproxy/demo/?srs=EPSG%3A28992&format=image%2Fpng&wms_layer=Zaanstad
2. Select CRS (coordinate system), 4326 for use together with the default adaptive basemap or 3857 with the defualt basemap. Right click on the map image and "Open image in new tab..." . Copy the image url:
   http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&SRS=EPSG%3A28992&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&STYLES=&BBOX=77018.328125,474244.265625,153485.671875,512224.734375&WIDTH=1208&HEIGHT=600
3. Remove the following parameters (these are filled in by the map) : BBOX, WIDTH, HEIGHT, VERSION, SRS.
   That produces the url to use: http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&REQUEST=GetMap&STYLES=
4. Fill in CRS and VERSION (in my example EPSG:3857 and 1.1.1 check the WMS url for the version).
   
   Pro tip, "Load via Server" is practical to use if the the WMS doesn't allow CORS.
   
   Pro tip 2, run Sense in the browser (http://localhost:4848/hub/my/work) and use the dev console (F12), good to spot errors from the WMS server.
   
   

It's common that customers keep geodata in shapefiles, here's how to load them in to QlikView and Qlik Sense using Qlik GeoAnalytics.

Reading local files requires care, here's a checklist:

Step-by-step guide

First place the shapefile in a zip file. In this example I'm using the Congressional Districts from US Census. A shapefile is actually several files, at least .shp file and .dbf file. Place the zip file on a webserver (Dropbox is handy).

1. Open the connector and choose "Simplify" (Load can also be used but it's good practice to simplify especially if the shapefile is large (>2MB).

2. Choose "Auto" for resolution.

3. Fill in the url: https://www2.census.gov/geo/tiger/TIGER2016/CD/tl_2016_us_cd115.zip or for local files file:///C:/ (Plus required)

4. A key field is handy but not mandatory, fill in GEOID in this example.

5. Fill in the CRS: EPSG:4269  (This shapefile is in coordinate system NAD 83, use internet search to find the EPSG code).

6. Click next, the operation return two tables: one table with index and the simplified geometry and second table with original non-simplified shapes and attributes. Uncheck "Geometry" in the second table if you just want the attributes.

7. Click Insert script and Load Data

URL

Local files

CRS

See below for example apps for Qlik Sens and QlikView as well as the shapefile example data.

Please note that when running the QlikView example you need to insert a connection string with the connector.

Hi All,

Created simple application State->District  wise layer map using KML.  Application used container object which has ability when you click on state automatically further drill down district will be display for India country. 

Hope this will be useful for new comers who wanted to learn google map using layer area option.

Thanks

Vikas

A list of all Operations and Connections available through the Qlik GeoAnalytics Connector

A more in depth analysis, including Use Cases and step by step instructions in setting up the following Operations, with screenshots:

• Routes
• Closest
• Dissolve
• Travel Areas
• Binning
• Cluster
• Within
• Loading a Shape File
• Loading a CAD file
• Converting from Northings/Eastings to Lat/Lon

Examples and guides for the Qlik GeoOperations. The examples are self sufficient with data and reloadable. The examples are minimal to explain to basic functionality. Please use the documentation for details of the operations. The examples works on Qlik SaaS and requires a GeoAnalytics license.

GeoOperations is simple, for instance, here's how to load a shapefile:

Load * Extension GeoOperations.ScriptEval('
load() DATASOURCE shape GEOFILE type="shape", 
url="https://dl.dropbox.com/s/ipi6tazxwkbuzu6/continents.zip?dl=0"');

Load * Extension GeoOperations.ScriptEval('
load() DATASOURCE cont GEOFILE type="shape", 
url="lib://DataFiles/continents.shpzip")';

GeoOperations Examples.qvf: Example with all basic operations

• Binning
• Cluster
• Closest
• Dissolve
• Intersect
• Load
• Routes
• Simplify
• TravelAreas
• Within
  

GeoOperations BigQuery.qvf : Example how to load from Google Big Query (requires a Google BigQuery license and instance).

GeoOperations CSV example.qvf : Example how to use more than one internal table as input.

Qlik Geocoding in GeoOperations.qvf : Example how to use Geocoding, TravelArea and Closest in runtime (requires a Qlik Geocoding license).

Note,  please use the GeoOperations template for Qlik Geocoding, available here: https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/Qlik-Geocoding-Geocoding-Template-Data-and-presentation/ta-p/1548595

Jan 27, 2022 updated with more examples and DataFiles access.

Mapping data based on postal code areas or center points is a common task. However postal codes is not a one global standard and there is lot of variation between countries in format and structure that makes the task challenging.

Also the availability of postal code areas varies, many countries are included in the Qlik Location database, but for others postal code areas need to be purchased separately.

Workflow

Here's the high level workflow, the bulk is data preparation before rendering

1. Prepare the data
- Make the postal codes unique
- Pad with zeroes
- Some countries are special
- Check coverage
- Build overview layers when possible

This app will show how to map postal code areas for five European countries: France, Great Britain, Germany and Italy. The indata is not clean and contains errors like in most uses cases.

Prepare the data

Make the postal codes unique

When mapping postal codes for several countries the first step is to make the postal code unique. The easiest way to do this is to add country code prefixed with a comma after the postal code.

=Postal & ',' & CountryCode as zip

Pad with zeroes

In many cases Excel drops leading zeroes in numerical postal codes. I can see that when looking at the max and min lengths. Note that not all postal codes are numeric though and UK is special so we fix that later. Pad with leading zeroes for France, Germany and Italy like this:

 

The postcodes are alphanumeric, and are variable in length: ranging from six to eight characters (including a space) long. Each post code is divided into two parts separated by a single space. More info at Wikipedia. Here's the three levels comparable to 2,3 and 4 digit numeric postal codes:

=If(IsNum(Mid(zip,2,1)),Left(zip,1),Left(zip,2)) & ',' & cc // Area

=subfield(zip,' ',1) & ',' & cc // District

=subfield(zip,' ',1) & ' ' & left(subfield(zip,' ',2),1) & ',' & cc // Sector

 

Check coverage

By checking the table at Qlik GeoAnalytics Coverage I can tell that there is built support for postal code areas for most of the countries I am interested. However:

(1) Postal code areas are missing for Italy.

(2) France doesn't support 2,3,4 level postal code areas.

(3) Northern Ireland is not included in the areas for Great Britain.

 

 

 

Truncate for 2 and 3 digit postal code levels

Germany and Switzerland are easy, just truncate the zip code and add the country code. Add the following before the load:

 

load *,

left(zip,2) & ',' & cc as zip2,

left(zip,3) & ',' & cc as zip3;

 

France, IntersectsMost

For France we will use administrative areas level 1 and level 2 as replacement of the non-existing postal code areas of 2 and 3 digit.

 

Using the QGA connector operation IntersectMosts will construct the hierarchy between postal code and admin boundaries:  Adm1>Adm2>Zip. For details see the load script.

 

 

Italy, Within

For Italy we will also use admin 1 and 2, and use a point layer for the postal code level.

 

Using the QGA connector operation Within we can link postal code center points to the administrative boundary level 2: Adm1>Adm2>Zip. For details see the load script.

 

 

Create map view

After that it's easy to put together a map view.

1. Create a drill down group Country > zip2 > zip3 > zip

2. Add map chart and a area layer, drill down group as dimension, color by Sum(Sales)

3. Add a point layer only visible on level "zip", add a set expression for location: only({<cc={'IT'}>}zip)

 

See the attached app and data for more details.

QGA can consume geodata from many different sources including Esri ArcGIS online, here's what's needed to show the data as a feature layer in QGA.

Step-by-step guide

Start by figuring out the correct parameters. In this case the source is administrative boundaries (Buurt) of the Dutch city Zwolle.

1. Usually an base url is given like this (https://services1.arcgis.com/3YlK2vfHGZtonb1r/ArcGIS/rest/services/Zwolle_WijkBuurt_gdb/FeatureServer/0), follow and click on "Query" at the bottom
2. Set parameters Where 1=1, Outfield BRTNR (this is the field we want to use as index) in, and set the output to geojson and clicked Query (Get)
3. Try out the the geojson in geojson.io, realise that projection needs to be 4326 (WGS84), changed the url param : outSR=4326.
   
   Note! If you want to use the builtin basemap, the geojson layer should be in WGS84 -> EPSG:4326
   
   
4. And voilá a valid geojson url! https://services1.arcgis.com/3YlK2vfHGZtonb1r/ArcGIS/rest/services/Zwolle_WijkBuurt_gdb/FeatureServer/0/query?where=1%3D1&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=BRTNR&returnGeometry=true&returnCentroid=false&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=4326&returnIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnDistinctValues=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&quantizationParameters=&sqlFormat=none&f=pgeojson&token=
5. Check that the geojson works by setting it up as a static geodata layer.
6. Then make a area layer with the geojson as input, BRTNR as dimension.I checked the geojson and used BRTNR as the "Source key attribute".
   
   
   See the attached Sense app for more details. Note 1, this example requires Qlik GeoAnalytics. Note 2, this app features data from an external service which might be out of service.

Authentication in ArcGIS

Special note regarding Authentication in ArcGIS (Thanks to ssamuels for the solution)

The authentication when working with secured map services from ESRI can be solved by setting up a REST connection in the loadscript to retrieve a new access token from the REST API of ArcGIS Online. This is done by sending a GenerateToken request to the URL "https://www.arcgis.com/sharing/generateToken?parameters".  The REST call must be a POST request over https and takes the following parameters:

username - Username of the user who wishes to get a token.

password - Password of user who wishes to get a token.

referer - The base URL of the web application that will invoke the services

expiration - The token expiration time in minutes (default value for this parameter is 60 minutes)

f - The response format. The value for this parameter must be "json".

The resulting access token is stored in the data model and can be used as a text variable to append to the location service url in the GeoAnalytics extension.

The GeoAnalytics connector accesses internet like all connectors through the service user that runs the Qlik Sense or QlikView server. This might complicate things for companies runs a web proxy which requires user authentication.

Note, log on to windows as the service user, that will create a profile used by the web proxy.

To overcome this the connector can be supplied with proxy settings. The connector is a .Net program so the settings are inherited for the .Net framework. Depending on the proxy the following the settings may work. The settings are to be inserted in the settings file at C:\Program Files\Common Files\Qlik\Custom Data\QvIdevioConnector\IdevioGeoAnalyticsConnector.exe.config path may vary due to how the installation was done (default folders). The settings are to be inserted in the end, in the <configuration> tag.

Ex 1: Tells the connector to use default credentials:

<system.net>

   <defaultProxy useDefaultCredentials="true" />

</system.net>

Ex 2: Add server to bypasslist (string is regexp):

<system.net>

<defaultProxy useDefaultCredentials="true" enabled="true" >

    <bypasslist> 

         <add address="https:\/\/ga\.qlikcloud\.com" /> 

    </bypasslist> 

</defaultProxy>

</system.net>

Ex 3: To specify the proxy explicitly:

  <system.net>

    <defaultProxy useDefaultCredentials="true" enabled="true">

      <proxy usesystemdefault="False" autoDetect="False" proxyaddress="http://proxy:8080" />

    </defaultProxy>

  </system.net>

For more information about proxy settings for .Net:

<defaultProxy> Element (Network Settings) | Microsoft Docs

Examples and guides for the Qlik GeoAnalytics Connector operations. The examples are self sufficient with data and reloadable. The examples are minimal to explain to basic functionality. Please use the documentation for details of the operations.

Although the examples are made with Qlik Sense the load script code will work in QlikView too. The following operations are covered

• AddressPointLookup (Separate license required, Qlik Geocoding)
• Binning 
• Closest 
• Cluster Guide 
• Dissolve Guide 
• Intersects 
• IntersectsMost 
• IpLookup 
• Load 
• NamedAreaLookup 
• NamedPointLookup 
• PointToAddressLookup (Separate license required, Qlik Geocoding)
• Routes 
• Simplify 
• SpatialIndex 
• TravelAreas 
• Within 

Edit, the examples have been updated to use map chart.

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you how to add in an address to lookup the latitude longitude coordinate and then calculate the travel distance by car within 5, 10, 15 mins.  These 2 actions can be initiated when doing the analysis and the results are calculated on the fly.

First, I will create 2 variables vAddress and vAddressPoint, and add in the variable input box and action button.

Next assign the vAddress to the variable input box so that when I type in the address to add a point, the address will be stored inside vAddress.

Next I will use GeoOperation AddressPointLookup to return me the AddressPoint by passing in the vAddress as input.

And then assign this as an action to the action button.  Set the action to set variable value, variable to vAddressPoint and the value to the GeoOperation.

When I click on the action button, the AddressPointLookup function will return the latitude longitude coordinate of the address and stored inside vAddressPoint.  I can then add this point onto the map as a point layer.

I will repeat this for the travel area: create 2 variables vTravelTime and vTravelArea, and add in the variable input dropdown and action button.

Next assign the vTravelTime to the variable dropdown and set the value to 5, 10, 15 and 20 mins.  Remember to set the value to the actual value.

Next I will use GeoOperation TravelAreas to return me the TravelArea by passing in the vTravelTime and vAddressPoint as input.

And then assign this as an action to the action button.  Set the action to set variable value, variable to vTravelArea and the value to the GeoOperation.

When I click on the action button, the TravelAreas function will return the shape of the TravelArea and stored inside vTravelArea.  I can then add this point onto the map as an area layer.

This will be the end result.

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you where to get Singapore Primary School information, how to calculate the 1km circle coverage of the school and which HDB unit falls within the 1km circle.

The information for Singapore Primary School is from https://data.gov.sg/dataset/school-directory-and-information

We can use RestfulAPI call to retrieve the information from https://data.gov.sg/api/action/datastore_search?resource_id=ede26d32-01af-4228-b1ed-f05c45a1d8ee&limit=100 

As I only need Primary School so I will add in a filter where mainlevel_code='PRIMARY'.  I have created another column to combine address, postal code and 'Singapore' to be used later to lookup latitude longitude coordinate.  RowNo is also added to create a unique identifier for the Primary School.

Next I will use the GeoOperation AddressPointLookup to geocode the lookup column to the respective latitude longitude coordinate as return as PrimarySchoolPoint.  (For more information on the operation AddressPointLookup do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...)

This will return a table containing both the Primary School information and the PrimarySchoolPoint.

Next for Singapore Citizen to register for a Primary School in Singapore, distance to the Primary School is taken into consideration.  I will need to find out the 1km circle of all the Primary School if I want to have priority during school admission.

Next I will use the GeoOperation TravelArea to calculate the 1km circle by using the transportation="bird" option.  This will draw a 1km circle regardless of the roads.  If I need to calculate the distance by car I can change the transportation="car".   (For more information on the operation TravelArea  do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector-geoanalytics_reference-Operation.htm) 

This will return a table containing the PrimarySchoolID and the shape of the 1km circle.

I can then use GeoOperation Within to determine which HDB unit falls within the 1km circle of the Primary School.  However, since the ScriptEval cannot take in 2 intable, I will need to store one of them into online storage and be referenced using the URL method.  (For more information on the operation Closest and the use of URL do refer to  https://community.qlik.com/t5/GeoAnalytics/GeoOperations-use-CLOSEST-with-two-loaded-tables/td-p/172...) 

This will return a table containing the PrimarySchoolID and a list of HDB units that falls within the 1km circle.

I can then put all these data into the map.  For e.g., if I select a Primary School, I will be able to see the 1km circle and all the HDB units that falls within.

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you where to get the MRT/LRT information and how to calculate the nearest routes from HDB to the closest MRT/LRT stations.

The information for the Singapore MRT/LRT is from https://www.kaggle.com/yxlee245/singapore-train-station-coordinates 

This is a screenshot of the data:

Next I will use the GeoOperation Closest to calculate the shortest distance between the HDB and the newly acquired MRT dataset.  However, since the ScriptEval cannot take in 2 intable, I will need to store one of them into online storage and be referenced using the URL method.  (For more information on the operation Closest and the use of URL do refer to  https://community.qlik.com/t5/GeoAnalytics/GeoOperations-use-CLOSEST-with-two-loaded-tables/td-p/1721665) 

I also get to know that if I use dropbox I will need to change the prefix www. to dl. and suffix dl=0 to dl=1 .  (For more information on this do refer to https://community.qlik.com/t5/GeoAnalytics/GeoAnalytics-Shapefile-issue/td-p/1456366)

While running the GeoOperation I faced another issue, the dataset is too big to run.   I will need to split the dataset into chunks of 100 and run it individual.  (For more information on how to split up the data do refer to https://community.qlik.com/t5/Knowledge/Splitting-up-GeoAnalytics-connector-operations/ta-p/1715346 )

This will return a table containing the mapping of MRTID and HDBID, the distance and routes between the 2 points.

I can then use the routes to create a line layer in the map to show the path between the 2 points

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you how to geocode an address in the HDB transaction into their latitude longitude coordinate for further Geo Operation and how to combine multiple HDB blocks into bins 

After I have imported the HDB resale transactions, I realized that I cannot plot the location of each HDB block onto the map.  I will need to do a lookup to provide a latitude longitude coordinate for each HDB block.

First I will need to find out all the distinct HDB block from the dataset.  I created a column HDBID that takes in block and street name to form an ID.  The country Singapore is added into the ID to ensure that the lookup is based on address in Singapore.  Go into the data manager and create a calculated field with the formula block&’ ‘&street_name&’ Singapore’

Looking at the street name I noticed` there are short form NTH, STH and C’WEALTH.  These words may not be fully interpreted in the lookup so I will have to rename these words to its full word.

Let me create another table with a distinct list of all HDBID and replace all the 3 short-forms and name this column as lookup

I will call the GeoOperation AddressPointLookup to geocode the lookup column to the respective latitude longitude coordinate as return as HDBPoint.  (For more information on the operation AddressPointLookup do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector-geoanalytics_reference-Operation.htm)

With this I will have a table with the HDBID as the linking key back to the HDB Resale Transaction and the HDBPoint which stores the latitude longitude coordinate of the HDB block.  This is a sample of the table:

As there are many HDB Blocks, showing them on a map is not easy to visualize

Let me use the GeoOperation Binning to group them into bins.  I will pass in the HDBID as the key field, HDBPoint as the point field from the HDB_LatLong table , set the type to be hexagonal, grid size to be 0.01 which is equivalent to 100m and the gridWithHeightRation to be 1.  (For more information on the operation Binning do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector-geoanalytics_reference-Operation.htm)

This is how it would look like

And I can color code the Bin, for e.g., Avg Resale Price so that it is easier to visualize as compared to the latitude longitude coordinate.

When I zoomed down to a few bins, the map will look weird as the bin covers the whole area:

Another way is to use the layer display to determine which layer to show under the zoom level:

In this map I have put in 3 layers (town, bin and point) determined by the zoom:

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

In this series of video and documents, I will walk though a real life example of finding my dream home in Singapore using data from various sources, combining with Qlik GeoAnalytics to analyse the suitable units based on distance to MRT station and within 1km of a Primary School.

In Singapore we have government houses which is term as HDB (Housing Development Board) flats which is the most affordable form of housing.  We can buy a newly built HDB unit or from the resale market.  New HDB units are cheaper but due to the current pandemic, all the building projects are being delayed due to shortage of manpower and supplies.  I have no choice but to look at the resale market.  So instead of approaching a property agent, I would like to do some homework on the resale property market and the potential unit that meet most if not all of my considerations.

All the resale transactions of the HDB flats are available at https://data.gov.sg/dataset/resale-flat-prices.  I can use Qlik Sense to ingest this set of data to analyse the past trend and to gauge whether my existing budget is enough.  On top of that I also want to live near my parents-in-law as I will need their help to take care of my children.  One of them is going to Primary School and in Singapore, living near to a Primary School affects priority admission.  I would like to live within 1km circle of an ideal Primary School.  As I will need to take public transport to work, so distance to the nearest MRT (Mass Rapid Transit) or LRT (Light Rail Transit) is also a factor.

I have build a Qlik Sense dashboard with all these considerations and the following is a video on how I use the dashboard to find my dream home.

I have also documented all the building blocks of this dashboard as follows:

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

I am making this post because we are soon switching to a new IP data provider.
The new data contains more ip entries with City name but provides no admin codes
where previously we had admin code for some of the ip entries.

I am sharing two example apps here that shows how to do IP data lookup
followed by a closest operation in order to retrieve the closest admin area
for each IP address.

The app "IPDataUSAdm1Code_GeoConnector.qvf" contains a load script for
Qlik Sense Server/Desktop using the GeoAnalytics connector.
It also contains some sheets that explains how to generate that load script.

The app "IPDataUSAdm1_GeoOperations.qvf" contains a load script that uses
GeoAnalytics operations. GeoAnalytics operations is only available on Qlik Cloud Services.

Both apps contains US ip addresses as examples but the apps should work with
other countries if the list of ip addresses is replaced. Some countries have both a number code and a letter code for their admin areas, if the code retrieved from the operation is not of the type you want there is always the option of retrieving admin name instead.

This workshop contains the following 3 topics, and was run at Qonnections 2019. 

TABLE OF CONTENTS

Exercise 1 - How to improve Lookup Accuracy 

Exercise 2 - IP lookup and how to visualize “Internet users”

Exercise 3 – How to map a picture

The detailed instructions including all data and example applications are attached in the zip file. The estimated time to run the excercises is around 2 hours. 

Prerequisties: Qlik Sense Feb- and Qlik GeoAnalytics.

Special thanks to Patric Nordström, Director, and Johan Persson, Sr Architect, for all their contribution to this workshop.

How to Create the Magic Quadrant

A magic quadrant chart is possible with the use of the basic map chart and various layers. This will use the basic map chart in the included charts. This will also use point layers for the symbols and area layers for the grid. It has endless capabilities for use!

1. Add the map chart.
   1. In Map Settings set the Base map to NONE.
   2. Set Projection to User Defined Degrees.
   3. Turn Off Legend in Legend Setting.
   4. Turn off Scale Bar in Presentation Settings.

1. Add area layers for box.
   1. Add a layer titled box.
   2. Dimension equals 1.
   3. Location equals: ='[[[0,0],[100,0],[100,100],[0,100]]]'

1. Add area layers for Grid.
   1. If using a 4 quadrant plane use the following:
      1.      Add a layer titled Grid.
      2.      Dimension equals 1.
      3.      Location equals: ='['& '[[0,0],[50,0],[50,50],[0,50]],' & '[[50,50],[100,50],[100,100],[50,100]]' & ']'
   2. If using a 16 quadrant plane use the following:
      1.      You need to add 4 grid layers.
      2.      Each dimension is equal to 1.
      3.      Locations are as follows:

Grid1: ='['&'[[0,0],[25,0],[25,25],[0,25]],' &'[[25,25],[50,25],[50,50],[25,50]]'& ']'

Grid2: ='['&'[[50,0],[50,25],[75,25],[75,0]],' &'[[75,25],[100,25],[100,50],[75,50]]'& ']'

Grid3: ='['&'[[0,50],[25,50],[25,75],[0,75]],' &'[[25,75],[25,100],[50,100],[50,75]]'& ']'

Grid4: ='['&'[[50,50],[75,50],[75,75],[50,75]],'&'[[75,75],[100,75],[100,100],[75,100]]'& ']'

1. Set Grid and Box Colors to desired colors.
2. For your dots or bubbles on the grid, add a point layer
   1. Use a unique dimension for the point layer.
      1.      If you need separate type shapes, limit your dimension and set shape in settings.
   2. As location you need to have two separate fields with values that range from 0-100
      1.      Set the “Y” value as the Latitude.
      2.      Set the “X” value as the Longitude.
   3. If desired color by a particular method.

Quadrant Labels

For Labels you will need to add another point layer.

1. Add point layer.
2. Dimension is Label.
3. Location is as follows:
   1. LabelY as Latitude.
   2. LabelX as Longitude.
4. Make bubble size 0 and opacity far left.
5. Under general options set show labels to ON and Centered.
6. Inline load with coordinates is as follows:

4 Quadrant

QuadrantLabels:

load * Inline [

Label, LabelX, LabelY

UpperRight, 75, 98

UpperLeft, 25, 98

LowerRight, 75, 2

LowerLeft, 25, 2

];

16 Quadrant

load * Inline [

Label, LabelX, LabelY

X4, 87.5, 2

X3, 62.5, 2

X2, 37.5, 2

X1, 12.5, 2

Y1, 2,12.5

Y2, 2,37.5

Y3, 2,62.5

Y4, 2,87.5

];

Finished Product

Update, the example app now have the drawing as a background.

Floor plans and cad drawings can be useful, for instance in production dashboards, to visualize status and flows in a factory. However cad drawings are in many cases a mess to work with.

Here's a simple, pragmatic approach to digitize rather than import the actual drawing. The major issue with most CAD drawings is lack of structure, details without meaning and the objects on the right level are missing. It’s not the format or alignment that is the problem. In many cases it's better to use the drawing as a input to produce the polygons and information needed.

In this tutorial I use QGIS for digitizing and out put to a format that is easy to import to QGA. In this example the drawing is digitized in a local reference system, ie no alignment is made with the rest of the world.

Step-by-step guide

1. Install QGA basic, Plus not needed if Dropbox et al is used.
2. Install QGIS (https://www.qgis.org)
3. Get a picture of the floor plan in an image format like PNG or JPG.
4. Drag and drop the image to the layers panel of QGIS. Choose a meter based Coordinate Reference System, for instance EPSG:3857 is a good choice.
   
   
   
   
5. Layer→ Create Layer→ New Shapefile Layer... Choose Polygon, same CRS, 3857. Add "Name" text field for convenience.
   
   
   
   
6. Save layer in a folder. Preferable in dropbox folder.
   
   
   
   
7. Right click on the new polygon layer and "Toggle editing" to start editing.
   
   
   
8. Start adding features (Ctrl +)
   
   
   
9. Click on the edges of the desired polygon, set id and name. Use node tool if fine tuning of edges is necessary.
   
   
   
10. When done "Toggle editing"to save.
    
    
    
11. Zip the shapefile to an archive and get the dropbox url. (replace www with dl to get direct link)
    
    
    
12. Create a new Sense app and use the drop url in the Load operator of QGA connector. CRS: EPSG:3857. Note change url from 'www' to 'dl' for direct file access in dropbox.
    
    
    

Example app

The example app features sites and a drawing visible when one site is selected.

If you want to add and image background, here's how to scale and translate the image, in my example:

Tip 1: get a white background use ='[[ [-180, -80], [-180,  80], [ 180,  80], [ 180, -80], [-180, -80] ]]' for location, set color to white and make the layer non selectable and not part of autozoom.

Tip 2, Use a bubble layer with coordinates for different sites, when one site is selected turn on drawing and the white background.