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:

- numbervar lat1 which is the latitude of the first point
- numbervar lon1 which is the longitude of the first point
- numbervar lat2 which is the latitude of the second point
- numbervar lon2 which is the longitude of the second point
- 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)*

John SchulzI 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?

Jamie WisemanPost authorhi 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

Ken HamadyWhen 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.