How to get the value causing "Incorrect arguments to st_distance_sphere" error?

Question

I have a MySQL query:

UPDATE
    `location` `l1`
SET
    `l1`.`city_id` = (
        SELECT
            `c1`.`id`
        FROM
            `city` `c1`
        ORDER BY
             ST_Distance_Sphere(`c1`.`coordinates`, `l1`.`coordinates`) ASC
        LIMIT
            1
    )

that produces an error:

Incorrect arguments to st_distance_sphere

when executed against a subset of the dataset.

How do I get the value thats causing st_distance_sphere to fail?

Answer 1

Argument of the point class is out of the range. Besides, the sequence of arguments are longitude and latitude which is different from the usual. The following is from mysql 5.7 official reference.

https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html

The geometry arguments should consist of points that specify (longitude, latitude) coordinate values:

Longitude and latitude are the first and second coordinates of the point, respectively.

Both coordinates are in degrees.

Longitude values must be in the range (-180, 180]. Positive values are east of the prime meridian.

Latitude values must be in the range [-90, 90]. Positive values are north of the equator.

Answer 2

You will get this error when either latitude or longitude value is out of range. Therefore, check for ABS(long) > 180 OR ABS(lat) > 90, e.g.

SELECT
    *
FROM
    `location`
WHERE
    ABS(ST_X(`coordinates`)) > 180 OR
    ABS(ST_Y(`coordinates`)) > 90

In my case, an entry had POINT value POINT(-0.006014 99.99999999000001), i.e. the latitude value was out of range.