annotate schema/geonames-import/import-geonames.sh @ 2455:54c9fe587fe6

Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 74a75a5ce770
children ca7821e1f720
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1109
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 #!/bin/bash
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3 # This is Free Software under GNU Affero General Public License v >= 3.0
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 # without warranty, see README.md and license for details.
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6 # SPDX-License-Identifier: AGPL-3.0-or-later
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 # License-Filename: LICENSES/AGPL-3.0.txt
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 # Copyright (C) 2018 by via donau
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10 # – Österreichische Wasserstraßen-Gesellschaft mbH
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 # Software engineering by Intevation GmbH
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13 # Author(s):
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 # * Sascha Wilde <wilde@intevation.de>
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 ME=$(basename $0)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 MYHOME=$(realpath $(dirname $0))
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 CCs="AT BG HR HU RO SK"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 # Defaults:
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 datadir=$(realpath "$MYHOME/data")
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 db=gemma
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 port=5432
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 drop=0
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 usage()
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 cat <<EOF
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 $ME [OPTION]...
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 Import geonames data (used for extended search features) into gemma database.
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 If data was downloaded previously, it will be reused. To force update
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 of data, first delete the existing data directory or use a fresh one.
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 Options:
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 -d, --db=NAME create the database NAME. Default: "gemma"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 -p, --port=PORT connect do the postgresql cluster at PORT.
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 Default is the postgresql standard port 5432
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 -D, --datadir=PATH download data to PATH. Default: "$datadir"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 --drop drop geonames data
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 --help display this help and exit
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 EOF
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 fatal()
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 echo >&2 "$1"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 exit 23
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 dl() {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 [ -d "$datadir" ] || mkdir "$datadir"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 cd "$datadir"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 for cc in $CCs ; do
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 if [ -f "${cc}.zip" ] ; then
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 echo "Keeping existing ${cc}.zip ..."
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 else
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 echo "Fetching ${cc}.zip ..."
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 curl -O "https://download.geonames.org/export/dump/${cc}.zip"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 fi
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63 done
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 unpack()
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 cd "$datadir" || fatal "$datadir not missing!"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
69 for cc in $CCs ; do
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70 if [ -f "${cc}.zip" ] ; then
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 if [ -f "${cc}.txt" ] ; then
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 echo "Reusing existing ${cc}.txt"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
73 else
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74 echo "Extracting ${cc}.zip ..."
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
75 unzip "${cc}.zip" "${cc}.txt"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
76 fi
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 else
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 fatal "Missing archive: ${cc}.zip"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 fi
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 done
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
81 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
82
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
83 drop_data()
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84 {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 echo "Dropping geonames data..."
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 psql -q -p "$port" -d "$db" -c "TRUNCATE TABLE waterway.geonames;"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 import_data()
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 {
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 for cc in $CCs ; do
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 fqfn="${datadir}/${cc}.txt"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 echo "Importing $fqfn into database..."
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
94 psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95 -v filename="$fqfn" \
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 -f "$MYHOME/import-geonames.sql"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97 done
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
98 }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
99
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
100 # Parse options:
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
101
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 OPTS=`getopt \
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103 -l help,db:,port:,datadir:,drop \
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
104 -o D:d:p: -n "$ME" -- "$@"`
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
105 [ $? -eq 0 ] || { usage ; exit 1 ; }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
106
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
107 eval set -- "$OPTS"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
108
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
109 while true ; do
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
110 case "$1" in
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
111 --db|-d)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
112 db="$2"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
113 shift 2
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
114 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
115 --port|-p)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
116 port="$2"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
117 shift 2
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
118 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
119 --datadir|-D)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
120 datadir="$2"
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
121 shift 2
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
122 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
123 --drop)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
124 drop=1
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
125 shift 1
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
126 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
127 --help)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
128 { usage ; exit 0 ; }
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
129 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
130 --)
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
131 shift
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
132 break
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
133 ;;
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
134 esac
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
135 done
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
136
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
137
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
138 # Main ------------------------------------------------------------
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
139
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
140 if [ $drop -eq 0 ] ; then
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
141 dl
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
142 unpack
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
143 drop_data
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
144 import_data
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
145 else
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
146 drop_data
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
147 fi