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?