3

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:

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?

8

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;
    }
0

It's Lat/Lon on 4326.

Exactly what is happening here is you are trying to get a Lat value bigger than the max posible, since this is a Spherical coordinate system.

Latitude varies from 0 to 90 degrees, think of it as referenced from the equator, which is 0 degrees, to the north it varies from [0 to 90], and to the south from [0 to -90].

Longitude varies from -180 to 180, so it changes according to how many degrees you move away fom the meridian, from [0 to 180] when you move east and [0 to -180] when you move west.

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.