Skip to Content

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) 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) copy the code from below to paste in the formula

 

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)

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply