MySQL Bugs: #92349: Storage Format is wrong regarding POINT data with SRID=2443-2461,6669-6687,30161

Bug #92349	Storage Format is wrong regarding POINT data with SRID=2443-2461,6669-6687,30161
Submitted:	10 Sep 2018 9:22	Modified:	25 Sep 2018 7:55
Reporter:	Yoshiaki Yamasaki	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In my understanding, storage format of POINT data is longitude in the first coordinate and latitude in the second coordinate. However, storage format is wrong using SRID=2443-2461,6669-6687,30161-30179.

It seems like MySQL don't care coordinate order in PROJCS. However, when MySQL develop some spatial function like ST_Azimuth in PostGIS, it makes serious problem.

 ST_Azimuth
 https://postgis.net/docs/ST_Azimuth.html

Therefore, could you fix this problem?

How to repeat:
Following test case has 2 important points.

 1. 'axis-order=long-lat' option ignored.
    => When I use other SRID(ex:4326), 'axis-order=long-lat' option works well.

 2. Storage format is wrong.
    => Coordinate order of SRID=2443 is latitude in the first coordinate and longitude in the second coordinate. 

       * SRID=2443-2461 and 6669-6687 and 30161-30179 are same order.

Note:
 - SRID=2443-2461 and 6669-6687 and 30161-30179 are "Japan Plane Rectangular CS".
 - "Japan Plane Rectangular CS" is very important in Japan. If MySQL can't treat "Japan Plane Rectangular CS" correctly, it is very difficult for MySQL GIS feature to spread in Japan.

******************************************************************
[Test Case]
- 'axis-order=long-lat' option ignored with SRID=2443(PROJCS)
- Storage format is wrong.

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 2443)) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| 8B0900000101000000000000000000F03F0000000000000040 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 2443, 'axis-order=long-lat')) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| 8B0900000101000000000000000000F03F0000000000000040 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 2443, 'axis-order=lat-long')) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| 8B0900000101000000000000000000F03F0000000000000040 |
+----------------------------------------------------+
1 row in set (0.00 sec)

******************************************************************
[Other Test Case]
- 'axis-order=long-lat' option works well with SRID=4326(GEOGCS)

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 4326)) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| E610000001010000000000000000000040000000000000F03F |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 4326, 'axis-order=long-lat')) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| E61000000101000000000000000000F03F0000000000000040 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_GeomFromText('POINT(1 2)', 4326, 'axis-order=lat-long')) AS storage_format;
+----------------------------------------------------+
| storage_format                                     |
+----------------------------------------------------+
| E610000001010000000000000000000040000000000000F03F |
+----------------------------------------------------+
1 row in set (0.00 sec)

******************************************************************

Suggested fix:
- 'axis-order=long-lat' option works with PROJCS
- Storage format reconciles with OpenGIS Standards(regarding PROJCS).

I'm sorry above comment has following typographical error.

  - Before: 'axis-order=long-lat' option ignored.
  - After: 'axis-order' option ignored.

Hello Yoshiaki-San,

Thank you for the report and feedback!
Observed with 8.0.12.

Thanks,
Umesh

If MySQL don't care coordinate order in PROJCS, MySQL cause following problem also.

[Problem]
Reverse axis-order of stored value.

[Example]
mysql> SELECT SRS_NAME, SRS_ID, RIGHT(DEFINITION, 61) FROM information_schema.st_spatial_reference_systems WHERE SRS_ID=2443 AND DEFINITION LIKE 'PROJCS%';
+----------------------------------------+--------+---------------------------------------------------------------+
| SRS_NAME                               | SRS_ID | RIGHT(DEFINITION, 61)                                         |
+----------------------------------------+--------+---------------------------------------------------------------+
| JGD2000 / Japan Plane Rectangular CS I |   2443 | 1"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","2443"]] |
+----------------------------------------+--------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT ST_AsGeoJson(ST_GeomFromText('POINT(1 2)', 2443), 0, 2);
+------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJson(ST_GeomFromText('POINT(1 2)', 2443), 0, 2)                                                    |
+------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "EPSG:2443"}}, "type": "Point", "coordinates": [1.0, 2.0]} |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Note]
- Axis order of SRID=2443 is NORTH, EAST. We can check it via st_spatial_reference_systems.

- GeoJSON format specifies that the axis order is always longitude-latitude.

  * Axis Order in Spatial Reference Systems
    https://mysqlserverteam.com/axis-order-in-spatial-reference-systems/
      => "The ST_GeomFromGeoJSON and ST_AsGeoJSON functions don’t have this option since the GeoJSON format specifies that the axis order is always longitude-latitude."

  * The GeoJSON Format
    https://tools.ietf.org/html/rfc7946
      => "Point coordinates are in x, y order (easting, northing for projected coordinates, longitude, and latitude for geographic coordinates):"

Posted by developer:

Hi,

I don't understand what you think is the problem here. The storage format is longitude-latitude for geographic (i.e., ellipsoidal longitude-latitude) data, and X-Y for all other SRSs. SRIDs 2443-2461, 6669-6687, and 30161-30179 are all projections, so the axes are in length units (meters), not in degrees longitude and latitude. Hence, the storage is in X-Y order.

OpenGIS and SQL/MM standards mention only X and Y axes, not longitude and latitude. Since there historically have been several interpretations (long-lat or lat-long), we have added the axis-order option to specify it explicitly. But it doesn't apply to the case where X and Y are clearly defined, as they are in Cartesian systems. It only applies to geographic data. Neither of the SRIDs mentioned in the report are for geographic SRSs, so the option doesn't take effect for these values.

When it comes to ST_GeomFromGeoJSON and ST_GeoJSON, it's a bit tougher. The GeoJSON format is only for geographic data. The latest version even specifies that it should always be WGS 84 (SRID 4326). Earlier versions specified options for other SRSs, but that has been removed in later versions. MySQL currently allows you to set the SRID on import/export, but will make no attempt at converting or transforming coordinate values. To really comply with the later GeoJSON specifications, MySQL should probably refuse to import/export anything else than SRID 4326. But that is a change in behavior that we'll have to introduce gently.

I understansd following your points.
 - "axis-order" option works only for geographic data. It doesn't work for projection data.
 - The GeoJSON format is only for geographic data. It is not for projection data.(*)
   * The latest version of GeoJSON only support WGS 84 (SRID 4326).

However, I still have concerns. Therefore, please let me confirm following things.

 1. Do you recognize that the definition of the order of X and Y in the projected coordinate system depends on the coordinate system?

 2. In the text below you are expressing "currently supports", but do you realize that there may be problems in the future if the order of X and Y is different?

https://mysqlserverteam.com/axis-order-in-spatial-reference-systems/
> In a Cartesian SRS, it doesn’t really matter which coordinate is on which axis. The axes are orthogonal and the units are the same on both axes, so if a user consistently puts the X value in the Y coordinate and the Y value in the X coordinate, it doesn’t affect computations (in the functions MySQL currently supports).

[Note for No.1]
> 1. Do you recognize that the definition of the order of X and Y in the projected coordinate system depends on the coordinate system?

Axis order of world's popular projected coordinate systems(ex. UTM(32601-32660), Mercator(3395), Web Mercator(3857), etc) is east-north, but Axis order of Japanese popular projected coordinate systems(ex. Japan Plane Rectangular(6669-6687) ) is north-east.

Japan Plane Rectangular is effective only in Japan, but it is very popular in Japan. Therefore, on the premise that all projection coordinate systems are east-north, there is a fear that it will not be used only in Japan, and it is strongly concerned.

mysql> SELECT SRS_NAME, SRS_ID, RIGHT(DEFINITION, 61) FROM information_schema.st_spatial_reference_systems WHERE SRS_ID=6669 OR SRS_ID=3395;
+----------------------------------------+--------+---------------------------------------------------------------+
| SRS_NAME                               | SRS_ID | RIGHT(DEFINITION, 61)                                         |
+----------------------------------------+--------+---------------------------------------------------------------+
| WGS 84 / World Mercator                |   3395 | 1"]],AXIS["E",EAST],AXIS["N",NORTH],AUTHORITY["EPSG","3395"]] |
| JGD2011 / Japan Plane Rectangular CS I |   6669 | 1"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","6669"]] |
+----------------------------------------+--------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

[Note for No.2]
> 2. In the text below you are expressing "currently supports", but do you realize that there may be problems in the future if the order of X and Y is different?

For example, when implementing a function like ST_Azimuth, there is a difference between the formula for easting-northing and the formula for northing-easting. I am worried that this will also be an important issue.

ST_Azimuth
https://postgis.net/docs/ST_Azimuth.html

I also think there is a problem.  I don't know where it's happening, but the order or coordinates is getting reversed for me in the following case.

Longitude: -77.016389
Latitude:  38.904722

Should correspond to a point in Washington DC.

Corresponding geojson:

{
  "type": "Feature",
  "geometry": {
    "coordinates": [
      -77.016389,
      38.904722
    ],
    "type": "Point"
  },
  "properties": {
    "description": "Washington DC coordinates, from wikipedia.org"
  }
}

WKT should be: POINT (-77.016389 38.904722)

However, the following query results in reversed order for the coordinates in WKT:

SELECT ST_AsText(ST_GeomFromGeoJSON('{
    '>   \"type\": \"Feature\",
    '>   \"geometry\": {
    '>     \"coordinates\": [
    '>       -77.016389,
    '>       38.904722
    '>     ],
    '>     \"type\": \"Point\"
    '>   },
    '>   \"properties\": {
    '>     \"description\": \"Washington DC coordinates, from wikipedia.org\"
    '>   }
    '> }'));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON('{
  \"type\": \"Feature\",
  \"geometry\": {
    \"coordinates\": [
      -77.016389,
      38.904722
    ],
    \"type\": \"Point\"
  },
  \"properties\": {
    \"description\": \"Washington DC coordinates, from wikipedia.or |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POINT(38.904722 -77.016389)                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Maybe the issue is in the ST_GeomFromGeoJSON function?

I want to point out that I'm even getting different results from the example at https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html

Actual result:

mysql> SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
mysql> SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
+--------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@json)) |
+--------------------------------------+
| POINT(0 102)                         |
+--------------------------------------+

Expected result:

mysql> SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
+--------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@json)) |
+--------------------------------------+
| POINT(102 0)                         |
+--------------------------------------+

Please ignore my previous comments.  

I hadn't clued into the fact that MySQL complies with the axis order standard for latitude-longitude that many others don't.