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 ST_SwapXY()
on 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 @lat
s with @lon
s in the MBRContains()
call.
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?
@lon - 3.0/cos(radians(@lat))
, etc.position
column or the spatial index would not work.