Source: postgis
Version: 3.1.1+dfsg-1+deb11u1
Severity: important
Tags: upstream fixed-upstream pending
Control: submitter -1 Stephan Großberndt <s.grossber...@sidebysite.de>
Control: fixed -1 postgis/3.1.4+dfsg-1
Dear Maintainer,
As reported by Stephan Großberndt <s.grossber...@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)
