Nearby Customers using SAP HANA spatial capabilities & SAPUI5

[sreehari_vpillai]

What is it all about !

I recently had to develop a Fiori like application for a direct sales scenario ( DSD module ) .The salesmen visit vending machines located all over UAE and do a stock audit along with the price tag in each spiral with in the machine . Vending machines are defined as customers in customer master.

So of course in the application, there must be a search help to choose the customer. There we tried to do some improvisation and turned out awesome .

 

What we did

Before explaining further , lets see the data we had with us in SAP. All the customers' , including the vending machines' geo location is maintained in customer master. We have 2 append fields in KNA1 which stores the latitude and longitude. Without which the whole solution wouldn't be materialized.

 

The SAPUI5 side

Firstly , we need to capture the location of salesman. When he opens the Fiori application( We have customized Fiori Client , but this works in browser too ) , we call the navigator service which returns the latitude and longitude.

if (!navigator.geolocation) { 

   //no access to read location data

}

else{

navigator.geolocation.getCurrentPosition(function(position) {

   var latitude = position.coords.latitude ;

   var longitude = position.coords.longitude ;



   //send these cordinates to backend.





}.bind(this),function(error){

   //ignore the error .. something wrong with lcaiton service

}.bind(this));





}

This is how we capture the geo location. If we running this with in the cordova container, then app should have access to read device location .

The ABAP & HANA Side 

Ok , now how do we find the customers nearby ? Either we must use location services API like google to send all the information to them and get it done. But for this particular scenario , this is not required.

2 options  :

Option 1 -  Manually write a logic to find distance to each customer from the geo-coordinate passed from the UI  .

Here is how you can do it. Link



Option 2 : 

We are running on SAP HANA . SAP HANA has built in spacial capabilities .

Let me show you the SQL behind first how does this work .

select 

(NEW ST_POINT('POINT(25.261515 55.366487)',4326).ST_DISTANCE(NEW ST_POINT('POINT(25.201272 55.274903)',4326),'meter'))/1000 as Distance

from dummy ;

And the result returned is 10.889638126959479 KM



We wrote a wrapper AMDP class , which calculates the distance from the input Geo coordinates to each customer( vending machines ) and returns only the list with in 500m limit.

  METHOD get_customers_nearby BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT using KNA1 .

       LT_KNA1_DATA =

         select KUNNR ,

                LZONE ,

                NAME1 ,

                NAME2 ,

                cast( NEW ST_POINT('POINT(' || ZLAT || '  ' || ZLONG || ')',4326).

                            ST_DISTANCE(NEW ST_POINT('POINT(' || :LATITUDE || '  ' || :LONGITUDE || ')',4326)) as integer ) 

                as DISTANCE_IN_M

                from KNA1 where LZONE = :ROUTE and ZLAT != '' and ZLONG != '';



     LT_KNA1 =   select KUNNR ,

                        LZONE ,

                        NAME1 ,

                        NAME2 ,

                        DISTANCE_IN_M



                        from   :LT_KNA1_DATA

                               where

                               DISTANCE_IN_M <=  :DISTANCEINM

                              order by DISTANCE_IN_M asc;



  ENDMETHOD.

 

Output in SAPUI5 App 



 

Enjoy innovating ...

Sreehari Pillai