Data truncation on spatial query after updating from MySql 5.7 to 8.0

Question

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 @lats with @lons 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?

Answer 1

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 ST_ or MBR function.