[QGIS-Developer] QGIS3: reminder of an annoying bug

[Richard Duivenvoorde]

On 19-01-18 19:21, Tobias Wendorff wrote:
> I'm sorry to push this post again, but this could be quite a blocker
> for the use in Europe (especially in Germany).
> 
> Clearly, a workaround is to inform and sensitize people that a library
> misinterprets the projection and passes it on to QGIS in the wrong way.
> But this will not reach all affected users.
> 
> Could someone perhaps tell me who is responsible for the database of
> projections or at least give me an impetus on how to do it myself?
> I will then gladly make a patch out of it.

Hi Tobias,

(others please correct me if I'm wrong, I'm not authorative on this!),

I found a script which to me looks like it creates the

https://github.com/qgis/QGIS/blob/master/scripts/qgis_srs.sh

which can apparently create qgis.db or src.db:

https://github.com/qgis/QGIS/blob/master/scripts/qgis_srs.sh#L266

--qgis Create a database to be used as the 'qgis.db' upgraded replacement.
--srs  Create a database to be used as the 'srs.db' upgraded replacement.

I am not sure if this is used nowadays. But IF it is, it would be good
if we could document the whole chain of tools and descisions which have
been taken to give QGIS it's crs's...

In the default profile:

.share/local/QGIS/QGIS3/profiles/default there is a qgis.db sqlite
database which has a table tbl_src and a view vw_srs

and references to both of these in the code:

richard at west~/dev/qgis/git/qgis/src (master)$ grep -ir vw_srs *

app/qgscustomprojectiondialog.cpp:  // This is a must, or else we can't
select it from the vw_srs table.
core/qgsapplication.cpp:    if ( sqlite3_exec( database.get(), "DROP
VIEW vw_srs", nullptr, nullptr, &errmsg ) != SQLITE_OK )
core/qgsapplication.cpp:      QgsDebugMsg( QString( "vw_srs didn't
exists in private qgis.db: %1" ).arg( errmsg ) );
core/qgsapplication.cpp:                       "CREATE VIEW vw_srs AS"
gui/qgsprojectionselectiontreewidget.cpp:  QString sql = QStringLiteral(
"select description, srs_id from vw_srs where %1" ).arg( sqlFilter );
gui/qgsprojectionselectiontreewidget.cpp:  QString sql = QStringLiteral(
"select description, srs_id, upper(auth_name||':'||auth_id), is_geo,
name, parameters, deprecated from vw_srs where %1 order by
name,description" )

richard at west~/dev/qgis/git/qgis/src (master)$ grep -ir tbl_srs *

app/qgscustomprojectiondialog.cpp:  QString sql = QStringLiteral(
"select srs_id,description,parameters from tbl_srs" );
app/qgscustomprojectiondialog.cpp:  QString sql = "delete from tbl_srs
where srs_id=" + quotedValue( id );
app/qgscustomprojectiondialog.cpp:    sql = "update tbl_srs set
description="
core/qgis.h://! Magic number for a geographic coord sys in QGIS srs.db
tbl_srs.srs_id
core/qgsapplication.cpp:    int res = sqlite3_exec( database.get(),
"SELECT epsg FROM tbl_srs LIMIT 0", nullptr, nullptr, &errmsg );
core/qgsapplication.cpp:                         "ALTER TABLE tbl_srs
RENAME TO tbl_srs_bak;"
core/qgsapplication.cpp:                         "CREATE TABLE tbl_srs ("
core/qgsapplication.cpp:                         "CREATE INDEX
idx_srsauthid on tbl_srs(auth_name,auth_id);"
core/qgsapplication.cpp:                         "INSERT INTO
tbl_srs(srs_id,description,projection_acronym,ellipsoid_acronym,parameters,srid,auth_name,auth_id,is_geo,deprecated)
SELECT
srs_id,description,projection_acronym,ellipsoid_acronym,parameters,srid,'','',is_geo,0
FROM tbl_srs_bak;"
core/qgsapplication.cpp:                         "DROP TABLE
tbl_srs_bak", nullptr, nullptr, &errmsg ) != SQLITE_OK
core/qgsapplication.cpp:                       " FROM tbl_srs a"
core/qgscoordinatereferencesystem.cpp:    QString sql = QStringLiteral(
"select srs_id from tbl_srs" );
core/qgscoordinatereferencesystem.cpp:                  "from tbl_srs
where " + expression + '=' + quotedValue( value ) + " order by deprecated";
core/qgscoordinatereferencesystem.cpp:  myRecord = getRecord( "select *
from tbl_srs where parameters=" + quotedValue( myProj4String ) + " order
by deprecated" );
core/qgscoordinatereferencesystem.cpp:      myRecord = getRecord(
"select * from tbl_srs where parameters=" + quotedValue(
proj4StringModified.trimmed() ) + " order by deprecated" );
core/qgscoordinatereferencesystem.cpp:    QString sql = QStringLiteral(
"SELECT * FROM tbl_srs WHERE " );
core/qgscoordinatereferencesystem.cpp:  QString mySql = QString( "select
srs_id,parameters from tbl_srs where "
core/qgscoordinatereferencesystem.cpp:  QString mySql = QStringLiteral(
"select parameters from tbl_srs where srs_id = %1 order by deprecated"
).arg( srsId );
core/qgscoordinatereferencesystem.cpp:    mySql = "insert into tbl_srs
(srs_id,description,projection_acronym,ellipsoid_acronym,parameters,is_geo)
values ("
core/qgscoordinatereferencesystem.cpp:    mySql = "insert into tbl_srs
(description,projection_acronym,ellipsoid_acronym,parameters,is_geo)
values ("
core/qgscoordinatereferencesystem.cpp:  QString mySql = QStringLiteral(
"select count(*) from tbl_srs" );
core/qgscoordinatereferencesystem.cpp:  if ( sqlite3_exec(
database.get(), "alter table tbl_srs add noupdate boolean", nullptr,
nullptr, nullptr ) == SQLITE_OK )
core/qgscoordinatereferencesystem.cpp:    ( void )sqlite3_exec(
database.get(), "update tbl_srs set noupdate=(auth_name='EPSG' and
auth_id in (5513,5514,5221,2065,102067,4156,4818))", nullptr, nullptr,
nullptr );
core/qgscoordinatereferencesystem.cpp:  ( void )sqlite3_exec(
database.get(), "UPDATE tbl_srs SET srid=141001 WHERE srid=41001 AND
auth_name='OSGEO' AND auth_id='41001'", nullptr, nullptr, nullptr );
core/qgscoordinatereferencesystem.cpp:    sql = QStringLiteral( "SELECT
parameters,noupdate FROM tbl_srs WHERE auth_name='EPSG' AND
auth_id='%1'" ).arg( it.key() );
core/qgscoordinatereferencesystem.cpp:        sql = QStringLiteral(
"UPDATE tbl_srs SET parameters=%1 WHERE auth_name='EPSG' AND auth_id=%2"
).arg( quotedValue( proj4 ) ).arg( it.key() );
core/qgscoordinatereferencesystem.cpp:      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)" )
core/qgscoordinatereferencesystem.cpp:  sql = QStringLiteral( "DELETE
FROM tbl_srs WHERE auth_name='EPSG' AND NOT auth_id IN (" );
core/qgscoordinatereferencesystem.cpp:  sql = QStringLiteral( "select
auth_name,auth_id,parameters from tbl_srs WHERE auth_name<>'EPSG' AND
NOT deprecated AND NOT noupdate" );
core/qgscoordinatereferencesystem.cpp:            sql = QStringLiteral(
"UPDATE tbl_srs SET parameters=%1 WHERE auth_name=%2 AND auth_id=%3" )
gui/qgsprojectionselectiontreewidget.cpp:  QString sql = QStringLiteral(
"select parameters from tbl_srs where srs_id=%1" ).arg( srsId );
gui/qgsprojectionselectiontreewidget.cpp:  QString sql = QStringLiteral(
"select %1 from tbl_srs where srs_id=%2" )
gui/qgsprojectionselectiontreewidget.cpp:  QString sql = QStringLiteral(
"select distinct auth_name from tbl_srs" );
server/qgsconfigparserutils.cpp:  QString mySql = QStringLiteral(
"select upper(auth_name||':'||auth_id) from tbl_srs" );


Hope this is usefull or else somebody else let us know if I'm totally wrong.

Regards,

Richard