def find_users_by_pokemon(self, pokemon, spawntype): self.open_db() dbh = self.db.cursor() fmt = "select t2.discordid, ST_AsText(t1.coord) from {me} as t1, {master} as t2 where t1.dexno = {dexno} and t1.settingid = t2.settingid and spawntype={spawntype}" sql = fmt.format(me=self.table_name, master=self.master_table, spawntype=spawntype, dexno=self.pokemons[pokemon]) dbh.execute(sql) users = [] user_loc = Point() while True: row = dbh.fetchone() if row == None: break discordid, coord = row lat_lng = [ float(value) for value in coord.replace("POINT(", "").replace( ")", "").split(" ") ] user_loc.latitude = lat_lng[0] user_loc.longitude = lat_lng[1] users.append(discordid) pass dbh.close() self.db.commit() self.close_db() return users
def test_point_assign_coordinates(self): point = Point(self.lat + 10, self.lon + 10, self.alt + 10) point.latitude = self.lat point.longitude = self.lon point.altitude = self.alt self.assertEqual(point[0], self.lat) self.assertEqual(point[1], self.lon) self.assertEqual(point[2], self.alt) self.assertEqual(self.coords, tuple(point)) self.assertEqual(point.latitude, self.lat) self.assertEqual(point.longitude, self.lon) self.assertEqual(point.altitude, self.alt)
def choose_listeners(self, pokemon, spawntype, center): self.open_db() longest = geopy.distance.distance(kilometers=16) hexgon = "POLYGON((" + ",".join([ "%g %g" % (point.latitude, point.longitude) for point in [ longest.destination(center, angle) for angle in range(0, 361, 60) ] ]) + "))" dbh = self.db.cursor() fmt = "select t2.discordid, t2.pm_channel, t1.distance, ST_AsText(t1.coord) from {me} as t1, {master} as t2 where t2.on_off = 'y' and MBRContains(ST_GeomFromText('{hexgon}'), t1.coord) and t1.settingid = t2.settingid and dexno={dexno} and t1.spawntype = {spawntype}" sql = fmt.format(me=self.table_name, master=self.master_table, hexgon=hexgon, dexno=self.pokemons[pokemon], spawntype=spawntype) dbh.execute(sql) users = [] user_loc = Point() while True: row = dbh.fetchone() if row == None: break discordid, pm_channel, distance, coord = row lat_lng = [ float(value) for value in coord.replace("POINT(", "").replace( ")", "").split(" ") ] user_loc.latitude = lat_lng[0] user_loc.longitude = lat_lng[1] dt = geopy.distance.vincenty(center, user_loc) if dt.m <= distance: users.append((discordid, pm_channel, center, dt.m)) pass pass dbh.close() self.db.commit() self.close_db() return users
def select_listeners(udb, sdb, pokemon, center): longest = geopy.distance.distance(kilometers=10) hexgon = "POLYGON((" + ",".join([ "%g %g" % (point.latitude, point.longitude) for point in [ longest.destination(center, angle) for angle in range(0, 361, 60) ] ]) + "))" dbh = udb.db.cursor() sql = "select t2.discordid, t2.distance, ST_AsText(t1.coord) from {seldb} as t1, {master} as t2, pokemon as t3 where MBRContains(ST_GeomFromText('{hexgon}'), t1.coord) and t1.settingid = t2.settingid and t3.dexno = t1.dexno and t3.name='{pokemon}' and t2.on_off = 'y'".format( seldb=sdb.table_name, master=sdb.master_table, hexgon=hexgon, pokemon=pokemon) dbh.execute(sql) users = [] user_loc = Point() while True: row = dbh.fetchone() if row == None: break discordid, distance, coord = row lat_lng = [ float(value) for value in coord.replace("POINT(", "").replace( ")", "").split(" ") ] user_loc.latitude = lat_lng[0] user_loc.longitude = lat_lng[1] dt = geopy.distance.vincenty(center, user_loc) if dt.m <= distance: users.append((discordid, center, round(dt.m), pokemon)) pass pass dbh.close() udb.db.commit() return users