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. 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)

To report this post you need to login first.

4 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) 
    2. Ken Hamady

      When I copy the function code the dashes come in as ASCII 150 rather than ASCII 45, so they aren’t read correctly by the CR function editor.  But, the minus signs in the sample formula came in correctly.

      Also, all the quotes come in angled, and I get an error unless I correct them.

       

      (0) 
      1. Jamie Wiseman Post author

        hi Ken, i apologize for the huge delay in getting back to you. I have updated this blog post to have a working url to a sample report where you can copy the code from.

        regards, -jamie

        (0) 

Leave a Reply