def uber_ride_lat_lng():
content = request.json
day = content["day"]
month = content["month"]
year = content["year"]
start_lat = content["start"]["lat"]
start_lng = content["start"]["lng"]
end_lat = content["end"]["lat"]
end_lng = content["end"]["lng"]
delta= 0.07
results = None
while (results is None or len(results) == 0) and delta < 0.7:
max_start_lat = start_lat + delta
min_start_lat = start_lat - delta
max_end_lat = end_lat + delta
min_end_lat = end_lat - delta
max_start_lng = start_lng + delta
min_start_lng = start_lng - delta
max_end_lng = end_lng + delta
min_end_lng = end_lng - delta
results = read_sql("SELECT day, month, year, start_lat, start_long, end_lat, end_long, speed_mph_mean, uber.start_junction_id as start_junction_id, uber.end_junction_id as end_junction_id from uber join (select junction_id as start_junction_id, osm_node_id as start_osm_node, latitude as start_lat, longitude as start_long from OSM_nodes) as start_osm_nodes on start_osm_nodes.start_junction_id=uber.start_junction_id join (select junction_id as end_junction_id, osm_node_id as end_osm_node, latitude as end_lat, longitude as end_long from OSM_nodes) as end_osm_nodes on end_osm_nodes.end_junction_id=uber.end_junction_id where day={} and month={} and year={} and start_lat > {} and start_lat < {} and end_lat > {} and end_lat < {} and start_long > {} and start_long < {} and end_long > {} and end_long < {} limit 5000".format(day, month, year, min_start_lat, max_start_lat, min_end_lat, max_end_lat, min_start_lng, max_start_lng, min_end_lng, max_end_lng))
delta += 0.1
return simple_jsonify_df(results)
def citibike_lat_lng():
content = request.json
day = content["day"]
month = content["month"]
year = content["year"]
start_lat = content["start"]["lat"]
start_lng = content["start"]["lng"]
end_lat = content["end"]["lat"]
end_lng = content["end"]["lng"]
delta= 0.07
results = None
while (results is None or len(results) == 0) and delta < 1.5:
max_start_lat = start_lat + delta
min_start_lat = start_lat - delta
max_start_lng = start_lng + delta
min_start_lng = start_lng - delta
max_end_lat = end_lat + delta
min_end_lat = end_lat - delta
max_end_lng = end_lng + delta
min_end_lng = end_lng - delta
results = read_sql("select tripduration, starttime, stoptime, `start station name`, `end station name` from citibike where starttime > STR_TO_DATE('{}-{}-{}', \"%%m-%%d-%%Y\") and stoptime < STR_TO_DATE('{}-{}-{}', \"%%m-%%d-%%Y\") and `start station latitude` > {} and `start station latitude` < {} and `start station longitude` > {} and `start station longitude` < {} and `end station latitude` > {} and `end station latitude` < {} and `end station longitude` > {} and `end station longitude` < {} limit 500".format(month, day, year, month, int(day)+1, year, min_start_lat, max_start_lat, min_start_lng, max_start_lng, min_end_lat, max_end_lat, min_end_lng, max_end_lng, ))
delta += 0.5
return simple_jsonify_df(results)
def citibike():
content = request.json
if "condition" in where_clause:
where_clause = content["condition"]
else:
where_clause = ""
sql_stmt = "select * from citibike {}".format(where_clause)
results = read_sql(sql_stmt)
return simple_jsonify_df(results)
def uber_ride():
content = request.json
if "condition" in content:
where_clause = content["condition"] #this is such bad code :P, anyone could do an sql injection here
else:
where_clause = ""
sql_stmt = "select count(*) from uber {}".format(where_clause)
results = read_sql(sql_stmt)
return simple_jsonify_df(results)
def shortest_path_algorithm():
global graph
content = request.json
start_junction = content["start"]
end_junction = content["end"]
path = djikstra(graph, start_junction, end_junction)
setStr = "("
for junction_id in path:
setStr += "'" + junction_id + "'" +", "
setStr = setStr[:-2] + ")"
edges = read_sql("select * from OSM_nodes where junction_id in {}".format(setStr))
edges = edges.set_index("junction_id").loc[path]
return simple_jsonify_df(edges)
def get_closest_node():
content = request.json
latitude = content["lat"]
longitude = content["lng"]
return simple_jsonify_df(read_sql("select * from OSM_nodes where not isnull(latitude) order by (latitude-{})*(latitude-{}) + (longitude-{})*(longitude-{}) asc limit 1".format(latitude, latitude, longitude, longitude)))