# Calculating Length of Linestrings in WGS84 in Miles

I have a few thousand LINESTRING features in a MySQL database. I'm trying to figure out the cumulative length of all the lines -- in miles. Using the `GLength` function I can retrieve the length in (what I assume to be) degrees, but I'm not sure how to get this to miles.

I also have QGIS and ArcMap, which both return the same degree values for the lines.

From what I've read the data needs to be projected, but I'm unsure what to project it to.

I'd like to perform the calculation within MySQL, if possible.

This seems to be a painful lacuna in MySQL. There is a semi-accurate solution here, using `sin` and `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:

1. Move to PostgreSQL and PostGIS. This is the preferred option if you are going to use GIS data intensively in the future.
2. Use a web service like this one or this one.
3. Write a Python script and process the data outside the database. Note the last comment - it is important!

Code:

``````import math

def distance_on_unit_sphere(lat1, long1, lat2, long2):

# Convert latitude and longitude to

# phi = 90 - latitude

# theta = longitude

# 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) +
math.cos(phi1)*math.cos(phi2))
arc = math.acos( cos )

# Remember to multiply arc by the radius of the earth
# in your favorite set of units to get length.
return arc
``````

There's a very elegant python solution in Python, WKT, WGS-84: Calculating length in meters.

From what I can tell, MySQL doesn't support projections (though there is a provisional extension), which is the common pattern for solving problems like this one. Another similar question about computing distances between points in MySQL should give you a good lead.

The basic pattern is: load the Vincenty stored procedure (or great circle distance if data is <~10m accuracy), break down your linestrings into points using `NumPoints` and `PointN` (ref), then compute the distance for each line segment between points in your dataset.

This isn't an analysis which is well suited to SQL, you may find it easier pulling out the WKT and importing it with Shapely to perform the analysis itself.