[PostgreSQL provider] Retrieve CRS from spatial_ref_sys

instead of relying on QgsCoordinateReferenceSystem::createFromPostgisSrid() which contrary to what its name suggest doesn't use postgis at all, but the QGIS CRS database. I detected the issue initially on a database where my spatial_ref_sys table had been emptied and a few entries where assigned with SRIDs in the EPSG range but with definitions that weren't the ones from EPSG. There is no guarantee that EPSG:XXXX gets a SRID of XXXX And the past implementation of the fallback case of QgsPostgresProvider::crs() only looked at proj4text instead of using the WKT from srtext.
qgis · May 22, 2021 · 25faed2 · 25faed2
1 parent f21ea8c
commit 25faed2
Show file tree

Hide file tree

Showing 2 changed files with 43 additions and 5 deletions.
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -4658,9 +4658,6 @@ QgsCoordinateReferenceSystem QgsPostgresProvider::crs() const
   QgsCoordinateReferenceSystem srs;
   int srid = mRequestedSrid.isEmpty() ? mDetectedSrid.toInt() : mRequestedSrid.toInt();
 
-  // TODO QGIS 4 - move the logic from createFromSridInternal to sit within the postgres provider alone
-  srs.createFromPostgisSrid( srid );
-  if ( !srs.isValid() )
   {
     static QMutex sMutex;
     QMutexLocker locker( &sMutex );
@@ -4672,10 +4669,23 @@ QgsCoordinateReferenceSystem QgsPostgresProvider::crs() const
       QgsPostgresConn *conn = connectionRO();
       if ( conn )
       {
-        QgsPostgresResult result( conn->PQexec( QStringLiteral( "SELECT proj4text FROM spatial_ref_sys WHERE srid=%1" ).arg( srid ) ) );
+        QgsPostgresResult result( conn->PQexec( QStringLiteral( "SELECT auth_name, auth_srid, srtext, proj4text FROM spatial_ref_sys WHERE srid=%1" ).arg( srid ) ) );
         if ( result.PQresultStatus() == PGRES_TUPLES_OK )
         {
-          srs = QgsCoordinateReferenceSystem::fromProj( result.PQgetvalue( 0, 0 ) );
+          const QString authName = result.PQgetvalue( 0, 0 );
+          const QString authSRID = result.PQgetvalue( 0, 1 );
+          const QString srText = result.PQgetvalue( 0, 2 );
+          bool ok = false;
+          if ( authName == QLatin1String( "EPSG" ) || authName == QLatin1String( "ESRI" ) )
+          {
+            ok = srs.createFromUserInput( authName + ':' + authSRID );
+          }
+          if ( !ok && !srText.isEmpty() )
+          {
+            ok = srs.createFromUserInput( srText );
+          }
+          if ( !ok )
+            srs = QgsCoordinateReferenceSystem::fromProj( result.PQgetvalue( 0, 3 ) );
           sCrsCache.insert( srid, srs );
         }
       }

diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -2607,6 +2607,34 @@ def _test(vl, extent, ids):
 
         _test(vl, QgsRectangle(-181, -90, 181, 90), [1, 2, 3])  # no use of spatial index currently
 
+    def testReadCustomSRID(self):
+        """Test that we can correctly read the SRS from a custom SRID"""
+
+        md = QgsProviderRegistry.instance().providerMetadata("postgres")
+        conn = md.createConnection(self.dbconn, {})
+
+        # Cleanup if needed
+        try:
+            conn.dropVectorTable('qgis_test', 'test_custom_srid')
+        except QgsProviderConnectionException:
+            pass
+
+        conn.executeSql("DELETE FROM spatial_ref_sys WHERE srid = 543210 AND auth_name='FOO' AND auth_srid=32600;")
+        conn.executeSql("""INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (543210, 'FOO', 32600, 'PROJCS["my_projection",GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH]]','+proj=tmerc +lat_0=0 +lon_0=0 +k=1 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs');""")
+
+        conn.executeSql('''
+        CREATE TABLE "qgis_test"."test_custom_srid" (
+            gid serial primary key,
+            geom geometry(Point, 543210)
+        );''')
+
+        layer = QgsVectorLayer(self.dbconn + ' sslmode=disable key=\'gid\'table="qgis_test"."test_custom_srid" (geom) sql=', 'test', 'postgres')
+
+        conn.executeSql("DELETE FROM spatial_ref_sys WHERE srid = 543210 AND auth_name='FOO' AND auth_srid=32600;")
+
+        self.assertTrue(layer.isValid())
+        self.assertEqual(layer.crs().description(), 'my_projection')
+
 
 class TestPyQgsPostgresProviderCompoundKey(unittest.TestCase, ProviderTestCase):