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
