Coordinates swapping with ST_transform and SRID 3116, postgis 3.1

[marbotte]

It seems that the c function associated with ST_Transform(geom,3116) swaps X and Y coordinates. I am not sure it concerns only the Magna-Sirgas SRIDs, but other random SRIDs did not result in the same bug, all the Magna Sirgas in meter are concerned though. This bug does not concern the form ST_transform(geom,'proj4text'), which seem to use another source function.

So when I use the following code:

WITH a AS(
SELECT
  ST_Transform(
    ST_SetSRID(
      ST_MakePoint(-73.84, 7.33)
    ,4326)
  ,3116 ) geom
), b AS(
SELECT
  ST_Transform(
    ST_SetSRID(
      ST_MakePoint(-73.84, 7.33)
    ,4326)
  ,(SELECT proj4text FROM spatial_ref_sys WHERE srid=3116) ) geom
)
SELECT ST_X(geom), ST_Y(geom)
FROM a
UNION ALL
SELECT ST_X(geom), ST_Y(geom)
FROM b

        st_x        |        st_y        
--------------------+--------------------
 1302328.1712723123 | 1026224.6948008615
 1026224.6948008615 | 1302328.1712723123

My installation:

SELECT postgis_full_version();

POSTGIS="3.1.3 008d2db" [EXTENSION] PGSQL="130" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"

SELECT version()

PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 11.3.0 p4) 11.3.0, 64-bit

[marbotte]

Sorry I put it in postgis 3.2.x, but it is postgis 3.1. I am new here and I don't know how to change that…

[robe]

I don't seem to have a problem with this:

On mine

                                                                                             postgis_full_version

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
 POSTGIS="3.2.2 628da50" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTO
BUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER
(1 row)

WITH a AS(
SELECT
  ST_Transform(
    ST_SetSRID(
      ST_MakePoint(-73.84, 7.33)
    ,4326)
  ,3116 ) geom
), b AS(
SELECT
  ST_Transform(
    ST_SetSRID(
      ST_MakePoint(-73.84, 7.33)
    ,4326)
  ,(SELECT proj4text FROM spatial_ref_sys WHERE srid=3116) ) geom
)
SELECT ST_X(geom), ST_Y(geom)
FROM a
UNION ALL
SELECT ST_X(geom), ST_Y(geom)
FROM b

yields:

        st_x        |       st_y
--------------------+-------------------
 1026224.6948008615 | 1302328.171272312
 1026224.6948008615 | 1302328.171272312
(2 rows)

I haven't checked on a setup with 3.1. Any chance you can upgrade your 3.1? 3.1.3 is pretty old. Latest 3.1 is 3.1.7

[marbotte]

I guess this means it is resolved in postgis 3.2. I cannot update postgis in my installation right now, but since I have the temporary solution of using the "proj4text", my goal here was to raise the issue, not to get a solution for myself! Except if someone want to dig more in the versions, I guess we can flag the ticket as "solved"?