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

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

Options: ReplyQuote


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


Sorry, only registered users may post in this forum.

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.