MySQL 8 longitude-latitude order mismatch

[obnijnil]

Given a spatial model Place:

class CreatePlaces < ActiveRecord::Migration[6.1]
  def change
    create_table :places do |t|
      t.point :loc, null: false, srid: 4326, index: { type: :spatial }
    end
  end
end

Then create a place of (lon, lat): (122, 47) using the default RGeo::Geographic.spherical_factory(srid: 4326) factory from WKT:

irb(main):001:0> Place.create(loc: 'Point(-122 47)')
[...]
Traceback (most recent call last):
        1: from (irb):1
ActiveRecord::StatementInvalid (Mysql2::Error: Latitude -122.000000 is out of range in function st_geomfromwkb. It must be within [-90.000000, 90.000000].)

or from the constructor:

irb(main):002:0> Place.create(loc: RGeo::Geographic.spherical_factory(srid: 4326).point(-122, 47))
[...]
Traceback (most recent call last):
        2: from (irb):1
        1: from (irb):2:in `rescue in irb_binding'
ActiveRecord::StatementInvalid (Mysql2::Error: Latitude -122.000000 is out of range in function st_geomfromwkb. It must be within [-90.000000, 90.000000].)

From the error we can see the longitude was sent to the latitude in MySQL in the reversed order, while it's supposed to use the lon-lat order in the WKT format from the doc https://github.com/stadia/activerecord-mysql2rgeo-adapter#reading-and-writing-spatial-columns. And after some research, I found MySQL 8 uses the default lat-lon order which can be customized by an additional parameter 'axis-order=long-lat' to parse WKT:

mysql> set @loc = ST_GeomFromText('Point(47 -122)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Longitude(@loc), ST_Latitude(@loc);
+---------------------+-------------------+
| ST_Longitude(@loc)  | ST_Latitude(@loc) |
+---------------------+-------------------+
| -122.00000000000001 |                47 |
+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> set @loc = ST_GeomFromText('Point(-122 47)', 4326, 'axis-order=long-lat');
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Longitude(@loc), ST_Latitude(@loc);
+---------------------+-------------------+
| ST_Longitude(@loc)  | ST_Latitude(@loc) |
+---------------------+-------------------+
| -122.00000000000001 |                47 |
+---------------------+-------------------+
1 row in set (0.00 sec)

but uses lon-lat order in the constructor:

mysql> set @loc = ST_SRID(Point(-122, 47), 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Longitude(@loc), ST_Latitude(@loc);
+---------------------+-------------------+
| ST_Longitude(@loc)  | ST_Latitude(@loc) |
+---------------------+-------------------+
| -122.00000000000001 |                47 |
+---------------------+-------------------+
1 row in set (0.00 sec)

Fine, we can reverse the lon-lat order when creating records:

irb(main):003:0> place = Place.create(loc: 'Point(47 -122)')
[...]
irb(main):004:0> puts place.loc.lon, place.loc.lat
47.0
-90.0

The lon-lat is mismatched here, and the longitude is truncated. Reload from the database:

irb(main):005:0> place.reload
[...]
irb(main):006:0> puts place.loc.lon, place.loc.lat
-90.0
47.0
=> nil

The lon-lat is matched, but the longitude is still truncated (since it's been truncated before saving to the database).

So the question is, although the record can be saved to the database in the lat-lon order, the lon/lat behavior is not consistent/correct.