MySQL 5.7+ has new spatial functions and indexes build in. There is a function ST_Distance_Sphere(point1, point2) which returns meters between point1 and point2 on Earth.

Point1 and point2 should be valid longitude and latitude coordinates in that order.

If you have point column where latitude is first and longitude second, then you will have to swap them: POINT(ST_Y(position), ST_X(position))

If you just have lat and lng columns, then you will have to make them points with POINT(lng, lat)

Let say I have a table of cities and I have a position column which is POINT(lng, lat) and I want to get all the cities within 100km of Riga, then MySQL query would be:

SET @p = (SELECT position FROM cities WHERE name = 'Riga');

SELECT *, ST_Distance_Sphere(position, @p) AS distance
FROM cities
HAVING distance < 100000
ORDER BY distance ASC

If you don't need to know distance and order is not important for you, for example, you just want to draw items on the map and you already know their lat/lng, then this query would be fastest:

SELECT * FROM cities WHERE ST_Distance_Sphere(position, @p) < 1000000

wow good question, there are spatial trees in MySQL, the question here should be more about the properties needed not what's better -> does it need to be consistent ? sharded ? distributions ? size ?

SQL is still the most consistent standard database we have. and I takes less memory than ES ofc..... but if you need to shard it for high traffic and you can live with eventual consistency i would take ES or i would go for MongoDB which as a nice native interface for that as well.

docs.mongodb.com/v3.2/applications/geospatial-ind…