def _get_geodata(self, column, value, multi_op='AND'):
'''Documentation regarding FTS search: https://www.sqlite.org/fts3.html#full_text_index_queries
A query such as:
zipdb.get_geodata(['place_name', 'admin_name1', 'country_code'], ['MADRID', 'MADRID', 'ES'])
should return something like:
{ 'accuracy': 4.0,
'admin_code1': 'MD',
'admin_code2': 'M',
'admin_code3': '28079',
'admin_name1': 'MADRID',
'admin_name2': 'MADRID',
'admin_name3': 'MADRID',
'country_code': 'ES',
'country_name': None,
'location': '40.4165,-3.7026',
'place_name': 'MADRID',
'representative_point': '40.4165,-3.7026',
'zip_code': '28001'
}
'''
if type(value) is str and type(column) is str and column in self.names:
query = 'SELECT * FROM {} WHERE {} MATCH "{}:{}"'.format(
self.name, self.name, column.lower(), value.lower())
elif type(value) is str and type(
column) is str and column not in self.names:
query = 'SELECT * FROM {} WHERE {} MATCH "{}"'.format(
self.name, self.name, value.lower())
elif type(column) is list and type(value) is list and len(
column) == len(value):
op = ' {} '.format(multi_op)
query = op.join([
"{}:{}".format(c.lower(), v.lower())
for c, v in zip(column, value)
])
query = 'SELECT * FROM {} WHERE {} MATCH "{}"'.format(
self.name, self.name, query)
else:
return None
self.cursor.execute(query)
results = self.cursor.fetchall()
if results is None or len(results) == 0:
return None
#else
d = results[0]
d['location'] = '{},{}'.format(results[0]['latitude'],
results[0]['longitude'])
d['representative_point'] = d['location']
if len(results) > 1:
geo_points = [(r['latitude'], r['longitude']) for r in results]
geo_points = MultiPoint(geo_points)
repr_lat, repr_lon = geo_points.representative_point().coords[0]
d['representative_point'] = '{},{}'.format(repr_lat, repr_lon)
del d['latitude']
del d['longitude']
d['country_name'] = None
return d
def _get_geodata(self, column, value, multi_op='AND'):
"""Queries the database.
:param column: Column to query.
:param value: Value of the given column. Provide the value in upper case.
:return: dictionary with geographical information like:
{
'country_code': 'US',
'country_name': 'UNITED STATES',
'location': {'lat': 37.09024, 'lon': -95.712891},
'representative_point': {'lat': 37.09024, 'lon': -95.712891}
}
A representative point is a kind of centroid calculated when there are many. For example, if you query with country_code: US, there are many different entries.
The location dictionary would be populated with the first geopoint, and the rerpesentative_point would be calculated using the method representative_point of a shapely.geometry.MultiPoint
See https://toblerity.org/shapely/manual.html#object.representative_point
However, in this database, there should be only one possible entry for each query.
"""
if type(column) is str:
self.cursor.execute('SELECT * FROM {} WHERE {} = "{}"'.format(
self.name, column, value))
elif type(column) is list and type(value) is list and len(
column) == len(value):
op = ' {} '.format(multi_op)
query = op.join(
['"{}" = "{}"'.format(e[0], e[1]) for e in zip(column, value)])
query = 'SELECT * FROM {} WHERE {}'.format(self.name, query)
self.cursor.execute(query)
results = self.cursor.fetchall()
if results is None or len(results) == 0:
return None
d = results[0]
d['location'] = '{},{}'.format(results[0]['latitude'],
results[0]['longitude'])
d['representative_point'] = d['location']
if len(results) > 1:
geo_points = [(r['latitude'], r['longitude']) for r in results]
geo_points = MultiPoint(geo_points)
repr_lat, repr_lon = geo_points.representative_point().coords[0]
d['representative_point'] = '{},{}'.format(repr_lat, repr_lon)
del d['latitude']
del d['longitude']
if 'index' in d:
del d['index']
return d
def _get_geodata(self, column, value, multi_op='AND', str_ip=True):
"""Queries the database.
:param column: Column or list of columns to query.
:param value: Value or list of values of the given column/s. Provide the value/s in upper case.
:return: dictionary with geographical information like:
{
'place_name': 'MONTERREY',
'country_code': 'MX',
'country_name': 'MEXICO',
'location': {'lat': 25.66667, 'lon': -100.31667},
'region_name': 'NUEVO LEON',
'representative_point': {'lat': 21.210829999999998, 'lon': -100.21194},
'zip_code': '64830'
}
"""
if column == 'ip':
if str_ip:
try:
value = ip2int(str(value))
except Exception as e:
logger.warning('Error in ip2int with ip: |{}|'.format(
str(value)))
return None
idx = bisect.bisect_right(self.ip_int_to_list, value)
ip_to = self.ip_int_to_list[idx]
query = 'SELECT * FROM {} WHERE ip_to = "{}"'.format(
self.name, ip_to)
#query = 'SELECT * FROM {} WHERE {} BETWEEN ip_from AND ip_to'.format(self.name, value)
elif type(column) is str:
query = 'SELECT * FROM {} WHERE {} = "{}"'.format(
self.name, column, value)
elif type(column) is list and type(value) is list and len(
column) == len(value):
op = ' {} '.format(multi_op)
query = op.join(
['"{}" = "{}"'.format(e[0], e[1]) for e in zip(column, value)])
query = 'SELECT * FROM {} WHERE {}'.format(self.name, query)
else:
return None
logger.debug('Query: {}'.format(query))
self.cursor.execute(query)
results = self.cursor.fetchall()
logger.debug('Results {}'.format(results))
if results is None or len(results) == 0:
return None
d = results[0]
d['location'] = '{},{}'.format(results[0]['latitude'],
results[0]['longitude'])
d['representative_point'] = d['location']
if len(results) > 1:
logger.debug('More than 1 result. Creating centroid.')
geo_points = [(r['latitude'], r['longitude']) for r in results]
geo_points = MultiPoint(geo_points)
repr_lat, repr_lon = geo_points.representative_point().coords[0]
d['representative_point'] = '{},{}'.format(repr_lat, repr_lon)
logger.debug('Centroid done.')
for k in ['latitude', 'longitude', 'ip_from', 'ip_to', 'index']:
if k in d:
del d[k]
return d
