forked from codingisacopingstrategy/raduga-server
-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_cities.py
101 lines (81 loc) · 2.9 KB
/
db_cities.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
import json
import utils
import csv
import hashlib
from app import psql
from psycopg2 import ProgrammingError
logger = utils.install_logger()
def position_to_point(position):
lng = position[0]
while lng < 0:
lng += 360
return (int(lng * 2), int((position[1] - 90) * -2))
def xy(lat, lng):
return "%dx%d" % position_to_point((lng, lat))
def install_schema():
logger.info("Installing database schema")
cur = psql.cursor()
cur.execute(open("scrape/worldcities.sql", 'r').read())
psql.commit()
def city_id(lat, lng):
return hashlib.md5(("%s:%s" % (lat, lng)).encode('utf-8')).hexdigest()
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 fill_db():
with open('scrape/cities.txt', 'rU') as f:
for row in f:
row = row.split(",")
(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()
def fill_db_cn():
psql.cursor().execute("DELETE FROM worldcities WHERE country = 'cn'")
psql.commit()
#return
cn_names = open('scrape/cn_names.txt').read().strip().split("\n")
with open('scrape/cn.csv', 'rU') as f:
i = 0
for row in f:
row = row.strip().split(",")
(name_en,lat,lng,country,iso2,admin,capital,population,population_proper) = row
country = 'cn'
lat = float(lat)
lng = float(lng)
if not population:
continue
pop = int(population)
if pop < 50000:
continue
name = cn_names[i]
i += 1
id = city_id(lat, lng)
city = {'id': id, 'latitude': lat, 'longitude': lng, 'xy': xy(lat, lng), 'name': name, 'name_en': name_en, 'country': 'cn'}
save(city)
psql.commit()
print(f"{i} records inserted.")
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()
fill_db_cn()