Bug #101359 st_geomfromgeojson does not accept longitude of -180
Submitted: 28 Oct 2020 13:51 Modified: 28 Oct 2020 15:33
Reporter: Eric de Ruiter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[28 Oct 2020 13:51] Eric de Ruiter
Description:
Currently the st_geomfromgeojson function does not accept a longitude of -180.

How to repeat:
Execute the following query:

  SELECT st_geomfromgeojson('{"type": "MultiLineString", "coordinates": [[[170.0, 45.0], [180.0, 45.0]], [[-180.0, 45.0], [-170.0, 45.0]]]}');

This will result in the following error message:

 [22001][3616] Data truncation: Longitude -180.000000 is out of range in function st_geomfromgeojson. It must be within (-180.000000, 180.000000].

This geojson string is taken directly from the geojson rfc ( https://tools.ietf.org/html/rfc7946#section-3.1.9 )

A more real life example: the timezone boundary dataset contains some timezones with a polygon using a -180 longitude coordinate (for some of the alaska timezones): https://github.com/evansiroky/timezone-boundary-builder/releases/download/2020a/timezones....

Suggested fix:
A longitude of -180 should be accepted as the rfc for geojson references this multiple times.
[28 Oct 2020 14:07] MySQL Verification Team
Hello Eric de Ruiter,

Thank you for the report and feedback.

regards,
Umesh
[28 Oct 2020 14:13] MySQL Verification Team
Imho this is documented behavior, quoting from https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html

 For geographic SRS geometry arguments, if any argument has a longitude or latitude that is out of range, an error occurs:

    If a longitude argument is not in the range (−180, 180], an ER_LONGITUDE_OUT_OF_RANGE error occurs.

    If a latitude argument is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error occurs. 

Also - https://dev.mysql.com/worklog/task/?id=9449
[28 Oct 2020 15:33] Eric de Ruiter
Ok, but that means the st_geomfromgeojson function does not accept all valid geojson. Which is a bug imho.

The geojson spec practically mandates the use of -180 longitude (for example see section 3.1.9):

   In representing Features that cross the antimeridian,
   interoperability is improved by modifying their geometry.  Any
   geometry that crosses the antimeridian SHOULD be represented by
   cutting it in two such that neither part's representation crosses the
   antimeridian.

   For example, a line extending from 45 degrees N, 170 degrees E across
   the antimeridian to 45 degrees N, 170 degrees W should be cut in two
   and represented as a MultiLineString.

   {
       "type": "MultiLineString",
       "coordinates": [
           [
               [170.0, 45.0], [180.0, 45.0]
           ], [
               [-180.0, 45.0], [-170.0, 45.0]
           ]
       ]
   }