Generate db file

Generate db file with cities

Download datas

Download cities15000.txt and alternateNames.txt from http://download.geonames.org/export/dump/

Importing datas in database

Open sqlite3 with command line and execute the following commands:

sqlite> CREATE TABLE cities(_id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, asciiname TEXT NOT NULL, alternatenames TEXT, latitude REAL NOT NULL, longitude REAL NOT NULL, feature_class TEXT, feature_code TEXT, country_code TEXT, cc2 TEXT, admin1_code TEXT, admin2_code TEXT, admin3_code TEXT, admin4_code TEXT, population INTEGER, elevation INTEGER, dem INTEGER, timezone TEXT, modification_date TEXT);
sqlite> CREATE TABLE alternateNames(_id INTEGER NOT NULL PRIMARY KEY, geonameid INTEGER NOT NULL, isolanguage TEXT, alternate_name TEXT, isPrefferredName INTEGER, isShortName INTEGER, isColloquial INTEGER, isHistoric INTEGER, FOREIGN KEY(geonameid) REFERENCES cities(_id));
sqlite> .mode csv
sqlite> .separator "\t"
sqlite> .import cities15000.txt cities
sqlite> .import alternateNames.txt alternateNames
sqlite> DELETE FROM alternateNames WHERE isolanguage NOT IN ('fr', 'fra', 'fre', 'ar', 'ara', 'en', 'eng', 'id', 'ind');
sqlite> DELETE FROM alternateNames WHERE geonameid NOT IN (SELECT DISTINCT _id FROM cities);
sqlite> VACUUM;
sqlite> .save cities15000.sqlite
sqlite> .quit