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);
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:
(22001)(3732) 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?