Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
JWiseman
Active Contributor
0 Kudos


If you have data fields representing latitude and longitude and wish to calculate the distance between two points, you can use a Haversine calculation to approximate that distance.

 

In Crystal Reports there is no built in function that does that calculation, so this blog post will walk you through creating a Custom Function based on the Haversine formula.

 

Steps:


 

  1. Download the sample report from here.

  2. Open the sample report and go to the Report Menu > Formula Workshop > Report Custom Functions

  3. Open the HaversineCalculation function and copy the syntax


 

In your report:


 

1) in Crystal Reports go to the Report menu then Formula Workshop

 

2) right click on the Report Custom Functions folder and choose New

 

3) give the Custom Function a name of "HaversineCalculation"

 

4) paste the code that you copied earlier into your new Custom Function...the code will look something like the code below.  (please do not copy and paste the code seen below as you may have errors)

 

Function (numbervar lat1, numbervar lon1, numbervar lat2, numbervar lon2, stringvar unit)

// Haversine calculation
// R = earth’s radius (mean radius = 6,371km)
// Δlat = lat2− lat1
// Δlong = long2− long1
// a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
// c = 2.atan2(√a, √(1−a))
// d = R.c

numbervar rpd:= 0.017453293;            // rad per degree (PI/180 where PI = 3.1415926535)
numbervar rMi:= 3956;                   // radius in miles 
numbervar rKm:= 6371;                   // radius in kilometers 
numbervar rFt:= rMi * 5282;             // radius in feet 
numbervar rM:= rKm * 1000;              // radius in meters 

numbervar dlon:= lon2 - lon1; 
numbervar dlat:= lat2 - lat1; 
numbervar rdlon:= dlon * rpd; 
numbervar rdlat:= dlat * rpd; 
numbervar rlat1:= lat1 * rpd; 
numbervar rlon1 = lon1 * rpd; 
numbervar rlat2:= lat2 * rpd; 
numbervar rlon2:= lon2 * rpd; 

numbervar a:= Sin(rdlat/2) * Sin(rdlat/2) + Cos(rlat1) * Cos(rlat2) * Sin(rdlon/2) * Sin(rdlon/2);
if a <> 1 then numbervar c:= 2* Atn(Sqr(a) / Sqr(1-a));

numbervar dMi:= rMi * c;    // distance in miles 
numbervar dKm:= rKm * c;    // distance in kilometers 
numbervar dFt:= rFt * c;    // distance in feet 
numbervar dM:= rM * c;      // distance in meters 

select unit
case "mi" : dMi
case "km" : dKm
case "ft" : dFt
case "m" : dM
default : dKM

 

5) Save and Close the custom function

 

You now have a Custom Function that you can use to calculate the distance between two points. The function is expecting the following inputs:

 

  1. numbervar lat1 which is the latitude of the first point

  2. numbervar lon1 which is the longitude of the first point

  3. numbervar lat2 which is the latitude of the second point

  4. numbervar lon2 which is the longitude of the second point

  5. stringvar unit which represents the final output unit...use "mi" for miles, "km" for kilometers, "ft" for feet, or "m" for meters


 

If you want to see a sample of the function working, create a new Formula and copy this syntax into the new formula.

 

numbervar lat1:= 33.9459; //33.9459N
numbervar lon1:= -77.1566; //77.1566W
numbervar lat2:= 33.9139; //33.9139N
numbervar lon2:= -77.3133; // 77.3133W
stringvar unit:= "km"; //can be km (kilometers), m (meters), mi (miles), ft (feet)

HaversineCalculation (lat1, lon1, lat2, lon2, unit)

4 Comments