Nearby Customers using SAP HANA spatial capabilities & SAPUI5

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