def copy_db_to_app():
cur_path = change_path_to_data()
conn = sqlite3.connect('data.db')
c = conn.cursor()
query = "SELECT latitude, longitude, weight FROM new_places"
places = []
for line in c.execute(query):
places.append(line)
conn.close()
os.chdir('../app')
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
query = "DELETE FROM locations_location"
c.execute(query)
query = "DELETE FROM sqlite_sequence WHERE NAME = 'locations_location'"
c.execute(query)
for place in places:
c.execute(
"INSERT INTO locations_location(latitude, longitude, weight, name) VALUES(?,?,?, 'unidentified')",
place)
conn.commit()
conn.close()
def copy_db_to_app():
cur_path = change_path_to_data()
conn = sqlite3.connect("data.db")
c = conn.cursor()
query = "SELECT latitude, longitude, weight FROM new_places"
places = []
for line in c.execute(query):
places.append(line)
conn.close()
os.chdir("../app")
conn = sqlite3.connect("db.sqlite3")
c = conn.cursor()
query = "DELETE FROM locations_location"
c.execute(query)
query = "DELETE FROM sqlite_sequence WHERE NAME = 'locations_location'"
c.execute(query)
for place in places:
c.execute(
"INSERT INTO locations_location(latitude, longitude, weight, name) VALUES(?,?,?, 'unidentified')", place
)
conn.commit()
conn.close()
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 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)
