Merge lp:~xnox/ubuntu-geonames/fix-duplicates into lp:ubuntu-geonames

[Dimitri John Ledkov]

* This fixes database import due to data format changes
* Also fixes documentation and sample config
* This updates geoname-modpython.py query
* This does not _fix_ cherrypy version geoname.py (as it appears to not be used / broken ?!)

Instead of doing sql query for each potential location, we build the two location lists (for normal and alternative names) and execute a single query across both tables with a UNION. As a side-effect, UNION removes duplicate entries. Thus instead of 20 New Yorks, we get only 4.

If you are deploying this, please note:
- Due to database changes it's probably best to drop the whole database, recreate it & re-import the dataset.
- And don't forget the re-index with sphinx.

[Evan (ev)]

Yes, the cherrypy version was the initially developed one, which was dropped for the mod_python variant.

[Evan (ev)]

Looks good! Please file an RT for deployment.

review: Approve

=== modified file 'README'
--- README	2010-09-03 07:35:55 +0000
+++ README	2012-08-23 04:40:25 +0000
@@ -1,12 +1,12 @@
 - Install postgres and create a 'geonames' database.
-- Download Geonames from http://download.geonames.org/export/dump
+- Create a geouser account with password geopw
 - Run import-geonames.sh
-- Create a geouser account with password geopw, grant permissions on the table:
-  GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo TO geouser
-  (slightly excessive, it just needs to query)
-- Install and configure Sphinx (/etc/sphinxsearch/sphinx.conf ) from universe.
-- Create an index on geonameid which should reduce query times:
-  CREATE INDEX geoname_id_idx ON geoname(geonameid);
+  + it downloads & populates the database
+  + grants slightly excessive permissions to geouser for queries
+- Install sphinxsearch and configure (/etc/sphinxsearch/sphinx.conf ) from universe.
+  + run $ sudo indexer geonames
+  + Enable /etc/default/sphinxsearch
+  + start $ sudo service sphinxsearch start
 - Install sphinxapi-python from ppa:~ev and use or modify the sphinx.conf
   provided.
 - Install CherryPy from universe.

=== modified file 'geoname-modpython.py'
--- geoname-modpython.py	2011-04-06 20:33:16 +0000
+++ geoname-modpython.py	2012-08-23 04:40:25 +0000
@@ -5,11 +5,12 @@
 statement = "SELECT geoname.name, admin1codes.name, countryInfo.name, \
 geoname.longitude, geoname.latitude FROM admin1codes, geoname, countryInfo \
 WHERE code = geoname.country||'.'||geoname.admin1 AND \
-countryInfo.iso_alpha2=geoname.country AND geoname.geonameid=%s;"
-altstatement = "SELECT alternatename.alternateName, admin1codes.name, countryInfo.name, \
+countryInfo.iso_alpha2=geoname.country AND geoname.geonameid in %s \
+UNION \
+SELECT alternatename.alternateName, admin1codes.name, countryInfo.name, \
 geoname.longitude, geoname.latitude FROM alternatename, admin1codes, geoname, countryInfo \
 WHERE code = geoname.country||'.'||geoname.admin1 AND \
-countryInfo.iso_alpha2=geoname.country AND alternatename.geonameid=geoname.geonameid AND alternatename.alternatenameId=%s;"
+countryInfo.iso_alpha2=geoname.country AND alternatename.geonameid=geoname.geonameid AND alternatename.alternatenameId in %s;"
 authstring = 'dbname=geonames user=geouser password=geopw host=localhost'
 jsonheader = '['
 jsonfooter = ']'
@@ -31,17 +32,23 @@
             connection = psycopg2.connect(authstring)
             cursor = connection.cursor()
             try:
+                # We need at least one value for the sql in operator
+                # and there are no locations with id 0
+                statement_ids = [0]
+                altstatement_ids = [0]
                 for x in result:
                     rawid = x['id']
                     idval = rawid / 10
                     idtype = rawid % 10
                     if idtype == 1:
-                        cursor.execute(statement % idval)
+                        statement_ids.append(idval)
                     else:
-                        cursor.execute(altstatement % idval)
-                    record = cursor.fetchone()
-                    if record:
-                        ret.append(jsonentry % record)
+                        altstatement_ids.append(idval)
+
+                cursor.execute(statement, (tuple(statement_ids), tuple(altstatement_ids)))
+                records = cursor.fetchall()
+                for record in records:
+                    ret.append(jsonentry % record)
             finally:
                 cursor.close()
                 connection.close()

=== modified file 'import-geonames.sh'
--- import-geonames.sh	2011-04-07 15:10:15 +0000
+++ import-geonames.sh	2012-08-23 04:40:25 +0000
@@ -9,7 +9,7 @@
 # allCountries.zip contains allCountries.txt
 # alternateNames.zip contains iso-languagecodes.txt alternateNames.txt
 ZIPFILES="allCountries.zip alternateNames.zip"
-TXTFILES="admin1Codes.txt admin1CodesASCII.txt countryInfo.txt timeZones.txt"
+TXTFILES="admin1CodesASCII.txt countryInfo.txt timeZones.txt"
 for i in $ZIPFILES $TXTFILES
 do
 	wget "http://download.geonames.org/export/dump/$i"
@@ -24,14 +24,14 @@
 grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
 tail -n +2 timeZones.txt > timeZones.txt.tmp
  
-sudo -u postgres psql geonames <<EOT
+psql geonames <<EOT
 BEGIN;
 DROP TABLE IF EXISTS geoname;
 CREATE TABLE geoname (
 	geonameid int,
 	name varchar(200),
 	asciiname varchar(200),
-	alternatenames varchar(6000),
+	alternatenames varchar(10000),
 	latitude float,
 	longitude float,
 	fclass char(1),
@@ -57,9 +57,11 @@
 	isoLanguage varchar(7),
 	alternateName varchar(200),
 	isPreferredName boolean,
-	isShortName boolean
+	isShortName boolean,
+	isColloquial boolean,
+	isHistoric boolean
 );
-copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName) from '$WORKPATH/alternateNames.txt' null as '';
+copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from '$WORKPATH/alternateNames.txt' null as '';
 
 DROP TABLE IF EXISTS countryinfo;
 CREATE TABLE countryinfo (
@@ -94,29 +96,24 @@
 );
 copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from '$WORKPATH/iso-languagecodes.txt.tmp' null as '';
 
-DROP TABLE IF EXISTS admin1Codes;
-CREATE TABLE admin1Codes (
-	code varchar(10),
-	name TEXT
-);
-copy admin1Codes (code, name) from '$WORKPATH/admin1Codes.txt' null as '';
-
-DROP TABLE IF EXISTS admin1CodesAscii;
-CREATE TABLE admin1CodesAscii (
+DROP TABLE IF EXISTS admin1codes;
+CREATE TABLE admin1codes (
 	code varchar(10),
 	name TEXT,
 	nameAscii TEXT,
 	geonameid int
 );
-copy admin1CodesAscii (code,name,nameAscii,geonameid) from '$WORKPATH/admin1CodesASCII.txt' null as '';
+copy admin1codes (code,name,nameAscii,geonameid) from '$WORKPATH/admin1CodesASCII.txt' null as '';
 
 DROP TABLE IF EXISTS timeZones;
 CREATE TABLE timeZones (
+	code CHAR(2),
 	timeZoneId VARCHAR(200),
 	GMT_offset numeric(3,1),
-	DST_offset numeric(3,1)
+	DST_offset numeric(3,1),
+	RAW_offset numeric(3,1)
 );
-copy timeZones (timeZoneId,GMT_offset,DST_offset) from '$WORKPATH/timeZones.txt.tmp' null as '';
+copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from '$WORKPATH/timeZones.txt.tmp' null as '';
 
 DROP TABLE IF EXISTS continentCodes;
 CREATE TABLE continentCodes (

=== modified file 'sphinx.conf'
--- sphinx.conf	2011-04-07 13:26:08 +0000
+++ sphinx.conf	2012-08-23 04:40:25 +0000
@@ -41,7 +41,7 @@
 query_log			= /var/log/sphinxsearch/query.log
 read_timeout		= 5
 max_children		= 30
-pid_file			= /var/run/searchd.pid
+pid_file			= /var/run/sphinxsearch/searchd.pid
 max_matches			= 1000
 seamless_rotate		= 1
 preopen_indexes		= 0