2
# If DB is local pass no arguments else
3
# ./import-geonames.sh user password host port dbname
5
PGUSER_PARAM=${PGUSER:+"--username $PGUSER"}
6
PGHOST=${2:+"--host $2"}
7
PGPORT=${3:+"--port $3"}
8
PGDBNAME=${4:+"--dbname $4"}
9
if [[ -z $PGUSER ]]; then
10
PSQL_CMD="psql geonames"
12
PSQL_CMD="psql $PGUSER_PARAM $PGPASS $PGHOST $PGPORT $PGDBNAME"
3
14
WORKPATH="$(mktemp -d)"
7
trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM
18
#trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM
9
20
# allCountries.zip contains allCountries.txt
10
21
# alternateNames.zip contains iso-languagecodes.txt alternateNames.txt
11
22
ZIPFILES="allCountries.zip alternateNames.zip"
24
35
grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
25
36
tail -n +2 timeZones.txt > timeZones.txt.tmp
29
40
DROP TABLE IF EXISTS geoname;
30
41
CREATE TABLE geoname (
33
44
asciiname varchar(200),
34
alternatenames varchar(10000),
45
alternatenames varchar(12000),
48
59
timezone varchar(40),
51
copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from '$WORKPATH/allCountries.txt' null as '';
62
\copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from $WORKPATH/allCountries.txt null as ''
53
64
DROP TABLE IF EXISTS alternatename;
54
65
CREATE TABLE alternatename (
55
66
alternatenameId int,
57
68
isoLanguage varchar(7),
58
alternateName varchar(200),
69
alternateName varchar(400),
59
70
isPreferredName boolean,
60
71
isShortName boolean,
61
72
isColloquial boolean,
64
copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from '$WORKPATH/alternateNames.txt' null as '';
75
\copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from $WORKPATH/alternateNames.txt null as '';
66
77
DROP TABLE IF EXISTS countryinfo;
67
78
CREATE TABLE countryinfo (
85
96
neighbours char(50),
86
97
equivalentFipsCode char(10)
88
copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode) from '$WORKPATH/countryInfo.txt.tmp' null as '';
99
\copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode) from $WORKPATH/countryInfo.txt.tmp null as ''
90
101
DROP TABLE IF EXISTS iso_languagecodes;
91
102
CREATE TABLE iso_languagecodes(
94
105
iso_639_1 VARCHAR(50),
95
106
language_name VARCHAR(200)
97
copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from '$WORKPATH/iso-languagecodes.txt.tmp' null as '';
108
\copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from $WORKPATH/iso-languagecodes.txt.tmp null as ''
99
110
DROP TABLE IF EXISTS admin1codes;
100
111
CREATE TABLE admin1codes (
106
copy admin1codes (code,name,nameAscii,geonameid) from '$WORKPATH/admin1CodesASCII.txt' null as '';
117
\copy admin1codes (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''
108
119
DROP TABLE IF EXISTS timeZones;
109
120
CREATE TABLE timeZones (
113
124
DST_offset numeric(3,1),
114
125
RAW_offset numeric(3,1)
116
copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from '$WORKPATH/timeZones.txt.tmp' null as '';
127
\copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from $WORKPATH/timeZones.txt.tmp null as ''
118
129
DROP TABLE IF EXISTS continentCodes;
119
130
CREATE TABLE continentCodes (
133
144
CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);
134
145
CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);
135
146
CREATE INDEX geoname_alternatename_idx ON alternatename(alternatenameId);
136
GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO geouser;
147
GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO $PGUSER;
148
GRANT SELECT ON geoname, admin1codes, countryInfo, alternatename TO public;