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)