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 faturaTask(request):
aa = main.read_sql_xx()
ss = main.read_sql_user(request.user)
id_user = ss.id[0]
df = main.read_sql(id_user)
df2 = main.read_sql2(id_user)
x = df.varcont
y = df2.varcont
new = []
for a in x:
new.append(a)
dd = pd.DataFrame(data=new, columns=['Calc'])
faturar_x = dd['Calc'].sum()
new2 = []
for a in y:
new2.append(a)
dd = pd.DataFrame(data=new2, columns=['Calc'])
faturar_y = round(float(dd['Calc'].sum()), 2)
faturamento = faturar_y + faturar_x
return render(request, 'conta/fatura.html', {
'faturar_x': faturar_x,
'faturar_y': faturar_y,
'faturamento': faturamento
})
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_edges(self, start_node):
return read_sql("SELECT * from edgelist where start_junction_id={}".format(start_node))
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)))
if not next_destinations:
return "Route Not Possible"
# next node is the destination with the lowest weight
current_node = min(next_destinations, key=lambda k: next_destinations[k][1])
# Work back through destinations in shortest path
path = []
while current_node is not None:
path.append(current_node)
next_node = shortest_paths[current_node][0]
current_node = next_node
# Reverse path
path = path[::-1]
return path
edgeList = read_sql("select start_junction_id, end_junction_id, avg_speed as weight, start_lat, start_long, end_lat, weight as end_long from edgelist;")
graph = Graph(edgeList)
@app.route("/short_path_alg", methods=["POST"])
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))
