[Qgis-developer] script to update the srs.db

Tim Sutton tim at linfiniti.com
Wed Jun 11 16:22:49 EDT 2008

Hi Richard

One thing I would like to do if you assemble small test datasets is to
create some unit tests for srs stuff. One basic form of test I am
thinking is to simply have a directory with a bunch of shapefiles with
a single feature each, each shp being in a different srs. The test
would load each one in turn and verify that it gets the correct epsg
etc for it. So if you so start getting little test files in, bear this
in mind so that we can use them for more formal unit testing too.

Secondly if I recall correctly, the reason we originally opted to use
our own srs identifier was that the srs db did inclide some srs
definitions that did not have an epsg code yet.

It would be good if we could maintain the original srs id pkeys in any
updates you do since changing them may introduce regressions for
people loading  .qgs project files.



On 6/11/08, Richard Duivenvoorde <rdmailings at duif.net> wrote:
> Hi Maciej,
> sorry for my late and lengthy reply, I missed this thread...
> but:
> NICE !!!
> This is the way to go for maintaining the srs.db.
> I tried your script and I am able to generate a new srs.db with it (for
> what I can see...  working with qgis).
> I cc Frank Warmerdam, hopefully he can add some expert judgement to my
> comments ...
> I hope other people can do more projection related testing. Or somebody
> can sent me some tests how to do some thorough projection testing.
> I ran your scripts on a debian etch box.
> I build the srs.db with SQLite Administrator on Windows.
> Some (maybe not so helpfull remarks):
> - I had to change some lines because calling epsg_tr.py at my machine
> resulted in a file not found error. Calling it via `python epsg_tr.py'
> fixed that for me
> - there were some errors like:
> ERROR 6: No translation for Transverse_Mercator_South_Orientated to
> PROJ.4 format is known.
> ...
> ERROR 6: EPSG PCS/GCS code 2218 not found in EPSG support files.  Is
> this a valid EPSG coordinate system?
> ERROR 6: EPSG PCS/GCS code 2221 not found in EPSG support files.  Is
> this a valid EPSG coordinate system?
> ...
> ERROR 6: No translation for Tunisia_Mining_Grid to PROJ.4 format is known.
> ...
> ERROR 6: EPSG PCS/GCS code 32600 not found in EPSG support files.  Is
> this a valid EPSG coordinate system?
> ERROR 6: EPSG PCS/GCS code 32700 not found in EPSG support files.  Is
> this a valid EPSG coordinate system?
> but after that all is running fine as a qgis srs.db
> Except that in my (windows) qgis I do not see any Geographic Coordinate
> Systems. These are also not in the view. So epsg:4326 is missing for
> example..?.. Never mind, I see your problem 2 now
> - epsg 31254 (which Marco H asked me to add to srs.db) is now in it
> - tbl_ellipsoid contains 42 records now (42 in the old also)
> - tbl_projection contains 125 records now (vs 123 in the old one)
> - tbl_srs contains 3519 records now (vs 2690 in the old one)
> - vw_srs conains 3105 records (vs 2690 in the old one)
> My 1 cent about your 'problems':
> -1- working with epsg instead of qgis_id looks logical for me, but I do
> not know the implications for such a decision.
> -2- I would say change it in the local epsg_tr.py for now, until it is
> changed in the epsg_tr.py script itself (or made an option in there?)
> -3- these bugs seem to be squashed already
> So my conclusion after some limited testing:
> - good job
> - we should use this script to generate the srs.db with every release
> - if everybody agrees Maciej can close some bugs :-)
> - can we do some more thorough testing? (though I think Maciej is one of
> the most critic users/testers of srs.db)
> - we should make a SrsDb-wiki-page in which we describe why we do this
> this way, and how to do it. Maciej can you do this? Please let people
> add some tests to it too.
> One question: is / should it be possible to add this all to the
> epsg_tr.py script, as a new --qgis option or so? Or do we really need
> this two-step process.
> Maciej, THANKS for you work!
> Regards,
> Richard Duivenvoorde
> Maciej Sieczka wrote:
>> Folks,
>> Now that the bug in GDAL [1] is fixed and backported to stable branch,
>> as well as included in the 1.5.2 RC3 [2] I have written a Shell script
>> to update QGIS srs.db, based on the installed PROJ.4 and GDAL tools
>> output. Attached.
>> The script creates an SQL plain text dump that can be imported into
>> SQlite db eg. using SQLite Database Browser's "Import > Database from
>> SQL file" tool.
>> The ellipsoids (tbl_ellipsoid) and projections (tbl_projection) tables
>> are created parsing the proj command output. I chose this approach,
>> because looking at the original tables content it seems the original
>> tables were created the same way.
>> The tbl_srs table is created parsing the epsg_tr.py output. EPSG codes
>> to process are taken from the installed GDAL's pcs.csv and gcs.csv files.
>> The script is rather self-explanatory. I have successfully used it to
>> create an updated srs.db and have been using the database with QGIS for
>> a couple of hours now. SRSs strings that were not recognizable by QGIS
>> due to infamous "k" parameter issue due to a change in GDAL [3] and QGIS
>> depending on string-wise SRS mathing [4] are now recogized fine.
>> Tables structure and final SQL statements creating the view and indices
>> were copied from the original srs.db shipped with QGIS trunk r8544,
>> after dumping it to a plain text format with SQLite Database Browser.
>> Please test/fix and let me know! If the tool is allright, I hope it can
>> be included in the QGIS repository and used to update the official
>> srs.db when necessary.
>> Problems:
>> 1. Due to changes in SRSs, projections and ellipsoids support in PROJ.4
>> and GDAL in last years, rows id's in the tables created by the script
>> are now different than they were in the original srs.db. Therefore, at
>> the SRS dialog startup, QGIS cannot find it's default ll/WGS84 SRS in
>> the updated srs.db, because it searches for "select srid from tbl_srs
>> where srs_id = 2585", which is now "SIRGAS / UTM zone 24S". Shouldn't
>> QGIS rather search by EPSG number? EPSG codes never change.
>> 2. In some PROJ.4 version the name "longlat" was changed into "lonlat"
>> (and "latlong" to "latlon"). However, epsg_tr.py creates proj4 strings
>> with the old name. My script parses proj command for the list of
>> available projections and epsg_tr.py for SRSs. Since "longlat" !=
>> "latlon", the "Geographic Coordinate Systems" list in the SRS dialog was
>> not active. Once I changed the "latlon" in tbl_projection to "latlong"
>> the list is back. What do we do? Should epsg_tr.py be changed to output
>> "latlon" rather than "latlong", or should my script replace "latlong"
>> instances?
>> 3. BTW I noticed that QGIS resets k factor to 0, no matter how the SRS
>> is chosen (automatically by SRS matching at layer load, or manually).
>> This is not related to my srs.db changes, it's reproducible with stock
>> srs.db too. A huge bug [5]! Please squash it! And this one [6] too, is
>> possible.
>> [1]http://trac.osgeo.org/gdal/ticket/2036#comment:14
>> [2]http://www.nabble.com/forum/ViewPost.jtp?post=17545259&framed=y
>> [3]http://trac.osgeo.org/gdal/changeset/12625
>> [4]http://trac.osgeo.org/qgis/ticket/1037
>> [5]http://trac.osgeo.org/qgis/ticket/1120
>> [6]http://trac.osgeo.org/qgis/ticket/1085
>> ------------------------------------------------------------------------
>> _______________________________________________
>> Qgis-developer mailing list
>> Qgis-developer at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-developer

Sent from my mobile device

Tim Sutton
QGIS Project Steering Committee Member - Release  Manager
Visit http://qgis.org for a great open source GIS
openModeller Desktop Developer
Visit http://openModeller.sf.net for a great open source ecological
niche modelling tool
Home Page: http://tim.linfiniti.com
Skype: timlinux
Irc: timlinux on #qgis at freenode.net

More information about the Qgis-developer mailing list