MySQL 8.0.11 comes with a catalog of 5108 spatial reference system (SRS) definitions. In a previous post, we covered the definitions of geographic SRSs. In this post we’ll go into the details of projected SRSs. (If you haven’t done so already, I suggest you read the previous post first.)

Projected SRSs are flat, Cartesian coordinate systems created by projecting points on (an oblate spheroidal model of) Earth onto a flat surface. They are flat maps. Unlike geographic systems, which deal with latitude and longitude, projected SRSs have X and Y axes in meters, survey feet or some other measure of length. Since Earth is round(ish) and maps are flat, some distortion is expected.

To get a feel for how projection may distort data, try peeling an orange (one big piece of orange peel) and flatten the orange peel on the table. Apart from the color, does it resemble an orange? I’d be amazed if it did.

But if we zoom in, some part of the orange peel has a close resemblance to some part of the orange. In the same way, a map projection may be accurate in a limited area. That’s why we have so many of them — 4628 different projected SRSs in MySQL 8.0.11. Some cover the globe (very inaccurate in some places), but most are meant to be used in a much smaller area, in which they are pretty accurate.

But if MySQL 8.0 has geography support, why do we need flat maps? There are several reasons:

• We want to display the result on a flat screen, in a map. Therefore, we also need a map definition.
• Computing in a Cartesian SRS is much faster than on an ellipsoid. If we’re operating in a small area, the accuracy may be good enough.
• Maps are easy for humans to use. Most of us understand coordinates on a flat, Cartesian plane much better than we understand the surface of an ellipsoid, which requires 3d thinking.

Projected SRS Definitions

One projected SRS we see almost daily is SRID 3857, named “WGS 84 / Pseudo-Mercator”. What is that? It’s the map we see on our screens if we look at Google Maps, Bing Maps, OpenStreetMap, or almost any web world map out there. Let’s look at the definition:

Like all WKT strings, the definition looks better in pretty-print:

Projected SRS definitions start with the string “PROJCS” (line 1) and a name (line 2), but MySQL doesn’t care about the names, only about the numbers. Then follows a geographic SRS definition (lines 3-34) for the SRS that this SRS is a projection of. To rephrase: The geographic SRS we define here is the SRS we’re projecting from when we create our projected SRS. The geographic SRS definition was covered in a previous post, so we won’t go into details here. Just note that the authority clause (line 33) of the geographic SRS is EPSG 4326, the World Geodetic System 1984. This means that our projected SRS (SRID 3857) is a projection of WGS 84 data onto a flat surface.

After the geographic SRS comes the declaration of which projection method is used to produce the map (lines 35-38). The name of the projection (line 36) is for humans. MySQL cares only about the authority code (line 37). This code is what makes MySQL recognize this as a popular visualization pseudo Mercator projection (see the table of supported projection methods below). The matching of the authority name (“EPSG”) is case-insensitive. If there is no authority clause, or the authority name or code is unknown, MySQL regards this as an unknown projection. Even if the details about the SRS aren’t known, MySQL knows that it’s a Cartesian system and some other parameters. MySQL can still do computations within the SRS, but data can’t be converted to or from the SRS.

Having an AUTHORITY subclause to the PROJECTION clause is a MySQL extension of the WKT format. The standard doesn’t allow it. But the standard also fails to specify canonical names for all the projections, so string matching is not a viable option either. The PROJECTION clause is also the only clause (where it matters) that doesn’t have an AUTHORITY clause in the standard, so it looks like an oversight by the standards committee. PostGIS has solved this by recording the actual projection type outside the definition (in their proj4 parameter string). We decided that adding an AUTHORITY clause was a better solution for us. It’s not according to the standard, but it is in the spirit of the standard.

After the projection method comes the projection parameters (lines 39-58). All parameters have a mandatory name, a mandatory value, and an optional authority clause. The authority clause refers to the parameter name/type, not the value. E.g., the latitude of natural origin (lines 39-43) would be EPSG 8801 also if the value was something else.

MySQL will verify that an SRS definition has all the required parameters for that specific projection method, and these, too, are recognized based on the authority code. However, if there is no authority code, or the authority is not “EPSG” (case insensitive matching), MySQL will try to match the parameter name with a list of known parameter names (case insensitive matching). There are different parameters for different projection methods, and it would take too long to go through all of them here. See below for summary tables of supported projection methods and mandatory parameters.

The map units are length units, not latitude and longitude. The unit specification (lines 59-63) specify exactly which unit, and the conversion factor to meters (line 61). In this case it’s 1, since 1 meter is 1 meter. But there are other SRSs that use other units, e.g., US survey foot, with a conversion factor of 0.30480060960121924.

The first axis (lines 64-67) has a name (line 65) and a direction (line 66). In this case the name is “X”, but even if it were “Y”, this would still be what MySQL understands as the X axis. Remember: Names have no meaning. Numbers do. MySQL considers the X axis to be the first axis, which is the axis that appears first in the SRS definition. Since the axis direction is east, the positive numbers on the axis are east of the origin.

The second axis (lines 68-71) has the name “Y” (line 69), and it is also what MySQL considers the Y axis. The direction (line 70) indicates that positive numbers are north of the origin.

Finally, there is the authority code of the SRS (line 72). MySQL uses the same SRIDs as the EPSG Dataset, so EPSG SRS number 3857 is MySQL SRID 3857.

Supported Projections

MySQL supports a number of projection methods. SRSs using unknown projection methods are allowed, but MySQL will not be able to check for mandatory parameters (it doesn’t know what they are), and will not be able to convert data to or from an unknown projection.

EPSG Guidance Note 7-2 has detailed explanations of how each projection works, including formulas.

Projection Parameters

MySQL recognizes projection parameters primarily by authority code. However, if there is no authority code, MySQL reverts to case-insensitive string matching on the parameter name.

For details about each parameter, look it up by code in the EPSG Online Registry.

Thank you for using MySQL !