def fetch_usacity(country, city, code, loc): cursor = local_conn.cursor() custom_tab = "cust_US" + country sql = "select name from custom.sqlite_master where type='table'" att_tables = [] for row in local_conn.execute(sql): att_tables.append(str(row[0])) sql = "select CC, AC, Ciudad, Latitud, Longitud from US%s where Ciudad == '%s' and AC == '%s'" % (country, city, code) if custom_tab in att_tables: union = " union select CC, AC, Ciudad, Latitud, Longitud from cust_US%s where Ciudad == '%s' and AC == '%s' " % (country, city, code) else: union = "" sql += union cursor.execute(sql) loc.country, loc.region_code, loc.city, loc.latdec, loc.longdec = cursor.next() loc.latitud = dectodeg(float(loc.latdec)) loc.longitud = dectodeg(float(loc.longdec)) sql = "select state, name from usaadmin where alfa == '%s' and code == '%s'" % (loc.country, loc.region_code) cursor.execute(sql) loc.country_code, loc.region = cursor.next() sql = "select name from usastates where alfa == '%s'" % loc.country cursor.execute(sql) loc.country = cursor.next()[0] loc.region += " (" + loc.country + ")" loc.country = "USA" fetch_zone_usa(cursor, loc)
def fetch_blindly_usacity(country, city, loc): '''get usa city data''' cursor = local_conn.cursor() sql = "select CC, AC, Ciudad, Latitud, Longitud from US%s where Ciudad == '%s'" % (country, city) cursor.execute(sql) try: loc.country, loc.region_code, loc.city, loc.latdec, loc.longdec = cursor.next() except StopIteration: return "localidad no encontrada: %s" % city loc.latitud = dectodeg(float(loc.latdec)) loc.longitud = dectodeg(float(loc.longdec)) sql = "select state, name from usaadmin where alfa == '%s' and code == '%s'" % (loc.country, loc.region_code) cursor.execute(sql) loc.country_code, loc.region = cursor.next() sql = "select name from usastates where alfa == '%s'" % loc.country cursor.execute(sql) loc.country = cursor.next()[0] loc.region += " (" + loc.country + ")" loc.country = "USA" sql = "select zones, name from zonetab where alfa == 'US'" for z, n in cursor.execute(sql): zz = z.split(";") for code in zz: if code.startswith(loc.country_code): if len(code) == len(loc.country_code): loc.zone = n break code = code[2:] if code.startswith('-'): code = code[1:].split(',') if loc.region_code not in code: loc.zone = n break else: # + code = code[1:].split(',') if loc.region_code in code: loc.zone = n break if loc.zone: break return loc
def format_latitud(self,kind='chart'): if kind == 'chart': chart = self.curr_chart else: chart = self.curr_click lat = dectodeg(chart.latitud)[:-2] if lat[0] == '-': let = 'S' lat = lat[1:] else: let = 'N' return lat[0:-2]+let+lat[-2:]
def format_longitud(self,kind='chart'): if kind == 'chart': chart = self.curr_chart else: chart = self.curr_click longitud = dectodeg(chart.longitud)[:-2] if longitud[0] == '-': let = 'W' longitud = longitud[1:] else: let = 'E' return longitud[0:-2]+let+longitud[-2:]
def coalesce_geo(plg,plt): if isinstance(plg,float): lg = dectodeg(plg) lt = dectodeg(plt) else: lg = plg.strip() lt = plt.strip() lg = lg[:-2] lt = lt[:-2] if lg.startswith('-'): lg = lg[1:] lg = lg[:-2].rjust(1,'0')+"W"+lg[-2:].rjust(2,'0') else: lg = lg[:-2].rjust(1,'0')+"E"+lg[-2:] if lt.startswith('-'): lt = lt[1:] lt = lt[:-2].rjust(1,'0')+"S"+lt[-2:].rjust(2,'0') else: lt = lt[:-2].rjust(1,'0')+"N"+lt[-2:] return lg+" "+lt