def get_admin1_id(self, place: Loc):
"""Search for Admin1 entry"""
lookup_target = place.admin1_name
if len(lookup_target) == 0:
return
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="name = ? AND country = ? AND f_code = ? ",
args=(lookup_target, place.country_iso, 'ADM1'),
result=Result.STRONG_MATCH),
Query(where="name LIKE ? AND country = ? AND f_code = ?",
args=(lookup_target, place.country_iso, 'ADM1'),
result=Result.WILDCARD_MATCH),
Query(where="name = ? AND f_code = ?",
args=(lookup_target, 'ADM1'),
result=Result.SOUNDEX_MATCH)
]
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
place.admin1_id = row_list[0][Entry.ADM1]
# Fill in Country ISO
if place.country_iso == '':
place.country_iso = row_list[0][Entry.ISO]
def get_admin2_name(self, place: Loc) -> str:
"""Search for Admin1 entry"""
lookup_target = place.admin2_id
if len(lookup_target) == 0:
return ''
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="admin2_id = ? AND country = ? AND admin1_id = ?",
args=(lookup_target, place.country_iso, place.admin1_id),
result=Result.STRONG_MATCH),
Query(where="admin2_id = ? AND country = ?",
args=(lookup_target, place.country_iso),
result=Result.PARTIAL_MATCH)
]
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
row = row_list[0]
place.admin2_name = row[Entry.NAME]
# self.logger.debug(f'adm2 nm = {place.admin2_name}')
return place.admin2_name
else:
return ''
def select_admin1(self, place: Loc):
"""Search for Admin1 entry"""
lookup_target = place.admin1_name
#pattern = self.create_wildcard(lookup_target)
if len(lookup_target) == 0:
return
sdx = get_soundex(lookup_target)
# self.logger.debug(f'sel adm1 patt={pattern} iso={place.country_iso}')
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="name = ? AND country = ? AND f_code = ? ",
args=(lookup_target, place.country_iso, 'ADM1'),
result=Result.STRONG_MATCH),
Query(where="name LIKE ? AND country = ? AND f_code = ?",
args=(lookup_target, place.country_iso, 'ADM1'),
result=Result.WILDCARD_MATCH),
Query(where="sdx = ? AND country = ? AND f_code=?",
args=(sdx, place.country_iso, 'ADM1'),
result=Result.SOUNDEX_MATCH)
]
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
def get_country_iso(self, place: Loc) -> str:
""" Return ISO code for specified country"""
lookup_target, modified = GeoKeys.country_normalize(place.country_name)
if len(lookup_target) == 0:
return ''
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="name = ? AND f_code = ? ",
args=(lookup_target, 'ADM0'),
result=Result.STRONG_MATCH),
# Query(where="name LIKE ? AND f_code = ? ",
# args=(self.create_wildcard(lookup_target), 'ADM0'),
# result=Result.PARTIAL_MATCH) #,
# Query(where="sdx = ? AND f_code = ? ",
# args=(GeoKeys.get_soundex (lookup_target), 'ADM0'),
# result=Result.PARTIAL_MATCH)
]
row_list, result_code = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
if len(row_list) > 0:
res = row_list[0][Entry.ISO]
if len(row_list) == 1:
place.country_name = row_list[0][Entry.NAME]
else:
res = ''
return res
def lookup_geoid(self, place: Loc) -> None:
"""Search for GEOID"""
result_place: Loc = Loc.Loc()
query_list = [
Query(where="geoid = ? ",
args=(place.target, ),
result=Result.STRONG_MATCH)
]
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.geodata', query_list=query_list)
if len(place.georow_list) == 0:
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
else:
place.georow_list = place.georow_list[:1]
place.result_type = GeoKeys.Result.STRONG_MATCH
# Add search quality score to each entry
for idx, rw in enumerate(place.georow_list):
self.copy_georow_to_place(row=rw, place=result_place)
update = list(rw)
update.append(1) # Extend list row and assign score
result_place.prefix = ''
res_nm = result_place.format_full_nm(None)
score = 0.0
# Remove items in prefix that are in result
tk_list = res_nm.split(",")
for item in tk_list:
place.prefix = re.sub(
item.strip(' ').lower(), '', place.prefix)
update[GeoKeys.Entry.SCORE] = int(score * 100)
place.georow_list[idx] = tuple(update)
def lookup_admin_dbid(self, place: Loc) -> None:
"""Search for DB ID"""
query_list = [
Query(where="id = ? ",
args=(place.target, ),
result=Result.STRONG_MATCH)
]
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
def select_country(self, place: Loc):
"""Search for Admin1 entry"""
lookup_target = place.country_iso
if len(lookup_target) == 0:
return
sdx = get_soundex(lookup_target)
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="country = ? AND f_code = ? ",
args=(place.country_iso, 'ADM0'),
result=Result.STRONG_MATCH),
Query(where="sdx = ? AND f_code=?",
args=(sdx, 'ADM0'),
result=Result.SOUNDEX_MATCH)
]
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
def get_alt_name(self, geoid) -> (str, str):
# retrieve alternate name
query_list = [
Query(where="geoid = ?",
args=(geoid, ),
result=Result.STRONG_MATCH)
]
select = 'name, lang'
row_list, res = self.db.process_query(select_string=select,
from_tbl='main.altname',
query_list=query_list)
if len(row_list) > 0:
return row_list[0][0], row_list[0][1]
else:
return '', ''
def advanced_search(self, place: Loc):
"""
Advanced search - support parameters for ISO and Feature class
"""
lookup_target = place.target
if len(lookup_target) == 0:
return
pattern = self.create_wildcard(lookup_target)
feature_pattern = self.create_wildcard(place.feature)
self.logger.debug(
f'Advanced Search. Targ=[{pattern}] feature=[{feature_pattern}]'
f' iso=[{place.country_iso}] ')
if len(place.feature) > 0:
query_list = [
Query(where="name LIKE ? AND country LIKE ? AND f_code LIKE ?",
args=(pattern, place.country_iso, feature_pattern),
result=Result.PARTIAL_MATCH)
]
else:
query_list = [
Query(where="name LIKE ? AND country LIKE ?",
args=(pattern, place.country_iso),
result=Result.PARTIAL_MATCH)
]
# Search main DB
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.geodata', query_list=query_list)
# self.logger.debug(f'main Result {place.georow_list}')
# Search admin DB
admin_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
place.georow_list.extend(admin_list)
def get_admin2_name_direct(self, admin1_id, admin2_id, iso) -> str:
"""Search for Admin2 entry"""
lookup_target = admin2_id
if len(lookup_target) == 0:
return ''
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="admin2_id = ? AND country = ? AND admin1_id = ?",
args=(lookup_target, iso, admin1_id),
result=Result.STRONG_MATCH),
Query(where="admin2_id = ? AND country = ?",
args=(lookup_target, iso),
result=Result.PARTIAL_MATCH)
]
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
row = row_list[0]
return row[Entry.NAME]
else:
return ''
def get_admin2_id(self, place: Loc):
"""Search for Admin1 entry"""
lookup_target = place.admin2_name
if len(lookup_target) == 0:
return
# Try each query until we find a match - each query gets less exact
query_list = []
if len(place.admin1_id) > 0:
query_list.append(
Query(where=
"name = ? AND country = ? AND admin1_id=? AND f_code=?",
args=(lookup_target, place.country_iso, place.admin1_id,
'ADM2'),
result=Result.STRONG_MATCH))
query_list.append(
Query(
where=
"name LIKE ? AND country = ? and admin1_id = ? AND f_code=?",
args=(lookup_target, place.country_iso, place.admin1_id,
'ADM2'),
result=Result.WILDCARD_MATCH))
query_list.append(
Query(
where=
"name LIKE ? AND country = ? and admin1_id = ? AND f_code=?",
args=(self.create_county_wildcard(lookup_target),
place.country_iso, place.admin1_id, 'ADM2'),
result=Result.WILDCARD_MATCH))
else:
query_list.append(
Query(where="name = ? AND country = ? AND f_code=?",
args=(lookup_target, place.country_iso, 'ADM2'),
result=Result.STRONG_MATCH))
query_list.append(
Query(where="name LIKE ? AND country = ? AND f_code=?",
args=(lookup_target, place.country_iso, 'ADM2'),
result=Result.WILDCARD_MATCH))
query_list.append(
Query(where="name LIKE ? AND country = ? AND f_code=?",
args=(self.create_county_wildcard(lookup_target),
place.country_iso, 'ADM2'),
result=Result.WILDCARD_MATCH))
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
row = row_list[0]
place.admin2_id = row[Entry.ADM2]
def get_db_version(self) -> int:
# If version table does not exist, this is V1
if self.db.table_exists('version'):
# query version ID
query_list = [
Query(where="version like ?",
args=('%', ),
result=Result.STRONG_MATCH)
]
select_str = '*'
row_list, res = self.db.process_query(select_string=select_str,
from_tbl='main.version',
query_list=query_list)
if len(row_list) > 0:
ver = int(row_list[0][1])
self.logger.debug(f'Database Version = {ver}')
return ver
# No version table, so this is V1
self.logger.debug('No version table. Version is 1')
return 1
def get_admin1_alt_name(self, place: Loc) -> (str, str):
"""Search for Admin1 entry"""
lookup_target = place.admin1_id
if len(lookup_target) == 0:
return '', ''
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="admin1_id = ? AND country = ? AND f_code = ? ",
args=(lookup_target, place.country_iso, 'ADM1'),
result=Result.STRONG_MATCH)
]
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
row = row_list[0]
admin1_name, lang = self.get_alt_name(row[Entry.ID])
return admin1_name, lang
else:
return '', ''
def get_country_name(self, iso: str) -> str:
""" return country name for specified ISO code """
if len(iso) == 0:
return ''
# Try each query until we find a match - each query gets less exact
query_list = [
Query(where="country = ? AND f_code = ? ",
args=(iso, 'ADM0'),
result=Result.STRONG_MATCH)
]
row_list, res = self.db.process_query_list(from_tbl='main.admin',
query_list=query_list)
if len(row_list) > 0:
res = row_list[0][Entry.NAME]
if iso == 'us':
res = 'United States'
else:
res = ''
return res
def select_admin2(self, place: Loc):
"""Search for Admin2 entry"""
lookup_target = place.admin2_name
if len(lookup_target) == 0:
return
place.target = lookup_target
# sdx = get_soundex(lookup_target)
# Try Admin query until we find a match - each query gets less exact
query_list = [
Query(
where="name = ? AND country = ? AND admin1_id = ? AND f_code=?",
args=(lookup_target, place.country_iso, place.admin1_id,
'ADM2'),
result=Result.STRONG_MATCH),
Query(where="name = ? AND country = ? AND f_code=?",
args=(lookup_target, place.country_iso, 'ADM2'),
result=Result.PARTIAL_MATCH),
Query(where="name LIKE ? AND country = ? AND f_code=?",
args=(self.create_wildcard(lookup_target), place.country_iso,
'ADM2'),
result=Result.PARTIAL_MATCH),
Query(where="name = ? AND f_code=?",
args=(lookup_target, 'ADM2'),
result=Result.PARTIAL_MATCH),
Query(where="name LIKE ? AND country = ? AND f_code=?",
args=(lookup_target, place.country_iso, 'ADM2'),
result=Result.WILDCARD_MATCH)
]
# self.logger.debug(f'Admin2 lookup=[{lookup_target}] country=[{place.country_iso}]')
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.admin', query_list=query_list)
if place.result_type == GeoKeys.Result.WILDCARD_MATCH:
# Found as Admin2 without shire
place.original_entry = re.sub('shire', '', place.original_entry)
if len(place.georow_list) == 0:
# Try city rather than County match.
save_admin2 = place.admin2_name
place.city1 = place.admin2_name
place.admin2_name = ''
# self.logger.debug(f'Try admin2 as city: [{place.target}]')
self.select_city(place)
if len(place.georow_list) == 0:
# not found. restore admin
place.admin2_name = save_admin2
place.city1 = ''
else:
# Found match as a City
place.place_type = Loc.PlaceType.CITY
match_adm1 = self.get_admin1_name_direct(
lookup_target=place.georow_list[0][Entry.ADM1],
iso=place.country_iso)
# self.logger.debug(f'pl_iso [{place.country_iso}] pl_adm1 {place.admin1_name} match_adm1=[{match_adm1}] ')
if place.admin1_name != match_adm1:
place.prefix = place.admin1_name.title()
place.admin1_name = ''
return
def select_city(self, place: Loc):
"""
Search for entry - try the most exact match first, then less exact matches
"""
lookup_target = place.target
if len(lookup_target) == 0:
return
#pattern = self.create_wildcard(lookup_target)
#quick_pattern = self.create_quick_wildcard(lookup_target)
sdx = get_soundex(lookup_target)
#self.logger.debug(f'CITY lkp targ=[{lookup_target}] adm1 id=[{place.admin1_id}]'
# f' adm2 id=[{place.admin2_id}] iso=[{place.country_iso}] patt =[{pattern}] sdx={sdx} pref={place.prefix}')
query_list = []
if len(place.country_iso) == 0:
# No country present - try lookup by name.
query_list.append(
Query(where="name = ?",
args=(lookup_target, ),
result=Result.PARTIAL_MATCH))
# lookup by wildcard name
query_list.append(
Query(
where="name LIKE ?",
args=(lookup_target, ), # quick_pattern,),
result=Result.WILDCARD_MATCH))
# lookup by soundex
query_list.append(
Query(where="sdx = ?",
args=(sdx, ),
result=Result.SOUNDEX_MATCH))
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.geodata', query_list=query_list)
# self.logger.debug(place.georow_list)
return
# Build query list - try each query in order until a match is found
# Start with the most exact match depending on the data provided.
if len(place.admin1_name) > 0:
# lookup by name, ADMIN1, country
query_list.append(
Query(where="name = ? AND country = ? AND admin1_id = ?",
args=(lookup_target, place.country_iso, place.admin1_id),
result=Result.STRONG_MATCH))
# lookup by wildcard name, ADMIN1, country
query_list.append(
Query(where="name LIKE ? AND country = ? AND admin1_id = ?",
args=(lookup_target, place.country_iso, place.admin1_id),
result=Result.WILDCARD_MATCH))
else:
# lookup by wildcard name, country
query_list.append(
Query(where="name LIKE ? AND country = ?",
args=(lookup_target, place.country_iso),
result=Result.WILDCARD_MATCH))
# Lookup by name, country
query_list.append(
Query(where="name = ? AND country = ?",
args=(lookup_target, place.country_iso),
result=Result.PARTIAL_MATCH))
if len(place.admin1_name) > 0:
# lookup by Soundex name, country and admin1
query_list.append(
Query(where="sdx = ? AND admin1_id = ? AND country = ?",
args=(sdx, place.admin1_id, place.country_iso),
result=Result.SOUNDEX_MATCH))
else:
# lookup by Soundex name, country
query_list.append(
Query(where="sdx = ? AND country = ?",
args=(sdx, place.country_iso),
result=Result.SOUNDEX_MATCH))
# Try each query in list
place.georow_list, place.result_type = self.db.process_query_list(
from_tbl='main.geodata', query_list=query_list)
