How to calculate distance between locations in NAV

0 Shares

Introduction

It might be useful to calculate distance between locations of stores in NAV. This can help for example in order to let customer know which is the nearest location in which a product is in stock. In case product is not in stock in the location where the customer is, for example.

One method to calculate estimate distance is to use the Haversine formula that returns straight line distance between the 2 locations.

Of course, this is not the distance on road, this formula gives an approximate result. If you need exact distance on road you can use various web services, for example google maps api. You will need an api key as well.

How to calculate distance in NAV

The Haversine formula is NAV looks like this:

GetDistanceBetweenPlaces(lat1 : Decimal;lon1 : Decimal;lat2 : Decimal;lon2 : Decimal) : Decimal
PIx := 3.141592653589793;
RADIUS := 6371;

dlat := Radians(lat2 - lat1);
dlon := Radians(lon2 - lon1);

a := MathLib.Sin(dlat / 2) * MathLib.Sin(dlat / 2) + 
     MathLib.Cos(Radians(lat1)) * MathLib.Cos(Radians(lat2)) * 
     MathLib.Sin(dlon / 2) * MathLib.Sin(dlon / 2);

angle := 2 * MathLib.Atan2(MathLib.Sqrt(a),MathLib.Sqrt(1 - a));
EXIT(angle * RADIUS);

LOCAL Radians(x : Decimal) : Decimal
EXIT(x * (PIx / 180));

So, you give as arguments the latitude and longitude of the 2 locations and the formula returns the distance in kilometers.

Unique combinations between each location

In order to use this in the most efficient way, I created also a table to store all location combinations and the distances between each location. It does not make a lot of sense to calculate the distance all the time when needed, as locations are not changing frequently.. Calculating distances upfront and only reading the distance from a table can improve a little bit the performance.

The table has only 3 fields: Store1, Store2 and Distance.

The algorithm to populate this table is this one:

RefreshStoresCombinationTable()
// used from page Store Distances
DotNetList1 := DotNetList1.List();

IF StoreRec.FINDSET THEN
  REPEAT
    DotNetList1.Add(StoreRec."No.");
  UNTIL StoreRec.NEXT = 0;

DotNetList2 := DotNetList2.List(DotNetList1);
NoOfElements := DotNetList1.Count;

Window.OPEN('Processing..\@1@@@@@@@@@@@@@@@@@@');

FOR i := 0 TO (NoOfElements - 1) DO BEGIN
  Window.UPDATE(1,ROUND(i / NoOfElements * 10000, 1));
  FOR j := i + 1 TO (NoOfElements - 1) DO
    CreateStoresCombination(DotNetList1.Item(i),DotNetList2.Item(j));
END;

Window.CLOSE;

This algorithm is creating unique combinations between each location (store) and populates the distance between each location based on the Haversine formula .
In case the combination already exists, it updates the distance.

You can see below the rest of the functions:


LOCAL CreateStoresCombination(Store1No : Code[10];Store2No : Code[10])
StoreRec.GET(Store1No);
StoreRec2.GET(Store2No);

IF StoreDistance.GET(Store1No,Store2No) THEN
  ModifyStoreDistanceRec(StoreDistance,StoreRec,StoreRec2)
ELSE
  InsertStoreDistanceRec(StoreDistance,StoreRec,StoreRec2);

If the set up data is missing on Stores (we don’t have the latitude and longitude of each address), we consider the distance is 999999. This is done in order to show those stores after the ones for which we have the exact distance in case we sort records by distance.

LOCAL ModifyStoreDistanceRec(VAR StoreDistance : Record "Store Distance";StoreRec : Record Store;StoreRec2 : Record Store)
lat1 := StoreRec.Latitude;
lon1 := StoreRec.Longitude;
lat2 := StoreRec2.Latitude;
lon2 := StoreRec2.Longitude;

IF (lat1 = 0) OR (lon1 = 0) OR (lat2 = 0) OR (lon2 = 0) THEN
  StoreDistance.Distance := 999999
ELSE
  StoreDistance.Distance := GetDistanceBetweenPlaces(lat1,lon1,lat2,lon2);

StoreDistance.MODIFY;

LOCAL InsertStoreDistanceRec(VAR StoreDistance : Record "Store Distance";StoreRec : Record Store;StoreRec2 : Record Store)
lat1 := StoreRec.Latitude;
lon1 := StoreRec.Longitude;
lat2 := StoreRec2.Latitude;
lon2 := StoreRec2.Longitude;

StoreDistance.INIT;
StoreDistance.Store1 := StoreRec."No.";
StoreDistance.Store2 := StoreRec2."No.";

IF (lat1 = 0) OR (lon1 = 0) OR (lat2 = 0) OR (lon2 = 0) THEN
  StoreDistance.Distance := 999999
ELSE
  StoreDistance.Distance := GetDistanceBetweenPlaces(lat1,lon1,lat2,lon2);

StoreDistance.INSERT;

How can this be used

If we ran from page the action “RefreshStoresCombinationTable” you will get the distance like this for each store combination:

Then, in code we can refer to this table in order to get distance and for example sort a table with available Inventory by which store is the closest compared to the current location:

IF NOT StoreDistance.GET(RelatedStoreG."No.",Store."No.") THEN
  IF StoreDistance.GET(Store."No.",RelatedStoreG."No.") THEN;

InvLookupTemp."Related Store Distance" := StoreDistance.Distance;

PS: the above view is sorted by 2 criteria: item is in stock (inventory > 0) and then by the distance from the current Store (in example S0002). So it basically shows first the closest stores that have the item in stock. For converting distances in miles I multiplied the distance in km with 0.621371192. 🙂