Longitude and Latitude order with ST_X, and ST_Y on SRID 4326

Question

I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.

Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:

INSERT INTO organization 
(loc, spots, name, phone, website, account_id, category_id) 
VALUES 
(
  ST_PointFromText("POINT(-113.532000 54.733000)", 4326), 
  10,
  "Baptiste",
  " ",
  null,
  null,
  null
); 

SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

SELECT 
   *, 

    ST_Y(loc) as lat,   
    ST_X(loc) as lng 

FROM 
  organization;

What am I not understanding about how these functions and coordinates work?

Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???

TIA

Answer 1

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing

• ST_Longitude()
• ST_Latitude()

For more information check out Does MySQL store point datatypes as LAT LNG or LNG LAT?