Food Delivery Analysis in Qliksense| Dynamic Views| Qlik Maps| Google Direction API| Ascii Decoding Algo| Haversine formula

Hi Folks!

I wanted to do a project on a location dataset to visualize using various Map layers in Qlik.

Before I could get hands on this data set, my aim was to plot a Route on a Map.

Map visuals have always fascinated me and so I decided to take shot at visualizing my own morning running route.

I got my datapoints from my Fitbit app and I uploaded it to my SaaS and got the below results.

However, I wanted to do more. I scoured the internet and got my hands on this simple dataset .

If you look at each field closely, one could derive a lot from them. So many ideas started flowing in my head, I completed the logical part of the project in 2-3 days.

ID,Delivery_person_ID,Delivery_person_Age,Delivery_person_Ratings,Restaurant_latitude,Restaurant_longitude,Delivery_location_latitude,Delivery_location_longitude,Type_of_order,Type_of_vehicle,Time_taken(min)

There are 4 key parts in this project according to me.

1. Haversine Theorem to calculate distance between two co-ordinates:

There are certain KPIs in my Dashboard which were derived only because of this little formula.

(2*(asin(sqrt(pow(sin(((Delivery_location_latitude*(Pi()/180))-(Restaurant_latitude*(Pi()/180)))/2),2) + cos(Delivery_location_latitude*(Pi()/180))* cos(Restaurant_latitude*(Pi()/180))* pow(sin(((Delivery_location_longitude*(Pi()/180))-(Restaurant_longitude*(Pi()/180)))/2),2)))))*6371

I took a reference from a Javascript code on GeeksForGeeks and wrote the above formula in Qlik.

Once I got the distance covered, and since I had the delivery time, using Speed = Distance/Time I got the driver's speed.

And since I had the Vehicle details, I calculated the estimated delivery time assuming their average pessimistic speed. I know I could get more accurate ETA, distance using the Google Direction API, but honestly I wanted to do Math formulas in Qlik and I felt directly getting this data using API calls was too easy and I won't be pushing myself to explore new things.

Finally, I was able to create the summary page of my Dashboard and plot the below layers on my Map and derive the following KPIs.

KPIs: Speed, Distance, Delivery Status - Delayed/On-Time

Map Layers:

Area Layer - States/UT in India

Point Layer 1 : Restaurant Location (Pin color as per average rating)

Point Layer 2 : Destination Location

Line Layer: Showing the connection between the Source to Destination

However, **** I was still hoping to do more and wanted to plot a route on the map that driver might have taken or took while delivering the parcel - similar to Zomato. My dataset didn't have route co-ordinates that I can plot on the Map easily using creating linestring, but then came I across the Google Maps API.

2. Google Direction API:

I quickly opened a free tier account on Google Cloud and enabled the Road APIs, got my API Key and using the Rest API connector in Qlik, started making calls with my co-ordinates Restaurant and Destination to get the route co-ordinates.

https://maps.googleapis.com/maps/api/directions/json?destination=$(dest_lat)%2C$(dest_lng)&origin=$(origin_lat)%2C$(origin_lng)&key=API_KEY;

But there was a minor problem, Google returned an encoded polygon string to convert the Lat/Long points in ASCII format.

I lost all hopes of plotting my route and was even ready to give up, but I came across a decoding algorithm written in Python. I feel fortunate that I have written programs in Python to automate some manual tasks at my previous job, I readily accepted the challenge to convert the decoding code in Qlik.

3. Decoding Algorithm

The below article details how Google encodes a set of lat/long points. The decoding algorithm is the reverse.

Google Article: https://developers.google.com/maps/documentation/utilities/polylinealgorithm

Python Code: https://github.com/geodav-tech/decode-google-maps-polyline/blob/master/polyline_decoder.py

To be honest, Python is a fairly easy programming language and Qlik has a rich set of Math/Logical functions. Hence, the conversion task was quite simple. Here is a code snippet of the algo in Qlik.

do while $(index) < len('$(polyline_str)') 
            for each unit in 'lat', 'lng'
                Trace unit= $(unit);
                let shift=0;
                let result = 0;
                let vFlag =0;
                
                do while $(vFlag) = 0
                      Trace Flag = $(vFlag);
                      let chr = Mid('$(polyline_str)',$(index)+1,1);
                      trace chr= '$(chr)';
                      let byte = ord('$(chr)') - 63;
                      TRACE Byte = $(byte);
                      
                      
                      index = $(index) + 1;
                      TRACE INDEX= $(index);
                      result = $(result) bitor (($(byte) bitand 31) << $(shift));
                      trace result= $(result);
                      //print('result2', result)
                      shift = shift+5;
                      if not ($(byte) >= 32) then
                          trace break 2nd loop;
                          Exit do;
                          vFlag=1;
                          Trace Flag = $(vFlag);
                      EndIf

                 loop; 
                

                if ($(result) bitand 1) then

                    $(unit)temp = bitnot($(result) >> 1);
                    Trace $(unit)temp = $($(unit)temp);
                else
                    $(unit)temp = $(result) >> 1;
                    Trace $(unit)temp = $($(unit)temp);
                endif
            
            Next ;    
        
        lat = $(lat)+$(lattemp);
        lng = $(lng)+$(lngtemp);
        
        Trace lat=$(lat) long=$(lng);
        
        seq=$(seq)+1;
        Trace seq: $(seq);
        
        Concatenate(Template)
        Load
            $(seq) as KEY,
            GeoMakePoint(($(lat)/100000.0),($(lng)/100000.0)) as Point
        AutoGenerate(1);
        
        
        if  $(index) >= len('$(polyline_str)') then
               trace break 1st loop;
                          
               Exit do;
                          
         EndIf
loop;

After going through all the hoops, I was finally able to achieve my goal. The little animated man on the bike is done using the Animator custom chart.

Now the last hurdle, Google API calls are free only for a certain limit. It was not cost effective to send origin-destination Lat/Long co-ordinates for 40k rows.

4. Dynamic Views/ On-Demand App template:

I cannot love Qlik enough for the ODAG functionality. In the moment I felt, the App Navigation links/Dynamic Views were made just for this particular scenario.

The entire Google API call code, Decoding algo, the route Map, and the animator chart are added in the template app.

When a particular Order is selected in the summary app, the Restaurant and Delivery Destination points are passed to the ODAG app and the above result is calculated./visualized. Saving me from a huge bill from Google :p