MySQL Forums
Forum List  »  GIS

ST_SRID() flips coordinates for SRID 4326; ST_GeomFromText() doesn't
Posted by: Peter Brodersen
Date: February 18, 2021 08:50AM


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.

ST_ASTEXT(ST_GeomFromText('POINT(55 12)')),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 25832)),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 4326)),
ST_X(ST_GeomFromText('POINT(55 12)')),
ST_X(ST_GeomFromText('POINT(55 12)', 25832)),
ST_X(ST_GeomFromText('POINT(55 12)', 4326)),

*************************** 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"

However the manual specifically states:
"ST_SRID() changes the geometry SRID value without transforming its coordinates."

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:

- Peter Brodersen

Options: ReplyQuote

Written By
ST_SRID() flips coordinates for SRID 4326; ST_GeomFromText() doesn't
February 18, 2021 08:50AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.