All we need is an easy explanation of the problem, so here it is.
I have the following table:
create table places ( id bigint auto_increment primary key, position point SRID 4326 not null ); create spatial index position on places (position); -- Some test data insert into places (position) values (st_srid(POINT(40.67 -73.94))), (st_srid(POINT(38.895 -77.036666666))), (st_srid(POINT(37.7775 -122.416388888))), (st_srid(POINT(52.516666666 13.383333333))), (st_srid(POINT(34.05223 -118.24368))), (st_srid(POINT(46.94798 7.44743))), (st_srid(POINT(46.2 6.15))), (st_srid(POINT(47.378611111 8.54))), (st_srid(POINT(47.560555555 7.590555555)));
on a MySql 8.0 database. The data is imported from a 5.7 database (the SRID is set to 4326 on every row of the
position column of the 5.7 database as well).
I’m aware that from 5.7 to 8.0, X and Y on geometry objects for SRID 4326 got swapped, so that points are now "latitude, longitude", so when I imported the data I applied
position. (Note that I also tried the query below on an identical table where I had not performed this step.)
When I try to run a query similar to the following:
SET @lon = 7.4653; SET @lat = 51.5136; SELECT p.id, ST_Distance_Sphere(p.position, ST_SRID(POINT(@lat, @lon), 4326)) AS distance, ST_AsText(p.position) FROM ( SELECT id, position FROM places WHERE MBRContains(ST_GeomFromText(CONCAT('Polygon((', @lat -3.0, ' ', @lon -3.0, ', ', @lat +3.0, ' ', @lon -3.0, ', ', @lat +3.0, ' ', @lon +3.0, ', ', @lat -3.0, ' ', @lon +3.0, ', ', @lat -3.0, ' ', @lon -3.0, '))'), 4326), position) OR position = ST_SRID(POINT(0.0, 0.0), 4326) LIMIT 10000 ) p ORDER BY distance LIMIT 10;
I get the following error:
 Data truncation: A parameter of function mbrcontains contains a geometry with latitude -122.416389, which is out of range. It must be within [-90.000000, 90.000000].
This also happens if I swap all
@lons in the
Have I missed some other step I should have taken when importing the data? Is there something I’ m not considering? How do I make the query work?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I have found out the problem.
When MySQL 8.0 inverted latitude and longitude, it didn’t invert the structure of the geometry itself, but only the representation in spatial functions.
I suppose this information is available somewhere in the documentation, but I personally wasn’t able to find anything that clearly states that the change from 5.7 to 8.0 only affected spatial functions and the textual representation of points, and not points themselves. Great job, Oracle. :/
So in short the solution is to simply use a table that does not swap x and y, and instead swap x and y everywhere in the query.
EDIT: furthermore, the
distance column of the query yields incorrect results using the order
POINT(@lat, @lon). It yields correct results by using
POINT(@lon, @lat), which must mean the inversion of latitude and longitude must not be taken into account when defining points, unless the definition passes through a
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂