MySQL 5.7 and 8.0 storing SRID 4326 in different binary formats

[Justas-S]

Given this example

create table geo_table (
    id int auto_increment not null,
    g geometry not null,
    primary key(id)
);

INSERT INTO geo_table (g) values (ST_GeomFromText('POLYGON((-16.067132663642 180,-16.379054277547 179.413509363,-16.433984277547 179.096609363,-16.63915 178.59683859512,-17.012041674368 178.725059363,-16.801354076947 179.36414266196,-16.555216566639 180,-16.067132663642 180))', 4326));
INSERT INTO geo_table (g) values (ST_GeomFromText('POLYGON((-16.067132663642 180,-16.379054277547 179.413509363,-16.433984277547 179.096609363,-16.63915 178.59683859512,-17.012041674368 178.725059363,-16.801354076947 179.36414266196,-16.555216566639 180,-16.067132663642 180))'));

select hex(g), hex(st_aswkb(g)) from geo_table;

Output from MySQL 8.0.19:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hex(g)                                                                                                                                                                                                                                                                                             | hex(st_aswkb(g))                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| E6100000010300000001000000080000000000000000806640F4D5329B2F1130C098D5FC773B6D664028797DB3096130C051D2846C17636640E50AF797196F30C0FEE0404D19536640083D9B559FA330C0CC77B1AF335766408299C529150331C04A06830EA76B6640EE00718A25CD30C0000000000080664073E943AC228E30C00000000000806640F4D5329B2F1130C0 | 01030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 |
| 0000000001030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 | 01030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Output from MySQL 5.7.25:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hex(g)                                                                                                                                                                                                                                                                                             | hex(st_aswkb(g))                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| E610000001030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 | 01030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 |
| 0000000001030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 | 01030000000100000008000000F4D5329B2F1130C0000000000080664028797DB3096130C098D5FC773B6D6640E50AF797196F30C051D2846C17636640083D9B559FA330C0FEE0404D195366408299C529150331C0CC77B1AF33576640EE00718A25CD30C04A06830EA76B664073E943AC228E30C00000000000806640F4D5329B2F1130C00000000000806640 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Conclusions

Things to note:

• Values from st_aswkb column are identical.
• MySQL 5.7 values are identical (excluding the initial 4 bytes which store the SRID)
• MySQL 8 values differ

If you take apart the version 8 WKB values you can see that coordinates of the first points, from the first rings are: 0000000000806640 F4D5329B2F1130C0 (where SRID 4326) and F4D5329B2F1130C0 0000000000806640 (where SRID 0). The points are flipped because of the SRID. I am not sure why this happens, MySQL 5.7 docs mention:

In MySQL, all computations are done assuming SRID 0, regardless of the actual SRID value

Although this is a simple selection query, is it considered a computation?

Regarding this library

When setting the raw attributes, the SpatialTrait assumes that values are in WKB, which as shown above is not always the case. Because of this, polygons returned from the example above using different versions of MySQL return different flipped coordinates.

[grimzy]

Thank you for reporting this.

We're aware of this issue and working on version 3 of this package for MySQL 8 and SRID support (#119).

• MySQL 5.7 doesn't have SRID support and therefore was saving everything with SRID 0 (4 null bytes at the start of the WKB)
• MySQL 8 looks at the SRID's definition to determine the order of X and Y in the WKT (which we use for inserting in this package)

In #119, we've added the new axis-order=long-lat option to make sure we consistently have the same order in our WKT. Note that this only affects the query and not how the data is stored in MySQL.

This article was very interesting: https://mysqlserverteam.com/axis-order-in-spatial-reference-systems/