Migration of running MySQL 5.7 geo query to MySQL 8

Question

We are testing a migration from MySQL 5.7 to MySQL 8 and have identified some queries that no longer work and was hoping to get insight into the most appropriate changes. I am not a geo expert and am trying to just get the 5.7 query working. If this is wrong place to ask this, just let me know.

Works in 5.7

SELECT color, region_detail_type, location_id, geo_high, geo_medium, geo_low FROM region WHERE
('region_detail_type' IN ("SalesRegion")) AND 
Intersects('bounding_box', 
GeomFromText("Polygon((-106.20421 41.206596, -101.72179 41.206596, 
-101.72179 37.64364, -106.20421 37.64364, -106.20421 41.206596))", 4326)) LIMIT 4000;

Based upon the creation of MBR and ST_ methods for some of the geo methods, I created the following:

Doesn't work in 8.0

SELECT color, region_detail_type, location_id, geo_high, geo_medium, geo_low FROM region WHERE
('region_detail_type' IN ("SalesRegion")) AND ST_Intersects('bounding_box',
 ST_GeomFromText("Polygon((-106.20421 41.206596, -101.72179 41.206596,
-101.72179 37.64364, -106.20421 37.64364, -106.20421 41.206596))", 4326)) LIMIT 4000;

but this fails with [22001][3617] Data truncation: Latitude -106.204210 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

I have seen this https://dba.stackexchange.com/questions/242001/mysql-8-st-geomfromtext-giving-error-latitude-out-of-range-in-function-st-geomfr and suspect that it is relevant but am not sure. Is it? Like would swapping the values in our Polygon call work?

Is there any easy way for me to adjust this SQL call so that it works?

Answer 1

See the documentation https://dev.mysql.com/blog-archive/axis-order-in-spatial-reference-systems/. Since version 8.0 MySQL considers that in a WKT that defines a geometry in EPSG:4326 coordinate system the coordinates must appear in latitude-longitude order. Notice that many other programs consider that the axis order in WKT is poorly defined in the OGC standard and they continue to use the convention that the coordinates are always in longitude-latitude, or easting-northing order.

Easiest for you is probably to use the 'axis-order=long-lat' parameter

 ST_GeomFromText("Polygon((-106.20421 41.206596, -101.72179 41.206596,
-101.72179 37.64364, -106.20421 37.64364, -106.20421 41.206596))", 4326,'axis-order=long-lat')