Geo Distance calculation via MySQL

Translate the following text into English: We have realized many websites and web applications with integrated map display (preferably Google Maps) in recent years. Whether it's a "store finder", delivery area determination or extravagant advertising campaigns, the calculation of distances between coordinates is always an essential requirement.

In this post, we want to discuss the various options for filtering results based on a radius or calculating a distance between two points.

When it comes to calculating the radius, the first thought might be to determine the latitudes and longitudes of a point 1 km away, to then have a factor that can be added or subtracted according to the desired radius, creating a square around the target point. A query as to whether another latitude and longitude is within that square is so inaccurate due to the curvature of the Earth that it's simply not worth the effort.

Fortunately, there are some mathematical formulas that can solve this problem. One of them is known as the "Haversine Formula," and it is defined as follows:

R = Earth's radius (various sources name between 6336 and 6399 km)
dlon = B_lon - A_lon
dlat = B_lat - A_lat

distance = R * (2 * arcsin(min(1,sqrt((sin(dlat/2))^2 + cos(A_lat) * cos(B_lat) * (sin(dlon/2))^2))))

This article isn't about the mathematical explanation of the distance calculation. Those who want to dive deeper into the topic should take a look at the post in the OpenGeoDB Wiki.

In the following sections, we'd like to describe a few practical applications.

Calculation via the Google Maps API

For the simple display of distance information, client-side calculations via the browser are the first choice. For this purpose, Google Maps offers the "computeDistanceBetween" function. The following example calculates the distance between two points in meters:

const latLng1 = new google.maps.LatLng(50.7271351,7.0725076);
const latLng2 = new google.maps.LatLng(50.9392606,6.9584879);

const distance = google.maps.geometry.spherical.computeDistanceBetween(latLng1, latLng2);

To use this function, make sure that the "Geometry" library is loaded. It can be added by using the appropriate parameter "libraries=geometry" when initializing "Google Map," as seen in the following example:

<script src="https://maps.googleapis.com/maps/api/js?key=YOUR_KEY&libraries=geometry" async="" defer=""></script>

Calculation via PHP

Do you need to perform the distance calculation server-side? In this case, as PHP developers, our next thought would be to implement the "Haversine Formula" or an alternative in PHP. Fortunately, there are already several packages, such as phayes/geoPHP, which have already addressed this problem.

If you need to calculate the distance to a small selection (up to about 100 entries), then this approach is absolutely legitimate. However, if the focus is more on filtering a large data set, e.g., for radius search in a store finder with 100,000 or more entries, then this method is not recommended. The data would first have to be loaded in total from the database and then iterated with a filter function. This would result in a significantly longer loading time.

In such a case, you should rather use a database-based calculation.

Calculation via MySQL 5.7+

In newer MySQL versions, a function for distance calculation is already included: "ST_Distance_Sphere." This function allows these calculations to be made at the database level. The following MySQL query can be used to calculate the distance in meters between two latitudes and longitudes:

select ST_Distance_Sphere(
    point(50.7271351,7.0725076),
    point(50.9392606,6.9584879)
)

Example in Laravel with MySQL 5.7+

This approach can, for example, be applied in Laravel as a scope on a model:

public function scopeAppendDistanceTo(Builder $query, $latitude, $longitude): Builder
{
    return $query->selectRaw("
       *, ST_Distance_Sphere(
            point(longitude, latitude),
            point(?, ?)
        ) as distance
    ", [
        $longitude,
        $latitude,
    ]);
}

// Use the newly created scope:
return Store::appendDistanceTo($latitude, $longitude)->get(); 

Where the first "point(longitude, latitude)" directly refers to the longitude and latitude on the model.

Example in Laravel with MySQL < 5.7

If Laravel is being used with an older MySQL version, there is still a remedy. This would involve using a manual calculation. This can also be implemented as a query scope on a model:

protected function scopeAppendDistanceTo(Builder $query, $latitude, $longitude): Builder
{
    return $query->selectRaw("
        *, ROUND(
            3959 * acos( cos( radians(?) ) 
            * cos( radians( latitude ) ) 
            * cos( radians( longitude ) - radians(?) ) 
            + sin( radians(?) ) 
            * sin( radians( latitude ) ) )
        , 2) AS distance", [
            $latitude,
            $longitude,
            $latitude
        ]
    );    
}

// Use the newly created scope:
return Store::appendDistanceTo($latitude, $longitude)->get(); 

Limitations and Potential Problems

All of the approaches mentioned above have certain limitations, and we would like to discuss these:

• The distance calculated is only "as the crow flies" between two locations. Accessibility to a location may be subject to certain restrictions, so the distance by road or walking route might be significantly longer. If you also want to handle this issue, you should look at Google's Distance Matrix API.
• The methods mentioned above are not 100% accurate. The Earth is not a perfect sphere, hence distance calculation can be subject to certain fluctuations. However, this should not be an issue for most applications.

What does SRID mean?

SRID is an acronym for "Spatial Reference Identifier," a spatial reference system identification. It's used to switch from a geographic view (3D representation) of the world to a projected view (2D map representation) of the world.

The Open Geospatial Consortium (OGC), a non-governmental voluntary organization, is responsible for defining and managing the SRIDs. The members of the organization include companies like Oracle, Google, Airbus, SAP, and FedEx, 100+ universities, and many more.

In the following table, we've summarized which SRIDs are currently used by different providers:

The following MySQL Select query can be used to display the SRIDs available in MySQL:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;

Anyone who wants to know more on the subject should just search for SRIDs in relation to MySQL on the internet.

Conclusion

The calculation of distance can be very complex, especially when it comes to precise mathematical calculations. However, the approaches listed above should be more than sufficient for most applications.

Do you need help with this topic? Then feel free to contact us without any obligation.