def get_closest_cities():
lat, lon = float(request.args.get('lat', 0)), float(request.args.get('lon', 0))
if lat == 0 or lon == 0:
raise RuntimeError("Invalid request")
q = "SELECT * FROM worldcities WHERE (point(%(lon)s, %(lat)s) <@> POINT(longitude, latitude)) / 1.60934 < 100 ORDER BY (point(%(lon)s, %(lat)s) <@> POINT(longitude, latitude)) ASC LIMIT 5"
cur = psql.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(q, {'lat': lat, 'lon': lon})
c = cur.fetchall()
c = [dict(city, id=utils.city_id(city)) for city in c]
return jsonify(dict(cities=c))
def find_rainbow_cities(GFS_SLUG):
logger.debug("loading list of cities")
cities = json.loads(open(os.path.join(os.path.abspath(os.path.dirname(__file__)), "data", "cities.json")).read())
logger.debug("loading specified rainbow analysis image")
CURRENT_GFS_FOLDER = os.path.join(settings.GFS_FOLDER, GFS_SLUG)
rainbow_analysis_image_path = os.path.join(CURRENT_GFS_FOLDER, "GFS_half_degree.%s.pwat.png" % GFS_SLUG)
try:
image = Image.open(rainbow_analysis_image_path)
except IOError:
logger.error("did not find image file")
return False
access = image.load()
rainbow_cities_json_path = os.path.join(CURRENT_GFS_FOLDER, "%s.rainbow_cities.json" % GFS_SLUG)
processed_path = os.path.join(CURRENT_GFS_FOLDER, "PROCESSED")
rainbow_cities = []
logger.debug("checking each city against rainbow analysis")
xys = []
for x in range(image.size[0]):
for y in range(image.size[1]):
if access[x, y] == 0:
xys.append("'%dx%d'" % (x, y))
if len(xys) > 0:
cur = psql.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("SELECT * FROM worldcities WHERE xy IN (" + (",".join(xys)) + ")")
rainbow_cities = cur.fetchall()
else:
rainbow_cities = []
if len(rainbow_cities) > 0:
names = u', '.join((city['name_en'] for city in rainbow_cities)).encode('utf8')
logger.debug(u"Found rainbow cities: %s" % names)
[send_push(c) for c in rainbow_cities]
_push(u"Rainbow cities: %s" % names, ["debug"])
logger.debug(u"Wrote: %s" % rainbow_cities_json_path)
files = [os.path.join(settings.GFS_FOLDER, "rainbow_cities.json"), rainbow_cities_json_path]
for fn in files:
with codecs.open(fn, 'w', 'utf8') as f:
f.write(json.dumps(rainbow_cities, indent=4, ensure_ascii=False))
logger.debug(u"Wrote {}".format(fn))
else:
logger.debug("no rainbow cities found")
with codecs.open(processed_path, 'w', 'utf8') as f:
f.write('processed')
def save(city):
cur = psql.cursor()
cur.execute("INSERT INTO worldcities (id, country, latitude, longitude, xy, name, name_en) SELECT %(id)s, %(country)s, %(latitude)s, %(longitude)s, %(xy)s, %(name)s, %(name_en)s WHERE NOT EXISTS (SELECT 1 FROM worldcities WHERE id = %(id)s)", city)
def install_schema():
logger.info("Installing database schema")
cur = psql.cursor()
cur.execute(open("scrape/worldcities.sql", 'r').read())
psql.commit()
(country, _unused, name, _code, pop, lat, lng) = row
lat = float(lat)
lng = float(lng)
pop = int(pop)
if pop < 50000:
continue
id = city_id(lat, lng)
city = {'id': id, 'latitude': lat, 'longitude': lng, 'xy': xy(lat, lng), 'name': name, 'name_en': name, 'country': country}
save(city)
psql.commit()
def fill_db_ru():
cities = json.loads(open('data/cities.json', 'r').read())
for c in cities:
id = city_id(c['lat'], c['lon'])
city = {'id': id, 'latitude': c['lat'], 'longitude': c['lon'], 'xy': xy(c['lat'], c['lon']), 'name': c['name_ru'], 'name_en': c['name_en'], 'country': 'ru'}
save(city)
psql.commit()
try:
cur = psql.cursor()
cur.execute("SELECT * FROM worldcities LIMIT 1")
except ProgrammingError:
psql.rollback()
install_schema()
finally:
cur.close()
#fill_db()
fill_db_ru()