Bug#1035922: postgis: Axis order regression since 3.1.1+dfsg-1+deb11u1

[Bas Couwenberg]

Source: postgis
Version: 3.1.1+dfsg-1+deb11u1
Severity: important
Tags: upstream fixed-upstream pending
Control: submitter -1 Stephan Großberndt <s.grossberndt at sidebysite.de>
Control: fixed -1 postgis/3.1.4+dfsg-1

Dear Maintainer,

As reported by Stephan Großberndt <s.grossberndt at sidebysite.de> in #1031410:

"
 at our company we were quite surprised by this seemingly minor update 3.1.1+dfsg-1+deb11u1, because it completely broke an application: Due to the change the x and y axis are now inverted when converting coordinates to EPSG 31466:

 Before (this output is from 11.19, but was like this on 13 before as well):

 SELECT geometry,ST_AsGeoJSON(ST_Transform(ST_SetSRID(geometry, 3857), 31466)) FROM osm_car_sharing_node LIMIT 1;
                       geometry                      |          st_asgeojson
 ----------------------------------------------------+--------------------------------------------------------------------
  0101000020110F000004F0844A1349264120ED527FE9DD5841 | {"type":"Point","coordinates":[2539841.86185744,5586869.51937972]}
 (1 row)


 Now:

 SELECT geometry, ST_AsGeoJSON(ST_Transform(ST_SetSRID(geometry, 3857), 31466)) FROM osm_car_sharing_node LIMIT 1;
 -[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------------
 geometry     | 0101000020110F000004F0844A1349264120ED527FE9DD5841
 st_asgeojson | {"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:31466"}},"coordinates":[5586869.519378289,2539841.861857439]}

 I understand the rationale for the change in general, but in my opinion such a major change really should not be part of such a minor update.

 Is there an option to fix this apart from changing all queries?
"

And:

"
 after further investigation this looks more like a bug in the backport.

 At first I thought the change was about flipping x and y for all coordinate systems except those containing "lat/lon", which did not make much sense to me, but I would have been willing to accept this.

 But apparently this flip is only in this PostGIS 3.1.1+dfsg-1+deb11u1 version, earlier and later PostGIS versions correctly return x as x and y as y.

 For this query:

 SELECT ST_AsGeoJSON(ST_Transform(ST_SetSRID('0101000020110F000004F0844A1349264120ED527FE9DD5841'::geometry, 3857), 31466));

 the following versions correctly return x=2539841,y=5586869:

 - PostgresQL 11 with PostGIS 2.5.1+dfsg-1 from Debian Sources
 - PostgresQL 11 with PostGIS 2.5.5+dfsg-1.pgdg100+2 from PostgreSQL Sources
 - PostgresQL 13 with PostGIS 3.1.1+dfsg-1 from Debian Sources
 - PostgresQL 13 with PostGIS 3.3.2+dfsg-1.pgdg110+1 from Debian Sources


 Only

 - PostgresQL 13 with PostGIS 3.1.1+dfsg-1+deb11u1 from Debian Sources

 incorrectly returns x=5586869,y=2539841

 Should I file another bug report for this? 
"

Some digging in the upstream stable-3.1 branch reveals two additional commits required to resolve this regression:

 https://trac.osgeo.org/postgis/changeset/11efb9f0cdc71cf2bdc4850491218495a07b18ba/git
 https://trac.osgeo.org/postgis/changeset/8baf0b07b26df12d246c82bdae8ecd77371f3d24/git

With these two commits added as patches to the postgis (3.1.1+dfsg-1+deb11u1) package, the order is correct again:

 gis=# SELECT ST_AsGeoJSON(ST_Transform(ST_SetSRID('0101000020110F000004F0844A1349264120ED527FE9DD5841'::geometry, 3857), 31466));
                                                          st_asgeojson                                                          
 -------------------------------------------------------------------------------------------------------------------------------
  {"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:31466"}},"coordinates":[2539841.861857439,5586869.519378289]}
 (1 row)

The axis order for the issue reported in #1031392 is also still correct:

 gis=# SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=3976;POINT(-25000 75000)'), 4326));
                        st_asewkt                        
 --------------------------------------------------------
  SRID=4326;POINT(-18.43494882292201 -89.27021258118658)
 (1 row)