Creating Your Own Spatial Reference Systems in MySQL 8.0

MySQL 8.0.11 comes with a catalog of 5108 spatial reference systems (SRSs). 4628 projections (flat maps), 479 geographic (ellipsoidal) representations of Earth, and one Cartesian all-purpose abstract plane (SRID 0). And if for some reason that isn’t enough, we can create our own.

Creating (and dropping) SRSs currently requires the SUPER privilege (this may be split into a separate privilege later). SRSs are created with CREATE SPATIAL REFERENCE SYSTEM statements. The syntax is simple:

CREATE SPATIAL REFERENCE SYSTEM srid
NAME 'a unique name'
DEFINITION 'the definition';

The SRID is the numeric identifier MySQL will use for this SRS. The name is only for human reference, but MySQL will make sure it is always unique. The definition is where all the details are hidden. Earlier blog posts have covered geographic and projected SRS definitions, so I won’t repeat them here. Go back to those posts for all the details.

In addition, there are two optional clauses: DESCRIPTION 'description text' specifies a string with a textual description of the SRS, and IDENTIFIED BY number ORGANIZATION 'organization name' specifies the organization that is the source of this SRS definition and the organization’s SRID for this SRS (which doesn’t have to match MySQL’s SRID). Both optional clauses are purely informational. MySQL does not use them. However, because the SQL/MM standard specifies it, MySQL enforces that the pair of organization name and organization specific SRID is unique.

All SRS definitions that are installed by default specify that coordinates are latitude first longitude second. As an example of a new SRS, let’s create a longitude-latitude version of WGS 84 (SRID 4326). All we have to do is swap the order of the two AXIS clauses in the definition:

If we query the ST_SPATIAL_REFERENCE_SYSTEMS view, we can find our SRS there:

This SRS behaves just like SRID 4326, but with the difference that the first coordinate in SRID 1004326 is longitude and the second is latitude. We can easily verify that by computing the distance between Trondheim (Norway) and London (UK) in the two SRSs. If the SRSs are the same apart from axis order, we should get the same distance in both SRSs:

The distance is indeed the same.

There’s no substantial difference between the SRS definitions that are installed by default and user defined SRSs. MySQL treats them exactly the same, and there is no performance penalty when using a user defined SRS.

Choosing an SRID

Which SRID should you choose for your home-made SRS? The SRID is an unsigned 32 bit integer, so we have 4294967296 possibilities to start with. But MySQL will be extended with more SRSs in the future, and you should pick an SRID that doesn’t come into conflict with any new system defined SRSs. Currently, MySQL reserves the following SRID ranges:

  • [0, 32767]
  • [60000000, 69999999]
  • [2000000000,  2147483647]

We are allowed to create SRSs in those ranges. After all, we have the SUPER privilege. But MySQL warns us that this is not a good idea:

So please be nice and don’t to that. 🙂

Dropping SRSs

As expected, we can also drop SRSs we create:

And it’s gone! We can also drop SRSs in the reserved regions, including those that are installed by default, but, again, please heed the warning and don’t do that. It’s not nice.

But we can’t always drop SRSs, even if we are SUPER. Consider this example:

Since MySQL needs the SRS definition for most spatial operations, it keeps track of the SRIDs mentioned in column restrictions and stops us if we try to drop an SRS that is in use. In this case it’s just a column that is restricted to the SRID, but there could also be indexes on this column. If the SRS were to disappear while there exists an index using it, MySQL wouldn’t know how to search or update the index. Therefore, we’re not allowed to remove SRSs that are in use.

In this case, we have no data in the table, so we can simply remove or modify the SRID restriction to point to a different SRID:

Note that this protection of SRSs only applies to SRIDs mentioned in table definitions. MySQL does not keep track of the SRIDs used in the values themselves. In this example the position column isn’t restricted to any SRID, but the new SRID is used in point values in that column:

We’re now in the situation that the SRID used by two of our geometries is currently undefined. MySQL will complain loudly about that. E.g., it won’t allow us to do any computations:

Why? Because the computation depends on the SRS definition. MySQL doesn’t know anything about this SRS, if it is geographic or projected, what the shape of the ellipsoid is, or which unit is used. But we’re not all lost. We can still look at the coordinates:

We have to suffer some warnings, but we can’t blame anyone else for that. It’s our own fault for dropping an SRS that was in use. How do we get out of this situation? We can either recreate our SRS, or we can cast our data to a different (existing) SRID. In this case we know that SRID 2004326 was actually the same as SRID 4326, so we can change our data to that SRID:

That concludes our tour of CREATE and DROP SPATIAL REFERENCE SYSTEM. Try it out, have fun, and please stay outside of the reserved SRID ranges!

Thank you for using MySQL !

About Norvald H. Ryeng

Norvald has been working as a software engineer on the MySQL Optimizer Team since 2011, where he mostly works on GIS. He is also the point of contact for package maintainers in Linux distributions. He holds a PhD in Computer and Information Science from the Norwegian University of Science and Technology.

Leave a Reply