Calculating distance using MySQL

On a client project recently, we had to make it easy to filter database query results based on the distance between a user and an entity in our database. It’s easy to get overwhelmed in that context, worrying about the crazy amount of PHP calculations you’re going to have to run.

And this isn’t the only project where this is a concern; there are plenty of other sorts of apps out there that in some way, shape, or form will have to find the simple distance between point A and point B.

Here are a few other sorts of apps that might need this functionality:

• A running or biking distance tracker; you will probably need to first find the distance between point A and point B. Then between point B and point C. Then between point C and point D. (This would be something called a «linestring», but it still comes down to the simple problem of distance on the earth.)
• A restaurant delivery service might need to be able to see if you are within their maximum delivery distance.

I’m sure you can think of plenty more.

MySQL can do that!

If you need to calculate this, you can actually get surprisingly far by just using MySQL!

MySQL 5.7 introduced ST_Distance_Sphere, which is a native function to calculate the distance between two points (on Earth).

Calculating like animals

Previously, you may have had to manually implement something like the haversine formula manually to get this simple measurement. However, this method has a couple drawbacks:

• You would have to write/maintain your own procedures
• It is quite a bit slower than the new ST_Distance_Sphere function

Testing it out

So let’s use ST_Distance_Sphere to calculate the distance between the Tighten headquarters in Chicago, Illinois and the 2017 Laracon venue in New York, New York!

I plugged the addresses into Google Maps and used their «Measure distance» function to get 713.83 miles (as the crow flies)

Tip: You can easily get the coordinates for a location from the Google Maps URL, or the «what’s here» contextual menu

First, we need the coordinates of the Tighten headquarters:

lat: 41.9631174, lon: -87.6770458

Next, we need the coordinates of the Laracon venue:

lat: 40.7628267, lon: -73.9898293

All right, let’s plug these in!

Note: The arguments for the point method are longitude first, then latitude; this is a common gotcha!

select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)
)     

This gets us 1148978.6738241839, which is in meters, so let’s convert it to miles: (1 meter is 0.000621371192 miles)

select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)
) * .000621371192

This returns 713.8304301984796, which is within rounding distance to the Google Maps 713.83 miles.

Combining this feature of MySQL with browser location services, you can get simple distances without any external APIs!

Examples in Laravel

Here is an example showing the distance between two points using the Laravel Query Builder and Artisan Tinker:

And the code:

dd(\DB::select(\DB::raw('
    select ST_Distance_Sphere(
        point(:lonA, :latA),
        point(:lonB, :latB)
    ) * 0.00621371192
'), [
    'lonA' => -87.6770458,
    'latA' => 41.9631174,
    'lonB' => -73.9898293,
    'latB' => 40.7628267,
]));

And here’s a similar method for selecting results based on proximity to a given position:

// Eloquent Scope:
public function scopeCloseTo(Builder $query, $latitude, $longitude)
{
    return $query->whereRaw("
       ST_Distance_Sphere(
            point(longitude, latitude),
            point(?, ?)
        ) * .000621371192 < delivery_max_range
    ", [
        $longitude,
        $latitude,
    ]);
}
// Using the scope:
return Restaurant::closeTo($myLatitude, $myLongitude); 

Caveats and limitations

However, I would be remiss if I didn’t mention the limitations of this method:

• As you have probably already gathered, this is only «as the crow flies». If you need distance with road routing or traffic taken into consideration, this method won’t be of much help.
• These MySQL functions default to using SRID 0, which is close enough for basic use, but you will want to match your use case if high fidelity accuracy is important. More below.

What’s SRID?

SRID is basically the method of conversion from spatial coordinates to the Earth’s coordinates. By default, MySQL uses SRID 0, which represents an «infinite flat Cartesian plane with no units assigned to its axes». Google and Bing, however, use SRID 3857, which is the «Spherical Mercator projection coordinate system.» (Note: Google Earth uses SRID 4326.)

For many applications, however, ST_Distance_Sphere is more than enough to build the functionality you need.

Conclusion

If you’re working in MySQL 5.7+ and need to find distances, remember to reach for ST_Distance_Sphere first. Even if it may not always be the right fit for your code, it’s an easy—and powerful—way to get, and query against, real-world distances across the face of the Earth.