Skip to content

Commit

Permalink
* Added script for updating the srs.db from the GDAL installation (th…
Browse files Browse the repository at this point in the history 
…anks msieczka !)

* Added a small README on the process on how to do the above
* Change the CRS recognition when reading from projects, so it tries to match the proj4 string if valid. This is a must when the srs.db now is updated
* Changed to read ellipsoid an projection acronyms from srs.db nistead of qgis.db (mostly)
* Misc. clean up of comments and deletion of stale code.
* Fixes #1534.

git-svn-id: http://svn.osgeo.org/qgis/trunk@11447 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
homann committed Aug 19, 2009
1 parent eb386a7 commit 4b4614f
Show file tree
Hide file tree
Showing 8 changed files with 410 additions and 167 deletions.
16 changes: 16 additions & 0 deletions resources/README
View file
@@ -0,0 +1,16 @@
Updating the srs.db
-------------------

The srs.db can be updated from the EPSG codes in the
GDAL installation:

1) Install latest GDAL, be sure to configure with --with-python.
2) Set PATH and PYTHONDIR if installed GDAL in non-standard location.
3) Run 'scripts/qgis_srs.sh > new_srs.sql'. It may take a minute or two.
4) Run 'sqlite3 new_srs.db < new_srs.sql'.'
4) Run 'sqlite3 new_srs.db <postprocess_srs.sql'

You can use sqlite3 to dump the contents from both srs.db and new_srs.db
before you decide to commit.

Magnus Homann 2009-08-19
Binary file modified resources/srs.db
View file
Binary file not shown.
26 changes: 13 additions & 13 deletions scripts/postprocess_srs.sql
View file
Expand Up @@ -3,18 +3,18 @@
-- sqlite3 srs.db <postprocess_srs.sql

-- Swedish RT-90 projections with increased accuracy
REPLACE INTO tbl_srs VALUES(984,'RT90 7.5 gon V','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=11.306250000000 +k=1.000006000000 +x_0=1500025.141 +y_0=-667.282 +ellps=GRS80 +units=m +no_defs',3019,3019,0);
REPLACE INTO tbl_srs VALUES(985,'RT90 5 gon V','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=13.55626666666 +k=1.000005800000 +x_0=1500044.695 +y_0=-667.130 +ellps=GRS80 +units=m +no_defs',3020,3020,0);
REPLACE INTO tbl_srs VALUES(986,'RT90 2.5 gon V','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=15.806284529444 +k=1.000005610240 +x_0=1500064.274 +y_0=-667.711 +ellps=GRS80 +units=m +no_defs',3021,3021,0);
REPLACE INTO tbl_srs VALUES(987,'RT90 0 gon','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=18.056300000000 +k=1.000005400000 +x_0=1500083.521 +y_0=-668.844 +ellps=GRS80 +units=m +no_defs',3022,3022,0);
REPLACE INTO tbl_srs VALUES(988,'RT90 2.5 gon O','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=20.306316666666 +k=1.000005200000 +x_0=1500102.765 +y_0=-670.706 +ellps=GRS80 +units=m +no_defs',3023,3023,0);
REPLACE INTO tbl_srs VALUES(989,'RT90 5 gon O','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=22.556333333333 +k=1.000004900000 +x_0=1500121.846 +y_0=-672.557 +ellps=GRS80 +units=m +no_defs',3024,3024,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 7.5 gon V from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=11.306250000000 +k=1.000006000000 +x_0=1500025.141 +y_0=-667.282 +ellps=GRS80 +units=m +no_defs',93019,93019,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 5 gon V from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=13.55626666666 +k=1.000005800000 +x_0=1500044.695 +y_0=-667.130 +ellps=GRS80 +units=m +no_defs',93020,93020,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 2.5 gon V from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=15.806284529444 +k=1.000005610240 +x_0=1500064.274 +y_0=-667.711 +ellps=GRS80 +units=m +no_defs',93021,93021,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 0 gon from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=18.056300000000 +k=1.000005400000 +x_0=1500083.521 +y_0=-668.844 +ellps=GRS80 +units=m +no_defs',93022,93022,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 2.5 gon O from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=20.306316666666 +k=1.000005200000 +x_0=1500102.765 +y_0=-670.706 +ellps=GRS80 +units=m +no_defs',93023,93023,0);
INSERT INTO tbl_srs VALUES(NULL,'RT90 5 gon O from WGS84','tmerc','GRS80',
'+proj=tmerc +lat_0=0 +lon_0=22.556333333333 +k=1.000004900000 +x_0=1500121.846 +y_0=-672.557 +ellps=GRS80 +units=m +no_defs',93024,93024,0);

-- S-JTSK/Krovak (Greenwich) (ticket #728)
insert into tbl_srs values (3520,'S-JTSK (Greenwich) / Krovak','krovak','bessel','+proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +pm=greenwich +units=m +no_defs','102067','102067','0');
INSERT INTO tbl_srs VALUES (NULL,'S-JTSK (Greenwich) / Krovak','krovak','bessel','+proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +pm=greenwich +units=m +no_defs','102067','102067','0');
278 changes: 278 additions & 0 deletions scripts/qgis_srs.sh
View file
@@ -0,0 +1,278 @@
#!/bin/sh

# AUTHOR: Maciej Sieczka, msieczka@sieczka.org, http://www.sieczka.org
#
# PURPOSE: Create a QGIS srs.db-compliant SQL script with SRS, ellipsoid
# and projections defs based on the output of installed PROJ.4 and
# GDAL.
#
# VERSION: 1.1.0, 2009.03.19
#
# COPYRIGHT: (c) 2008,2009 Maciej Sieczka
#
# LICENSE: This program is free software under the GNU General Public
# License (>=v2).

# CHANGELOG:
#
# 1.1.0:
# - Reorganize the code into functions.
# - Support qgis.db convenient update too.
# - Workaround the issue http://trac.osgeo.org/gdal/ticket/2900.
#
# 1.0.3: Minor cosmetics in comments.
#
# 1.0.2: Replace 'latlon' and 'lonlat' in the `proj -le` output, so that QGIS
# can parse the 'tbl_projection' table to provide the GCSs list in the
# 'Projection' dialog (BTW, the dialog should be called 'Coordinate
# system' actually, as a projection is only a component of a cs).
# More comments.
#
# 1.0.1: Typos in comments fixed.
#
# 1.0: First public release.

# USAGE: 1. qgis_srs.sh --full/--tmpl > output.sql
# 2. import output.sql into SQLite Database Browser
# 3. save as a new dbase, name it srs.db (--full) or qgis.db (--tmpl),
# use with QGIS

# DETAILS:
#
# 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.
#
# 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.

### FUNCTIONS ###

mk_tbl_bookmarks ()
{
# Create bookmarks table:

echo "CREATE TABLE tbl_bookmarks (
bookmark_id integer PRIMARY KEY,
name varchar(255) NOT NULL,
project_name varchar(32),
xmin double,
ymin double,
xmax double,
ymax double,
projection_srid integer
);"
}

mk_tbl_ellps ()
{
# Create ellipsoids table:

echo "CREATE TABLE tbl_ellipsoid (
acronym char(20) NOT NULL default '',
name char(255) NOT NULL default '',
radius char(255) NOT NULL default '',
parameter2 char(255) NOT NULL default '',
PRIMARY KEY (acronym)
);"
}

pop_tbl_ellps ()
{
# Populate ellipsoids table. Care about (possible) apostrophes in strings, which
# would brake the SQL syntax, as the "'" is also a string separator:

proj -le | sed 's/^ *//g' | tr -d "\t" | sed "s/ */ /g" | sed "s/'/''/g" | awk 'BEGIN {sep="'\'','\''"} NF>4 {printf $1 sep $4; for (i=5;i<NF+1;i++) {printf " "$i} print sep $2 sep $3} NF<5 {print $1 sep $4 sep $2 sep $3}' | while read i; do
echo "INSERT INTO tbl_ellipsoid VALUES('"${i}"');"
done
}

mk_tbl_projs ()
{
# Create projections table:

echo "CREATE TABLE tbl_projection (
acronym varchar(20) NOT NULL PRIMARY KEY,
name varchar(255) NOT NULL default '',
notes varchar(255) NOT NULL default '',
parameters varchar(255) NOT NULL default ''
);"
}

pop_tbl_projs ()
{
# Populate projections table:

# Process each proj4 projection acronym...

for i in `proj -l | cut -d" " -f1 | sed -e 's/lonlat/longlat/' -e 's/latlon/latlong/'` ; do

#...to extract it's parameters, making sure not more than 4 fields are created...

proj=`proj -l=$i | tr -d "\t" | sed 's/^ *//g' | sed 's/ : /\n/' | sed "s/'/''/g" | awk '{print "'\''"$0"'\''"}' | tr "\n" "," | sed 's/,$/\n/' | sed "s/','/ /4g"`

#...count the number of parameters...

proj_nf=`echo $proj | awk -F"','" '{print NF}'`

#...if only 3 (3 or 4 are possible) add an empty 4th one.

if [ $proj_nf -eq 3 ] ; then
proj=${proj}",''"
fi

# Create an SQL command for each proj:

echo "INSERT INTO tbl_projection VALUES("${proj}");"

done
}

mk_tbl_srss_srs ()
{
# Create SRSs table for srs.db:

echo "CREATE TABLE tbl_srs (
srs_id INTEGER PRIMARY KEY,
description text NOT NULL,
projection_acronym text NOT NULL,
ellipsoid_acronym NOT NULL,
parameters text NOT NULL,
srid integer NOT NULL,
epsg integer NOT NULL,
is_geo integer NOT NULL
);"
}

mk_tbl_srss_qgis ()
{
# Create SRSs table for qgis.db:

echo "CREATE TABLE tbl_srs (
srs_id INTEGER PRIMARY KEY,
description text NOT NULL,
projection_acronym text NOT NULL,
ellipsoid_acronym NOT NULL,
parameters text NOT NULL,
srid integer NULL,
epsg integer NULL,
is_geo integer NOT NULL
);"
}

pop_tbl_srss ()
{
# Populate SRSs table:

gdal_share=`gdal-config --datadir`
no=0

# Extract projected SRSs from the installed GDAL pcs.csv file:

#Find valid EPSG numbers parsing GDAL's pcs.csv:
for i in `awk 'NR>1' ${gdal_share}/pcs.csv | cut -d, -f1`; do

raw=`epsg_tr.py -proj4 $i 2>&1 | tr "\n" " " | sed 's/ <> $//' | grep -v "^ERROR 6: "`

if [ -n "$raw" ]; then

no=`expr $no + 1`
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
isgeo=0

echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"

fi

done

# Extract un-projected SRSs from the installed GDAL gcs.csv file:

#Find valid EPSG numbers parsing GDAL's gcs.csv:
for i in `awk 'NR>1' ${gdal_share}/gcs.csv | cut -d, -f1`; do

raw=`epsg_tr.py -proj4 $i 2>&1 | tr "\n" " " | sed 's/ <> $//' | grep -v "^ERROR 6: "`

if [ -n "$raw" ]; then

no=`expr $no + 1`
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
isgeo=1

echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"

fi

done
}

mk_view ()
{
# Final SQL statements:

echo "CREATE VIEW vw_srs as
select a.description as description,
a.srs_id as srs_id,
a.is_geo as is_geo,
b.name as name,
a.parameters as parameters,
a.epsg as epsg
from tbl_srs a
inner join tbl_projection b
on a.projection_acronym=b.acronym
order by
b.name, a.description;"
}

usage ()
{
echo "
Usage:
--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.
"
}

### DO IT ###

if [ "$1" = "--qgis" ]; then
echo "BEGIN TRANSACTION;"
mk_tbl_bookmarks
mk_tbl_ellps; pop_tbl_ellps
mk_tbl_projs; pop_tbl_projs
mk_tbl_srss_qgis
mk_view
echo "COMMIT;"

elif [ "$1" = "--srs" ]; then
echo "BEGIN TRANSACTION;"
mk_tbl_ellps; pop_tbl_ellps
mk_tbl_projs; pop_tbl_projs
mk_tbl_srss_srs; pop_tbl_srss
mk_view
echo "CREATE UNIQUE INDEX idx_srsepsg on tbl_srs(epsg);
CREATE UNIQUE INDEX idx_srssrid on tbl_srs(srid);
COMMIT;"

else
usage
fi

0 comments on commit 4b4614f

Please sign in to comment.