Recently, I had the task of calculating the distance between two locations using Google coordinates.
During my web search, I quickly came across the complex Haversine formula:
a=sin^2(Δϕ/2)+cos(ϕ1)⋅cos(ϕ2)⋅sin^2(Δλ/2)
c=2⋅arctan(√a/√(1−a))
d=R⋅cd=R⋅c
Where:
ϕ1 and ϕ2 are the latitudes of the two points in radians.
λ1 and λ2 are the longitudes of the two points in radians.
Δϕ=ϕ2−ϕ1 is the difference in latitude.
Δλ=λ2−λ1 is the difference in longitude.
R is the radius of the Earth (approximately 6371 kilometers).
d is the calculated distance between the two points in kilometers.
Such formulas were last encountered during my studies, and my first thought was honestly “Oh dear!”
Then it occurred to me that I probably wasn’t the first person to seek an answer on the internet, and if there’s enough information available online, ChatGPT has probably also learned it.
ChatGPT always claims it can’t program, but in my experience, if a problem has been discussed enough online, ChatGPT can reproduce the resulting code.
Furthermore, ChatGPT can apply what it has learned to a similar problem and provide valuable insights.
So, when I asked ChatGPT about the calculation, it immediately provided the formula I already knew from the internet.
To my surprise, ChatGPT also presented an example code in Python.
Now, Python is not my language, so I had a vague hope that ChatGPT could at least somewhat accurately translate the example into FileMaker functions.
So, I asked for a FileMaker example.
I was completely astonished when I was then shown a FileMaker formula with a “Let” function that allows the parameters to be passed quite easily:
Let (
[
lat1 = Substitute ( yourFieldForLatitude1 ; “,” ; “.” ) ;
lon1 = Substitute ( yourFieldForLongitude1 ; “,” ; “.” ) ;
lat2 = Substitute ( yourFieldForLatitude2 ; “,” ; “.” ) ;
lon2 = Substitute ( YourFieldForLongitude2 ; “,” ; “.” ) ;
R = 6371 ; // Earth radius in kilometers
// Conversion of the coordinates to radians
phi1 = lat1 * Pi / 180 ;
phi2 = lat2 * Pi / 180 ;
delta_phi = (lat2 - lat1) * Pi / 180 ;
delta_lambda = (lon2 - lon1) * Pi / 180 ;
// Haversine formula
a = Sin(delta_phi / 2) ^ 2 + Cos(phi1) * Cos(phi2) * Sin(delta_lambda / 2) ^ 2 ;
c = 2 * Atan(Sqrt(a) / Sqrt(1 - a)) ;
// Calculation of the distance
distance = R * c
] ;
distance
)
The calculation is even designed so that it can be implemented as a custom function without modifications!
And the fact that the formula was perfectly divided and better commented than my own functions really impressed me.
The task mentioned at the beginning could now be solved without further ado.
I created a Table1 with Google coordinates of locations and another Table2 with the 3 places serving as reference points for the distance.
Table1:
- Location
- Lat
- Long
- Distance
Table2:
- Location
- Lat
- Long
The two tables are connected via a simple “X” relationship.
So, the calculation for the “distance” must calculate the distances from all locations in Table2 and then determine the one with the shortest distance.
To solve this, a WHILE function can be used, which calculates the distance for each location and sets the OUT value only if the distance is less than the previously determined one.
To ensure that the first value is set in any case, the initial value is an absurdly high value that the first calculation will replace.
The formula looks like this:
WHILE (
[
max = Count ( Table2::Location ) ;
out = 99999999 ;
i = 1
] ;
i ≤ max ;
[
dist = distance ( Table1::LAT ; Table1::LON ; GetNthRecord ( Table2::LAT ; i ) ; GetNthRecord ( Table2::LON ; i ) ) ;
out = If ( dist < out ; dist ; out ) ;
i = i + 1
] ;
out
)
My conclusion at this point is:
ChatGPT can be a real time-saver for any FileMaker developer.
It doesn’t have to produce a perfect solution like in my case; a good starting point is often enough.
I can only recommend everyone to try it with a clearly defined problem.
As always, if you have any questions, feel free to send me an email, and I’ll try to answer as soon as possible.