Airport to Airport Distance

[ismailzkhan]

Dear Techies,

In a job I need to calculate Airport to Airport distance in Kms. I used to take help of https://www.travelmath.com/flying-distance to find this. As list is huge searching for each entry is taking so much time.

Airport codes are as per IATA

Is there any shortcut to do this. Please help.

Attached excel file on this.

Regards,
Ismail Khan

 

[Hui]

There is no simple way
I can write a UDF tomorrow

 

[Chihiro]

You could do it in two stages.

1. Obtain Lat/Lon info for starting airport to destination airport.
This can be done manually and stored as list.
I'd just use data from Openflights personally. Use "airports.dat", it's in csv format.
https://openflights.org/data.html

Store it in separate sheet and lookup value (I'd recommend separating departure and arrival into 2 columns for ease of doing lookup).

2. Use Haversine formula UDF to calculate great-circle distances.
You can find the code in link below.
https://chandoo.org/forum/threads/power-map.31577/#post-187726

Ex:

 

[ismailzkhan]

Dear Chihiro,

Wow !!. This works like a charm. Thanks a lot.

Regards,
Ismail Khan

 

[bosco_yip]

Or………………….

Use Haversine formula –
Finding distance between Airport locations in formula solution

1] In C8 (Distance in Km), enter formula :

=ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*6371

2] See attached file together with Airport distance calculation sheet based on IATA code and Airport Database 2017

p.s.

If you want Distance in Mile, please change 6371(Km) to 3958.756(Mile) and formula become >>

=ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*3958.756

Regards
Bosco

 

[pecoflyer]

Is the result of these calculations to be used in real world flight planning?

 

[Chihiro]

Probably not. Since OP was using link in initial post.

That site uses Vincenty algorithm & WGS84 ellipsoid model for Earth's shape.
This disregards any altitude info, much the same way as Haversine. Though Haversine uses perfect sphere.

Vincenty is more accurate, when compared to Haversine, but magnitude of difference is relatively small (sea level approximation difference/error margin of +/- 100m). Distance calculation result can differ between two methods by up to 0.5%.

Neither approach is appropriate for accurate flight path planning.

 

[Chihiro]

@ismailzkhan

Unless you are using UDF in code. I'd recommend going with Bosco's method (formula). It will be faster than UDF method I'd believe.

UDF was primarily written for use within code (such as tool for finding nearest endpoint for last mile fibre routing from list of geo-coordinates).

 

[ismailzkhan]

Hi Chihiro,

Noted. Thanks !!

 

[ismailzkhan]

@ bosco_yip

Thanks..

 

[duncan williamson]

View attachment 55889

Or………………….

Use Haversine formula –
Finding distance between Airport locations in formula solution

1] In C8 (Distance in Km), enter formula :

=ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*6371

2] See attached file together with Airport distance calculation sheet based on IATA code and Airport Database 2017

p.s.

If you want Distance in Mile, please change 6371(Km) to 3958.756(Mile) and formula become >>

=ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*3958.756

Regards
Bosco

I have used this Haversine formula before but as I lost the file I created for it, I searched my hard drive and found a file I had been working on in 2010. In that file is the following formula that also works:

=3958.756*ACOS(SIN(C11)*SIN(C12)+COS(C11)*COS(C12)*COS(D12-D11))

I don't know if that formula has a name ...

Duncan