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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
import os
import csv
import click
import time
import html
from geopy import geocoders
from dotenv import load_dotenv
from util import *
load_dotenv()
geolocator = geocoders.GoogleV3(os.getenv('MAPS_API_KEY'))
print('loading institutions...')
worksheet = fetch_worksheet("institutions")
keys, rows = fetch_google_sheet("institutions")
print("got {} rows".format(len(rows)))
countries = load_countries()
print('loaded countries...')
cname_lookup = {}
@click.command()
def s2_geocode_spreadsheet():
# row_tuples = []
for i, row in enumerate(rows):
# row_tuples.append((i, row,))
cname, name, address, lat, lng, org_type, extra_address, country = row
if len(cname) < 3:
print("very short cname: {}".format(cname))
if cname == name or cname not in cname_lookup:
cname_lookup[cname] = i
print("built lookup")
print("processing sheet...")
seen = {}
for i, row in enumerate(rows):
if (i % 1000) == 0:
print("{}...".format(i))
if row[1] in seen:
continue
seen[row[1]] = True
hit_api = s2_geocode_row(i, row)
if hit_api:
time.sleep(1)
# parallelize(s2_geocode_row, row_tuples)
def s2_geocode_row(i, row):
# 0 cname 1 name 2 address 3 lat 4 lng 5 org_type 6 notes 7 country
cname, name, address, lat, lng, org_type, extra_address, country = row
decoded_cname = html.unescape(cname)
# print(decoded_cname)
if cname != decoded_cname:
worksheet.update_cell(i+2, 0+1, decoded_cname)
cname = decoded_cname
if "@" in cname:
print(cname)
if lat and lng:
if not country:
update_country_from_address(address, i, countries, worksheet)
return True
return False
c_row = rows[cname_lookup[cname]]
if c_row[3] and c_row[4]:
print("name {}, found cname: {}".format(name, cname))
worksheet.update_cell(i+2, 2+1, c_row[2])
worksheet.update_cell(i+2, 3+1, c_row[3])
worksheet.update_cell(i+2, 4+1, c_row[4])
worksheet.update_cell(i+2, 7+1, c_row[7])
return True
if address:
address_to_geocode = address
elif name:
address_to_geocode = name
elif cname:
address_to_geocode = cname
if not address_to_geocode:
return False
print(address_to_geocode)
location = geolocator.geocode(address_to_geocode)
if location:
print("{} found: {}".format(i+1, name))
print(location.raw)
worksheet.update_cell(i+2, 2+1, location.address)
worksheet.update_cell(i+2, 3+1, location.latitude)
worksheet.update_cell(i+2, 4+1, location.longitude)
if address and address != location.address:
worksheet.update_cell(i+2, 6+1, address) # store alt address in "notes" field
#valid_count += 1
country = update_country_from_address(location.address, i, countries, worksheet)
row[2] = location.address
row[3] = location.latitude
row[4] = location.longitude
row[7] = country
return True
else:
print("{} not found: {}".format(i+1, address_to_geocode))
#invalid_count += 1
return False
def update_country_from_address(address, i, countries, worksheet):
address_partz = address.split(', ')
possible_country = address_partz[-1]
country = None
if possible_country in countries:
country = countries[possible_country]
elif "China" in address:
country = "China"
elif "Singapore" in address:
country = "Singapore"
elif "Taiwan" in address:
country = "Taiwan"
elif "Russia" in address:
country = "Russia"
elif "Ukraine" in address:
country = "Ukraine"
elif "Japan" in address:
country = "Japan"
elif "Iran" in address:
country = "Iran"
elif "South Korea" in address:
country = "South Korea"
elif "Egypt" in address:
country = "Egypt"
elif "پاکستان" in address:
country = "Pakistan"
elif "السعودية" in address:
country = "Saudi Arabia"
else:
print("unknown country: {}".format(address))
return ""
worksheet.update_cell(i+2, 7+1, country)
# print(country)
return country
if __name__ == '__main__':
s2_geocode_spreadsheet()
|
