MySQL 8.0 brings a lot of new spatial features. The main feature is the support for multiple spatial reference systems (SRSs).

Spatial reference systems is not a new concept in MySQL, but 8.0 is the first version where it affects computations, and it’s the first version where users actually have to think about it. So let’s start with the beginning: What is it?

What Is a Spatial Reference System?

All geometric objects (geometries for short), whether they are points, linestrings or polygons, exist in one, and only one, spatial reference system. The same applies to collection types. All elements in a geometry collection, multipoint, etc. must be in the same SRS.

Let’s use a point as an example. A point has X and Y coordinates, but those coordinates don’t mean anything unless we know if those are the coordinates on the same surface. E.g., POINT(1 2) on the soccer field outside my window is not the same as POINT(1 2) on a page in my notebook. The soccer field and my notebook are two distinct SRSs, and the two points aren’t the same even though both X and Y coordinates are equal. To be the same, they also have to be in the same SRS.

Each SRS is assigned an identifier, the spatial reference system identifier (SRID). For the two points to match, not only must X and Y coordinates match, but the SRIDs of the two points must also be the same. In fact, MySQL and other DBMSs will refuse to do any sort of computation unless all parameters are in the same SRS, i.e., unless they have the same SRID.

The SRID is part of the geometry value. E.g., a point with X and Y coordinates actually consists of three values: The X value, the Y value and the SRID value.

To find the SRID of a geometry, we can use the ST_SRID function:

We can also set the SRID of a geometry using ST_SRID(geometry, new srid):

It’s important to note that this only sets the SRID of the geometry. It doesn’t transform any coordinates.

We can also specify the SRID when creating the geometry:

By default, if we don’t specify an SRID, MySQL will create geometries in SRID 0. SRID 0 is MySQL’s notion of an abstract, unitless, infinite, Catesian plane. While all other SRSs refer to some surface and defines units for the axes, SRID 0 does not.

SRSs Supported by MySQL

SRID 0 is the default, but which other SRIDs can we use? MySQL 8.0 comes with the definitions of more than 5000 SRSs. These can be found in the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS view.

Let’s look more closely at a couple of them:

The first two columns are the unique name  and the SRID. The next two columns explain that both these SRS definitions are originally from the European Petroleum Survey Group (EPSG), where they have the same SRIDs as in MySQL. The EPSG Dataset is the de facto standard catalog of SRS definitions, and MySQL 8.0.11 contains all the 2d SRS definitions from the EPSG Dataset version 9.2. The description is empty for both these SRss.

The details of the definition string is a huge topic, far too big to cover here, so I’ll skip the details for now. I just want to point out a few things:

The definition of SRID 4326 starts with the string “GEOGCS”. That means that it is a geographic SRS. It represents the surface of the earth as an ellipsoid (a sphere with a slightly shortened north-south axis). The axes in geographic systems are latitude and longitude in some angle measure, which in this case is degrees. The axes are not orthogonal. All meridians meet at the North Pole and the South Pole, so they are clearly not parallel.

The definition of SRID 3857 starts with the string “PROJCS”, which means that it is a projection of the Earth’s surface to a flat plane with orthogonal X and Y axes in some length unit. This is a map. If we look closely at the string, we’ll also find the definition of SRID 4326
inside it. That means that this is a map projection from the SRID 4326 ellipsoid to a flat plane.

So what are these SRSs? These are two of the most used SRSs. SRID 4326 is GPS coordinates. SRID 3857 is the web map projection that you see on Google Maps, OpenStreetMap, and most other web maps. It’s not used for paper maps, though. Paper maps will probably be in one of the 4627 other projected SRSs defined in MySQL. Which one depends on which area of the globe the map covers.

SRID 0 is also listed, but there’s not much to read out of it:

The reason for the empty definition is that it simply can’t be represented by the definition string format. All definitions have to specify either a geographic system or a projection. SRID 0 is neither of those. SRID 0 is the only exception to this rule.

How Is It Used?

MySQL relies on the SRS definition for a number of things. The most important is to differentiate between Cartesian systems (SRID 0 or a projected SRS) and geographic systems. If it is a geographic system, the shape of the (model of the) Earth also affects computations. As a simple example, we can compute the distance between POINT(0 0) and POINT(1 1), first in SRID 0:

We can repeat the experiment in SRID 3857, our web map projection:

The answer is the same. That shouldn’t surprise us. Both systems are Cartesian, and the distance computed the exact same way. But we shouldn’t assume that the result mean the same. In SRID 3857, the units are meters, so this is the distance from the origin (in the ocean south of Accra in Ghana) to 1 meter north and 1 meter east of that point. The result is also in meters. But in SRID 0, the axis units are undefined, and so is the result unit. For all MySQL knows, it may not even be on Earth — it may be on a circuit board or a pool table.

And in our geographic SRS:

Again, the distance is measured from the origin, south of Ghana. But the coordinates are no longer in meters. SRID 4326 is in degrees, so this is the distance from the origin to a point 1 degree north and 1 degree east. However, the result is in meters, and these points are 156 km apart.