def create_finit(): cur_path = change_path_to_data() conn = sqlite3.connect('data.db') c = conn.cursor() query = "CREATE TABLE IF NOT EXISTS finit (initial INTEGER NOT NULL, final INTEGER NOT NULL)" c.execute(query) conn.commit() conn.close() back_to_path(cur_path)
def create_finit(): cur_path = change_path_to_data() conn = sqlite3.connect("data.db") c = conn.cursor() query = "CREATE TABLE IF NOT EXISTS finit (initial INTEGER NOT NULL, final INTEGER NOT NULL)" c.execute(query) conn.commit() conn.close() back_to_path(cur_path)
def plot(): cur_path = change_path_to_data() # make sure the value of resolution is a lowercase L, # for 'low', not a numeral 1 map = Basemap( projection='merc', lat_0=50, lon_0=-100, resolution='h', area_thresh=0.1, # llcrnrlat=39.596377, llcrnrlon=115.08728, # urcrnrlat=40.604778, urcrnrlon=121.107788) llcrnrlat=39.831715, llcrnrlon=116.169605, urcrnrlat=40.144738, urcrnrlon=116.757374) # map = Basemap(projection='merc', lat_0=57, lon_0=-135, # resolution='h', area_thresh=0.1, # llcrnrlon=-136.25, llcrnrlat=56, # urcrnrlon=-134.25, urcrnrlat=57.75) map.drawcoastlines() map.drawcountries() # map.fillcontinents(color='coral') map.drawmapboundary() conn = sqlite3.connect('data.db') c = conn.cursor() # query = "SELECT * FROM master000 WHERE id IN (SELECT initial FROM finit)" query = "SELECT * FROM master000 LIMIT 50000" # query = "SELECT * FROM new_places" lats = [] lons = [] labels = [] for line in c.execute(query): lats.append(line[1]) lons.append(line[2]) # labels.append(str(line[0]) + " " + str(line[3]) + " " + str(line[4])) # lon = -135.3318 # lat = 57.0799 x, y = map(lons, lats) map.plot(x, y, 'bo', markersize=6) # for label, xpt, ypt in zip(labels, x, y): # plt.text(xpt, ypt, label) conn.close() plt.show() back_to_path(cur_path)
def test_places(): cur_path = change_path_to_data() conn = sqlite3.connect("data.db") c = conn.cursor() places = [] distances = [] query = "SELECT * FROM places" for line in c.execute(query): places.append(line[1:5]) for place in places: distance = get_distance(place, places[0]) distances.append(distance) if all(distances[i] <= distances[i+1] for i in range(len(distances)-1)): print("Its sorted :D") else: print("Not sorted.") back_to_path(cur_path)
def test_places(): cur_path = change_path_to_data() conn = sqlite3.connect("data.db") c = conn.cursor() places = [] distances = [] query = "SELECT * FROM places" for line in c.execute(query): places.append(line[1:5]) for place in places: distance = get_distance(place, places[0]) distances.append(distance) if all(distances[i] <= distances[i + 1] for i in range(len(distances) - 1)): print("Its sorted :D") else: print("Not sorted.") back_to_path(cur_path)
def places_using_time(): cur_path = change_path_to_data() conn = sqlite3.connect('data.db') c = conn.cursor() initial = [] final = [] added_to_final = True added_to_initial = False for u in range(0, 1): user = get_folder(u) query = "SELECT * FROM master" + user last = [] for line in c.execute(query): line = list(line) line[4] = line[4][:-1] # print(line) if added_to_initial: if get_minutes(line[4], last[4]) > 10: # print("adding to final: ", line) final.append(line) added_to_final = True added_to_initial = False continue if added_to_final: # print("adding to initial: ", line) initial.append(line) added_to_initial = True added_to_final = False last = line if added_to_initial: final.append(last) # print(len(initial)) # print(len(final)) for i in range(0, len(initial) - 1): values = [initial[i][0], final[i][0]] query = "INSERT INTO finit VALUES({},{})".format( initial[i][0], final[i][0]) c.execute(query) conn.commit() conn.close() back_to_path(cur_path)
def plot(): cur_path = change_path_to_data() # make sure the value of resolution is a lowercase L, # for 'low', not a numeral 1 map = Basemap(projection='merc', lat_0=50, lon_0=-100, resolution='h', area_thresh=0.1, # llcrnrlat=39.596377, llcrnrlon=115.08728, # urcrnrlat=40.604778, urcrnrlon=121.107788) llcrnrlat=39.831715, llcrnrlon=116.169605, urcrnrlat=40.144738, urcrnrlon=116.757374) # map = Basemap(projection='merc', lat_0=57, lon_0=-135, # resolution='h', area_thresh=0.1, # llcrnrlon=-136.25, llcrnrlat=56, # urcrnrlon=-134.25, urcrnrlat=57.75) map.drawcoastlines() map.drawcountries() # map.fillcontinents(color='coral') map.drawmapboundary() conn = sqlite3.connect('data.db') c = conn.cursor() # query = "SELECT * FROM master000 WHERE id IN (SELECT initial FROM finit)" query = "SELECT * FROM master000 LIMIT 50000" # query = "SELECT * FROM new_places" lats = [] lons = [] labels = [] for line in c.execute(query): lats.append(line[1]) lons.append(line[2]) # labels.append(str(line[0]) + " " + str(line[3]) + " " + str(line[4])) # lon = -135.3318 # lat = 57.0799 x, y = map(lons, lats) map.plot(x, y, 'bo', markersize=6) # for label, xpt, ypt in zip(labels, x, y): # plt.text(xpt, ypt, label) conn.close() plt.show() back_to_path(cur_path)
def places_using_time(): cur_path = change_path_to_data() conn = sqlite3.connect('data.db') c = conn.cursor() initial = [] final = [] added_to_final = True added_to_initial = False for u in range(0, 1): user = get_folder(u) query = "SELECT * FROM master" + user last = [] for line in c.execute(query): line = list(line) line[4] = line[4][:-1] # print(line) if added_to_initial: if get_minutes(line[4], last[4]) > 10: # print("adding to final: ", line) final.append(line) added_to_final = True added_to_initial = False continue if added_to_final: # print("adding to initial: ", line) initial.append(line) added_to_initial = True added_to_final = False last = line if added_to_initial: final.append(last) # print(len(initial)) # print(len(final)) for i in range(0, len(initial) - 1): values = [initial[i][0], final[i][0]] query = "INSERT INTO finit VALUES({},{})".format(initial[i][0], final[i][0]) c.execute(query) conn.commit() conn.close() back_to_path(cur_path)
def test_distance(): cur_path = change_path_to_data() conn = sqlite3.connect("data.db") c = conn.cursor() places = [] distances = [] query = "SELECT * FROM master000" for line in c.execute(query): places.append(list(line)) for place in places: place.append(get_distance(place[1:], places[0][1:])) places.sort(key=lambda place: -place[5]) count = 0 for place in places: print(str(place[0]) + ": " + str(place[5])) count += 1 if count == 20: break back_to_path(cur_path)
def get_places_by_date_time(): cur_path = change_path_to_data() conn = sqlite3.connect('data.db') c = conn.cursor() new_places = [] for u in range(0, 10): user = get_folder(u) # Get all dates from the database query = "SELECT DISTINCT dated FROM master" + user # 6367 for 000, 256 for 001 dates = [] for line in c.execute(query): dates.append(line[0]) places = [] # places.append([172042, 39.955832, 116.329224, '2009-07-05', '04:44:31']) # For each date in the database, get all the locations and extract places based on 10 minute difference for d in dates: query = "SELECT * FROM master" + user + " WHERE dated = '" + str(d) + "'" # print(query) prev = 0 for line in c.execute(query): line = list(line) line[4] = line[4][:-1] if prev == 0: # print(line, prev) places.append(line) prev += 1 else: minutes = get_minutes(places[prev-1][4], line[4]) # print(minutes) if minutes > 15: distance = get_distance(places[prev-1][1:3], line[1:3]) if distance > 20: if line not in places: # line.append(1) # print(line, minutes, distance) places.append(line) prev += 1 # Add entries to new_places initial = len(new_places) if len(new_places) == 0: for p in places: p.append(1) new_places.append(p) else: for p in places: index = find_place_linear(new_places, p) if index == -1: p.append(1) new_places.append(p) else: new_places[index][5] += 1 print("User: "******"Number of places: ", len(places)) print("Number of places added: ", len(new_places) - initial) # print(new_places) print("Total new places: ", len(new_places)) # Remove entries from new_places table query = "DELETE FROM new_places" c.execute(query) query = "DELETE FROM sqlite_sequence WHERE NAME = 'new_places'" c.execute(query) for place in new_places: c.execute("INSERT INTO new_places(latitude, longitude, dated, timed, weight) VALUES (?, ?, ?, ?, ?)", place[1:]) # print(place[0:4]) conn.commit() conn.close() back_to_path(cur_path)
def get_places_by_date_time(): cur_path = change_path_to_data() conn = sqlite3.connect('data.db') c = conn.cursor() new_places = [] for u in range(0, 10): user = get_folder(u) # Get all dates from the database query = "SELECT DISTINCT dated FROM master" + user # 6367 for 000, 256 for 001 dates = [] for line in c.execute(query): dates.append(line[0]) places = [] # places.append([172042, 39.955832, 116.329224, '2009-07-05', '04:44:31']) # For each date in the database, get all the locations and extract places based on 10 minute difference for d in dates: query = "SELECT * FROM master" + user + " WHERE dated = '" + str( d) + "'" # print(query) prev = 0 for line in c.execute(query): line = list(line) line[4] = line[4][:-1] if prev == 0: # print(line, prev) places.append(line) prev += 1 else: minutes = get_minutes(places[prev - 1][4], line[4]) # print(minutes) if minutes > 15: distance = get_distance(places[prev - 1][1:3], line[1:3]) if distance > 20: if line not in places: # line.append(1) # print(line, minutes, distance) places.append(line) prev += 1 # Add entries to new_places initial = len(new_places) if len(new_places) == 0: for p in places: p.append(1) new_places.append(p) else: for p in places: index = find_place_linear(new_places, p) if index == -1: p.append(1) new_places.append(p) else: new_places[index][5] += 1 print("User: "******"Number of places: ", len(places)) print("Number of places added: ", len(new_places) - initial) # print(new_places) print("Total new places: ", len(new_places)) # Remove entries from new_places table query = "DELETE FROM new_places" c.execute(query) query = "DELETE FROM sqlite_sequence WHERE NAME = 'new_places'" c.execute(query) for place in new_places: c.execute( "INSERT INTO new_places(latitude, longitude, dated, timed, weight) VALUES (?, ?, ?, ?, ?)", place[1:]) # print(place[0:4]) conn.commit() conn.close() back_to_path(cur_path)