1

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?

10
  • can you add some data t your table, where the error occurs, t make a minimal reproducible example
    – nbk
    Jun 7 at 14:48
  • To get closer to a "square" for the bounding box, adjust: @lon - 3.0/cos(radians(@lat)), etc.
    – Rick James
    Jun 7 at 19:15
  • The test on [0,0] seems 'wrong'.
    – Rick James
    Jun 7 at 19:17
  • I test on 0,0 because some data points don't have a position, but I couldn't keep nulls in the position column or the spatial index would not work.
    – theberzi
    Jun 8 at 6:23
  • 1
    @theberzi - Yes. The longitude lines are closer together near the poles, hence "3.0" needs to be increased to account for it. Dividing by COS(lat) is a good first approximation (except very close to poles). More discussion of "find nearest": mysql.rjweb.org/doc.php/find_nearest_in_mysql
    – Rick James
    Jun 8 at 16:09
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.