Sorting by distance in MySQL with spatial analysis functions and data types

January 29, 2018, at 08:48 AM

I'm building a php web app with Laravel 5.5 and I need to display a list of places (eg. stores) sorted by their distance from a user-specified location. The places will be stored in a MySQL database and should be retrieved as Eloquent ORM model instances.

Doing some research I found many posts and questions on this topic (presenting different solutions), but, having very little experience with databases and geolocation/geospatial analysis, they mostly confused me, and I'd like to know what approach to follow and what are the best practices in this case.

Most answers I read suggest using the haversine formula or the spherical law of cosines in the SQL query, which would look something like (example taken from this answer):

$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

This post points out the fact that, over short distances, assuming the Earth flat and computing a simple euclidean distance is a good approximation and is faster than using the haversine formula.
Since I only need to sort places within a single city at a time, this seems to be a good solution.

However, most of these posts and SO answers however are a few years old and I was wondering if there is now (MySQL 5.7) a better solution.

For example, none of those post use any of MySQL “Spatial Analysis Functions”, like ST_Distance_Sphere and ST_Distance which seem to be exactly for that purpose.
Is there any reason (eg. performance, precision) not to use these functions instead of writing the formula in the query? (I don't know which algorithm is internally used for these functions)

I also don't know how I should store the coordinates of each place. Most of the examples I've seen assume the coordinates to be stored in separate lat, lon columns as doubles or as FLOAT(10,6) (as in this example by google), but also MySQL POINT data type seems appropriate for storing geographic coordinates.
What are the pros and cons of these two approaches?

How can indexes be used to speed up these kind of queries? For example I've read about “spatial indexes”, but I think they can only be used for limiting the results with something like MBRContains(), not to actually order the results by distance.

So, how should I store the coordinates of places and how should I query them to be ordered by distance?

Rent Charter Buses Company
MySQL table update is very slow - Please help how I can optimize this query

MySQL table update is very slow - Please help how I can optimize this query

A simple update of one column using primary key in the where clause takes 08 seconds to 1

Do i use Unique Index with additional Column or Index with less Column?,

Do i use Unique Index with additional Column or Index with less Column?,

For a Table that i'll just SELECT rows from it,

MySQL CSV export ID from second table

MySQL CSV export ID from second table

I have two tables, the first table is PRODUCTS with fields NAME, BUY, SELL, CATEGORY, In the second table CATEGORIES with fields NAME and ID

Query Debugging.

Query Debugging.

I have two tables i am using two quires to fetch data from them separately