Generated Column & POINT data bug?

[fergus (Fergus Caldicott)]

I have found a very odd behavior when trying to use a database generated column for a POINT data type.

Background: I have a reference data set that is periodically imported with separate fields for longitude and latitude (longitude_deg and latitude_deg, respectively). I am trying to use the Generated Column capability to set the value of a new field which will be a POINT data type representing the location of the lat/long in the other 2 fields.

To achieve this, I use the Generated Column expression:

ST_GeomFromText(CONCAT(‘POINT(’, longitude_deg, ’ ', latitude_deg, ‘)’), 4326)

When I try to save that field addition, it gives me an error “Latitude 142.193899 is out of range in function st_geomfromtext”.

I noticed that the latitude referenced in the error corresponds to the longitude of the first record in my table, suggesting that in the POINT definition of the Generated Column the latitude comes first and longitude second (contrary to standards and the POINT field reference data).

I have confirmed that behavior by hardcoding the numbers into my expression, latitude first and longitude second. When it saves the record to the database, they are in the normal order (longitude first, latitude second).

This image shows the column definition with a hard-code long/lat:

This shows the corresponding value that was saved to the records in the table (and you can see that the values have been switched to the expected long/lat.

[Inna_Shkolnaya]

Hello @fergus

Thank you for reporting this issue. The behavior you encountered is due to a change in MySQL 8.0, where the default axis order for SRID 4326 now follows the EPSG standard and expects coordinates in the format (latitude, longitude) instead of the more commonly used (longitude, latitude) order. https://dev.mysql.com/blog-archive/axis-order-in-spatial-reference-systems/?utm_source=chatgpt.com

You have two options to resolve this issue:

Option 1: Swap Latitude and Longitude in Your Expression

Since MySQL expects (latitude longitude), simply switch the order in your generated column expression:

ST_GeomFromText(CONCAT('POINT(', latitude_deg, ' ', longitude_deg, ')'), 4326)

This will ensure that the values are stored correctly.

Option 2: Use the axis-order=long-lat Parameter

If you prefer to keep the standard (longitude latitude) format, you can explicitly tell MySQL to use longitude first by adding 'axis-order=long-lat':

ST_GeomFromText(CONCAT('POINT(', longitude_deg, ' ', latitude_deg, ')'), 4326, 'axis-order=long-lat')

Let me know if this helped solve your problem.

Regards,
Inna

[fergus (Fergus Caldicott)]

Interesting. Thanks for the speedy response, Inna. I can work with that.