X

Learn MySQL with the Curriculum Team

Calculating Distances with MySQL's Spatial Data Extensions

Mark Lewin
MySQL Curriculum Developer

I used to work for a company specializing in Geographical Information Systems, and I still get excited about using computers to interact with geospatial data. Once a field that interested only geography geeks, GIS has now become mainstream, with just about every mobile app offering some kind of location awareness.

Whereas many such applications use some kind of third-party API to work with spatial data, with MySQL you can do this right in the database itself. MySQL provides a number of spatial data types which let you create and store simple geometric shapes like points, lines, and polygons that represent features like cities, roads, and countries respectively, together with their location in the real world.

Once you have created these geographical features in the database, you can use MySQL to understand the relationship between them. For example, many applications provide a "find my nearest..." capability, to locate things you are interested in, like coffee shops, gas stations, or furry conventions (hey, I don't judge!)

If you want to find the distance between your current location and your point of interest then Pythagoras has you covered. He defined a formula for calculating the distance between coordinates way back in the 5th century BC, and it looks like this:

distance = sqrt((X2 - X1)^2 + (Y2 - Y1)^2)

Which is great if you only ever want to calculate short distances, but fails to take into account the curvature of the earth which would really skew the results for longer ones. What you need then is the Haversine formula which was first used in the 19th century, to calculate distances for ocean-going vessels.

Here's what the Haversine formula looks like when implemented as a Java class:


public class Haversine {
    public static final double R = 6372.8; // In kilometers
    public static double haversine(double lat1, double lon1, double lat2, double lon2) {
        double dLat = Math.toRadians(lat2 - lat1);
        double dLon = Math.toRadians(lon2 - lon1);
        lat1 = Math.toRadians(lat1);
        lat2 = Math.toRadians(lat2);
 
        double a = Math.pow(Math.sin(dLat / 2),2) + Math.pow(Math.sin(dLon / 2),2) * Math.cos(lat1) * Math.cos(lat2);
        double c = 2 * Math.asin(Math.sqrt(a));
        return R * c;
    }
    public static void main(String[] args) {
        System.out.println(haversine(36.12, -86.67, 33.94, -118.40));
    }
}

The important thing to note here is the variable R, which corresponds to the approximate radius of the earth, in kilometres.

If your trigonometry is as basic as mine, you will appreciate the fact that MySQL wraps all this up for you in a single function called ST_Distance_Sphere:

ST_Distance_Sphere(pt1, pt2 [,radius])

You call this function with the two points pt1 and pt2, that you want to calculate the distance between. The radius parameter is optional, and if omitted, uses the value of 6,370,986 meters, being a reasonable approximation of the radius of the earth. (I see this optional extra parameter as being a great bit of future-proofing by MySQL, for when we are living on a terraformed Mars.)

Let's use ST_Distance_Sphere to calculate the spherical distance between London and San Francisco.

First, we need to create MySQL variables of POINT type to store the longitude/latitude coordinates of our two cities, in decimal degrees. MySQL has a convenience function called ST_GeomFromText for creating spatial types from what is termed Well-Known Text, a basic markup language for representing simple map geometries.

mysql> SET @london = ST_GeomFromText('POINT(0.13 51.5)');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sanfrancisco = ST_GeomFromText('POINT(122.4 37.8)');
Query OK, 0 rows affected (0.00 sec)

Then, we can call ST_Distance_Sphere on those two points:

mysql> SELECT ST_Distance_Sphere(@london, @sanfrancisco);
+--------------------------------------------+
| ST_Distance_Sphere(@london, @sanfrancisco) |
+--------------------------------------------+
|                          8613637.557000635 |
+--------------------------------------------+
1 row in set (0.00 sec)

The result is in meters, so the approximate distance is 8,613 kilometers.

Distance between London and San Francisco, following earth surface curvature

This is just a very simple example of the sort of functionality that is available to you once you take advantage of MySQL's data types. Find out more about MySQL's spatial data support in the MySQL Reference Manual.

Join the discussion

Comments ( 1 )
  • Mark Mogridge Thursday, March 29, 2018
    This is great stuff Mark L. - thanks! If you want to see a real surprise, calculate the actual distance from Europe to South Africa, then you'll really see the curvature of the earth!
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.