MySQL 8.0.11 comes with a catalog of 5108 spatial reference system (SRS) definitions, and 479 of these are geographic. We usually just refer to them by SRID, but in this blog post we’ll dive into the details and try to understand the definition itself.

Let’s start with an SRS most people know (though they may not know that they know it): SRID 4326, the World Geodetic System from 1984. This is the SRS we use every time we refer to GPS coordinates:

Whenever any spatial function is called, every time a geometry object is constructed from a literal in the query or read from disk or memory, MySQL looks up the SRID of that geometry  to understand what the coordinates mean. The first time MySQL encounters an SRID, it finds the SRID in the SRS_ID column and reads and parses the corresponding definition from column DEFINITION. The rest of the columns are just reference information for us humans. The parsed SRS definition is cached so that subsequent lookups of the SRID are fast.

The definition string is in a format called Well-Known Text (WKT). It’s the same WKT used to define points, lines and polygons with the ST_GeomFromText function, but the SRS definition is a different part of that language. It’s a pretty long string with several levels of nested blocks. It’s easier to read if we pretty-print it:

All geographic SRS definitions start with the string “GEOGCS” (line 1), short for geographic coordinate system. Then follows a name (line 2), which is only for human reference. In general, most strings are just informational and not used by MySQL. MySQL cares about numbers, because those affect computations.

The datum (lines 3-12) is the definition of the model of the Earth. It too has a name (line 4), and then a definition of the spheroid. The terms spheroid and ellipsoid are often used interchangeably in GIS. If you read the Wikipedia definitions, you’ll see that a spheroid is a type of ellipsoid, specifically an ellipsoid of revolution. When working with Earth, we’re interested in oblate spheroids, i.e., spheroids where the north-south axis is slightly shorter than the axis through the Equator.

The spheroid also has a name (line 6), followed by the first actual numbers in the definition string: the semi-major axis (line 7) and the inverse flattening (line 8). In this case the ellipsoidal model of Earth has a semi-major axis (distance from the center to the Equator) of 6378137 meters, and the semi-minor axis (half the distance between the North and South Poles) is 1/298.257223563th shorter than the semi-major axis. If the inverse flattening is set to 0 (a theoretically invalid value), the Earth is a perfect sphere.

The authority code (line 9) says that the source of this spheroid definition is the EPSG Dataset, and the EPSG identifier for the spheroid is 7030. Similarly, the authority clause for the datum (line 11) says that this is EPSG datum 6326. You can look up these codes in the EPSG Online Registry, and you will find that the definitions there match MySQL’s definitions. You will also find that there is a coordinate transformation with EPSG code 7030, in addition to spheroid 7030. The numbers are unique only within their type of element, so these EPSG authority codes must not be mistaken for SRIDs.

The prime meridian (lines 13-17) specifies where we start counting the longitude values. Again, the prime meridian name (line 14) is ignored by MySQL. What matters is the number of angle units east of Greenwich (line 15). In this case, we’re defining the Greenwich meridian, which, of course, is 0 degrees east of Greenwich. The authority clause (line 16) says that this is EPSG meridian 8901.

The direction of the meridian measure is always postive numbers going east from Greenwich, but the unit varies with the SRS unit definition (lines 18-22). Again, ignoring the name of the unit (line 19), we get to the conversion factor to radians (line 20). Since 2π radians bring you around the whole circle and 360 degrees do the same, we have 2π/360=0.017453292519943278. This is the unit used to specify the prime meridian. It is also the unit used in latitude and longitude coordinates of geometries in this SRS. The authority code (line 21) is the code that EPSG uses for degrees of angle.

The first axis (lines 23-26) has a name (line 24) that MySQL ignores. But we humans may find comfort in seeing that other people call this latitude. The axis direction (line 25) is what tells MySQL that this is a latitude axis, with positive numbers north of the Equator. The direction only says “NORTH”, meaning positive values going north, but MySQL knows that this is a geographic SRS, so north and south are angle measures, i.e., latitude.

The second axis (lines 27-30) also has a user-friendly name (line 28) and a direction (line 29). This time the direction is east, meaning that this is longitude measured with positive numbers east of the prime meridian we saw above (lines 13-17). The order of these two axis definitions is what decides the axis order of the SRS, i.e., that this is a latitude-longitude system, not a longitude-latitude system.

The final authority code (line 31) is the authority code of the entire geographic SRS. Since this is SRID 4326 and MySQL’s catalog of SRSs is a subset of the EPSG Dataset, it’s no surprise that this is EPSG SRS number 4326. In MySQL, we can assume that EPSG codes for SRSs are the same as MySQL’s SRIDs. The EPSG Dataset Guidance Note 7-1 (Sect. 5.9) reserves two ranges of ID numbers for EPSG SRSs. MySQL has reserved the same ranges for SRIDs and intend to use them only for SRSs from the EPSG Dataset.

All authority codes are optional. We could have specified the exact same SRS without any authority codes, and no computations would change. However, the authority codes may be used in the future to identify specific elements.

Now we know how to parse geographic SRS definitions like MySQL does it. If you’re ever unsure about the axis order or the units used, you now know where to find it and how to read it!

Thank you for using MySQL !