Merge lp:~popey/ubuntu-geonames/fix-1326918 into lp:ubuntu-geonames

[Alan Pope 🍺🐧🐱 🦄]

Adding timezoneid to json output which is needed by the new phone clock app. Tested on own server running at http://146.185.188.87/ - e.g. http://146.185.188.87/?query=Farnborough

Also added support for admin2 field which required modification to the import shell script because we previously weren't downloading and importing the admin2codes dump file.

You can see the result of the admin2 change at http://146.185.188.87/test/?query=Farnborough

[lp:~popey/ubuntu-geonames/fix-1326918]

31. By Alan Pope 🍺🐧🐱 🦄 on 2014-08-14

Add admin2codes support in import and web service

[Evan (ev)]

Looks good

review: Approve

=== modified file 'geoname-modpython.py'
--- geoname-modpython.py	2013-04-04 20:27:55 +0000
+++ geoname-modpython.py	2014-08-14 08:49:50 +0000
@@ -11,35 +11,40 @@
 SELECT
 geoname.name,
 admin1codes.name,
+admin2codes.name,
 countryInfo.name,
 geoname.longitude,
 geoname.latitude,
+geoname.timezone,
 geoname.population
 FROM geoname
 left join countryInfo on (geoname.country = countryInfo.iso_alpha2)
 left join admin1codes on (admin1codes.code = geoname.country||'.'||geoname.admin1)
+left join admin2codes on (admin2codes.code = geoname.country||'.'||geoname.admin1||'.'||geoname.admin2)
 WHERE geoname.geonameid in %s
 UNION
 SELECT
 alternatename.alternatename,
 admin1codes.name,
+admin2codes.name,
 countryInfo.name,
 geoname.longitude,
 geoname.latitude,
+geoname.timezone,
 geoname.population
 FROM
 alternatename
 left join geoname on (geoname.geonameid=alternatename.geonameid)
 left join countryInfo on (geoname.country = countryInfo.iso_alpha2)
 left join admin1codes on (admin1codes.code = geoname.country||'.'||geoname.admin1)
+left join admin2codes on (admin2codes.code = geoname.country||'.'||geoname.admin1||'.'||geoname.admin2)
 where alternatename.alternatenameId in %s
 ORDER by population desc;
 """
 jsonheader = '['
 jsonfooter = ']'
-jsonentry = '{"name" : "%s", "admin1" : "%s", "country" : "%s", ' \
-            '"longitude" : "%F", "latitude" : "%F" }'
-
+jsonentry = '{"name" : "%s", "admin1" : "%s", "admin2" : "%s", "country" : "%s", ' \
+            '"longitude" : "%F", "latitude" : "%F" , '"timezone" : "%s" }'
 
 def handler(req):
     fs = util.FieldStorage(req)
 
=== modified file 'import-geonames.sh'
--- import-geonames.sh	2013-09-13 15:51:45 +0000
+++ import-geonames.sh	2014-08-14 08:49:50 +0000
@@ -15,15 +15,15 @@
 
 PSQL_CMD="psql $PGUSER_PARAM $PGPASS $PGHOST $PGPORT $PGDBNAME"
 WORKPATH="$(mktemp -d)"
- 
+
 chmod 755 $WORKPATH
 cd $WORKPATH
 trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM
-pwd 
+pwd
 # allCountries.zip contains allCountries.txt
 # alternateNames.zip contains iso-languagecodes.txt alternateNames.txt
 ZIPFILES="allCountries.zip alternateNames.zip"
-TXTFILES="admin1CodesASCII.txt countryInfo.txt timeZones.txt"
+TXTFILES="admin1CodesASCII.txt admin2Codes.txt countryInfo.txt timeZones.txt"
 for i in $ZIPFILES $TXTFILES
 do
 	wget "http://download.geonames.org/export/dump/$i"
@@ -32,7 +32,7 @@
 do
 	unzip -o -qq $i
 done
- 
+
 # alter files for import
 tail -n +2 iso-languagecodes.txt > iso-languagecodes.txt.tmp
 grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
@@ -121,6 +121,15 @@
 );
 \copy admin1codes${LOAD_POSTFIX} (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''
 
+DROP TABLE IF EXISTS admin2codes${LOAD_POSTFIX};
+CREATE TABLE admin2codes${LOAD_POSTFIX} (
+        code varchar(80),
+        name TEXT,
+        nameAscii TEXT,
+        geonameid int
+);
+\copy admin2codes${LOAD_POSTFIX} (code,name,nameAscii,geonameid) from $WORKPATH/admin2Codes.txt null as ''
+
 DROP TABLE IF EXISTS timeZones${LOAD_POSTFIX};
 CREATE TABLE timeZones${LOAD_POSTFIX} (
 	code CHAR(2),
@@ -146,10 +155,12 @@
 INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('AN', 'Antarctica', 6255152);
 CREATE INDEX geoname_id_idx${LOAD_POSTFIX} ON geoname${LOAD_POSTFIX}(geonameid);
 CREATE INDEX geoname_admin1codes_code_idx${LOAD_POSTFIX} ON admin1codes${LOAD_POSTFIX}(code);
+CREATE INDEX geoname_admin2codes_code_idx${LOAD_POSTFIX} ON admin2codes${LOAD_POSTFIX}(code);
 CREATE INDEX geoname_countryinfo_isoalpha2_idx${LOAD_POSTFIX} ON countryinfo${LOAD_POSTFIX}(iso_alpha2);
 CREATE INDEX geoname_alternatename_idx${LOAD_POSTFIX} ON alternatename${LOAD_POSTFIX}(alternatenameId);
 ANALYZE geoname${LOAD_POSTFIX};
 ANALYZE admin1codes${LOAD_POSTFIX};
+ANALYZE admin2codes${LOAD_POSTFIX};
 ANALYZE countryinfo${LOAD_POSTFIX};
 ANALYZE alternatename${LOAD_POSTFIX};
 COMMIT;
@@ -167,10 +178,12 @@
 DROP TABLE IF EXISTS countryinfo${BACKUP_POSTFIX};
 DROP TABLE IF EXISTS iso_languagecodes${BACKUP_POSTFIX};
 DROP TABLE IF EXISTS admin1codes${BACKUP_POSTFIX};
+DROP TABLE IF EXISTS admin2codes${BACKUP_POSTFIX};                                                                                                                                                                                                  
 DROP TABLE IF EXISTS timeZones${BACKUP_POSTFIX};
 DROP TABLE IF EXISTS continentCodes${BACKUP_POSTFIX};
 DROP INDEX IF EXISTS geoname_id_idx;
 DROP INDEX IF EXISTS geoname_admin1codes_code_idx;
+DROP INDEX IF EXISTS geoname_admin2codes_code_idx;
 DROP INDEX IF EXISTS geoname_countryinfo_isoalpha2_idx;
 DROP INDEX IF EXISTS geoname_alternatename_idx;
 ALTER TABLE geoname RENAME TO geoname${BACKUP_POSTFIX};
@@ -178,6 +191,7 @@
 ALTER TABLE countryinfo RENAME TO countryinfo${BACKUP_POSTFIX};
 ALTER TABLE iso_languagecodes RENAME TO iso_languagecodes${BACKUP_POSTFIX};
 ALTER TABLE admin1codes RENAME TO admin1codes${BACKUP_POSTFIX};
+ALTER TABLE admin2codes RENAME TO admin2codes${BACKUP_POSTFIX};
 ALTER TABLE timeZones RENAME TO timeZones${BACKUP_POSTFIX};
 ALTER TABLE continentCodes RENAME TO continentCodes${BACKUP_POSTFIX};
 COMMIT;
@@ -192,13 +206,15 @@
 ALTER TABLE countryinfo${LOAD_POSTFIX} RENAME TO countryinfo;
 ALTER TABLE iso_languagecodes${LOAD_POSTFIX} RENAME TO iso_languagecodes;
 ALTER TABLE admin1codes${LOAD_POSTFIX} RENAME TO admin1codes;
+ALTER TABLE admin2codes${LOAD_POSTFIX} RENAME TO admin2codes;
 ALTER TABLE timeZones${LOAD_POSTFIX} RENAME TO timeZones;
 ALTER TABLE continentCodes${LOAD_POSTFIX} RENAME TO continentCodes;
 ALTER INDEX geoname_id_idx${LOAD_POSTFIX} RENAME TO geoname_id_idx;
 ALTER INDEX geoname_admin1codes_code_idx${LOAD_POSTFIX} RENAME TO geoname_admin1codes_code_idx;
+ALTER INDEX geoname_admin2codes_code_idx${LOAD_POSTFIX} RENAME TO geoname_admin2codes_code_idx;
 ALTER INDEX geoname_countryinfo_isoalpha2_idx${LOAD_POSTFIX} RENAME TO geoname_countryinfo_isoalpha2_idx;
 ALTER INDEX geoname_alternatename_idx${LOAD_POSTFIX} RENAME TO geoname_alternatename_idx;
 GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO $PGUSER;
-GRANT SELECT ON geoname, admin1codes, countryInfo, alternatename TO public;
+GRANT SELECT ON geoname, admin1codes, admin2codes, countryInfo, alternatename TO public;
 COMMIT;
 EOT