Bug report #18905

Deprecated CRSs: inconsistencies in srs.db and in its update process

Added by Andrea Giudiceandrea over 5 years ago. Updated over 5 years ago.

Category:Projection Support
Affected QGIS version:3.0.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:26737


Trying to investigate the bug #18896, I've found some inconsistencies in srs.db (in both QGIS 3.0.2 and 2.18.19) about the deprecated Coordinate Reference Systems.

In srs.db shipped with QGIS 3.0.2 (qgis-common-3.0.2-1) the table tbl_srs contains 283 CRSs (all projected ones) with " (deprecated)" postponed to the CRS name in the 'description' field, but only 227 CRSs of those have the field 'deprecated' = 1 (this is right), while 56 CRSs of those have the field 'deprecated' = 0 (this is wrong).

Moreover, there is a CRS which has 'deprecated' = 1 but not " (deprecated)" in the 'description' field: srs_id=26810 , description="S-JTSK (Greenwich) / Krovak East North", srid=102067 , auth_name=EPSG, deprecated=1. This inconsistency was introduced with commit https://github.com/qgis/QGIS/commit/792ac5a433bc59ff21618201109c0101f22eb189

For QGIS 2.18.19: srs.db in qgis-ltr-common-2.18.19-1 contains the same 227 CRSs (with " (deprecated)" in 'description' and 'deprecated' = 1) and 33 CRSs (with " (deprecated)" in 'description' and 'deprecated' = 0) that increase to 56 CRSs, the same as QGIS 3.0.2, after the postintall process (through crssync.exe in Windows). And there is also the same CRS with 'deprecated' = 1 but not " (deprecated)" in the 'description'.

It seems to me that the problem of CRSs with " (deprecated)" in 'description' field and 'deprecated' field wrongly set to 0 is in QgsCoordinateReferenceSystem::syncDatabase():

in the sql statement that imports in srs.db the missing CRSs from GDAL EPSG derived support csv files (gcs.csv, pcs.csv, ...):
[lines 1969-1975 for 3.0 and master, lines 1814-1820 for 2.18]

      sql = QStringLiteral( "INSERT INTO tbl_srs(description,projection_acronym,ellipsoid_acronym,parameters,srid,auth_name,auth_id,is_geo,deprecated) VALUES (%1,%2,%3,%4,%5,'EPSG',%5,%6,0)" )
            .arg( quotedValue( name ),
                  quotedValue( projRegExp.cap( 1 ) ),
                  quotedValue( ellps ),
                  quotedValue( proj4 ) )
            .arg( it.key() )
            .arg( OSRIsGeographic( crs ) );

as you can see, 'deprecated' field in tbl_srs is wrongly filled with 0 for each CRS imported, regardless of the value present for that CRS in the "DEPRECATED" column of the GDAL EPSG csv files, while loadIDs (in syncDatabase(), through GDAL_OGR OSRImportFromEPSG, SetEPSGProjCS, EPSGGetPCSInfo) correctly appends " deprecated" to the CRS name/description according to the value present for that CRS in the said "DEPRECATED" column.

So I think we need for now to:

- fix the sql INSERT INTO statement in order to correctly set the field 'deprecated' for imported CRSs

- fix srs.db
- - setting 'deprecated' = 1 for all already present CRSs with " (deprecated)" in 'description' field
- - appending " (deprecated)" to the content of the 'description' field of the aforementioned CRS with srid=102067

Eventually, we can also take care (I can help in this) of some discrepancies between srs.db and the EPSG database included in GDAL, about deprecated CRSs: for instance, in geocs.csv there are 35 Geographic CRSs with "1" in "DEPRECATED" column, but in srs.db none of those have 'deprecated' = 1 nor " deprecated" in 'description'.

Associated revisions

Revision e15f7cc0
Added by Jürgen Fischer over 5 years ago

crssync: also update 'deprecated' flag (fixes #18905)


#1 Updated by Andrea Giudiceandrea over 5 years ago

See also (about the last sentence of the bug report):
[gdal-dev] About deprecated CRS and the different behavior of EPSGGetPCSInfo and EPSGGetGCSInfo

#2 Updated by Jürgen Fischer over 5 years ago

  • % Done changed from 0 to 100
  • Status changed from Open to Closed

Also available in: Atom PDF