Merge lp:~timkuhlman/ubuntu-geonames/column-size-fiz into lp:ubuntu-geonames

[Tim Kuhlman]

Updated the DB schema to match the latest data and fixed the geoname-modpython.py syntax errors.

[Evan (ev)]

Wow, how did that ever work?

Looks sensible. +1

review: Approve

[Evan (ev)]

Have you thought of incorporating Stuart's suggestion, though?

https://bugs.launchpad.net/ubuntu-geonames/+bug/1517255/comments/1

I see no reason to stick to varchar.

[Tim Kuhlman (timkuhlman)]

> Have you thought of incorporating Stuart's suggestion, though?
>
> https://bugs.launchpad.net/ubuntu-geonames/+bug/1517255/comments/1
>
> I see no reason to stick to varchar.

I looked at that then found http://download.geonames.org/export/dump/readme.txt where it says the size should be 200. I think either will work but figured I would just stick to the recommended size unless there was a reason to diverge. Less to pop up in a diff and wonder about next someone looks.

=== modified file 'geoname-modpython.py'
--- geoname-modpython.py	2014-08-14 08:49:43 +0000
+++ geoname-modpython.py	2015-12-17 00:20:05 +0000
@@ -44,7 +44,7 @@
 jsonheader = '['
 jsonfooter = ']'
 jsonentry = '{"name" : "%s", "admin1" : "%s", "admin2" : "%s", "country" : "%s", ' \
-            '"longitude" : "%F", "latitude" : "%F" , '"timezone" : "%s" }'
+            '"longitude" : "%F", "latitude" : "%F" , "timezone" : "%s" }'
 
 def handler(req):
     fs = util.FieldStorage(req)

=== modified file 'import-geonames.sh'
--- import-geonames.sh	2014-08-14 08:49:43 +0000
+++ import-geonames.sh	2015-12-17 00:20:05 +0000
@@ -53,7 +53,7 @@
 	fclass char(1),
 	fcode varchar(10),
 	country varchar(2),
-	cc2 varchar(60),
+	cc2 varchar(200),
 	admin1 varchar(20),
 	admin2 varchar(80),
 	admin3 varchar(20),
@@ -93,12 +93,12 @@
 	tld char(3),
 	currency char(3),
 	currencyName character varying(20),
-	Phone char(20), 
-	postalCodeFormat char(60), 
-	postalCodeRegex char(200), 
-	languages character varying(200), 
+	Phone char(20),
+	postalCodeFormat char(60),
+	postalCodeRegex char(200),
+	languages character varying(200),
 	geonameId int,
-	neighbours char(50), 
+	neighbours char(50),
 	equivalentFipsCode char(10)
 );
 \copy countryInfo${LOAD_POSTFIX} (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 ''
@@ -114,7 +114,7 @@
 
 DROP TABLE IF EXISTS admin1codes${LOAD_POSTFIX};
 CREATE TABLE admin1codes${LOAD_POSTFIX} (
-	code varchar(10),
+	code varchar(20),
 	name TEXT,
 	nameAscii TEXT,
 	geonameid int
@@ -214,7 +214,7 @@
 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 ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO "$PGUSER";
 GRANT SELECT ON geoname, admin1codes, admin2codes, countryInfo, alternatename TO public;
 COMMIT;
 EOT