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.

2 Comments

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

  1. John Schulz

    I cannot get this calculation to work. I created the custom function and cut/paste the code.  When I go to check it, I get “the remaining text does not appear to be part of the formula”. It highlights when it gets to the – sign in the dlon numbervar.  Anyone have a suggestion?

     

    (0) 
    1. Jamie Wiseman Post author

      hi John,

      not sure why you’re getting that error especially at that location. however, here’s a report that has that custom function on it already. you can then Add it to your Repository should you wish to use it in other reports. the sample also has a couple of manual formulae on there for different flavours of the calculation but you can ignore those.

      regards,

      -jamie

      (0) 

Leave a Reply