Bug report #18905
Deprecated CRSs: inconsistencies in srs.db and in its update process
|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'.
#1 Updated by Andrea Giudiceandrea over 3 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