Calculating Length of Linestrings in WGS84 in Miles

Question

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.

score 4 · Answer 1 · answered Aug 01 '10 at 15:43

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:

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!

Code:

import math

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) + 
           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

score 1 · Answer 2 · edited Apr 13 '17 at 12:33

1

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

edited Apr 13 '17 at 12:33

Community

1

answered Dec 01 '10 at 22:09

Adam Matan

6,838
7
37
50

score 0 · Answer 3 · edited Apr 13 '17 at 12:33

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.

Calculating Length of Linestrings in WGS84 in Miles

3 Answers3

Linked