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:
|
1
2
3
4
5
|
mysql> CREATE SPATIAL REFERENCE SYSTEM 1004326 -> NAME 'WGS 84 (long-lat)' -> DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lon",EAST],AXIS["Lat",NORTH]]' -> DESCRIPTION 'WGS 84 with coordinate axes swapped to be longitude-latitude instead of latitude-longitude'; Query OK, 0 rows affected (0,00 sec) |
If we query the ST_SPATIAL_REFERENCE_SYSTEMS view, we can find our SRS there:
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=1004326\G *************************** 1. row *************************** SRS_NAME: WGS 84 (long-lat) SRS_ID: 1004326 ORGANIZATION: NULL ORGANIZATION_COORDSYS_ID: NULL DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lon",EAST],AXIS["Lat",NORTH]] DESCRIPTION: WGS 84 with coordinate axes swapped to be longitude-latitude instead of latitude-longitude 1 row in set (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> SELECT ST_Distance( -> ST_GeomFromText('POINT(63.4269 10.3958)', 4326), -> ST_GeomFromText('POINT(51.5081 -0.0761)', 4326) -> ) / 1000 AS km; +-------------------+ | km | +-------------------+ | 1464.250079639081 | +-------------------+ 1 row in set (0,00 sec) mysql> SELECT ST_Distance( -> ST_GeomFromText('POINT(10.3958 63.4269)', 1004326), -> ST_GeomFromText('POINT(-0.0761 51.5081)', 1004326) -> ) / 1000 AS km; +-------------------+ | km | +-------------------+ | 1464.250079639081 | +-------------------+ 1 row in set (0,00 sec) |
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:
|
1
2
3
4
5
6
|
mysql> CREATE SPATIAL REFERENCE SYSTEM 1 -> NAME 'Don\'t do this at home, kids' -> DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; Query OK, 0 rows affected, 1 warning (0,00 sec) Warning (Code 3715): The SRID range [0, 32767] has been reserved for system use. SRSs in this range may be added, modified or removed without warning during upgrade. |
So please be nice and don’t to that. 🙂
Dropping SRSs
As expected, we can also drop SRSs we create:
|
1
2
|
mysql> DROP SPATIAL REFERENCE SYSTEM 1004326; Query OK, 0 rows affected (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> CREATE SPATIAL REFERENCE SYSTEM 2004326 -> NAME 'Copy of WGS 84' -> DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; Query OK, 0 rows affected (0,00 sec) mysql> CREATE TABLE places ( -> id INTEGER PRIMARY KEY, -> name VARCHAR(200) NOT NULL, -> position POINT SRID 2004326 NOT NULL -> ); Query OK, 0 rows affected (0,00 sec) mysql> DROP SPATIAL REFERENCE SYSTEM 2004326; ERROR 3716 (SR005): Can't modify SRID 2004326. There is at least one column depending on it. |
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:
|
1
2
3
4
5
6
|
mysql> ALTER TABLE places MODIFY COLUMN position POINT SRID 4326 NOT NULL; Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP SPATIAL REFERENCE SYSTEM 2004326; Query OK, 0 rows affected (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> CREATE SPATIAL REFERENCE SYSTEM 2004326 -> NAME 'Copy of WGS 84' -> DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; Query OK, 0 rows affected (0,00 sec) mysql> CREATE TABLE places ( -> id INTEGER PRIMARY KEY, -> name VARCHAR(200) NOT NULL, -> position POINT NOT NULL -> ); Query OK, 0 rows affected (0,00 sec) mysql> INSERT INTO places VALUES (0, 'Origin', ST_GeomFromText('POINT(0 0)', 2004326)); Query OK, 1 row affected (0,00 sec) mysql> INSERT INTO places VALUES (1, 'Trondheim', ST_GeomFromText('POINT(63.4305 10.3950)', 2004326)); Query OK, 1 row affected (0,00 sec) mysql> DROP SPATIAL REFERENCE SYSTEM 2004326; Query OK, 0 rows affected (0,00 sec) |
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:
|
1
2
|
mysql> SELECT ST_Distance(a.position, b.position) FROM places AS a, places AS b; ERROR 3548 (SR001): There's no spatial reference system with SRID 2004326. |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> SELECT ST_SRID(position), ST_AsText(position) FROM places; +-------------------+-----------------------+ | ST_SRID(position) | ST_AsText(position) | +-------------------+-----------------------+ | 2004326 | POINT(0 0) | | 2004326 | POINT(10.395 63.4305) | +-------------------+-----------------------+ 2 rows in set, 4 warnings (0,00 sec) Warning (Code 3519): There's no spatial reference system with SRID 2004326. Warning (Code 3565): There's no spatial reference system with SRID 2004326. The axis order is unknown. Warning (Code 3519): There's no spatial reference system with SRID 2004326. Warning (Code 3565): There's no spatial reference system with SRID 2004326. The axis order is unknown. |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> UPDATE places SET position = ST_SRID(position, 4326); Query OK, 2 rows affected (0,09 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT ST_Distance(a.position, b.position) FROM places AS a, places AS b; +-------------------------------------+ | ST_Distance(a.position, b.position) | +-------------------------------------+ | 0 | | 7088897.097920951 | | 7088897.097920951 | | 0 | +-------------------------------------+ 4 rows in set (0,00 sec) |
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 !