Automating distance calculation in a spreadsheet?

[Mal]

Any suggestions on how to automate getting the GCM distance between two cities/airports in a spreadsheet?

I think I've seen this done before, but not sure how the lookup was done. May have used Gcmap.com to do it.

Suggestions?

 

[elbarto]

No idea. I just use great circle mapper as you've suggested to work out great circle distances, but i prefer the original gc.kls2.com rather than the new gcmap.com

 

[medhead]

I started to do this using a great circle formula. I got stumped on need the lat and long. I started recording the position for all my common airports, i.e. australian domestic, but gave up. I can send you a excel spreadsheet that does the calculation and shows the formula if you wish. But I think I found it originally by googling great circle distance calculation excel (or something like that).

I've been meaning to investigate online location data sources of airport location from either that open source flight log website (my open flights?) or by asking the creator of the Frequent Flyer Calculator App their data source (damn and now I'm really showing bad memory because I can't remember their name either)

 

[StevePER]

I think you could import your airport location data from somewhere like this:
.:: The Global Airport Database - By Arash Partow ::.

And then use the great circle algorithm to calculate a distance.

 

[thewinchester]

I looked into this a while back when building the QFF Calculator spreadsheet (original thread which started it all) but I couldn't find an easy way to do it.

After speaking with a few people, I managed to get together a pretty comprehensive list of distances from port to port, and was able to build a lookup function to present this (then obv. pass to other fields to do the calculations). I ran this data against QF's list for about 50 popular domestic and international routes and my distances were providing me points earning accuracy of +/- 10pts.

The spreadsheet in question is licensed under a Creative Commons license, so you are welcome to adapt or reuse the spreadsheet and the data - as long as you attribute the source and share the results under the same license.

 

[anat0l]

Two methods come to mind:

One is to somehow run a query through Great Circle Mapper and somehow fetch or store the resultant web page. You could then parse that to get the distance. GCM queries are in the URL so it is easy to generalise them.

The second and probably easier method is to borrow a bit from this: BMI Redemption Calculator (Mileage, tax & cash costs of redemptions in Excel) - FlyerTalk Forums

Download the zipped xls file, open up in Excel and use the Unhide Sheets context menu command to find the underlying data. There should be one worksheet that has been hidden which (a) has a table of lat/lon readings for many airports around the world (most common ones included), and (b) provides the calculation guts for GCM given two lat/lon pairs.

Hope this helps.

 

[tuppaware]

I have a database of GPS cords based on Airports for my app..
Using standard formulae to work out the distance based on the circumference of the earth (as a circle).
With an input of 2 airport codes. I could write a webpage that would output that for you, but I don't know how you'd get that into a spreadsheet (makes me shutter!)

 

[Mal]

Thanks for the info. Still assessing the best way to do this...

Sent from my GT-I9100 using AustFreqFly

 

[simongr]

The way I did this was to go to mileage monkey and select known ow hub ports and search for all destinations from there - then I did a point to point from the hub to all locations and mm spat out the destination pair distance (I did SYD-sin-SYD-bkk... And so on) then did a bit of jiggery pokery in excel to create the basis of a look up table...

The harder thing I haven't been able to do is then automate the predicted points earning depending on carrier fare basis and program credited to...