1
|
#!/bin/sh
|
2
|
|
3
|
# AUTHOR: Maciej Sieczka, [email protected]
|
4
|
#
|
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
|
7
|
# GDAL.
|
8
|
#
|
9
|
# VERSION: 1.0.2, 2008.07.05
|
10
|
#
|
11
|
# COPYRIGHT: (c) 2008 Maciej Sieczka
|
12
|
#
|
13
|
# LICENSE: This program is free software under the GNU General Public
|
14
|
# License (>=v2).
|
15
|
|
16
|
# CHANGELOG:
|
17
|
#
|
18
|
# 1.0.2: Replace 'latlon' and 'lonlat' in the `proj -le` output so that QGIS can
|
19
|
# parse the 'tbl_projection' table to provide the GCSs list in the
|
20
|
# 'Projection' dialog (it should be called 'Coordinate system' actually,
|
21
|
# as a projection is only a component of a cs).
|
22
|
# More comments.
|
23
|
# 1.0.1: Typos in comments fixed.
|
24
|
# 1.0: First public release.
|
25
|
|
26
|
# USAGE: 1. qgis_srs.sh > output.sql
|
27
|
# 2. import output.sql into SQLite Database Browser
|
28
|
# 3. save as a new dbase, name it srs.db, use with QGIS
|
29
|
|
30
|
# DETAILS:
|
31
|
#
|
32
|
# The script creates an SQL plain text dump that can be imported into
|
33
|
# SQlite db eg. using SQLite Database Browser's "Import > Database from
|
34
|
# SQL file" tool.
|
35
|
#
|
36
|
# The ellipsoids (tbl_ellipsoid) and projections (tbl_projection) tables
|
37
|
# are created parsing the proj command output. I chose this approach,
|
38
|
# because looking at the original tables content it seems the original
|
39
|
# tables were created the same way.
|
40
|
#
|
41
|
# The tbl_srs table is created parsing the epsg_tr.py output. EPSG codes
|
42
|
# to process are taken from the installed GDAL's pcs.csv and gcs.csv files.
|
43
|
#
|
44
|
# Tables structure and final SQL statements creating the view and indices
|
45
|
# were copied from the original srs.db shipped with QGIS trunk r8544,
|
46
|
# after dumping it to a plain text format with SQLite Database Browser.
|
47
|
|
48
|
### DO IT ###
|
49
|
|
50
|
# Create ellipsoids table:
|
51
|
|
52
|
echo "BEGIN TRANSACTION;
|
53
|
CREATE TABLE tbl_ellipsoid (
|
54
|
acronym char(20) NOT NULL default '',
|
55
|
name char(255) NOT NULL default '',
|
56
|
radius char(255) NOT NULL default '',
|
57
|
parameter2 char(255) NOT NULL default '',
|
58
|
PRIMARY KEY (acronym));"
|
59
|
|
60
|
# Populate ellipsoids table. Care about (possible) apostrophes in strings, which would brake the SQL syntax, as the "'" is also a string separator:
|
61
|
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
|
62
|
echo "INSERT INTO tbl_ellipsoid VALUES('"${i}"');"
|
63
|
done
|
64
|
|
65
|
|
66
|
|
67
|
# Create projections table:
|
68
|
|
69
|
echo "CREATE TABLE tbl_projection (
|
70
|
acronym varchar(20) NOT NULL PRIMARY KEY,
|
71
|
name varchar(255) NOT NULL default '',
|
72
|
notes varchar(255) NOT NULL default '',
|
73
|
parameters varchar(255) NOT NULL default ''
|
74
|
);"
|
75
|
|
76
|
# Populate projections table:
|
77
|
|
78
|
# Process each proj4 projection acronym...
|
79
|
for i in `proj -l | cut -d" " -f1 | sed -e 's/lonlat/longlat/' -e 's/latlon/latlong/'` ; do
|
80
|
|
81
|
#...to extract it's parameters, making sure not more than 4 fields are created...
|
82
|
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"`
|
83
|
|
84
|
#...count the number of parameters...
|
85
|
proj_nf=`echo $proj | awk -F"','" '{print NF}'`
|
86
|
|
87
|
#...if only 3 (3 or 4 are possible) add an empty 4th one.
|
88
|
if [ $proj_nf -eq 3 ] ; then
|
89
|
proj=${proj}",''"
|
90
|
fi
|
91
|
|
92
|
# Create an SQL command for each proj:
|
93
|
echo "INSERT INTO tbl_projection VALUES("${proj}");"
|
94
|
|
95
|
done
|
96
|
|
97
|
|
98
|
|
99
|
# Create SRSs table:
|
100
|
|
101
|
echo "CREATE TABLE tbl_srs (
|
102
|
srs_id INTEGER PRIMARY KEY,
|
103
|
description text NOT NULL,
|
104
|
projection_acronym text NOT NULL,
|
105
|
ellipsoid_acronym NOT NULL,
|
106
|
parameters text NOT NULL,
|
107
|
srid integer NOT NULL,
|
108
|
epsg integer NOT NULL,
|
109
|
is_geo integer NOT NULL);"
|
110
|
|
111
|
# Populate SRSs table:
|
112
|
|
113
|
gdal_share=`gdal-config --datadir`
|
114
|
no=0
|
115
|
|
116
|
# Extract projected SRSs from the installed GDAL pcs.csv file:
|
117
|
|
118
|
for i in `awk 'NR>1' ${gdal_share}/pcs.csv | cut -d, -f1`; do
|
119
|
|
120
|
raw=`epsg_tr.py -proj4 $i | tr "\n" " " | sed 's/ <> $//' | grep -v "Unable to translate coordinate system"`
|
121
|
|
122
|
if [ -n "$raw" ]; then
|
123
|
|
124
|
no=`expr $no + 1`
|
125
|
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
|
126
|
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
|
127
|
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
|
128
|
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
|
129
|
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
|
130
|
isgeo=0
|
131
|
|
132
|
echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"
|
133
|
|
134
|
fi
|
135
|
|
136
|
done
|
137
|
|
138
|
# Extract un-projected SRSs from the installed GDAL gcs.csv file:
|
139
|
|
140
|
for i in `awk 'NR>1' ${gdal_share}/gcs.csv | cut -d, -f1`; do
|
141
|
|
142
|
raw=`epsg_tr.py -proj4 $i | tr "\n" " " | sed 's/ <> $//' | grep -v "Unable to translate coordinate system"`
|
143
|
|
144
|
if [ -n "$raw" ]; then
|
145
|
|
146
|
no=`expr $no + 1`
|
147
|
name=`echo $raw | sed 's/^# //' | grep -o "^.\{1,\} <[[:digit:]]\{1,\}>" | sed 's/ <[[:digit:]]\{1,\}>//' | sed "s/'/''/g"`
|
148
|
proj=`echo $raw | grep -o "+proj=[^[:space:]]\{1,\}" | cut -d"=" -f2`
|
149
|
ellps=`echo $raw | grep -o "+ellps=[^[:space:]]\{1,\}" | cut -d"=" -f2`
|
150
|
srs=`echo $raw | grep -o "+proj.\{1,\} +no_defs"`
|
151
|
epsg=`echo $raw | grep -o ' <[[:digit:]]\{1,\}> ' | sed 's/[^[:digit:]]//g'`
|
152
|
isgeo=1
|
153
|
|
154
|
echo "INSERT INTO tbl_srs VALUES(${no},'${name}','${proj}','${ellps}','${srs}',${epsg},${epsg},${isgeo});"
|
155
|
|
156
|
fi
|
157
|
|
158
|
done
|
159
|
|
160
|
|
161
|
|
162
|
# Final SQL statements:
|
163
|
|
164
|
echo "CREATE VIEW vw_srs as
|
165
|
select a.description as description,
|
166
|
a.srs_id as srs_id,
|
167
|
a.is_geo as is_geo,
|
168
|
b.name as name,
|
169
|
a.parameters as parameters,
|
170
|
a.epsg as epsg
|
171
|
from tbl_srs a
|
172
|
inner join tbl_projection b
|
173
|
on a.projection_acronym=b.acronym
|
174
|
order by
|
175
|
b.name, a.description;
|
176
|
CREATE UNIQUE INDEX idx_srsepsg on tbl_srs(epsg);
|
177
|
CREATE UNIQUE INDEX idx_srssrid on tbl_srs(srid);
|
178
|
COMMIT;"
|
179
|
|