def score_location(current_location, path, db_path): ''' This function is used for comparing a user's given location against the scores for 1,000 most populous cities in the country. It returns a string the gives the percentile their location falls into. Input: current_location: user's input path: The path to the scored registry of cities db_path: The path to the ski-resorts database ''' conn = lite.connect(db_path) c = conn.cursor() conn.create_function('time_between', 4, compute_time_between) lat, lon, city, state = get_lat_lon(current_location, locality=True) params = (lon,lat,lon,lat) query = "SELECT SUM(area), SUM(time_between(lon, lat, ?, ?)), COUNT(*)\ FROM main WHERE time_between(lon, lat, ?, ?) < 3.25" result = c.execute(query, params) area, time, count = list(result)[0] score = (area)/(time/count) info = (city, state, area, time, count,lat,lon,score) conn.commit() conn.close() percentile = compare_score(info, path) if type(percentile) == str: return percentile else: rv = (city + ", " + state + " is in the " + str(round(((1-percentile) * 100),1)) + " percentile of places to live for access to ski resorts") return rv
def gather_city_data(city_csv, db_name, output_name): ''' This function creates a csv that saves the total acreage, number of resorts and time to drive to them for all resorts within a 3.25 hour drive of the city. ''' conn = lite.connect(db_name) c = conn.cursor() conn.create_function('time_between', 4, compute_time_between) cities = pd.read_csv(city_csv) rows = [] for i, city in cities.iterrows(): lat, lon = get_lat_lon(city['city'] + " " + city['state']) params = (lon,lat,lon,lat) query = "SELECT SUM(area), SUM(time_between(lon, lat, ?, ?)), COUNT(*) \ FROM main WHERE time_between(lon, lat, ?, ?) < 3.25" result = c.execute(query, params) area, time, count = list(result)[0] rows.append((city['city'], city['state'], area, time, count, lat, lon)) conn.commit() conn.close() labels = ['city','state','area','time','number', 'lat', 'lon'] csv_writer(labels,rows,output_name)
def gather_city_data(city_csv, db_name, output_name): ''' This function creates a csv that saves the total acreage, number of resorts and time to drive to them for all resorts within a 3.25 hour drive of the city. ''' conn = lite.connect(db_name) c = conn.cursor() conn.create_function('time_between', 4, compute_time_between) cities = pd.read_csv(city_csv) rows = [] for i, city in cities.iterrows(): lat, lon = get_lat_lon(city['city'] + " " + city['state']) params = (lon, lat, lon, lat) query = "SELECT SUM(area), SUM(time_between(lon, lat, ?, ?)), COUNT(*) \ FROM main WHERE time_between(lon, lat, ?, ?) < 3.25" result = c.execute(query, params) area, time, count = list(result)[0] rows.append((city['city'], city['state'], area, time, count, lat, lon)) conn.commit() conn.close() labels = ['city', 'state', 'area', 'time', 'number', 'lat', 'lon'] csv_writer(labels, rows, output_name)
def get_gps_coordinates(db_name, output_file): ''' This function was used to git the gps coordinates for the resorts in our database ''' conn = lite.connect(db_name) c = conn.cursor() query = "SELECT ID, city, state FROM main" resorts = c.execute(query) conn.commit() coordinates = [] for resort in resorts: ID, city, state = resort current_location = city + " " + state lat, lon = get_lat_lon(current_location) info = [ID, lat, lon] coordinates.append(info) labels = ['ID', 'lat', 'lon'] csv_writer(labels, coordinates, output_file)
def score_location(current_location, path, db_path): ''' This function is used for comparing a user's given location against the scores for 1,000 most populous cities in the country. It returns a string the gives the percentile their location falls into. Input: current_location: user's input path: The path to the scored registry of cities db_path: The path to the ski-resorts database ''' conn = lite.connect(db_path) c = conn.cursor() conn.create_function('time_between', 4, compute_time_between) lat, lon, city, state = get_lat_lon(current_location, locality=True) params = (lon, lat, lon, lat) query = "SELECT SUM(area), SUM(time_between(lon, lat, ?, ?)), COUNT(*)\ FROM main WHERE time_between(lon, lat, ?, ?) < 3.25" result = c.execute(query, params) area, time, count = list(result)[0] score = (area) / (time / count) info = (city, state, area, time, count, lat, lon, score) conn.commit() conn.close() percentile = compare_score(info, path) if type(percentile) == str: return percentile else: rv = (city + ", " + state + " is in the " + str(round(((1 - percentile) * 100), 1)) + " percentile of places to live for access to ski resorts") return rv
def build_ranking(search_dict, database_name='ski-resorts.db'): ''' The main ranking algorithm. It takes the search results and builds a query that returns the top three results. The program returns the IDs, which are the primary keys for the resorts ''' db = sqlite3.connect(database_name) db.create_function('score_size', 2, score_size) db.create_function('time_between', 4, compute_time_between) cursor = db.cursor() parameters = [] query = 'SELECT ID, ' #size_score + run_score AS total_score,' ### SCORE RUNS ### addition, parameters = score_runs(search_dict, parameters) query += addition ### SCORE SIZE ### choice = search_dict['Resort Size'] # print(choice) query += "score_size(area, " + "'" + choice + "')" + ' AS total_score' # Connect table ### CUTTING ATTRIBUTES ### where = [] ### DISTANCE ### where.append(" time_between(lon,lat,?,?) <= ?") max_time = float(search_dict['max_drive_time']) + 0.5 #Marginally increase bounds print(max_time) cur_loc = search_dict['current_location'] u_lat, u_lon = get_lat_lon(cur_loc) parameters.extend([u_lon, u_lat, max_time]) query += ' FROM main WHERE' ### NIGHT SKIING ### if search_dict['night skiing'] != 'Indifferent': where.append(" night=1") ### MAX TICKET ### if search_dict['max_tic_price']: price = int(search_dict['max_tic_price']) parameters.append(price) where.append(" (max_price <= ? OR max_price='N/A')") ### Terrain Park ### if int(search_dict['Terrain parks']) > 1: where.append(" park > 0") where = " AND".join(where) query += where query += ' ORDER BY total_score DESC LIMIT 3' print('QUERY', query) parameters = tuple(parameters) print('PARAMS', parameters) exc = cursor.execute(query, parameters) output = exc.fetchall() resort_ids = [] for i in range(len(output)): resort_id = output[i][0] resort_ids.append(resort_id) return resort_ids