MySQL 8.0 has many new GIS features, including a catalog of spatial reference systems (SRSs) and support for geographic (latitude-longitude) computations.
In a Cartesian SRS, it doesn’t really matter which coordinate is on which axis. The axes are orthogonal and the units are the same on both axes, so if a user consistently puts the X value in the Y coordinate and the Y value in the X coordinate, it doesn’t affect computations (in the functions MySQL currently supports).
It’s very different with geographic coordinates. If we mix up latitude and longitude coordinates, we get incorrect results. To make things worse, the GIS community has a history of using latitude-longitude order in some systems and longitude-latitude in other systems.
OpenGIS Standards
MySQL tries to be compliant with relevant standards. In the area of GIS, the most important standards are the OpenGIS specification and related standards from the Open Geospatial Consoritum (OGC), and SQL/MM — Part 3: Spatial. There should be a good reason to deviate from those standards.
In 2008, the OGC published their Axis Order Policy and Recommendation document (OGC 08-038r5). Sect. 2, Case 1 of that policy states that “Coordinats are expressed using the axis order as defined in the CRS”. (A CRS is a coordinate reference system, which is equivalent to a spatial reference system (SRS) in MySQL.)
MySQL 8.0 follows this recommendation. If the SRS is defined as latitude-longitude, MySQL treats it as latitude-longitude. If the SRS is defined as longitude-latitude, MySQL treats it as longitude-latitude.
The EPSG Dataset
MySQL’s list of SRSs is a subset of the EPSG Dataset, the de-facto standard catalog of SRS definitions. The EPSG has standardized on latitude-longitude order, so all geographic SRSs defined by default in MySQL are latitude-longitude.
Let’s examine the most common one, WGS 84 (SRID 4326), which is used for GPS coordinates:
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=4326\G *************************** 1. row *************************** SRS_NAME: WGS 84 SRS_ID: 4326 ORGANIZATION: EPSG ORGANIZATION_COORDSYS_ID: 4326 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"]] DESCRIPTION: NULL 1 row in set (0,00 sec) |
The axes are defined at the end of that definition: “AXIS["Lat",NORTH],AXIS["Lon",EAST]“. That means that the first coordinate is latitude (positive numbers going north), and the second coordinate is longitude (positive numbers going east).
What happens if we switch the axes in our query? Let’s try computing the distance between Trondheim (Norway) and London (UK). First we do it correctly, with latitude-longitude ordering of the coordinates:
|
1
2
3
4
5
6
7
|
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) |
And then we repeat it with longitude-latitude coordinates:
|
1
2
3
4
5
6
7
|
mysql> SELECT ST_Distance(ST_GeomFromText('POINT(10.3958 63.4269)', 4326), ST_GeomFromText('POINT(-0.0761 51.5081)', 4326))/1000 as km; +--------------------+ | km | +--------------------+ | 1755.6335365509924 | +--------------------+ 1 row in set (0,00 sec) |
So if we mix up the axis order, we’re almost 300 km off on this distance. Exactly how far off the results will be depends on both location and distance. Close to the equator a degree of latitude is almost the same length as a degree of longitude. But further toward the poles the difference increases. In Trondheim a degree of latitude is more than twice the length of a degree of longitude. With such differences it is obvious that swapping axes will result in incorrect results.
This case goes undetected. There’s no way for MySQL to figure out that we entered the wrong coordinates. But there is one case where MySQL may help us figure it out: MySQL will print a helpful error message if the coordinates are outside the valid range:
|
1
2
|
mysql> SELECT ST_GeomFromText('POINT(100 1)', 4326); ERROR 3617 (22S03): Latitude 100.000000 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]. |
But that only applies if the coordinate value is outside the valid range. It may catch some cases where axes are swapped, but only if the longitude (mistakenly entered as latitude) is more than 90 degrees east or west.
Workarounds
What can we do if we have data in the other axis order? There are several solutions, depending on exactly what the problem is.
When importing/exporting geometries as WKT or WKB, the functions accept a parameter to override the default axis order:
|
1
2
3
4
5
6
7
|
mysql> SELECT ST_Equals(ST_GeomFromText('POINT(10.3958 63.4269)', 4326, 'axis-order=long-lat'), ST_GeomFromText('POINT(63.4269 10.3958)', 4326)) AS equals; +--------+ | equals | +--------+ | 1 | +--------+ 1 row in set (0,00 sec) |
The axis-order option is allowed in ST_AsText, ST_AsBinary, ST_GeomFromText, ST_GeomFromWKB, and all geometry specific variants of those. The axis-order option can be “lat-long“, “long-lat” or “srid-defined” (the default).
The ST_GeomFromGeoJSON and ST_AsGeoJSON functions don’t have this option since the GeoJSON format specifies that the axis order is always longitude-latitude.
If the data is already in MySQL and in the wrong axis-order, function ST_SwapXY will swap the coordinates. This is useful if we consistently have stored data in the incorrect axis order. It can also be used as an alternative to the axis-order option to import/export functions. But I’d argue that it’s easier to read an axis-order option that explicitly specifies an axis order instead of trying to track the order through calls to ST_SwapXY.
We can also define the same SRS with longitude-latitude axis order and repeat our query in that SRS:
|
1
2
3
4
5
6
7
8
9
10
|
mysql> CREATE SPATIAL REFERENCE SYSTEM 104326 NAME 'WGS 84 N-E' 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" Query OK, 0 rows affected (0,00 sec) mysql> SELECT ST_Distance(ST_GeomFromText('POINT(10.3958 63.4269)', 104326), ST_GeomFromText('POINT(-0.0761 51.5081)', 104326))/1000 as km; +-------------------+ | km | +-------------------+ | 1464.250079639081 | +-------------------+ 1 row in set (0,00 sec) |
If know we want the data to always be in longitude-latitude order, this is one way of achieving that. However, this definition will only be valid within MySQL unless the SRS is also defined in the systems we exchange data with.
Storage Format
It’s important to note that the axis order of the SRS is only relevant to import/export. The storage format in MySQL is unchanged. The few functions that supported geographic coordinates in MySQL 5.7 stored longitude in the first coordinate and latitude in the second coordinate. MySQL 8.0 still does that. This is visible if we look at the raw storage format of
a point:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 4326)) AS storage_format; +----------------------------------------------------+ | storage_format | +----------------------------------------------------+ | E610000001010000000000000000000040000000000000F03F | +----------------------------------------------------+ 1 row in set (0,00 sec) mysql> SELECT HEX(ST_GeomFromText('POINT(2 1)', 4326, 'axis-order=long-lat')) AS storage_format; +----------------------------------------------------+ | storage_format | +----------------------------------------------------+ | E610000001010000000000000000000040000000000000F03F | +----------------------------------------------------+ 1 row in set (0,00 sec) mysql> SELECT HEX(ST_GeomFromText('POINT(2 1)', 104326)) AS storage_format; +----------------------------------------------------+ | storage_format | +----------------------------------------------------+ | 8697010001010000000000000000000040000000000000F03F | +----------------------------------------------------+ 1 row in set (0,00 sec) |
The coordinate values are in the last 16 bytes (32 hex digits) of those strings, and those bytes don’t change. So even if we swap axes, the stored coordinates are the same. The change in the beginning of the result of the third query is caused by the SRID (104326 instead of 4326). But in all three cases, the longitude (2 degrees east) is stored first, and the latitude (1 degree north) second.