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.

[maxKimoby]

This adapter is not functional with SRID 4326 specifically because of that. MySQL accepts latlon in its spatial columns while RGeo specifications accept lonlat.

Can we have an option to specify the latlon order so this works with MySQL? Also, the README examples do not work because columns are named lonlat when MySQL accepts latlon.

Another option would be to completely bypass RGeo active record for values and insert directly with WKT as well as returning WKT from select queries.