Using Qlik Sense to find my dream home - Part 4

[Edy_Tan]

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&lim... 

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...) 

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