A Little Noise

MySQL supports geometry types. The basic type is the POINT, and from that you can build linestrings and polygons and various combinations of those.

Everybody wants to store latitude and longitude, and since those are two numbers representing a point on the planet, they seem like a natural fit to store in a POINT type.

However, here there be dragons. Coordinates on a flat piece of paper are different than on a sphere. On a flat piece of paper, (-180, 0) and (180, 0) are 360 apart. On a sphere, they are the same point. How is MySQL supposed to know which to use when you ask for ST_Distance?

The answer is to specify a spatial reference identifier (SRID). A popular SRID with latitude and longitude on our planet is 4326.

MySQL 5.7 acts like everything is a flat sheet of paper, even if you specify SRID 4326. MySQL 5.7 doesn’t know what ‘latitude’ and ‘longitude’ mean, but the effect is that the longitude is the first coordinate (X), the latitude is the second coordinate (Y). Since latitude and longitude are angle measures rather than linear distances, ST_Distance only kinda works for points that are quite close to each other and far from the international date line and the poles.

SET @point180W = 0xE6100000010100000000000000008066C00000000000000000;
SET @point180E = 0xE6100000010100000000000000008066400000000000000000;

ST_AsText(@point180W)   ST_SRID(@point180W)
POINT(-180 0)           4326
ST_AsText(@point180E)   ST_SRID(@point180E)
POINT(180 0)            4326
ST_Distance(@point180W, @point180E)
360

In 8.0, MySQL learned about SRIDs. For SRID 4326, MySQL says the first coordinate (X) is latitude, the second (Y) is longitude.

ST_AsText(@point180W)   ST_SRID(@point180W)
POINT(0 -180)           4326
ST_AsText(@point180E)   ST_SRID(@point180E)
POINT(0 180)            4326
ST_Distance(@point180W, @point180E)
0

If you had points in 5.7 with SRID 0 (meaning no SRID), then they should still work the same in 8.0, like everything is a flat piece of paper.

But, if you had points in 5.7 with SRID 4326, you’ll find that they are backwards from what you expect after you upgrade to 8.0. The effect is that 5.7 seemed like it stored them in long/lat, but really the underlying storage has not changed; 5.7 just didn’t bother to check which one was supposed to be which.

If you have upgraded to 8.0 and find that your points seem to be “backwards”, you can take any of several approaches:

1. Leave it alone. 5.7 was wrong. 8.0 is now consistent with EPSG:4326. Adjust your expectations and queries accordingly.
   
2. Since 5.7 behaved as if there was no SRID for everything except the ST_SRID function, you can update your data and remove the SRID.
   Since columns in 8.0 have an SRID associated, you can’t just do:
   UPDATE table SET column = ST_SRID(column, 0);
   You’ll get ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column … Consider changing the SRID of the geometry or the SRID property of the column.
   First ALTER TABLE table MODIFY column LONGBLOB;
   Then use the above UPDATE to remove the SRID. Finally, use another ALTER TABLE to modify the column back to the appropriate geometry type with SRID 0.
   
3. 5.7 had the effect of treating lat/long as long/lat. If you populated your columns with that expectation, then you can swap them:
   UPDATE table SET column = ST_SwapXY(column);
   This will take the Statue of Liberty out of central Greenland and put it back in New York where it belongs.