The spatial function ST_Transform returns weird results in MySQL. I tested the following query and compared with PostGIS and obtained very different results:
The query (exactly the same used in MySQL and PostGIS): SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(5 45)', 4326), 2154));
Using PostGIS (version: 3.5.2 with proj version: 9.4.0), I get the following result:
POINT(857581.8993196681 6435414.747835401)
Using MySQL (version: 8.0.41) I get the following result:
POINT(6297547.01826163 3142340.2635587133)
I checked with other tools and the result from PostGIS is correct. So why is the result from MySQL so different? I guess it's not a bug or it would have been reported for a while, but then what am I doing wrong with this query?
Note that I tried for multiple lat/lon values and the results are always different, even for POINT(0 0), though the difference is small in this case.
After some more tests and digging, I found the issue: MySQL considers that WKT with EPSG=4326 are given in lat-long while PostGIS considers that they are given in long-lat. This behavior can be customized using the axis-order option for the ST_GeomFromText function in MySQL.
The MySQL query
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(5 45)', 4326, 'axis-order=long-lat'), 2154));
returns a result very close to the one with the initial query in PostGIS.