This seems to be a painful lacuna in MySQL. There is a semi-accurate solution here, using
cos without any projection. This should suffice if you don't need an extremely accurate result, because the
sin/cos approach assumes perfectly spherical earth, which is slightly incorrect.
Other possible solutions:
- Move to PostgreSQL and PostGIS. This is the preferred option if you are going to use GIS data intensively in the future.
- Use a web service like this one or this one.
- Write a Python script and process the data outside the database. Note the last comment - it is important!
def distance_on_unit_sphere(lat1, long1, lat2, long2):
# Convert latitude and longitude to
# spherical coordinates in radians.
degrees_to_radians = math.pi/180.0
# phi = 90 - latitude
phi1 = (90.0 - lat1)*degrees_to_radians
phi2 = (90.0 - lat2)*degrees_to_radians
# theta = longitude
theta1 = long1*degrees_to_radians
theta2 = long2*degrees_to_radians
# Compute spherical distance from spherical coordinates.
# For two locations in spherical coordinates
# (1, theta, phi) and (1, theta, phi)
# cosine( arc length ) =
# sin phi sin phi' cos(theta-theta') + cos phi cos phi'
# distance = rho * arc length
cos = (math.sin(phi1)*math.sin(phi2)*math.cos(theta1 - theta2) +
arc = math.acos( cos )
# Remember to multiply arc by the radius of the earth
# in your favorite set of units to get length.