5

I built a wep app storing GPS coordinates, as SRID 4326. Coordinates are stored as Lon/Lat, and that was working fine on MySQL 5.6 and MySQL 5.7.

Since I upgraded to MySQL 8, I get the following error when trying to construct such a geometry:

SELECT ST_GeomFromText('POINT(-118 0)', 4326);

Latitude -118.000000 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

I checked the MySQL documentation on SRS, which shows this entry for SRID 4326:

DEFINITION: ...
            AXIS["Lat",NORTH],AXIS["Long",EAST]

Confirming that they assume it's Lat/Lon.

I've always seen SRID 4326 referenced as Lon/Lat before:

  • spatialreference.org shows longitude first in bounds
  • this answer on gis.stackexchange says "The coordinates in (EPSG) 4326 are long/lat", this post on postgis.net says the same
  • ... and many more answers here and there on StackExchange and other sites

However, I just discovered the epsg.io site, that may be the canonical reference on the subject (?) that states that latitude comes first:

Axes: latitude, longitude.

So, which one should I trust? Is it Lat/Lon or Lon/Lat?

6
12

In principle, it should always be lat/lon as that is what the current EPSG database defines it as. Unfortunately, over the years computer scientists have visited and made a decision to use lon/lat as that works for their high school maths mapping to X,Y and is easy.

So whenever you receive a file of coordinates in EPSG:4326 you need to check who sent them to you, if either of the columns exceeds 90 or plot them on a map and see if they are in the right place. If you are using someone else's code you should look inside the code and see what they are doing. Ideally you will see something like:

double ulLon, ulLat;
// Let's get upper-left corner coords
CRS.AxisOrder aorder = CRS.getAxisOrder(reqExtentInTileCrs.getCoordinateReferenceSystem());
switch (aorder) {
    case EAST_NORTH:
        ulLon = reqExtentInTileCrs.getMinX();
        ulLat = reqExtentInTileCrs.getMaxY();
        break;
    case NORTH_EAST:
        if (LOGGER.isLoggable(Level.FINE)) LOGGER.log(Level.FINE, "Inverted tile coords!");
        ulLon = reqExtentInTileCrs.getMinY();
        ulLat = reqExtentInTileCrs.getMaxX();
        break;
    default:
        LOGGER.log(Level.WARNING, "unexpected axis order " + aorder);
        return ret;
}
1
1

The format on EPSG:4326 is Lat/Lon, contrary to the format we regularly see in other systems: Lon/Lat.

The error arises by having a Lat value bigger than the maximum possible.

Latitude ranges from -90 to 90 degrees, where the equator would be located at 0 degrees, the North Pole at 90 degrees, and the South Pole at -90 degrees.

Longitude ranges from -180 to 180 degrees, where the meridian would be located at 0 degrees, the Longitude would increase from 0 to 180 degrees while moving towards the East, and decrease from 0 to -180 degrees while moving towards the West

In this sense, your example should be as this:

DECLARE @Lat float = [0]; -- [-90 to 90]         
DECLARE @Lon float = [118]; -- [-180 to 180]         
SELECT ST_GeomFromText('POINT(Lat Lon)', 4326);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.