MySQL :: Geography 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).

Projections

Projected SRSs are Cartesian planes, just like SRID 0. The math for this has been supported since spatial support was introduced in MySQL many years ago. The difference with 8.0 is that we can finally add the metadata expalining what the coordinates mean.

For example, MySQL has always allowed us to create POINT(570915 7036162) in SRID 25832, but what do the coordinates mean? Earlier versions of MySQL weren’t able to answer this question, but MySQL 8.0 can: The point is 570915 meters east of the origin of UTM band 32 and 7036162 meters north of the Equator. These are the coordinates of a point in Trondheim, Norway (OK, I admit that MySQL doesn’t really know that the city is called Trondheim, but it knows where on Earth this point is).

Geography

While supporting projected SRSs is mostly about having the metadata to explain what the coordinates mean, geographic SRSs are different. Projections are Cartesian systems, meaning that they are flat planes with orthogonal X and Y axes. Geographic SRSs are ellipsoids (“flattened spheres”) with latitude and longitude coordinates. The axes aren’t orthogonal. Instead, all the meridians meet at the North Pole and at the South Pole. The length of a degree of longitude varies from 0 to more than 111 km. Also, the coordinate system wraps around, so that -179.99999 degrees east is next to +180 degrees east.

Geographic coordinates are obviously very different from Cartesian coordinates. In addition to metadata about what the coordinates mean, we also need to change all the computations to get a meaningful result. That is exactly what we’ve done for many spatial functions in MySQL 8.0.

E.g., MySQL 5.7 and earlier can only do Cartesian computations, so the distance between POINT(0 0) and POINT(1 1) in SRID 4326 (which is a geographic SRS) would be reported as approximately 1.4 degrees, which is a meaningless measure since one degree of latitude and one degree of longitude are not the same distance. MySQL 8.0 doesn’t do this mistake:

mysql> SELECT ST_Distance(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(1 1)', 4326));
+---------------------------------------------------------------------------------------+
| ST_Distance(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(1 1)', 4326)) |
+---------------------------------------------------------------------------------------+
| 156897.79947260793 |
+---------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT ST_Distance(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(1 1)', 4326));

+---------------------------------------------------------------------------------------+

| ST_Distance(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(1 1)', 4326)) |

+---------------------------------------------------------------------------------------+

| 156897.79947260793 |

+---------------------------------------------------------------------------------------+

1 row in set (0,00 sec)

MySQL 8.0 understands that these are points in degrees of latitude and longitude on an ellipsoid and computes the distance in meters.

Functions Supporting Geographic Computations

Most spatial functions in MySQL support geographic SRSs. There are many functions, like ST_EndPoint, ST_NumPoints, etc. that don’t really do advanced geometric computations. They only get or set properties of geometries, and the result is not affected by the type of SRS. Many functions fall into this category, and they obviously support geographic data out of the box.

But there are some functions that need to do computations differently on Cartesian and geographic coordinates. In MySQL 8.0.11, the following functions support geographic computations:

There are still some functions that don’t support geographic computations, most notably set operations (ST_Union, ST_Intersection, etc.). But I hope we can add those later. To be sure, just try. If a function doesn’t support geography, MySQL will print a helpful error message:

mysql> SELECT ST_Union(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(0 1)', 4326));
ERROR 3618 (22S00): st_union(POINT, POINT) has not been implemented for geographic spatial reference systems.

1 2	mysql> SELECT ST_Union(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POINT(0 1)', 4326)); ERROR 3618 (22S00): st_union(POINT, POINT) has not been implemented for geographic spatial reference systems.

As we can see, the error is explicit about which data types are supported. That is because geography support may arrive for some data types earlier than for others. ST_Distance is one example. It supports points, but not linestrings or polygons. The goal is of course full support for all types in all functions, but it will probably arrive piece by piece.