MySQL Bugs: #88869: WKB format has different axis order.

Bug #88869	WKB format has different axis order.
Submitted:	12 Dec 2017 9:18	Modified:	13 Dec 2017 9:05
Reporter:	Yoshiaki Yamasaki	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	8.0.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	gis, MySQL

Description:
WKB format has different axis order.

How to repeat:
[premise1: Axis order depends on SRID]

Axis order depends on SRID.

For example:
 - Axis order of SRID=4326 is North,East
 - Axis order of SRID=2443 is North,East
 - Axis order of SRID=3857 is East,North

You can check axis order by http://www.epsg-registry.org/.

 * Access http://www.epsg-registry.org/ and click "retrieve by code".
   Enter SRID(EPSG) in Code and click "Retrieve".

   SRID=4326
    > Name: Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: degree 

   SRID=2443
    > Name: Cartesian 2D CS. Axes: northing, easting (X,Y). Orientations: north, east. UoM: m. 

   SRID=3857
    > Name: Cartesian 2D CS. Axes: easting, northing (X,Y). Orientations: east, north. UoM: m. 

[premise2: Check WKB format in HEX]

- 0=0000000000000000
- 1=000000000000F03F
- 2=0000000000000040

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(0 0)', 0)));
+-------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(0 0)', 0))) |
+-------------------------------------------------+
| 010100000000000000000000000000000000000000      |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(1 0)', 0)));
+-------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(1 0)', 0))) |
+-------------------------------------------------+
| 0101000000000000000000F03F0000000000000000      |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(2 0)', 0)));
+-------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(2 0)', 0))) |
+-------------------------------------------------+
| 010100000000000000000000400000000000000000      |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(2 1)', 0)));
+-------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(2 1)', 0))) |
+-------------------------------------------------+
| 01010000000000000000000040000000000000F03F      |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(2 2)', 0)));
+-------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(2 2)', 0))) |
+-------------------------------------------------+
| 010100000000000000000000400000000000000040      |
+-------------------------------------------------+
1 row in set (0.00 sec)

[Check WKB format in HEX(SRID=4236,2443,3857)]

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 4326)));
+----------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 4326))) |
+----------------------------------------------------+
| 0101000000000000000000F03F0000000000000040         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 2443)));
+----------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 2443))) |
+----------------------------------------------------+
| 0101000000000000000000F03F0000000000000040         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 3857)));
+----------------------------------------------------+
| HEX(ST_AsWKB(ST_GeomFromText('POINT(1 2)', 3857))) |
+----------------------------------------------------+
| 0101000000000000000000F03F0000000000000040         |
+----------------------------------------------------+
1 row in set (0.00 sec)

=> Axis order depends on SRID, but the result is same().

Suggested fix:
Idea1:
Changing geometry format depend on SRID.

Idea2:
Create new geometry data type like GEOGRAPHY of PostGIS. The new geometry data type has correct axis order.

Hello Yoshiaki,

Thank you for the report and test case.

Thanks,
Umesh

Posted by developer:
 
The axis order is decided by the spatial reference system definition, according to OGC Axis Order Policy Guidance (08-038r5, http://www.ogcnetwork.net/axisorder). Both WKT and WKB input/output will default to that axis order. All spatial reference systems delivered with MySQL are from the EPSG Dataset, which uses latitude-longitude ordering for all geographical reference systems.

ST_GeomFromText('POINT(1 2)', 4326) therefore constructs a point with latitude=1 and longitude=2.

This can be overriden by the user: ST_GeomFromText('POINT(1 2)', 4326, 'axis-order=long-lat') constructs a point with latitude=2 and longitude=1.

The same option can be used on output: ST_AsText(ST_GeomFromText('POINT(1 2)', 4326), 'axis-order=long-lat') constructs a point with latitude=1 and longitude=2 (spatial reference system axis order), and outputs it as longitude-latitude, i.e., 'POINT(2 1)'.

The internal binary string representation of geometries is similar to WKB, but the axis order is always longitude-latitude: HEX(ST_GeomFromText('POINT(1 2)', 4326) will return a WKB-like string with 2 as the first coordinate value and 1 as the second value.

Hello.

Excue me for interrupting.

I think,
the reason why MySQL flips X and Y for geometries on latitude-longitude CS
is
to force "left-handed coordinate system" to "right-handed coordinate system",
becase
almost geometry engines assumes right-handed system.

If it is collect,
MySQL should flip X and Y if axis order is Norhing-Easting (and other left-handed system),
regardless of geodetic (latitude-longitude) CS or cartesian (projected) CS.

MySQL does not care if the system is left-handed or right-handed when producing WKB output. MySQL returns the coordinates in the order the axes have been defined in the spatial reference system definition, in accordance with the recommendations in OGC 08-038r5 Axis Order Policy Guidance (http://www.ogcnetwork.net/axisorder).