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()