from __future__ import absolute_import
from __future__ import print_function
import six
import csv
import unicodecsv
import re
import sys
from six import BytesIO
from zipfile import ZipFile
from six.moves.urllib import request
from six.moves.urllib.error import URLError
from ..get_database_connection import get_database_connection
from ..utils import parse_number, batched, normalize_text
GEONAMES_ZIP_URL = "http://download.geonames.org/export/dump/allCountries.zip"
geonames_field_mappings = [
('geonameid', 'text primary key'),
('name', 'text'),
('asciiname', 'text'),
('alternatenames', None),
('latitude', 'real'),
('longitude', 'real'),
('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', None),
('dem', None),
('timezone', None),
('modification_date', None)
]
def read_geonames_csv():
print("Downloading geoname data from: " + GEONAMES_ZIP_URL)
try:
url = request.urlopen(GEONAMES_ZIP_URL)
except URLError:
print("If you are operating behind a firewall, try setting the HTTP_PROXY/HTTPS_PROXY environment variables.")
raise
zipfile = ZipFile(BytesIO(url.read()))
print("Download complete")
# Loading geonames data may cause errors without setting csv.field_size_limit:
if sys.platform == "win32":
max_c_long_on_windows = (2**32 / 2) - 1
csv.field_size_limit(max_c_long_on_windows)
else:
csv.field_size_limit(sys.maxint if six.PY2 else six.MAXSIZE)
with zipfile.open('allCountries.txt') as f:
reader = unicodecsv.DictReader(f,
fieldnames=[
k for k, v in geonames_field_mappings],
encoding='utf-8',
delimiter='\t',
quoting=csv.QUOTE_NONE)
for d in reader:
d['population'] = parse_number(d['population'], 0)
d['latitude'] = parse_number(d['latitude'], 0)
d['longitude'] = parse_number(d['longitude'], 0)
if len(d['alternatenames']) > 0:
d['alternatenames'] = d['alternatenames'].split(',')
else:
d['alternatenames'] = []
yield d
def import_geonames(drop_previous=False):
connection = get_database_connection(create_database=True)
cur = connection.cursor()
if drop_previous:
print("Dropping geonames data...")
cur.execute("""DROP TABLE IF EXISTS 'geonames'""")
cur.execute("""DROP TABLE IF EXISTS 'alternatenames'""")
cur.execute("""DROP TABLE IF EXISTS 'alternatename_counts'""")
cur.execute("""DROP INDEX IF EXISTS 'alternatename_index'""")
cur.execute("""DROP TABLE IF EXISTS 'adminnames'""")
table_exists = len(list(cur.execute("""SELECT name FROM sqlite_master
WHERE type='table' AND name='geonames'"""))) > 0
if table_exists:
print("The geonames table already exists. "
"Run this again with --drop-previous to recreate it.")
return
# Create table
cur.execute("CREATE TABLE geonames (" + ",".join([
'"' + k + '" ' + sqltype
for k, sqltype in geonames_field_mappings if sqltype]) + ")")
cur.execute('''CREATE TABLE alternatenames
(geonameid text, alternatename text, alternatename_lemmatized text)''')
cur.execute('''CREATE TABLE adminnames
(name text,
country_code text, admin1_code text, admin2_code text, admin3_code text,
PRIMARY KEY (country_code, admin1_code, admin2_code, admin3_code))''')
i = 0
geonames_insert_command = 'INSERT INTO geonames VALUES (' + ','.join([
'?' for x, sqltype in geonames_field_mappings if sqltype]) + ')'
alternatenames_insert_command = 'INSERT INTO alternatenames VALUES (?, ?, ?)'
adminnames_insert_command = 'INSERT OR IGNORE INTO adminnames VALUES (?, ?, ?, ?, ?)'
for batch in batched(read_geonames_csv()):
geoname_tuples = []
alternatename_tuples = []
adminname_tuples = []
for geoname in batch:
i += 1
total_row_estimate = 11000000
if i % (total_row_estimate / 40) == 0:
print(i, '/', total_row_estimate, '+ geonames imported')
connection.commit()
if re.match(r"ADM[1-3]$", geoname['feature_code']) or re.match(r"PCL[IH]$", geoname['feature_code']):
adminname_tuples.append((
geoname['name'],
geoname['country_code'],
geoname['admin1_code'],
geoname['admin2_code'],
geoname['admin3_code'],))
geoname_tuples.append(
tuple(geoname[field]
for field, sqltype in geonames_field_mappings
if sqltype))
for possible_name in set([geoname['name'], geoname['asciiname']] + geoname['alternatenames']):
normalized_name = normalize_text(possible_name)
# require at least 2 word characters.
if re.match(r"(.*\w){2,}", normalized_name):
alternatename_tuples.append((
geoname['geonameid'],
possible_name,
normalized_name.lower()))
cur.executemany(geonames_insert_command, geoname_tuples)
cur.executemany(alternatenames_insert_command, alternatename_tuples)
cur.executemany(adminnames_insert_command, adminname_tuples)
print("Creating indexes...")
cur.execute('''
CREATE INDEX alternatename_index
ON alternatenames (alternatename_lemmatized);
''')
connection.commit()
cur.execute('''CREATE TABLE alternatename_counts
(geonameid text primary key, count integer)''')
cur.execute('''
INSERT INTO alternatename_counts
SELECT geonameid, count(alternatename)
FROM geonames INNER JOIN alternatenames USING ( geonameid )
GROUP BY geonameid
''')
connection.commit()
connection.close()
if __name__ == '__main__':
import argparse
parser = argparse.ArgumentParser()
parser.add_argument(
"--drop-previous", dest='drop_previous', action='store_true')
parser.set_defaults(drop_previous=False)
args = parser.parse_args()
import_geonames(args.drop_previous)
