Ubuntu Geonames

Merge lp:~gnuoy/ubuntu-geonames/add-support-for-remote-db into lp:ubuntu-geonames

  1. add-support-for-remote-db
  2. Merge into trunk
Proposed by Liam Young
Status: Merged
Merged at revision: 26
Proposed branch: lp:~gnuoy/ubuntu-geonames/add-support-for-remote-db
Merge into: lp:ubuntu-geonames
Diff against target: 171 lines (+76/-12)
2 files modified
import-geonames.sh (+27/-12)
sphinx.conf.tmpl (+49/-0)
To merge this branch: bzr merge lp:~gnuoy/ubuntu-geonames/add-support-for-remote-db
Reviewer Review Type Date Requested Status
Dimitri John Ledkov Approve
Review via email: mp+166050@code.launchpad.net

Description of the change

These changes are to enable deploying geonames via juju. The charm is a WIP at the moment but needs the sphinx template so the credentials of the remote db can be substituted in. Since the db is on another server I've also added options to import-geonames.sh to specify host, port etc of the remote db

To post a comment you must log in.
Revision history for this message
Dimitri John Ledkov (xnox) wrote :

looks good.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'import-geonames.sh'
--- import-geonames.sh 2012-08-23 04:23:45 +0000
+++ import-geonames.sh 2013-05-28 13:39:30 +0000
@@ -1,11 +1,25 @@
1#!/bin/bash1#!/bin/bash
2 2# If DB is local pass no arguments else
3# ./import-geonames.sh -u user -h host -p port -d dbname
4while getopts u:h:p:d: flag; do
5 case $flag in
6 u) PGUSER=$OPTARG;
7 PGUSER_PARAM="--username $OPTARG";;
8 h) PGHOST="--host $OPTARG";;
9 p) PGPORT="--port $OPTARG";;
10 d) PGDBNAME="--dbname $OPTARG";;
11 esac
12done
13PGDBNAME=${PGDBNAME:="geonames"}
14PGUSER=${PGUSER:="geouser"}
15
16PSQL_CMD="psql $PGUSER_PARAM $PGPASS $PGHOST $PGPORT $PGDBNAME"
3WORKPATH="$(mktemp -d)"17WORKPATH="$(mktemp -d)"
4 18
5chmod 755 $WORKPATH19chmod 755 $WORKPATH
6cd $WORKPATH20cd $WORKPATH
7trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM21trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM
8 22pwd
9# allCountries.zip contains allCountries.txt23# allCountries.zip contains allCountries.txt
10# alternateNames.zip contains iso-languagecodes.txt alternateNames.txt24# alternateNames.zip contains iso-languagecodes.txt alternateNames.txt
11ZIPFILES="allCountries.zip alternateNames.zip"25ZIPFILES="allCountries.zip alternateNames.zip"
@@ -24,14 +38,14 @@
24grep -v '^#' countryInfo.txt > countryInfo.txt.tmp38grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
25tail -n +2 timeZones.txt > timeZones.txt.tmp39tail -n +2 timeZones.txt > timeZones.txt.tmp
26 40
27psql geonames <<EOT41$PSQL_CMD <<EOT
28BEGIN;42BEGIN;
29DROP TABLE IF EXISTS geoname;43DROP TABLE IF EXISTS geoname;
30CREATE TABLE geoname (44CREATE TABLE geoname (
31 geonameid int,45 geonameid int,
32 name varchar(200),46 name varchar(200),
33 asciiname varchar(200),47 asciiname varchar(200),
34 alternatenames varchar(10000),48 alternatenames varchar(12000),
35 latitude float,49 latitude float,
36 longitude float,50 longitude float,
37 fclass char(1),51 fclass char(1),
@@ -48,20 +62,20 @@
48 timezone varchar(40),62 timezone varchar(40),
49 moddate date63 moddate date
50);64);
51copy 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 '';65\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 ''
5266
53DROP TABLE IF EXISTS alternatename;67DROP TABLE IF EXISTS alternatename;
54CREATE TABLE alternatename (68CREATE TABLE alternatename (
55 alternatenameId int,69 alternatenameId int,
56 geonameid int,70 geonameid int,
57 isoLanguage varchar(7),71 isoLanguage varchar(7),
58 alternateName varchar(200),72 alternateName varchar(400),
59 isPreferredName boolean,73 isPreferredName boolean,
60 isShortName boolean,74 isShortName boolean,
61 isColloquial boolean,75 isColloquial boolean,
62 isHistoric boolean76 isHistoric boolean
63);77);
64copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from '$WORKPATH/alternateNames.txt' null as '';78\copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from $WORKPATH/alternateNames.txt null as '';
6579
66DROP TABLE IF EXISTS countryinfo;80DROP TABLE IF EXISTS countryinfo;
67CREATE TABLE countryinfo (81CREATE TABLE countryinfo (
@@ -85,7 +99,7 @@
85 neighbours char(50), 99 neighbours char(50),
86 equivalentFipsCode char(10)100 equivalentFipsCode char(10)
87);101);
88copy 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 '';102\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 ''
89103
90DROP TABLE IF EXISTS iso_languagecodes;104DROP TABLE IF EXISTS iso_languagecodes;
91CREATE TABLE iso_languagecodes(105CREATE TABLE iso_languagecodes(
@@ -94,7 +108,7 @@
94 iso_639_1 VARCHAR(50),108 iso_639_1 VARCHAR(50),
95 language_name VARCHAR(200)109 language_name VARCHAR(200)
96);110);
97copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from '$WORKPATH/iso-languagecodes.txt.tmp' null as '';111\copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from $WORKPATH/iso-languagecodes.txt.tmp null as ''
98112
99DROP TABLE IF EXISTS admin1codes;113DROP TABLE IF EXISTS admin1codes;
100CREATE TABLE admin1codes (114CREATE TABLE admin1codes (
@@ -103,7 +117,7 @@
103 nameAscii TEXT,117 nameAscii TEXT,
104 geonameid int118 geonameid int
105);119);
106copy admin1codes (code,name,nameAscii,geonameid) from '$WORKPATH/admin1CodesASCII.txt' null as '';120\copy admin1codes (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''
107121
108DROP TABLE IF EXISTS timeZones;122DROP TABLE IF EXISTS timeZones;
109CREATE TABLE timeZones (123CREATE TABLE timeZones (
@@ -113,7 +127,7 @@
113 DST_offset numeric(3,1),127 DST_offset numeric(3,1),
114 RAW_offset numeric(3,1)128 RAW_offset numeric(3,1)
115);129);
116copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from '$WORKPATH/timeZones.txt.tmp' null as '';130\copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from $WORKPATH/timeZones.txt.tmp null as ''
117131
118DROP TABLE IF EXISTS continentCodes;132DROP TABLE IF EXISTS continentCodes;
119CREATE TABLE continentCodes (133CREATE TABLE continentCodes (
@@ -133,6 +147,7 @@
133CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);147CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);
134CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);148CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);
135CREATE INDEX geoname_alternatename_idx ON alternatename(alternatenameId);149CREATE INDEX geoname_alternatename_idx ON alternatename(alternatenameId);
136GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO geouser;150GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO $PGUSER;
151GRANT SELECT ON geoname, admin1codes, countryInfo, alternatename TO public;
137COMMIT;152COMMIT;
138EOT153EOT
139154
=== added file 'sphinx.conf.tmpl'
--- sphinx.conf.tmpl 1970-01-01 00:00:00 +0000
+++ sphinx.conf.tmpl 2013-05-28 13:39:30 +0000
@@ -0,0 +1,49 @@
1source geonamessrc
2{{
3type = pgsql
4sql_host = {host}
5sql_user = {user}
6sql_pass = {password}
7sql_db = {database}
8sql_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'
9sql_attr_uint = population
10}}
11
12source altnamessrc
13{{
14type = pgsql
15sql_host = {host}
16sql_user = {user}
17sql_pass = {password}
18sql_db = {database}
19sql_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))
20sql_attr_uint = population
21}}
22
23index geonames
24{{
25 source = geonamessrc
26 source = altnamessrc
27 path = /var/lib/sphinxsearch/data/geonames
28 docinfo = extern
29 morphology = none
30 stopwords =
31 min_word_len = 2
32 min_prefix_len = 0
33 min_infix_len = 0
34 charset_type = utf-8
35 charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+4E00..U+9FFF, U+3000..U+30FF
36}}
37searchd
38{{
39port = 3312
40log = /var/log/sphinxsearch/searchd.log
41query_log = /var/log/sphinxsearch/query.log
42read_timeout = 5
43max_children = 30
44pid_file = {sphinx_pid_file_location}
45max_matches = 1000
46seamless_rotate = 1
47preopen_indexes = 0
48unlink_old = 1
49}}

Subscribers

People subscribed via source and target branches

to all changes: