Mar 192011
 

You have to hand it to Google’s engineers, even the best GPS will have a hard job beating this: driving directions from Nordkapp in Norway to Pan Saung in Myanmar (Burma). 14’281 kilometres will take just over a week of continuous driving:

How did I do this in Excel? GeodesiX

 Posted by at 12:28 am  Tagged with:
Oct 202010
 

Addin for Microsoft Excel which allows you to perform forward and reverse geocoding, both by address and latitude / longitude, calculate Great Circle Distances using Vincenty’s algorithm, calculate travel distances and durations and verify the results with a Google Maps Task Pane, all inside your comfortable Excel interface.

This is useful for creating GoogleMap applications to find places from a list, like this.

Implements three Excel formulas:

=Geocode(request, location)

Request is the field to return:

  • status The status of the geocode request (Fetching, OK, N matches, etc.)
  • latitude The latitude of ‘location’
  • longitude The longitude of ‘location’
  • and so on: formatted_address, country political,administrative_area_level_1 political, administrative_area_level_2 political, administrative_area_level_3 political, locality political, sublocality political, route, street_number, postal_code, types, location_type, partial_match, point_of_interest, establishment, viewpointne, viewpointsw, airport establishment transit_station, bus_station transit_station, establishment, natural_feature, neighborhood political, postal_town, premise, street_address, subpremise

Location is the name or address of a place or point of interest

=GreatCircleDistance(latitude1, longitude1, latitude2, longitude2)

Calculates the Great Circle Distance using Vincenty’s Formula, with fallback to the Haversine formula when Vincenty’s method doesn’t converge.

=Travel(request, origin, destination, mode)

Calculates the distance or duration to get from origin to destination, according to Google Directions.

Request is the field to return:

  • distance The distance in metres from origin to destination
  • duration The estimated duration from origin to destination

Origin and destination are the names or addresses of the start and finish.

Mode is the mode of transport to use: Driving, Bicycling or Walking

Free download here

Enjoy!

Screenshot, click to enlarge: