Merge lp:~mterry/ubuntu-geonames/search-alt into lp:ubuntu-geonames

[Michael Terry]

[Karl Lattimer (karl-qdh)]

Tested here and this branch adds the necessary indexes for alternate names.

review: Approve

[Evan (ev)]

Looks good! Testing with Shanghai (上海) returns the correct result here. Thanks a bunch!

review: Approve

=== modified file 'geoname-modpython.py'
--- geoname-modpython.py	2011-04-05 19:00:42 +0000
+++ geoname-modpython.py	2011-04-06 20:34:50 +0000
@@ -6,6 +6,10 @@
 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, \
+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;"
 authstring = 'dbname=geonames user=geouser password=geopw host=localhost'
 jsonheader = '['
 jsonfooter = ']'
@@ -28,7 +32,13 @@
             cursor = connection.cursor()
             try:
                 for x in result:
-                    cursor.execute(statement % x['id'])
+                    rawid = x['id']
+                    idval = rawid / 10
+                    idtype = rawid % 10
+                    if idtype == 1:
+                        cursor.execute(statement % idval)
+                    else:
+                        cursor.execute(altstatement % idval)
                     record = cursor.fetchone()
                     if record:
                         ret.append(jsonentry % record)

=== modified file 'import-geonames.sh'
--- import-geonames.sh	2011-04-06 13:10:44 +0000
+++ import-geonames.sh	2011-04-06 20:34:50 +0000
@@ -135,6 +135,7 @@
 CREATE INDEX geoname_id_idx ON geoname(geonameid);
 CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);
 CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);
-GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo TO geouser;
+CREATE INDEX geoname_alternatename_idx ON alternatename(alternatenameId);
+GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO geouser;
 COMMIT;
 EOT

=== modified file 'sphinx.conf'
--- sphinx.conf	2011-04-05 19:17:07 +0000
+++ sphinx.conf	2011-04-06 20:34:50 +0000
@@ -5,13 +5,25 @@
 sql_user = geouser
 sql_pass = geopw
 sql_db = geonames
-sql_query = SELECT geoname.geonameid, geoname.name, geoname.alternatenames, geoname.population AS population FROM geoname WHERE geoname.fclass='P'
+sql_query = SELECT geoname.geonameid*10+1 AS id, geoname.name AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM geoname WHERE geoname.fclass='P'
+sql_attr_uint = population
+}
+
+source altnamessrc
+{
+type = pgsql
+sql_host = localhost
+sql_user = geouser
+sql_pass = geopw
+sql_db = geonames
+sql_query = SELECT alternatename.alternatenameId*10+2 AS id, alternatename.alternateName AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM alternatename JOIN geoname on (geoname.geonameid = alternatename.geonameid AND geoname.name != alternatename.alternateName) WHERE (geoname.fclass='P' AND (LENGTH(alternatename.isoLanguage) < 4 OR alternatename.isoLanguage is null))
 sql_attr_uint = population
 }
 
 index geonames
 {
 	source			= geonamessrc
+	source			= altnamessrc
 	path			= /var/lib/sphinxsearch/data/geonames
 	docinfo			= extern
 	morphology			= none