I have recently been catching up with new Python features, starting with Data Classes which were introduced in Python version 3.7. For information and code examples I have referred to:
Data Classes in Python 3.7+ (Guide) at Real Python
The link provides detailed information on the basics of data classes, leading on to more advanced features and examples, including:
I have adapted this code to create Excel UDFs that will find the distance between two locations either defined by latitude and longitude, or by city names linked to a table with latitude and longitude. A spreadsheet with examples and location data for Australian cities can be downloaded from:
The code below should be copied to a pyxll module that is loaded at start-up:
from dataclasses import dataclass
from math import asin, cos, radians, sin, sqrt
@dataclass
class Position:
name: str
lat: float = 0.0
lon: float = 0.0
def distance_to(self, other):
r = 6371 # Earth radius in kilometers
lam_1, lam_2 = radians(self.lon), radians(other.lon)
phi_1, phi_2 = radians(self.lat), radians(other.lat)
h = (sin((phi_2 - phi_1) / 2)**2
+ cos(phi_1) * cos(phi_2) * sin((lam_2 - lam_1) / 2)**2)
return 2 * r * asin(sqrt(h))
@xl_func()
@xl_arg('posn1', 'numpy_row')
@xl_arg('posn2', 'numpy_row')
def Sphere_dist(posn1, posn2):
startpnt = Position('startpnt', *posn1)
endpnt = Position('endpnt', *posn2)
return startpnt.distance_to(endpnt)
@dataclass
class AusCities:
name: str
lat: float = 0.0
lon: float = 0.0
state: str = ''
pop: int = 0
def distance_to(self, other):
r = 6371 # Earth radius in kilometers
lam_1, lam_2 = radians(self.lon), radians(other.lon)
phi_1, phi_2 = radians(self.lat), radians(other.lat)
h = (sin((phi_2 - phi_1) / 2)**2
+ cos(phi_1) * cos(phi_2) * sin((lam_2 - lam_1) / 2)**2)
return 2 * r * asin(sqrt(h))
@xl_func()
@xl_arg('cities', 'numpy_array<var>')
@xl_arg('posn1', 'str')
@xl_arg('posn2', 'str')
def City_dist(cities, posn1, posn2):
try:
i = np.where(cities[:,0] == posn1)[0][0]
except:
return "Name 1 not found"
startpnt = AusCities(cities[i,0], cities[i,1], cities[i,2], cities[i,5], cities[i,7])
try:
i = np.where(cities[:,0] == posn2)[0][0]
except:
return "Name 2 not found"
endpnt = AusCities(cities[i,0], cities[i,1], cities[i,2], cities[i,5], cities[i,7])
dist = startpnt.distance_to(endpnt)
return [[startpnt.name, startpnt.lat, startpnt.lon],[endpnt.name, endpnt.lat, endpnt.lon],
['Distance', '', dist]]
Note that the position data is defined in the order of Latitude, Longitude, which is the standard order, rather than Longitude, Latitude as used in the Real Python code.
The Sphere_dist function has input of the latitude and longitude of two points and returns the spherical distance between them.
The City_dist function has input of a table of cities with position and other data (which must be in the same format as in the spreadsheet), and two city names. It returns the names of the two cities with their latitude and longitude, and the distance between them.
The results of the two functions are approximate, because the Earth is not an exact sphere. A more accurate approximation can be found with the Vincenty method; see Spherical Geometry and Vincenty’s Formulae.
Newton Excel Bach, not (just) an Excel Blog 
I found your page when I was just a fresh graduate developing excel spreadsheets. More than a decade has passed and I am here again to seek guidance. I am so motivated to see you are still as active and passionate. Thanks a lot.
Cheers to people who work with numbers without a $ on the front.
LikeLike
😊
LikeLike