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)))