def load_regions(cursor): cursor.execute("""CREATE TABLE IF NOT EXISTS regions ( region VARCHAR(64), PRIMARY KEY(region), region_code CHAR(4), country_code CHAR(2), lat FLOAT, lon FLOAT, last_word VARCHAR(32), INDEX(last_word(10))); """) reader = csv.reader(open(geodict_config.source_folder+'us_statepositions.csv', 'rb')) us_state_positions = {} for row in reader: try: region_code = row[0] lat = row[1] lon = row[2] except: continue us_state_positions[region_code] = { 'lat': lat, 'lon': lon } reader = csv.reader(open(geodict_config.source_folder+'us_statenames.csv', 'rb')) country_code = 'US' for row in reader: try: region_code = row[0] state_names = row[2] except: continue state_names_list = state_names.split('|') lat = us_state_positions[region_code]['lat'] lon = us_state_positions[region_code]['lon'] for state_name in state_names_list: state_name = state_name.strip() last_word, index, skipped = geodict_lib.pull_word_from_end(state_name, len(state_name)-1) cursor.execute(""" INSERT IGNORE INTO regions (region, region_code, country_code, lat, lon, last_word) values (%s, %s, %s, %s, %s, %s) """, (state_name, region_code, country_code, lat, lon, last_word))
def load_countries(cursor): cursor.execute("""CREATE TABLE IF NOT EXISTS countries ( country VARCHAR(64), PRIMARY KEY(country), country_code CHAR(2), lat FLOAT, lon FLOAT, last_word VARCHAR(32), INDEX(last_word(10))); """) reader = csv.reader(open(geodict_config.source_folder+'countrypositions.csv', 'rb')) country_positions = {} for row in reader: try: country_code = row[0] lat = row[1] lon = row[2] except: continue country_positions[country_code] = { 'lat': lat, 'lon': lon } reader = csv.reader(open(geodict_config.source_folder+'countrynames.csv', 'rb')) for row in reader: try: country_code = row[0] country_names = row[1] except: continue country_names_list = country_names.split(' | ') lat = country_positions[country_code]['lat'] lon = country_positions[country_code]['lon'] for country_name in country_names_list: country_name = country_name.strip() last_word, index, skipped = geodict_lib.pull_word_from_end(country_name, len(country_name)-1) cursor.execute(""" INSERT IGNORE INTO countries (country, country_code, lat, lon, last_word) values (%s, %s, %s, %s, %s) """, (country_name, country_code, lat, lon, last_word))
def load_cities(cursor): cursor.execute("""CREATE TABLE IF NOT EXISTS cities ( city VARCHAR(80), country CHAR(2), PRIMARY KEY(city, country), region_code CHAR(2), population INT, lat FLOAT, lon FLOAT, last_word VARCHAR(32), INDEX(last_word(10))); """) reader = csv.reader(open(geodict_config.source_folder+'worldcitiespop.csv', 'rb')) for row in reader: try: country = row[0] city = row[1] region_code = row[3] population = row[4] lat = row[5] lon = row[6] except: continue if population is '': population = 0 city = city.strip() last_word, index, skipped = geodict_lib.pull_word_from_end(city, len(city)-1) cursor.execute(""" INSERT IGNORE INTO cities (city, country, region_code, population, lat, lon, last_word) values (%s, %s, %s, %s, %s, %s, %s) """, (city, country, region_code, population, lat, lon, last_word))