· 9 years ago · Nov 28, 2016, 07:40 AM
1Headquarters, Havenmeesterweg 1, Haarlemmermeer
2Alice, Spoorstraat 2, Leeuwarden
3Bob, Stadskanaal
4Charlie, Maastricht
5Devon, Woerden
6Eddy, P.J. Jongstraat, Lutjebroek
7Freddy, Roosendaal
8Giles, Almere
9Harry, Spoorstraat 4, Winterswijk
10Igor, Middelburg
11Janine, Terschelling
12
13locations = [
14['Headquarters',52.3046539,4.7588565],
15['Alice',53.1975889,5.8055371],
16['Bob',52.9919853,6.9462217],
17['Charlie',50.8513682,5.6909725],
18['Devon',52.0798287,4.8627239],
19['Eddy',52.6979589,5.2007523],
20['Freddy',51.535849,4.4653213],
21['Giles',52.3507849,5.2647016],
22['Harry',51.9698835,6.7204984],
23['Igor',51.4987962,3.610998],
24['Janine',53.3978747,5.3466786]
25];
26
27import urllib
28import sqlite3
29import json
30
31
32def main():
33 SERVICE_URL = "http://maps.googleapis.com/maps/api/geocode/json?"
34
35 conn = sqlite3.connect('geodata.sqlite')
36 cur = conn.cursor()
37
38 cur.execute('''
39 CREATE TABLE IF NOT EXISTS Locations (target TEXT,
40 address TEXT, geodata TEXT)''')
41
42 filehandle = open("locations.data")
43 count = 0
44 for line in filehandle:
45 target, _, address = line.partition(', ')
46 address = address.strip()
47 cur.execute(
48 "SELECT geodata FROM Locations WHERE target= ?", (buffer(target), ))
49
50 try:
51 data = cur.fetchone()[0]
52 print "Found in database ", address
53 continue
54 except:
55 pass
56
57 print 'Resolving', address
58 url = SERVICE_URL + urllib.urlencode(
59 {"sensor": "false", "address": address})
60 print 'Retrieving', url
61 urlhandle = urllib.urlopen(url)
62 data = urlhandle.read()
63 print 'Retrieved', len(data),
64 'characters', data[:20].replace('n', ' ')
65 count = count + 1
66 try:
67 js = json.loads(str(data))
68 except:
69 continue
70
71 if 'status' not in js or (
72 js['status'] != 'OK' and js['status'] != 'ZERO_RESULTS'):
73 print '==== Failure To Retrieve ====',
74 data
75 break
76
77 cur.execute('''INSERT INTO Locations (target, address, geodata)
78 VALUES ( ?, ?, ? )''', (
79 buffer(target), buffer(address), buffer(data)))
80 conn.commit()
81
82if __name__ == '__main__':
83 main()
84
85import sqlite3
86import json
87import codecs
88
89
90def main():
91 OUTPUT_FILE = 'locations.js'
92
93 conn = sqlite3.connect('geodata.sqlite')
94 cur = conn.cursor()
95
96 cur.execute('SELECT * FROM Locations')
97 filehandle = codecs.open(OUTPUT_FILE, 'w', "utf-8")
98 filehandle.write("locations = [n")
99 count = 0
100 for row in cur:
101 '''
102 row[0]: target
103 row[1]: address
104 row[2]: lat & long
105 '''
106 target = str(row[0]).split(',')[0]
107 data = str(row[2])
108 try:
109 js = json.loads(str(data))
110 except:
111 continue
112
113 if not('status' in js and js['status'] == 'OK'):
114 continue
115
116 lat = js["results"][0]["geometry"]["location"]["lat"]
117 lng = js["results"][0]["geometry"]["location"]["lng"]
118 if lat == 0 or lng == 0:
119 continue
120 loc = js['results'][0]['formatted_address'].replace("'", "")
121 try:
122 print target, loc, lat, lng
123
124 count = count + 1
125 if count > 1:
126 filehandle.write(",n")
127 output = "['"+target+"',"+str(lat)+","+str(lng)+"]"
128 filehandle.write(output)
129 except:
130 continue
131
132 filehandle.write("n];n")
133 cur.close()
134 filehandle.close()
135 print count, "records written to", OUTPUT_FILE
136
137if __name__ == '__main__':
138 main()
139
140<html>
141 <head>
142 <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
143 <meta charset="utf-8">
144 <title>Employee location overview</title>
145 <script src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
146 <script src="http://google-maps-utility-library-v3.googlecode.com/svn/trunk/markerclusterer/src/markerclusterer_compiled.js"></script>
147 <script src="locations.js"></script>
148 <script>
149 function initialize() {
150 // 51.0, 4.0 is roughly the center of The Netherlands
151 const Lat = 51.0;
152 const Lng = 4.0;
153 var mapOptions = {
154 // 7 keeps the country roughly full-screen, adjust if it doesn't
155 zoom: 7,
156 center: new google.maps.LatLng(Lat,Lng),
157 mapTypeId: google.maps.MapTypeId.ROADMAP
158 }
159 var map = new google.maps.Map(document.getElementById('map_canvas'), mapOptions);
160
161 i = 0;
162 var markers = [];
163 for ( pos in locations ) {
164 i = i + 1;
165 var row = locations[pos];
166 window.console && console.log(row);
167 console.log(row)
168 var currentCoord = new google.maps.LatLng(row[1], row[2]);
169 var marker = new google.maps.Marker({
170 position: currentCoord,
171 map: map,
172 title: row[0]
173 });
174 markers.push(marker);
175 }
176 }
177 </script>
178 </head>
179 <body onload="initialize()">
180 <div id="map_canvas" style="height: 100%"></div>
181 </body>
182</html>