forked from hariharsubramanyam/geonames_to_postgresql
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathget.sh
246 lines (225 loc) · 8.74 KB
/
get.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
#!/bin/bash
#===============================================================================
#
# FILE: getgeo.sh
#
# USAGE: ./getgeo.sh
#
# DESCRIPTION: run the script so that the geodata will be downloaded and inserted into your
# database
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Andreas (aka Harpagophyt )
# COMPANY: <a href="http://forum.geonames.org/gforum/posts/list/926.page" target="_blank" rel="nofollow">http://forum.geonames.org/gforum/posts/list/926.page</a>
# VERSION: 1.3
# CREATED: 07/06/2008
# REVISION: 1.1 2008-06-07 replace COPY continentCodes through INSERT statements.
# 1.2 2008-11-25 Adjusted by Bastiaan Wakkie in order to not unnessisarily
# download.
# 1.3 2011-08-07 Updated script with tree changes. Removes 2 obsolete records from "countryinfo" dump image,
# updated timeZones table with raw_offset and updated postalcode to varchar(20).
#===============================================================================
#!/bin/bash
WORKPATH="${HOME}/tmp/GIS/gisnames/geodata"
TMPPATH="tmp"
PCPATH="pc"
PREFIX="_"
DBHOST="127.0.0.1"
DBPORT="5432"
DBUSER="postgres"
FILES="allCountries.zip alternateNames.zip userTags.zip admin1CodesASCII.txt admin2Codes.txt countryInfo.txt featureCodes_en.txt iso-languagecodes.txt timeZones.txt"
psql -U $DBUSER -h $DBHOST -p $DBPORT -c "CREATE DATABASE geonames WITH TEMPLATE = template0 ENCODING = 'UTF8';"
psql -U $DBUSER -h $DBHOST -p $DBPORT geonames <<EOT
DROP TABLE geoname CASCADE;
CREATE TABLE geoname (
geonameid INT,
name VARCHAR(200),
asciiname VARCHAR(200),
alternatenames TEXT,
latitude FLOAT,
longitude FLOAT,
fclass CHAR(1),
fcode VARCHAR(10),
country VARCHAR(2),
cc2 TEXT,
admin1 VARCHAR(20),
admin2 VARCHAR(80),
admin3 VARCHAR(20),
admin4 VARCHAR(20),
population BIGINT,
elevation INT,
gtopo30 INT,
timezone VARCHAR(40),
moddate DATE
);
DROP TABLE alternatename;
CREATE TABLE alternatename (
alternatenameId INT,
geonameid INT,
isoLanguage VARCHAR(7),
alternateName TEXT,
isPreferredName SMALLINT,
isShortName SMALLINT,
isColloquial SMALLINT,
isHistoric SMALLINT
);
DROP TABLE countryinfo;
CREATE TABLE "countryinfo" (
iso_alpha2 CHAR(2),
iso_alpha3 CHAR(3),
iso_numeric INTEGER,
fips_code CHARACTER VARYING(3),
country CHARACTER VARYING(200),
capital CHARACTER VARYING(200),
areainsqkm DOUBLE PRECISION,
population INTEGER,
continent CHAR(2),
tld CHAR(10),
currency_code CHAR(3),
currency_name CHAR(15),
phone CHARACTER VARYING(20),
postal CHARACTER VARYING(60),
postalRegex CHARACTER VARYING(200),
languages CHARACTER VARYING(200),
geonameId INT,
neighbours CHARACTER VARYING(50),
equivalent_fips_code CHARACTER VARYING(3)
);
DROP TABLE iso_languagecodes;
CREATE TABLE iso_languagecodes(
iso_639_3 CHAR(4),
iso_639_2 VARCHAR(50),
iso_639_1 VARCHAR(50),
language_name VARCHAR(200)
);
DROP TABLE admin1CodesAscii;
CREATE TABLE admin1CodesAscii (
code CHAR(20),
name TEXT,
nameAscii TEXT,
geonameid INT
);
DROP TABLE admin2CodesAscii;
CREATE TABLE admin2CodesAscii (
code CHAR(80),
name TEXT,
nameAscii TEXT,
geonameid INT
);
DROP TABLE featureCodes;
CREATE TABLE featureCodes (
code CHAR(7),
name VARCHAR(200),
description TEXT
);
DROP TABLE timeZones;
CREATE TABLE timeZones (
countryCode VARCHAR(4),
timeZoneId VARCHAR(200),
GMT_offset REAL,
DST_offset REAL,
raw_offset REAL
);
DROP TABLE continentCodes;
CREATE TABLE continentCodes (
code CHAR(2),
name VARCHAR(20),
geonameid INT
);
DROP TABLE postalcodes;
CREATE TABLE postalcodes (
countrycode CHAR(2),
postalcode VARCHAR(20),
placename VARCHAR(180),
admin1name VARCHAR(100),
admin1code VARCHAR(20),
admin2name VARCHAR(100),
admin2code VARCHAR(20),
admin3name VARCHAR(100),
admin3code VARCHAR(20),
latitude FLOAT,
longitude FLOAT,
accuracy SMALLINT
);
ALTER TABLE ONLY alternatename
ADD CONSTRAINT pk_alternatenameid PRIMARY KEY (alternatenameid);
ALTER TABLE ONLY geoname
ADD CONSTRAINT pk_geonameid PRIMARY KEY (geonameid);
ALTER TABLE ONLY countryinfo
ADD CONSTRAINT pk_iso_alpha2 PRIMARY KEY (iso_alpha2);
ALTER TABLE ONLY countryinfo
ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);
ALTER TABLE ONLY alternatename
ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);
EOT
# check if needed directories do already exsist
if [ -d "$WORKPATH" ]; then
echo "$WORKPATH exists..."
sleep 0
else
echo "$WORKPATH and subdirectories will be created..."
mkdir -p $WORKPATH/{$TMPPATH,$PCPATH}
echo "created $WORKPATH"
fi
echo
echo ",---- STARTING (downloading, unpacking and preparing)"
cd $WORKPATH/$TMPPATH
for i in $FILES
do
wget -N -q "http://download.geonames.org/export/dump/$i" # get newer files
if [ $i -nt $PREFIX$i ] || [ ! -e $PREFIX$i ] ; then
cp -p $i $PREFIX$i
unzip -u -q $i
case "$i" in
iso-languagecodes.txt)
tail -n +2 iso-languagecodes.txt > iso-languagecodes.txt.tmp;
;;
countryInfo.txt)
grep -v '^#' countryInfo.txt > countryInfo.txt.tmp;
;;
timeZones.txt)
tail -n +2 timeZones.txt > timeZones.txt.tmp;
;;
esac
echo "| $1 has been downloaded";
else
echo "| $i is already the latest version"
fi
done
# download the postalcodes. You must know yourself the url
cd $WORKPATH/$PCPATH
wget -q -N "http://download.geonames.org/export/zip/allCountries.zip"
if [ $WORKPATH/$PCPATH/allCountries.zip -nt $WORKPATH/$PCPATH/allCountries$PREFIX.zip ] || [ ! -e $WORKPATH/$PCPATH/allCountries.zip ]; then
echo "Attempt to unzip $WORKPATH/$PCPATH/allCountries.zip file..."
unzip -u -q $WORKPATH/$PCPATH/allCountries.zip
sed -i -e 's/\\//g' allCountries.txt
cp -p $WORKPATH/$PCPATH/allCountries.zip $WORKPATH/$PCPATH/allCountries$PREFIX.zip
echo "| ....zip has been downloaded"
else
echo "| ....zip is already the latest version"
fi
echo "+---- FILL DATABASE ( this takes 2 days on my machine )"
psql -e -U $DBUSER -h $DBHOST -p $DBPORT geonames <<EOT
copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2,admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from '${WORKPATH}/${TMPPATH}/allCountries.txt' null as '';
copy postalcodes (countrycode,postalcode,placename,admin1name,admin1code,admin2name,admin2code,admin3name,admin3code,latitude,longitude, accuracy) from '${WORKPATH}/${PCPATH}/allCountries.txt' null as '';
copy timeZones (countryCode,timeZoneId,GMT_offset,DST_offset,raw_offset) from '${WORKPATH}/${TMPPATH}/timeZones.txt.tmp' null as '';
copy featureCodes (code,name,description) from '${WORKPATH}/${TMPPATH}/featureCodes_en.txt' null as '';
copy admin1CodesAscii (code,name,nameAscii,geonameid) from '${WORKPATH}/${TMPPATH}/admin1CodesASCII.txt' null as '';
copy admin2CodesAscii (code,name,nameAscii,geonameid) from '${WORKPATH}/${TMPPATH}/admin2Codes.txt' null as '';
copy iso_languagecodes (iso_639_3,iso_639_2,iso_639_1,language_name) from '${WORKPATH}/${TMPPATH}/iso-languagecodes.txt.tmp' null as '';
copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,country,capital,areainsqkm,population,continent,tld,currency_code,currency_name,phone,postal,postalRegex,languages,geonameid,neighbours,equivalent_fips_code) from '${WORKPATH}/${TMPPATH}/countryInfo.txt.tmp' null as '';
copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from '${WORKPATH}/${TMPPATH}/alternateNames.txt' null as '';
INSERT INTO continentCodes VALUES ('AF', 'Africa', 6255146);
INSERT INTO continentCodes VALUES ('AS', 'Asia', 6255147);
INSERT INTO continentCodes VALUES ('EU', 'Europe', 6255148);
INSERT INTO continentCodes VALUES ('NA', 'North America', 6255149);
INSERT INTO continentCodes VALUES ('OC', 'Oceania', 6255150);
INSERT INTO continentCodes VALUES ('SA', 'South America', 6255151);
INSERT INTO continentCodes VALUES ('AN', 'Antarctica', 6255152);
CREATE INDEX index_countryinfo_geonameid ON countryinfo USING hash (geonameid);
CREATE INDEX index_alternatename_geonameid ON alternatename USING hash (geonameid);
EOT
echo "'----- DONE ( have fun... )"