Hi,
When I use ST_SRID() with SRID 4326 (WGS84) the X and Y coordinates are flipped. I was wondering whether this is a bug or as intended.
In this example the X coordinate returned by ST_ASTEXT() (as well as with ST_X()) is 55 in all examples except for the last one.
SELECT
ST_ASTEXT(POINT(55,12)),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)')),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 25832)),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 4326)),
ST_ASTEXT(ST_SRID(POINT(55,12),25832)),
ST_ASTEXT(ST_SRID(POINT(55,12),4326)),
ST_X(POINT(55,12)),
ST_X(ST_GeomFromText('POINT(55 12)')),
ST_X(ST_GeomFromText('POINT(55 12)', 25832)),
ST_X(ST_GeomFromText('POINT(55 12)', 4326)),
ST_X(ST_SRID(POINT(55,12),25832)),
ST_X(ST_SRID(POINT(55,12),4326))
\G
*************************** 1. row ***************************
ST_ASTEXT(POINT(55,12)): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)')): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 25832)): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 4326)): POINT(55 12)
ST_ASTEXT(ST_SRID(POINT(55,12),25832)): POINT(55 12)
ST_ASTEXT(ST_SRID(POINT(55,12),4326)): POINT(12 55)
ST_X(POINT(55,12)): 55
ST_X(ST_GeomFromText('POINT(55 12)')): 55
ST_X(ST_GeomFromText('POINT(55 12)', 25832)): 55
ST_X(ST_GeomFromText('POINT(55 12)', 4326)): 55
ST_X(ST_SRID(POINT(55,12),25832)): 55
ST_X(ST_SRID(POINT(55,12),4326)): 12
1 row in set (0.00 sec)
This might be based on an idea that WGS84 is usually written as Latitude,Longitude, roughly corresponding to Y,X instead of X,Y as mentioned here:
"Coordinate system: Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: degree"
https://epsg.io/4326
However the manual specifically states:
"ST_SRID() changes the geometry SRID value without transforming its coordinates."
https://dev.mysql.com/doc/refman/8.0/en/gis-general-property-functions.html#function_st-srid
I get that "transformed" might only refer to transformation due to different spatial reference systems, however flipping the X and Y coordinate also seems like a transformation.
In any case it seems weird that the flip (if it is an intended feature) only happens when using ST_SRID() and not when specifying the same specific SRID as an argument for ST_GeomFromText()
It might be related to this bug report:
https://bugs.mysql.com/bug.php?id=94382
- Peter Brodersen