3
# AUTHOR: Maciej Sieczka, msieczka@sieczka.org, http://www.sieczka.org
5
# PURPOSE: Create a QGIS srs.db-compliant SQL script with SRS, ellipsoid
6
# and projections defs based on the output of installed PROJ.4 and
9
# VERSION: 1.1.0, 2009.03.19
11
# COPYRIGHT: (c) 2008,2009 Maciej Sieczka
13
# LICENSE: This program is free software under the GNU General Public
19
# - Reorganize the code into functions.
20
# - Support qgis.db convenient update too.
21
# - Workaround the issue http://trac.osgeo.org/gdal/ticket/2900.
23
# 1.0.3: Minor cosmetics in comments.
25
# 1.0.2: Replace 'latlon' and 'lonlat' in the `proj -le` output, so that QGIS
26
# can parse the 'tbl_projection' table to provide the GCSs list in the
27
# 'Projection' dialog (BTW, the dialog should be called 'Coordinate
28
# system' actually, as a projection is only a component of a cs).
31
# 1.0.1: Typos in comments fixed.
33
# 1.0: First public release.
35
# USAGE: 1. qgis_srs.sh --full/--tmpl > output.sql
36
# 2. import output.sql into SQLite Database Browser
37
# 3. save as a new dbase, name it srs.db (--full) or qgis.db (--tmpl),
42
# The script creates an SQL plain text dump that can be imported into
43
# SQlite db eg. using SQLite Database Browser's "Import > Database from
46
# The ellipsoids (tbl_ellipsoid) and projections (tbl_projection) tables
47
# are created parsing the proj command output. I chose this approach,
48
# because looking at the original tables content it seems the original
49
# tables were created the same way.
51
# The tbl_srs table is created parsing the epsg_tr.py output. EPSG codes
52
# to process are taken from the installed GDAL's pcs.csv and gcs.csv files.
54
# Tables structure and final SQL statements creating the view and indices
55
# were copied from the original srs.db shipped with QGIS trunk r8544,
56
# after dumping it to a plain text format with SQLite Database Browser.
62
# Create bookmarks table:
64
echo "CREATE TABLE tbl_bookmarks (
65
bookmark_id integer PRIMARY KEY,
66
name varchar(255) NOT NULL,
67
project_name varchar(32),
72
projection_srid integer
78
# Create ellipsoids table:
80
echo "CREATE TABLE tbl_ellipsoid (
81
acronym char(20) NOT NULL default '',
82
name char(255) NOT NULL default '',
83
radius char(255) NOT NULL default '',
84
parameter2 char(255) NOT NULL default '',
91
# Populate ellipsoids table. Care about (possible) apostrophes in strings, which
92
# would brake the SQL syntax, as the "'" is also a string separator:
94
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
95
echo "INSERT INTO tbl_ellipsoid VALUES('"${i}"');"
101
# Create projections table:
103
echo "CREATE TABLE tbl_projection (
104
acronym varchar(20) NOT NULL PRIMARY KEY,
105
name varchar(255) NOT NULL default '',
106
notes varchar(255) NOT NULL default '',
107
parameters varchar(255) NOT NULL default ''
113
# Populate projections table:
115
# Process each proj4 projection acronym...
117
for i in `proj -l | cut -d" " -f1 | sed -e 's/lonlat/longlat/' -e 's/latlon/latlong/'` ; do
119
#...to extract it's parameters, making sure not more than 4 fields are created...
121
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"`
123
#...count the number of parameters...
125
proj_nf=`echo $proj | awk -F"','" '{print NF}'`
127
#...if only 3 (3 or 4 are possible) add an empty 4th one.
129
if [ $proj_nf -eq 3 ] ; then
133
# Create an SQL command for each proj:
135
echo "INSERT INTO tbl_projection VALUES("${proj}");"
142
# Create SRSs table for srs.db:
144
echo "CREATE TABLE tbl_srs (
145
srs_id INTEGER PRIMARY KEY,
146
description text NOT NULL,
147
projection_acronym text NOT NULL,
148
ellipsoid_acronym NOT NULL,
149
parameters text NOT NULL,
150
srid integer NOT NULL,
151
epsg integer NOT NULL,
152
is_geo integer NOT NULL
158
# Create SRSs table for qgis.db:
160
echo "CREATE TABLE tbl_srs (
161
srs_id INTEGER PRIMARY KEY,
162
description text NOT NULL,
163
projection_acronym text NOT NULL,
164
ellipsoid_acronym NOT NULL,
165
parameters text NOT NULL,
168
is_geo integer NOT NULL
174
# Populate SRSs table:
176
gdal_share=`gdal-config --datadir`
179
# Extract projected SRSs from the installed GDAL pcs.csv file:
181
#Find valid EPSG numbers parsing GDAL's pcs.csv:
182
for i in `awk 'NR>1' ${gdal_share}/pcs.csv | cut -d, -f1`; do
184
raw=`epsg_tr.py -proj4 $i 2>&1 | tr "\n" " " | sed 's/ <> $//' | grep -v "^ERROR 6: "`
186
if [ -n "$raw" ]; then
189
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
190
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
191
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
192
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
193
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
196
echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"
202
# Extract un-projected SRSs from the installed GDAL gcs.csv file:
204
#Find valid EPSG numbers parsing GDAL's gcs.csv:
205
for i in `awk 'NR>1' ${gdal_share}/gcs.csv | cut -d, -f1`; do
207
raw=`epsg_tr.py -proj4 $i 2>&1 | tr "\n" " " | sed 's/ <> $//' | grep -v "^ERROR 6: "`
209
if [ -n "$raw" ]; then
212
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
213
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
214
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
215
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
216
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
219
echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"
228
# Final SQL statements:
230
echo "CREATE VIEW vw_srs as
231
select a.description as description,
235
a.parameters as parameters,
238
inner join tbl_projection b
239
on a.projection_acronym=b.acronym
241
b.name, a.description;"
249
--qgis Create a database to be used as the 'qgis.db' upgraded replacement.
250
--srs Create a database to be used as the 'srs.db' upgraded replacement.
256
if [ "$1" = "--qgis" ]; then
257
echo "BEGIN TRANSACTION;"
259
mk_tbl_ellps; pop_tbl_ellps
260
mk_tbl_projs; pop_tbl_projs
265
elif [ "$1" = "--srs" ]; then
266
echo "BEGIN TRANSACTION;"
267
mk_tbl_ellps; pop_tbl_ellps
268
mk_tbl_projs; pop_tbl_projs
269
mk_tbl_srss_srs; pop_tbl_srss
271
echo "CREATE UNIQUE INDEX idx_srsepsg on tbl_srs(epsg);
272
CREATE UNIQUE INDEX idx_srssrid on tbl_srs(srid);
added
removed
scripts/qgis_srs.sh
