MySQL provides a set of types and functions for handling spatial data.

These deal with the abstraction of geometry rather than geography, so there are a few additional considerations when using them to handle real world location data.

A quick refresher on the spatial geometry

By default, the data types represent a position on a 2-dimensional plane. Calculating things like distance between points a 2D plane mostly comes down to straightforward trigonometry:

However, real world location doesn't exist on a 2D plane. Real world location exists on the surface of the earth. The earth isn't really a nice geometric shape at all, but you could approximate it as a sphere or an ellipsoid. The third dimension adds complications to the maths because in order to calculate distance between points, you need to account for the curve of the earth between them.

If you need high accuracy then this gets quite difficult, but most applications don't need that much accuracy. Here we'll approximate the earth as a sphere, and leave it at that.

Getting it in and out of MySQL

The core part of MySQL that we're interested in is the POINT datatype.

There are various syntaxes for inserting POINT data, but the one we'll use is to use the ST_GeomFromText(WKT, SRID).

The first parameter, WKT, is a Well Known Text representation of geometry. We'll be using POINT(X Y) string.

The second parameter, SRID, is the Spatial Reference System Identifier. By default this is 0, which means no spatial reference system and represents a 2D plane. We're using 4326, which denotes geographical location data.

CREATE TABLE City (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64),
    `location` POINT,
    PRIMARY KEY (`id`),
    SPATIAL INDEX(`locationGeo`)
);

INSERT INTO `City`(`Name`, `location`) VALUES 
("London", ST_GeomFromText("POINT(51.5072 -0.1275)", 4326)),
("Birmingham", ST_GeomFromText("POINT(52.48 -1.9025)", 4326)),
("Manchester", ST_GeomFromText("POINT(53.4794 -2.2453)", 4326)),
("Leeds", ST_GeomFromText("POINT(53.7997 -1.5492)", 4326));

Querying

Retrieving this data comes with a caveat:

SELECT location FROM city WHERE `name` = 'London'

This gives back some junk like the following:

This is not really what we wanted. MySQL stores this field in a binary format and we're seeing its bytes encoded into text. To render this in a more friendly way we have a few options:

SELECT 
    ST_X(location) AS latitude, 
    ST_Y(location) AS longitude,
    ST_ASTEXT(location) AS `point`
FROM city WHERE `name` = 'London'

Distance

A common thing to want to do is to calculate the distance between two points. Let's calculate the distance between each pair of cities in our database.

We can do this by using the ST_Distance_Sphere() function. Note: Unfortunately, until very recently, MariaDB did not provide this function. If your version does not provide it, look at the bottom of this post for an implementation you can drop in to your database.

SELECT city.`name`, ROUND(st_distance_sphere(city.location, city2.location) / 1000) AS distance, city2.`name`
    FROM city 
    CROSS JOIN city AS city2 ON city.Id > city2.Id;

This gives the results (in kilometres):

Evaluating the exact accuracy of these data is actually quite complicated so I'm not going to do it, other than to say that they're pretty close.

ST_Distance_Sphere() for MariaDB

See the dedicated post for an implementation of ST_Distance_Sphere() in MariaDB