def switch_event_list(full_trip_id, trip_locations_id, switch_event_id, switch_event_name=None, event_day=None, full_day=True):
# new_trip_locations_id, new_detail = remove_event(trip_locations_id, switch_event_id)
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("SELECT name, city, county, state, coord_lat, coord_long,ranking, adjusted_visit_length FROM poi_detail_table WHERE index=%s;" % (switch_event_id))
name, city, county, state,coord_lat, coord_long,poi_rank, adjusted_normal_time_spent = cur.fetchone()
event_type = event_type_time_spent(adjusted_normal_time_spent)
avialable_lst = ajax_available_events(county, state)
cur.execute("SELECT trip_location_ids,details FROM full_trip_table WHERE full_trip_id=%s;" % (full_trip_id))
full_trip_detail = cur.fetchone()
full_trip_detail = ast.literal_eval(full_trip_detail)
full_trip_ids = [ast.literal_eval(item)['id'] for item in full_trip_detail]
switch_lst = []
for item in avialable_lst:
index = item[0]
if index not in full_trip_ids:
event_ids = [switch_event_id, index]
event_ids, google_ids, name_list, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type='switch')
if min(driving_time_list[0], walking_time_list[0]) <= 60:
cur.execute("SELECT ranking, review_score, adjusted_visit_length FROM poi_detail_table WHERE index=%s;" % (index))
target_poi_rank, target_rating, target_adjusted_normal_time_spent = cur.fetchone()
target_event_type = event_type_time_spent(target_adjusted_normal_time_spent)
switch_lst.append([target_poi_rank, target_rating, target_event_type == event_type])
#need to sort target_event_type, target_poi_rank AND target_rating
return {switch_event_id: switch_lst}
def create_day_trip(day_labels, city_poi_list_info, city, state, regular, available_days, i, v, not_visited_poi_lst):
day_trip_id = '-'.join([str(state).upper().replace(' ','-'), str(city.upper().replace(' ','-')),str(int(regular)), str(available_days),str(i)])
big_ix, med_ix,small_ix = helpers.create_big_med_small_lst(day_labels, city_poi_list_info, v)
event_ids, event_type = helpers.create_event_id_list(big_ix, med_ix, small_ix)
event_ids, event_type = helpers.db_event_cloest_distance(event_ids = event_ids, event_type = event_type, city_name = city)
event_ids, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type)
event_ids, driving_time_list, walking_time_list, total_time_spent, not_visited_poi_lst = \
helpers.db_adjust_events(event_ids, driving_time_list, walking_time_list, not_visited_poi_lst, event_type, city)
details = helpers.db_city_day_trip_details(event_ids, i, city, state)
event_ids = event_ids.tolist()
return day_trip_id, event_ids, event_type, details, not_visited_poi_lst
def add_event(trip_locations_id, event_day, new_event_id=None, event_name=None, full_day=True, unseen_event=False):
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("SELECT * FROM day_trip_table WHERE trip_locations_id='%s'" % (trip_locations_id))
(index, trip_locations_id, full_day, regular, county, state, detail, event_type, event_ids) = cur.fetchone()
if unseen_event:
index += 1
trip_locations_id = '-'.join([str(eval(i)['id']) for i in eval(detail)]) + '-' + event_name.replace(' ', '-') + '-' + event_day
cur.execute("SELECT details FROM day_trip_locations WHERE trip_locations_id='%s';" % (trip_locations_id))
a = cur.fetchone()
if bool(a):
conn.close()
return trip_locations_id, a[0]
else:
cur.execute("SELECT max(index) FROM day_trip_locations;")
index = cur.fetchone()[0] + 1
detail = list(eval(detail))
#need to make sure the type is correct for detail!
new_event = "{'address': 'None', 'id': 'None', 'day': %s, 'name': u'%s'}" % (event_day, event_name)
detail.append(new_event)
#get the right format of detail: change FROM list to string AND remove brackets AND convert quote type
new_detail = str(detail).replace('"', '').replace('[', '').replace(']', '').replace("'", '"')
cur.execute("INSERT INTO day_trip_locations VALUES (%i, '%s',%s,%s,'%s','%s','%s');" % (index, trip_locations_id, full_day, False, county, state, new_detail))
conn.commit()
conn.close()
return trip_locations_id, detail
else:
event_ids = helpers.db_event_cloest_distance(trip_locations_id, new_event_id)
event_ids, google_ids, name_list, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type='add')
trip_locations_id = '-'.join(event_ids) + '-' + event_day
cur.execute("SELECT details FROM day_trip_locations WHERE trip_locations_id='%s';" % (trip_locations_id))
if not cur.fetchone():
details = []
helpers.db_address(event_ids)
for item in event_ids:
cur.execute("SELECT index, name, address FROM poi_detail_table WHERE index = '%s';" % (item))
a = cur.fetchone()
detail = {'id': a[0],'name': a[1], 'address': a[2], 'day': event_day}
details.append(detail)
#need to make sure event detail can append to table!
cur.execute("insert into day_trip_table (trip_locations_id,full_day, regular, county, state, details, event_type, event_ids) VALUES ( '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');" % (trip_locations_id, full_day, False, county, state, details, event_type, event_ids))
conn.commit()
conn.close()
return trip_locations_id, details
else:
conn.close()
#need to make sure type is correct.
return trip_locations_id, a[0]
def switch_event_list(full_trip_id,
trip_locations_id,
switch_event_id,
switch_event_name=None,
event_day=None,
full_day=True):
# new_trip_locations_id, new_detail = remove_event(trip_locations_id, switch_event_id)
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute(
"SELECT name, city, county, state, coord_lat, coord_long,ranking, adjusted_visit_length FROM poi_detail_table WHERE index=%s;"
% (switch_event_id))
name, city, county, state, coord_lat, coord_long, poi_rank, adjusted_normal_time_spent = cur.fetchone(
)
event_type = event_type_time_spent(adjusted_normal_time_spent)
avialable_lst = ajax_available_events(county, state)
cur.execute(
"SELECT trip_location_ids,details FROM full_trip_table WHERE full_trip_id=%s;"
% (full_trip_id))
full_trip_detail = cur.fetchone()
full_trip_detail = ast.literal_eval(full_trip_detail)
full_trip_ids = [ast.literal_eval(item)['id'] for item in full_trip_detail]
switch_lst = []
for item in avialable_lst:
index = item[0]
if index not in full_trip_ids:
event_ids = [switch_event_id, index]
event_ids, google_ids, name_list, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(
event_ids, event_type='switch')
if min(driving_time_list[0], walking_time_list[0]) <= 60:
cur.execute(
"SELECT ranking, review_score, adjusted_visit_length FROM poi_detail_table WHERE index=%s;"
% (index))
target_poi_rank, target_rating, target_adjusted_normal_time_spent = cur.fetchone(
)
target_event_type = event_type_time_spent(
target_adjusted_normal_time_spent)
switch_lst.append([
target_poi_rank, target_rating,
target_event_type == event_type
])
#need to sort target_event_type, target_poi_rank AND target_rating
return {switch_event_id: switch_lst}
def get_fulltrip_data(state, city, n_days, full_day=True, regular=True, debug=True, visible=True):
'''
Get the default full trip data for each city(county)
'''
counties = helpers.find_county(state, city)
n_days = int(n_days)
if counties:
counties_str = '-'.join(counties).upper().replace(' ','-')
full_trip_id = '-'.join([str(state.upper()), counties_str,str(int(regular)), str(n_days)])
else:
full_trip_id = '-'.join([str(state.upper()), str(city.upper().replace(' ','-')),str(int(regular)), str(n_days)])
if not helpers.check_full_trip_id(full_trip_id):
trip_location_ids, full_trip_details,county_list_info =[],[],[]
county_list_info = helpers.db_start_location(counties, state, city)
county_list_info = np.array(county_list_info)
if county_list_info.shape[0] == 0:
print city, state, county, "is not in our database!!!!?"
return city, state, county
new_end_day = max(county_list_info.shape[0]/6, 1)
if n_days > new_end_day:
return get_fulltrip_data(state, city, new_end_day)
poi_coords = county_list_info[:,1:3]
kmeans = KMeans(n_clusters=n_days).fit(poi_coords)
day_labels = kmeans.labels_
day_order = helpers.kmeans_leabels_day_order(day_labels)
not_visited_poi_lst = []
for i,v in enumerate(day_order):
if counties:
counties_str = '-'.join(counties).upper().replace(' ','-')
day_trip_id = '-'.join([str(state.upper()), counties_str,str(int(regular)), str(n_days), str(i)])
else:
day_trip_id = '-'.join([str(state).upper(), str(city.upper().replace(' ','-')),str(int(regular)), str(n_days),str(i)])
current_events, big_ix, small_ix, med_ix = [],[],[],[]
for ix, label in enumerate(day_labels):
if label == v:
time = county_list_info[ix,3]
event_ix = county_list_info[ix,0]
current_events.append(event_ix)
if time > 180 :
big_ix.append(ix)
elif time >= 120 :
med_ix.append(ix)
else:
small_ix.append(ix)
big_ = helpers.sorted_events(county_list_info, big_ix)
med_ = helpers.sorted_events(county_list_info, med_ix)
small_ = helpers.sorted_events(county_list_info, small_ix)
event_ids, event_type = helpers.create_event_id_list(big_, med_, small_)
event_ids, event_type = helpers.db_event_cloest_distance(event_ids = event_ids, event_type = event_type, city_name = city)
event_ids, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type)
event_ids, driving_time_list, walking_time_list, total_time_spent, not_visited_poi_lst = \
helpers.db_adjust_events(event_ids, driving_time_list, walking_time_list, not_visited_poi_lst, event_type, city)
details = helpers.db_day_trip_details(event_ids, i)
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute('select max(index) from day_trip_table;')
max_index = cur.fetchone()[0]
index = max_index + 1
if helpers.check_day_trip_id(day_trip_id):
cur.execute("SELECT index FROM day_trip_table WHERE trip_locations_id = '%s';" % (day_trip_id))
cur = conn.cursor()
index = cur.fetchone()[0]
cur.execute("DELETE FROM day_trip_table WHERE trip_locations_id = '%s';" % (day_trip_id))
conn.commit()
if counties:
cur.execute("insert into day_trip_table (index, trip_locations_id, full_day, regular, county, state, details, event_type, event_ids) VALUES ( %s, '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');" %(index, day_trip_id, full_day, regular, json.dumps(counties), state, str(details).replace("'", "''"), event_type, str(list(event_ids))))
else:
cur.execute("insert into day_trip_table (index, trip_locations_id, full_day, regular, county, state, details, event_type, event_ids) VALUES ( %s, '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');" %(index, day_trip_id, full_day, regular, counties, state, str(details).replace("'", "''"), event_type, str(list(event_ids))))
conn.commit()
conn.close()
trip_location_ids.append(day_trip_id)
full_trip_details.extend(details)
username_id = 1
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("select max(index) from full_trip_table;")
full_trip_index = cur.fetchone()[0] + 1
if counties:
cur.execute("insert into full_trip_table(index, username_id, full_trip_id,trip_location_ids, regular, county, state, details, n_days, visible) VALUES (%s, %s, '%s', '%s', %s, '%s', '%s', '%s', %s, %s);" %(full_trip_index, username_id , full_trip_id, str(trip_location_ids).replace("'","''"), regular, json.dumps(counties), state, str(full_trip_details).replace("'", "''"), n_days, visible))
else:
cur.execute("insert into full_trip_table(index, username_id, full_trip_id,trip_location_ids, regular, county, state, details, n_days, visible) VALUES (%s, %s, '%s', '%s', %s, '%s', '%s', '%s', %s, %s);" %(full_trip_index, username_id , full_trip_id, str(trip_location_ids).replace("'","''"), regular, counties, state, str(full_trip_details).replace("'", "''"), n_days, visible))
conn.commit()
conn.close()
print "finish update %s, %s into database" %(state, str(counties))
else:
print "%s, %s already in database" %(state, str(counties))
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("select trip_location_ids, details from full_trip_table where full_trip_id = '%s';" % (full_trip_id))
trip_location_ids, details = cur.fetchone()
conn.close()
full_trip_details = ast.literal_eval(details)
trip_location_ids = ast.literal_eval(trip_location_ids)
return full_trip_id, full_trip_details, trip_location_ids
def get_city_trip_data(state, city, n_days, full_day=True, regular=True, visit_speed='normal', visible=True):
'''
Get the default full trip data for each city(county)
'''
n_days = int(n_days)
if visit_speed == 'normal':
num_poi_per_day = 8
else:
num_poi_per_day = 8
full_trip_id = '-'.join([str(state.upper()).replace(' ','-'), str(city.upper().replace(' ','-')),str(int(regular)), str(n_days)])
if not helpers.check_full_trip_id_city(full_trip_id):
trip_location_ids, full_trip_details =[],[]
city_poi_list_info = helpers.db_start_city_poi(city,state)
available_days = len(city_poi_list_info)/num_poi_per_day
available_surr_days, city_surr_poi_list_info = 0, []
city_day_labels, city_day_order, city_surr_day_labels,city_surr_day_order = [],[],[],[]
if available_days < n_days:
city_surr_poi_list_info, available_surr_days = helpers.db_city_and_surrounding_poi(city, state, n_days-available_days, num_poi_per_day)
else:
available_days = n_days
if (not city_poi_list_info) and (not city_surr_poi_list_info):
print 'there is no availables pois for the city {0}, {1} and surrounding areas'.format(city, state)
available_days = 0
return full_trip_id, [], [], available_days
if available_days > 0:
city_poi_coords_lst = np.array(city_poi_list_info)[:,1:3]
kmeans = KMeans(n_clusters=available_days).fit(city_poi_coords_lst)
city_day_labels = kmeans.labels_
city_day_order = helpers.kmeans_leabels_day_order(city_day_labels)
if available_surr_days > 0:
city_surr_poi_coords_lst = np.array(city_surr_poi_list_info)[:,1:3]
kmeans = KMeans(n_clusters=available_surr_days).fit(city_surr_poi_coords_lst)
city_surr_day_labels = kmeans.labels_
city_surr_day_order = helpers.kmeans_leabels_day_order(city_surr_day_labels)
poi_list_info = city_poi_list_info + city_surr_poi_list_info
day_labels = np.concatenate((city_day_labels, [surr_label+available_days for surr_label in city_surr_day_labels]), axis=0)
day_order = np.concatenate((city_day_order, [order+available_days for order in city_surr_day_order]), axis=0)
total_available_days = available_days+available_surr_days
else:
poi_list_info = city_poi_list_info
day_labels = city_day_labels
day_order = city_day_order
total_available_days = available_days
not_visited_poi_lst = []
old_big_ix, old_med_ix,old_small_ix = [],[],[]
for i,v in enumerate(day_order):
day_trip_id = '-'.join([str(state).upper().replace(' ','-'), str(city.upper().replace(' ','-')),str(int(regular)), str(total_available_days),str(i)])
big_ix, med_ix, small_ix = helpers.create_big_med_small_lst(day_labels, poi_list_info, v)
city_poi_list_info = np.array(poi_list_info)[:,:7].astype(np.float)
big_ix, med_ix, small_ix = helpers.sorted_events(city_poi_list_info, big_ix, old_big_ix),helpers.sorted_events(city_poi_list_info, med_ix, old_med_ix),helpers.sorted_events(city_poi_list_info, small_ix,old_small_ix)
event_ids, event_type = helpers.create_event_id_list(big_ix, med_ix, small_ix)
event_ids, event_type = helpers.db_event_cloest_distance(event_ids = event_ids, event_type = event_type, city_name = city)
event_ids, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type)
event_ids, driving_time_list, walking_time_list, total_time_spent, not_visited_poi_lst = \
helpers.db_adjust_events(event_ids, driving_time_list, walking_time_list, not_visited_poi_lst, event_type, city)
old_big_ix = np.array([b for b in big_ix if b[0] not in event_ids])
old_med_ix =np.array([m for m in med_ix if m[0] not in event_ids])
old_small_ix = np.array([s for s in small_ix if s[0] not in event_ids])
details = helpers.db_city_day_trip_details(event_ids, i, city, state)
event_ids = map(int,event_ids)
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute('SELECT max(index) FROM day_trip_table_city;')
max_index = cur.fetchone()[0]
index = max_index + 1
if helpers.check_day_trip_id_city(day_trip_id):
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("SELECT index FROM day_trip_table_city WHERE trip_locations_id = %s;",(day_trip_id,))
index = cur.fetchone()[0]
cur.execute("DELETE FROM day_trip_table_city WHERE trip_locations_id = %s;",(day_trip_id,))
conn.commit()
cur.execute("INSERT INTO day_trip_table_city (index, trip_locations_id, full_day, regular, city, state, details, event_type, event_ids) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s);",(index, day_trip_id, full_day, regular, city, state, json.dumps(details), event_type, json.dumps(event_ids)))
conn.commit()
conn.close()
trip_location_ids.append(day_trip_id)
full_trip_details.extend(details)
username_id = 1
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("SELECT max(index) FROM full_trip_table_city;")
full_trip_index = cur.fetchone()[0] + 1
cur.execute("INSERT INTO full_trip_table_city (index, username_id, full_trip_id, trip_location_ids, regular, city, state, details, n_days, visible) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);" ,(full_trip_index, username_id , full_trip_id, json.dumps(trip_location_ids), regular, city, state, json.dumps(full_trip_details), n_days, visible))
conn.commit()
conn.close()
print "finish update %s, %s into database" %(state, city)
else:
print "%s, %s already in database" %(state, city)
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("select trip_location_ids, details from full_trip_table_city where full_trip_id = '%s';" % (full_trip_id))
trip_location_ids, details = cur.fetchone()
conn.close()
full_trip_details = json.loads(details)
trip_location_ids = json.loads(trip_location_ids)
trip_location_ids =[str(x) for x in trip_location_ids]
print trip_location_ids, type(trip_location_ids)
return full_trip_id, full_trip_details, trip_location_ids
def db_outside_google_driving_walking_time(city_id, start_coord_lat, start_coord_long, event_ids, event_type, origin_city, origin_state):
'''
Get estimated travel time FROM google api.
Limit 1000 calls per day.
'''
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
google_ids = []
driving_time_list = []
walking_time_list = []
name_list = []
api_i = 0
city_to_poi_id = str(int(city_id)) + '0000' + str(int(event_ids[0]))
if not check_city_to_poi(city_to_poi_id):
cur.execute("SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;" % (event_ids[0]))
dest_name, dest_coord_lat, dest_coord_long = cur.fetchone()
orig_coords = str(start_coord_lat) + ',' + str(start_coord_long)
dest_coords = str(dest_coord_lat) + ',' + str(dest_coord_long)
orig_name = origin_city
google_result = helpers.find_google_result(orig_coords, dest_coords, orig_name, dest_name, api_i)
while google_result == False:
api_i += 1
if api_i > len(api_key)-1:
print "all api_key are used"
else:
google_result = helpers.find_google_result(orig_coords, dest_coords, orig_name, dest_name, api_i)
driving_result, walking_result, google_driving_url, google_walking_url = google_result
if (driving_result['rows'][0]['elements'][0]['status'] == 'NOT_FOUND') and (walking_result['rows'][0]['elements'][0]['status'] == 'NOT_FOUND'):
new_event_ids = list(event_ids)
new_event_ids.pop(0)
new_event_ids = db_outside_event_cloest_distance(start_coord_lat, start_coord_long, event_ids=new_event_ids, event_type=event_type)
return db_outside_google_driving_walking_time(city_id, start_coord_lat, start_coord_long, new_event_ids, event_type, origin_city, origin_state)
try:
city_to_poi_driving_time = driving_result['rows'][0]['elements'][0]['duration']['value'] / 60
except:
print city, state, dest_name, driving_result #need to debug for this
try:
city_to_poi_walking_time = walking_result['rows'][0]['elements'][0]['duration']['value'] / 60
except:
city_to_poi_walking_time = 9999
'''
Need to work on rest of it!
'''
cur.execute("SELECT max(index) FROM google_city_to_poi_table")
index = cur.fetchone()[0]+1
driving_result = str(driving_result).replace("'", '"')
walking_result = str(walking_result).replace("'", '"')
orig_name = orig_name.replace("'", "''")
dest_name = dest_name.replace("'", "''")
cur.execute("INSERT INTO google_city_to_poi_table VALUES (%i, %s, %i, '%s','%s', '%s','%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', %s, %s);" % (index, city_to_poi_id, city_id, origin_city.replace("'", "''"), origin_state, orig_name, dest_name, event_ids[0], start_coord_lat, start_coord_long, dest_coord_lat, dest_coord_long, orig_coords, dest_coords, google_driving_url, google_walking_url, str(driving_result), str(walking_result), city_to_poi_driving_time,city_to_poi_walking_time))
conn.commit()
name_list.extend([orig_name + " to " + dest_name,dest_name + " to " + orig_name])
google_ids.extend([city_to_poi_id] * 2)
driving_time_list.extend([city_to_poi_driving_time] * 2)
walking_time_list.extend([city_to_poi_walking_time] * 2)
else:
cur.execute("SELECT orig_name, dest_name, city_to_poi_driving_time, city_to_poi_walking_time FROM google_city_to_poi_table WHERE city_to_poi_id = %s;" %(city_to_poi_id))
orig_name, dest_name, city_to_poi_driving_time, city_to_poi_walking_time = cur.fetchone()
name_list.append(orig_name + " to " + dest_name)
google_ids.extend([city_to_poi_id] * 2)
driving_time_list.extend([city_to_poi_driving_time] * 2)
walking_time_list.extend([city_to_poi_walking_time] * 2)
for i,v in enumerate(event_ids[:-1]):
id_ = str(int(v)) + '0000' + str(int(event_ids[i+1]))
result_check_travel_time_id = helpers.check_travel_time_id(id_)
if not result_check_travel_time_id:
cur.execute("SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;" % (v))
orig_name, orig_coord_lat, orig_coord_long = cur.fetchone()
orig_idx = v
cur.execute("SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;" % (event_ids[i + 1]))
dest_name, dest_coord_lat, dest_coord_long = cur.fetchone()
dest_idx = event_ids[i+1]
orig_coords = str(orig_coord_lat) + ',' + str(orig_coord_long)
dest_coords = str(dest_coord_lat) + ',' + str(dest_coord_long)
google_result = helpers.find_google_result(orig_coords, dest_coords, orig_name, dest_name, api_i)
while google_result == False:
api_i += 1
if api_i > len(api_key)-1:
print "all api_key are used"
else:
google_result = helpers.find_google_result(orig_coords, dest_coords, orig_name, dest_name, api_i)
driving_result, walking_result, google_driving_url, google_walking_url = google_result
if (driving_result['rows'][0]['elements'][0]['status'] == 'NOT_FOUND') and (walking_result['rows'][0]['elements'][0]['status'] == 'NOT_FOUND'):
new_event_ids = list(event_ids)
new_event_ids.pop(i+1)
new_event_ids = helpers.db_event_cloest_distance(event_ids=new_event_ids, event_type=event_type)
return helpers.db_google_driving_walking_time(new_event_ids, event_type)
try:
google_driving_time = driving_result['rows'][0]['elements'][0]['duration']['value']/60
except:
print v, id_, driving_result #need to debug for this
try:
google_walking_time = walking_result['rows'][0]['elements'][0]['duration']['value']/60
except:
google_walking_time = 9999
cur.execute("SELECT max(index) FROM google_travel_time_table")
index = cur.fetchone()[0] + 1
driving_result = str(driving_result).replace("'", '"')
walking_result = str(walking_result).replace("'", '"')
orig_name = orig_name.replace("'","''")
dest_name = dest_name.replace("'","''")
cur.execute("INSERT INTO google_travel_time_table VALUES (%i, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', %s, %s);" % (index, id_, orig_name, orig_idx, dest_name, dest_idx, orig_coord_lat, orig_coord_long, dest_coord_long, dest_coord_long, orig_coords, dest_coords, google_driving_url, google_walking_url, str(driving_result), str(walking_result), google_driving_time, google_walking_time))
conn.commit()
name_list.append(orig_name + " to " + dest_name)
google_ids.append(id_)
driving_time_list.append(google_driving_time)
walking_time_list.append(google_walking_time)
else:
cur.execute("SELECT orig_name, dest_name, google_driving_time, google_walking_time FROM google_travel_time_table WHERE id_field = '%s';" % (id_))
orig_name, dest_name, google_driving_time, google_walking_time = cur.fetchone()
name_list.append(orig_name + " to " + dest_name)
google_ids.append(id_)
driving_time_list.append(google_driving_time)
walking_time_list.append(google_walking_time)
conn.close()
return event_ids, google_ids, name_list, driving_time_list, walking_time_list
def get_fulltrip_data(state, city, n_days, full_day=True, regular=True, debug=True, visible=True):
'''
Get the default full trip data for each city(county)
'''
counties = helpers.find_county(state, city)
n_days = int(n_days)
if counties:
counties_str = '-'.join(counties).upper().replace(' ','-')
full_trip_id = '-'.join([str(state.upper()), counties_str,str(int(regular)), str(n_days)])
else:
full_trip_id = '-'.join([str(state.upper()), str(city.upper().replace(' ','-')),str(int(regular)), str(n_days)])
if not helpers.check_full_trip_id(full_trip_id, debug):
trip_location_ids, full_trip_details,county_list_info =[],[],[]
county_list_info = helpers.db_start_location(counties, state, city)
county_list_info = np.array(county_list_info)
# print county_list_info
if county_list_info.shape[0] == 0:
print city, state, county, "is not in our database!!!!?"
return city, state, county
new_end_day = max(county_list_info.shape[0]/6, 1)
if n_days > new_end_day:
return get_fulltrip_data(state, city, new_end_day)
# time_spent = county_list_info[:,3]
poi_coords = county_list_info[:,1:3]
kmeans = KMeans(n_clusters=n_days).fit(poi_coords)
day_labels = kmeans.labels_
day_order = helpers.kmeans_leabels_day_order(day_labels)
# print day_labels, day_order
not_visited_poi_lst = []
for i,v in enumerate(day_order):
if counties:
counties_str = '-'.join(counties).upper().replace(' ','-')
day_trip_id = '-'.join([str(state.upper()), counties_str,str(int(regular)), str(n_days), str(i)])
else:
day_trip_id = '-'.join([str(state).upper(), str(city.upper().replace(' ','-')),str(int(regular)), str(n_days),str(i)])
current_events, big_ix, small_ix, med_ix = [],[],[],[]
for ix, label in enumerate(day_labels):
if label == v:
time = county_list_info[ix,3]
event_ix = county_list_info[ix,0]
current_events.append(event_ix)
if time > 180 :
big_ix.append(ix)
elif time >= 120 :
med_ix.append(ix)
else:
small_ix.append(ix)
# print big_ix, med_ix, small_ix
big_ = helpers.sorted_events(county_list_info, big_ix)
med_ = helpers.sorted_events(county_list_info, med_ix)
small_ = helpers.sorted_events(county_list_info, small_ix)
# if len(big_)+len(med_)+len(small_)==0:
# print "not more event for days " , day_trip_id
# # return [day_trip_id, "not more event for days " ]
# break
# print big_, med_, small_
event_ids, event_type = helpers.create_event_id_list(big_, med_, small_)
# print event_ids, event_type
event_ids, event_type = helpers.db_event_cloest_distance(event_ids = event_ids, event_type = event_type, city_name = city)
# event_ids, google_ids, name_list, driving_time_list, walking_time_list = \
event_ids, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(event_ids, event_type)
# print 'event_ids, google_ids, name_list', event_ids, google_ids, name_list
# print 'driving and walking time list: ', driving_time_list, walking_time_list
# event_ids, driving_time_list, walking_time_list, total_time_spent = db_remove_extra_events(event_ids, driving_time_list, walking_time_list)
event_ids, driving_time_list, walking_time_list, total_time_spent, not_visited_poi_lst = \
helpers.db_adjust_events(event_ids, driving_time_list, walking_time_list, not_visited_poi_lst, event_type, city)
# db_address(event_ids)
details = helpers.db_day_trip_details(event_ids, i)
#insert to day_trip ....
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute('select max(index) from day_trip_table;')
max_index = cur.fetchone()[0]
index = max_index + 1
#if exisitng day trip id..remove those...
if helpers.check_day_trip_id(day_trip_id):
cur.execute("SELECT index FROM day_trip_table WHERE trip_locations_id = '%s';" % (day_trip_id))
cur = conn.cursor()
index = cur.fetchone()[0]
cur.execute("DELETE FROM day_trip_table WHERE trip_locations_id = '%s';" % (day_trip_id))
conn.commit()
if counties:
cur.execute("insert into day_trip_table (index, trip_locations_id, full_day, regular, county, state, details, event_type, event_ids) VALUES ( %s, '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');" %(index, day_trip_id, full_day, regular, json.dumps(counties), state, str(details).replace("'", "''"), event_type, str(list(event_ids))))
else:
cur.execute("insert into day_trip_table (index, trip_locations_id, full_day, regular, county, state, details, event_type, event_ids) VALUES ( %s, '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');" %(index, day_trip_id, full_day, regular, counties, state, str(details).replace("'", "''"), event_type, str(list(event_ids))))
conn.commit()
conn.close()
trip_location_ids.append(day_trip_id)
full_trip_details.extend(details)
# full_trip_id = '-'.join([str(state.upper()), str(county.upper().replace(' ','-')),str(int(regular)), str(n_days)])
username_id = 1
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("select max(index) from full_trip_table;")
full_trip_index = cur.fetchone()[0] + 1
if counties:
cur.execute("insert into full_trip_table(index, username_id, full_trip_id,trip_location_ids, regular, county, state, details, n_days, visible) VALUES (%s, %s, '%s', '%s', %s, '%s', '%s', '%s', %s, %s);" %(full_trip_index, username_id , full_trip_id, str(trip_location_ids).replace("'","''"), regular, json.dumps(counties), state, str(full_trip_details).replace("'", "''"), n_days, visible))
else:
cur.execute("insert into full_trip_table(index, username_id, full_trip_id,trip_location_ids, regular, county, state, details, n_days, visible) VALUES (%s, %s, '%s', '%s', %s, '%s', '%s', '%s', %s, %s);" %(full_trip_index, username_id , full_trip_id, str(trip_location_ids).replace("'","''"), regular, counties, state, str(full_trip_details).replace("'", "''"), n_days, visible))
conn.commit()
conn.close()
print "finish update %s, %s into database" %(state, str(counties))
else:
print "%s, %s already in database" %(state, str(counties))
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("select trip_location_ids, details from full_trip_table where full_trip_id = '%s';" % (full_trip_id))
trip_location_ids, details = cur.fetchone()
conn.close()
full_trip_details = ast.literal_eval(details)
trip_location_ids = ast.literal_eval(trip_location_ids)
# full_trip_details = json.loads(details)
# trip_location_ids = json.loads(trip_location_ids)
print 'full trip notes: ', full_trip_id, full_trip_details, trip_location_ids
return full_trip_id, full_trip_details, trip_location_ids
def db_outside_google_driving_walking_time(city_id, start_coord_lat,
start_coord_long, event_ids,
event_type, origin_city,
origin_state):
'''
Get estimated travel time FROM google api.
Limit 1000 calls per day.
'''
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
google_ids = []
driving_time_list = []
walking_time_list = []
name_list = []
api_i = 0
city_to_poi_id = str(int(city_id)) + '0000' + str(int(event_ids[0]))
if not check_city_to_poi(city_to_poi_id):
cur.execute(
"SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;"
% (event_ids[0]))
dest_name, dest_coord_lat, dest_coord_long = cur.fetchone()
orig_coords = str(start_coord_lat) + ',' + str(start_coord_long)
dest_coords = str(dest_coord_lat) + ',' + str(dest_coord_long)
orig_name = origin_city
google_result = helpers.find_google_result(orig_coords, dest_coords,
orig_name, dest_name, api_i)
while google_result == False:
api_i += 1
if api_i > len(api_key) - 1:
print "all api_key are used"
else:
google_result = helpers.find_google_result(
orig_coords, dest_coords, orig_name, dest_name, api_i)
driving_result, walking_result, google_driving_url, google_walking_url = google_result
if (driving_result['rows'][0]['elements'][0]['status'] == 'NOT_FOUND'
) and (walking_result['rows'][0]['elements'][0]['status']
== 'NOT_FOUND'):
new_event_ids = list(event_ids)
new_event_ids.pop(0)
new_event_ids = db_outside_event_cloest_distance(
start_coord_lat,
start_coord_long,
event_ids=new_event_ids,
event_type=event_type)
return db_outside_google_driving_walking_time(
city_id, start_coord_lat, start_coord_long, new_event_ids,
event_type, origin_city, origin_state)
try:
city_to_poi_driving_time = driving_result['rows'][0]['elements'][
0]['duration']['value'] / 60
except:
print city, state, dest_name, driving_result #need to debug for this
try:
city_to_poi_walking_time = walking_result['rows'][0]['elements'][
0]['duration']['value'] / 60
except:
city_to_poi_walking_time = 9999
'''
Need to work on rest of it!
'''
cur.execute("SELECT max(index) FROM google_city_to_poi_table")
index = cur.fetchone()[0] + 1
driving_result = str(driving_result).replace("'", '"')
walking_result = str(walking_result).replace("'", '"')
orig_name = orig_name.replace("'", "''")
dest_name = dest_name.replace("'", "''")
cur.execute(
"INSERT INTO google_city_to_poi_table VALUES (%i, %s, %i, '%s','%s', '%s','%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', %s, %s);"
% (index, city_to_poi_id, city_id, origin_city.replace(
"'", "''"), origin_state, orig_name, dest_name, event_ids[0],
start_coord_lat, start_coord_long, dest_coord_lat,
dest_coord_long, orig_coords, dest_coords, google_driving_url,
google_walking_url, str(driving_result), str(walking_result),
city_to_poi_driving_time, city_to_poi_walking_time))
conn.commit()
name_list.extend(
[orig_name + " to " + dest_name, dest_name + " to " + orig_name])
google_ids.extend([city_to_poi_id] * 2)
driving_time_list.extend([city_to_poi_driving_time] * 2)
walking_time_list.extend([city_to_poi_walking_time] * 2)
else:
cur.execute(
"SELECT orig_name, dest_name, city_to_poi_driving_time, city_to_poi_walking_time FROM google_city_to_poi_table WHERE city_to_poi_id = %s;"
% (city_to_poi_id))
orig_name, dest_name, city_to_poi_driving_time, city_to_poi_walking_time = cur.fetchone(
)
name_list.append(orig_name + " to " + dest_name)
google_ids.extend([city_to_poi_id] * 2)
driving_time_list.extend([city_to_poi_driving_time] * 2)
walking_time_list.extend([city_to_poi_walking_time] * 2)
for i, v in enumerate(event_ids[:-1]):
id_ = str(int(v)) + '0000' + str(int(event_ids[i + 1]))
result_check_travel_time_id = helpers.check_travel_time_id(id_)
if not result_check_travel_time_id:
cur.execute(
"SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;"
% (v))
orig_name, orig_coord_lat, orig_coord_long = cur.fetchone()
orig_idx = v
cur.execute(
"SELECT name, coord_lat, coord_long FROM poi_detail_table WHERE index = %s;"
% (event_ids[i + 1]))
dest_name, dest_coord_lat, dest_coord_long = cur.fetchone()
dest_idx = event_ids[i + 1]
orig_coords = str(orig_coord_lat) + ',' + str(orig_coord_long)
dest_coords = str(dest_coord_lat) + ',' + str(dest_coord_long)
google_result = helpers.find_google_result(orig_coords,
dest_coords, orig_name,
dest_name, api_i)
while google_result == False:
api_i += 1
if api_i > len(api_key) - 1:
print "all api_key are used"
else:
google_result = helpers.find_google_result(
orig_coords, dest_coords, orig_name, dest_name, api_i)
driving_result, walking_result, google_driving_url, google_walking_url = google_result
if (driving_result['rows'][0]['elements'][0]['status']
== 'NOT_FOUND') and (
walking_result['rows'][0]['elements'][0]['status']
== 'NOT_FOUND'):
new_event_ids = list(event_ids)
new_event_ids.pop(i + 1)
new_event_ids = helpers.db_event_cloest_distance(
event_ids=new_event_ids, event_type=event_type)
return helpers.db_google_driving_walking_time(
new_event_ids, event_type)
try:
google_driving_time = driving_result['rows'][0]['elements'][0][
'duration']['value'] / 60
except:
print v, id_, driving_result #need to debug for this
try:
google_walking_time = walking_result['rows'][0]['elements'][0][
'duration']['value'] / 60
except:
google_walking_time = 9999
cur.execute("SELECT max(index) FROM google_travel_time_table")
index = cur.fetchone()[0] + 1
driving_result = str(driving_result).replace("'", '"')
walking_result = str(walking_result).replace("'", '"')
orig_name = orig_name.replace("'", "''")
dest_name = dest_name.replace("'", "''")
cur.execute(
"INSERT INTO google_travel_time_table VALUES (%i, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', %s, %s);"
%
(index, id_, orig_name, orig_idx, dest_name, dest_idx,
orig_coord_lat, orig_coord_long, dest_coord_long,
dest_coord_long, orig_coords, dest_coords, google_driving_url,
google_walking_url, str(driving_result), str(walking_result),
google_driving_time, google_walking_time))
conn.commit()
name_list.append(orig_name + " to " + dest_name)
google_ids.append(id_)
driving_time_list.append(google_driving_time)
walking_time_list.append(google_walking_time)
else:
cur.execute(
"SELECT orig_name, dest_name, google_driving_time, google_walking_time FROM google_travel_time_table WHERE id_field = '%s';"
% (id_))
orig_name, dest_name, google_driving_time, google_walking_time = cur.fetchone(
)
name_list.append(orig_name + " to " + dest_name)
google_ids.append(id_)
driving_time_list.append(google_driving_time)
walking_time_list.append(google_walking_time)
conn.close()
return event_ids, google_ids, name_list, driving_time_list, walking_time_list
def add_event(trip_locations_id,
event_day,
new_event_id=None,
event_name=None,
full_day=True,
unseen_event=False):
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("SELECT * FROM day_trip_table WHERE trip_locations_id='%s'" %
(trip_locations_id))
(index, trip_locations_id, full_day, regular, county, state, detail,
event_type, event_ids) = cur.fetchone()
if unseen_event:
index += 1
trip_locations_id = '-'.join([
str(eval(i)['id']) for i in eval(detail)
]) + '-' + event_name.replace(' ', '-') + '-' + event_day
cur.execute(
"SELECT details FROM day_trip_locations WHERE trip_locations_id='%s';"
% (trip_locations_id))
a = cur.fetchone()
if bool(a):
conn.close()
return trip_locations_id, a[0]
else:
cur.execute("SELECT max(index) FROM day_trip_locations;")
index = cur.fetchone()[0] + 1
detail = list(eval(detail))
#need to make sure the type is correct for detail!
new_event = "{'address': 'None', 'id': 'None', 'day': %s, 'name': u'%s'}" % (
event_day, event_name)
detail.append(new_event)
#get the right format of detail: change FROM list to string AND remove brackets AND convert quote type
new_detail = str(detail).replace('"', '').replace('[', '').replace(
']', '').replace("'", '"')
cur.execute(
"INSERT INTO day_trip_locations VALUES (%i, '%s',%s,%s,'%s','%s','%s');"
% (index, trip_locations_id, full_day, False, county, state,
new_detail))
conn.commit()
conn.close()
return trip_locations_id, detail
else:
event_ids = helpers.db_event_cloest_distance(trip_locations_id,
new_event_id)
event_ids, google_ids, name_list, driving_time_list, walking_time_list = helpers.db_google_driving_walking_time(
event_ids, event_type='add')
trip_locations_id = '-'.join(event_ids) + '-' + event_day
cur.execute(
"SELECT details FROM day_trip_locations WHERE trip_locations_id='%s';"
% (trip_locations_id))
if not cur.fetchone():
details = []
helpers.db_address(event_ids)
for item in event_ids:
cur.execute(
"SELECT index, name, address FROM poi_detail_table WHERE index = '%s';"
% (item))
a = cur.fetchone()
detail = {
'id': a[0],
'name': a[1],
'address': a[2],
'day': event_day
}
details.append(detail)
#need to make sure event detail can append to table!
cur.execute(
"insert into day_trip_table (trip_locations_id,full_day, regular, county, state, details, event_type, event_ids) VALUES ( '%s', %s, %s, '%s', '%s', '%s', '%s', '%s');"
% (trip_locations_id, full_day, False, county, state, details,
event_type, event_ids))
conn.commit()
conn.close()
return trip_locations_id, details
else:
conn.close()
#need to make sure type is correct.
return trip_locations_id, a[0]