MySql distance features you should know about!

There are many categories of apps out there that in some way/shape/form need to be able to find the simple distance between point A and point B.

A few examples might be:

• A running or biking distance tracker will probably need to find the distance between point A and point B. Then point B and point C. Then point C and point D. (This would be 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 come up with a few more.

So to these ends, you can actually get surprisingly far by just using MySql!

MySql 5.7 introduced st_distance_sphere which is a native function to calculate a distance between two points (on Earth).

Previously you may have had to manually use something like the haversine formula to get this simple measurement. This has a few drawbacks:

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

So let's see some code.

Let's calculate the distance between the tighten.co headquarters and the 2017 Laracon venue!

I plugged the addressed into google maps and used their "Measure distance" function to get 713.83 miles (as the crow flies)

Note: You can easily get the coordinates for a location from the google maps url!

First we need the coordinates of the tighten.co headquarters:

lat: 41.9631174, lon: -87.6770458

Next we need the coordinates of the Laracon venue:

lat: 40.7628267, lon: -73.9898293

All-right lets plug these in! (Note: The arguments to point 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 gets us 713.8304301984796 which is within rounding distance to the Google Maps 713.83 miles.

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

Here is an example using the Laravel Query Builder and Artisan Tinker:

So 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 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 to your use case.

If you are interested in diving a little deeper here is a starting point on 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)