def db_upload(self, file):
print(f'Читаем файл {file} ...')
with open(file, encoding='Windows-1251') as csv_file:
reader = csv.DictReader(csv_file, delimiter=';')
csv_list = []
routes_list = []
for row in reader:
csv_list.append({'latitude': row['Latitude_WGS84'],
'longitude': row['Longitude_WGS84'],
'name': row['Name'],
'routes': row['RouteNumbers']})
# Очистка списка от одинаковых словарей
csv_list = [dict(tuple_item) for tuple_item in {tuple(dictionary.items()) for dictionary in csv_list}]
# Переводим направления в список, а также получаем список направлений
for item in csv_list:
item['routes'] = item['routes'].split('; ')
routes_list.extend(item['routes'])
# При записи в БД направлений предварительно убираем дубликаты
print('Записываем направления...')
for route in set(routes_list):
try:
if Route.objects.filter(name=route).values('name')[0]['name'] == route:
pass
else:
Route(name=route).save()
except IndexError:
Route(name=route).save()
# Записываем в БД станции (дубликаты уже все убраны)
print('Записываем станции...')
for station in csv_list:
try:
if len(Station.objects.filter(name=station['name']).values('routes__name').all()) > 0 and \
len(Station.objects.filter(name=station['name']).values('name').all()) > 0:
for routes__name in Station.objects.filter(name=station['name']).values('routes__name').all():
# Если запись в БД существует - пропускаем
if len(Station.objects.filter(name=station['name']).values('routes__name').all()) > 0 and \
routes__name['routes__name'] in station['routes'] and \
Station.objects.filter(name=station['name']).values('name')[0]['name'] == station['name']:
pass
else:
self.station_save_to_db(item, station)
else:
self.station_save_to_db(item, station)
except IndexError:
self.station_save_to_db(item, station)
def index():
if request.method == 'POST':
route_name = request.form['name']
route_start = request.form['begin']
route_stop = request.form['end']
route, coords, distances = create_route(route_name, route_start,
route_stop)
new_route = Route(name=route_name,
start=route_start,
stop=route_stop,
route=route,
coords=coords,
distances=distances,
prev_coord=coords[0],
prev_distance=0,
user=current_user,
done=False)
try:
db.session.add(new_route)
db.session.commit()
return render('index.html')
except:
return 'There was an issue adding your route'
else:
return render('index.html')
def add_custom_route():
data = request.json
print("hi")
# errors = []
route_data = data['name']
custom_route = Route(name=data['name'],
description=data['description'],
userId=data['userId'],
staticImageURL=str(data['staticImageURL']),
requestData=str(data['requestData']),
starting_point=str(data['starting_point']),
streetBike=data['streetBike'],
routeVisibility=data['routeVisibility'],
totalDistance=data['totalDistance'],
totalElevation=data['totalElevation'],
totalDuration=data['totalDuration'],
travelingMode=data['travelingMode'])
# print(data)
# print(str(data["staticImageURL"]))
# print(custom_route.to_dict())
# if errors:
# return jsonify(errors)
db.session.add(custom_route)
db.session.commit()
# return jsonify(data)
return {"message": "ok"}
def routePost():
data = request.json
print(data)
route = Route(
name=data['name'],
startLong=data['startLong'],
startLat=data['startLat'],
endLat=data['endLat'],
endLong=data['endLong'],
distance=data['distance'],
description=data['description'],
userId=data['userId'],
createdAt=datetime.now(),
updatedAt=datetime.now()
)
db.session.add(route)
db.session.commit()
return route.to_dict()
print("POST ROUTE IS CONNECTING")
def wrapper(*args, **kwargs): # type: ignore
routes = f(*args, **kwargs)
for r in routes:
rm = Route(
route=str(r['routeNum']),
shape_id=str(r['shapeID']),
first_stop=str(r['firstStop']),
headsign=str(r['headsign']),
)
yield rm
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', 'r') as csvfile:
csv_reader = csv.DictReader(csvfile, delimiter=';')
for line in csv_reader:
station = Station()
station.latitude = line["Latitude_WGS84"]
station.longitude = line["Longitude_WGS84"]
station.name = line["Name"]
string = line["RouteNumbers"].split(";")
station.save()
for i in range(len(string)):
route = Route()
route.name = string[i].replace(" ", "")
route.save()
station.routes.add(route)
station.save()
del string
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', newline='', encoding='cp1251') as csvfile:
stations_reader = csv.DictReader(csvfile, delimiter=';')
for row in tqdm(stations_reader):
station = Station()
station.name = row['Name']
station.longitude = row['Longitude_WGS84']
station.latitude = row['Latitude_WGS84']
station.save()
routes = [route for route in row['RouteNumbers'].split('; ')]
for route in routes:
object = Route.objects.filter(name=route).first()
if object:
station.routes.add(object)
else:
route = Route(name=route)
route.save()
station.routes.add(route)
station.save()
def routes():
if request.method == "GET":
route = Route.query.get_or_404(1).to_dict()
return make_response({"routes": route}, 200)
if request.method == "POST":
if not request.is_json:
raise errors.InvalidUsage(
"Incorrect request format! Request data must be JSON")
data = request.get_json(silent=True)
if not data:
raise errors.InvalidUsage(
"Invalid JSON received! Request data must be JSON")
if "routes" not in data:
raise errors.InvalidUsage("'routes' missing in request data")
routes = data["routes"]
if not isinstance(routes, list):
raise errors.InvalidUsage("'routes' should be a list")
if not routes:
raise errors.InvalidUsage("'routes' is empty")
if "stack_id" not in data:
raise errors.InvalidUsage("'stack_id' missing in request data")
stack_id = data["stack_id"]
if not stack_id:
raise errors.InvalidUsage("'stack_id' is empty")
for row in routes:
route_entry = Route(
demand_id=row["demand_id"],
depot_id=row["depot_id"],
vehicle_id=row["vehicle_id"],
stop_number=row["stop_number"],
stack_id=stack_id,
)
db.session.add(route_entry)
db.session.commit()
return make_response(jsonify({"routes": routes}), 201)
def post(self, route_id):
if route_id:
model = Route.query.get(route_id)
else:
model = Route()
db.session.add(model)
form = RouteForm(request.form, model, csrf_enabled=False)
if form.validate_on_submit():
form.populate_obj(model)
db.session.commit()
flash('Route updated.', 'success')
return redirect(url_for('routes.index'))
return render_template('routes/form.html', form=form, model=model)
def create_route():
r=Route()
db.session.add(r)
db.session.commit()
data=request.get_json()
stops=data['stops']
for i in range(len(stops)):
l=Leg(route_id=r.id, stop_id=stops[i], leg_no=i+1)
try:
db.session.add(l)
db.session.commit()
except Exception as ex:
print(ex)
db.session.rollback()
return make_response('An error ocurred while processing route stops', 500)
res={"routeid":r.id}
return make_response(jsonify(res), 200)
def post(self):
args = self.reqparse.parse_args()
origin_point = args.get('origin_point')
destination_point = args.get('destination_point')
autonomy = args.get('autonomy')
fuel_price = args.get('fuel_price')
# validate origin point and destination point
if Route.query.filter_by(origin_point=origin_point).count() == 0:
return {'error': 'Origin point \'%s\' not found' % origin_point}, 400
if Route.query.filter_by(destination_point=destination_point).count() == 0:
return {'error': 'Destination point \'%s\' not found' % destination_point}, 400
cost, path = Route.calculate(origin_point, destination_point,
autonomy, fuel_price)
return {'cost': cost, 'path': path}
async def route_add_url(message: types.Message, state: FSMContext):
"""Set route url and create route from state."""
await state.update_data(url=message.text)
state_data = await state.get_data()
url = extract_url(state_data['url'])
with db_session() as db:
user = (db.query(User).filter(User.uid.__eq__(
message.from_user.id)).first())
route = Route(url=url, name=state_data['name'], user=user)
db.add(route)
await state.finish()
await message.answer(
f'Маршрут "<b>{route.name}</b>" добавлен.'
'\nПосмотрите список всех маршрутов и настройте уведомления '
'командой /routes. \nДобавьте еще один маршрут командой /routeadd.',
reply_markup=types.ReplyKeyboardRemove(),
)
def create_route():
data = request.get_json() or {}
if data.get("from") is None or data.get("to") is None or data.get("passenger-places") is None \
or data.get("arrive-by") is None:
return bad_request("Must include from, to passenger-places and time")
route = Route()
route.from_dict(data)
route.driver_id = g.current_user.id
db.session.add(route)
db.session.commit()
response = jsonify(route.to_dict())
response.status_code = 201
response.headers['Location'] = url_for('api.get_route', drive_id=route.id)
return response
def routes():
"""Find routes between two airports."""
from_airport = int(request.args.get("from_airport"))
to_airport = int(request.args.get("to_airport"))
redis_key = "|".join(["routes", str(from_airport), str(to_airport)])
try:
result = redis_store.get(redis_key)
redis_is_connected = True
if result:
return jsonify(routes=pickle.loads(result))
except RedisConnectionError:
redis_is_connected = False
result = Route.get_path(from_airport, to_airport)
if redis_is_connected:
redis_store.set(redis_key, pickle.dumps(result), 86400)
return jsonify(routes=result)
def createRoute(form, departurelocation, arrivallocation):
creator = User.query.filter_by(id=current_user.get_id()).first()
creatorname = creator.username
# Driver id is None wanneer de creator geen driver is zodat er later een driver zich kan aanbieden voor de route
if form.type.data == 'Driver': # TODO: does this work with translation?
driverid = creator.id
else:
driverid = None
# departure_location_lat = uniform(49.536612, 51.464020)
# departure_location_long = uniform(2.634966, 6.115877)
# arrival_location_lat = uniform(49.536612, 51.464020)
# arrival_location_long = uniform(2.634966, 6.115877)
d = form.date.data
route = Route( # creator=creatorname,
departure_location_lat=departurelocation.latitude,
departure_location_long=departurelocation.longitude, arrival_location_lat=arrivallocation.latitude,
arrival_location_long=arrivallocation.longitude, driver_id=driverid, departure_time=d,
departure_location_string=form.start.data, arrival_location_string=form.destination.data,
playlist=form.playlist.data, passenger_places=form.places.data, maximum_deviation=15)
db.session.add(route)
db.session.commit()
def import_routes(file_name: str) -> None:
"""Import routes."""
airlines_cache = {}
airports_cache = {}
if file_name[0] != "/":
file_name = current_dir + "/" + file_name
with open(file_name, "r") as csvfile:
csvreader = csv.DictReader(csvfile)
for idx, row in enumerate(csvreader):
if not all([
row["Source airport"], row["Destination airport"],
row["Airline"]
]):
print("incorrect row")
continue
if row["Airline"] in airlines_cache:
airline_id = airlines_cache[row["Airline"]]
else:
airline = Airline.query.filter(
Airline.iata == row["Airline"]).first()
airline_id = getattr(airline, "id", None)
airlines_cache[row["Airline"]] = airline_id
if not airline_id:
print("no airline", row["Airline"])
continue
if row["Source airport"] in airports_cache:
source_airport = airports_cache[row["Source airport"]]
else:
source_airport = Airport.query.filter(
Airport.iata_faa == row["Source airport"]).first()
airports_cache[row["Source airport"]] = source_airport
if not source_airport:
print("no source_airport", row["Source airport"])
continue
if row["Destination airport"] in airports_cache:
destination_airport = airports_cache[
row["Destination airport"]]
else:
destination_airport = Airport.query.filter(
Airport.iata_faa == row["Destination airport"]).first()
airports_cache[
row["Destination airport"]] = destination_airport
if not destination_airport:
print("no destination_airport", row["Destination airport"])
continue
# Create Route.
Route(
source=source_airport.id,
destination=destination_airport.id,
airline=airline_id,
distance=get_distance(
source_airport.latitude,
source_airport.longitude,
destination_airport.latitude,
destination_airport.longitude,
),
codeshare=row["Codeshare"] == "Y",
equipment=row["Equipment"],
).save(idx % chunk_size == 0)
print(idx, source_airport.airport_name, "-",
destination_airport.airport_name)
db.session.commit() # save last chunk
stop_time=get_arrivals_datestr_to_datetime('1/11/2020', '11:11 PM'),
estimated=1,
latitude=21.3315100,
longitude=-157.8658300,
shape_id='20160',
canceled=0,
)),
])
def test_get_arrivals(arrivals, row, expected):
responses.add(responses.GET, ANY_URL, body=arrivals, status=200)
resp = list(get_arrivals(79))
assert expected == resp[row]
@responses.activate
@pytest.mark.parametrize('row,expected,extracted_stop_id', [
(0,
Route(
route='2L',
shape_id='2L0009',
first_stop='KAPIOLANI COMMUNITY COLLEGE (Stop: 4538)',
headsign='SCHOOL STREET - Limited Stops',
), 4538),
])
def test_get_routes(routes, row, expected, extracted_stop_id):
responses.add(responses.GET, ANY_URL, body=routes, status=200)
resp = list(get_routes('2L'))
rm = resp[row]
assert expected == rm
# assert
def test_route__first_stop_id(first_stop, expected):
rm = Route(route='_', shape_id='_', headsign='_', first_stop=first_stop)
assert rm.first_stop_id == expected
def pointlat():
"调用接口，响应位置ajax"
terminalid = request.form.get('terminalid')
data = get_state(terminalid)
if data == {}:
return json.dumps(data)
i = request.form.get('i', 0)
data['coordinates'] = [
float(data['longitude']) / 3600000,
float(data['latitude']) / 3600000
]
data['state'] = ''
route = Route.query.filter(Route.terminalid == terminalid).order_by(
Route.finish_time.desc()).first()
route2 = Route.query.filter(Route.terminalid == terminalid).order_by(
Route.finish_time.desc())
# print route.create_time,len(json.loads(route.linejson)["listData"])
try:
if len(json.loads(route.linejson)["listData"]) == 0:
route = route2[1]
except Exception as e:
route == None
if route == None:
datas = {"listData": [data]}
routeadd = Route(linejson=json.dumps(datas), terminalid=terminalid)
db.session.add(routeadd)
db.session.commit()
data['state'] = '在线'
else:
if route.finish_time - route.create_time < datetime.timedelta(
minutes=30) and datetime.datetime.now(
) - route.create_time <= datetime.timedelta(minutes=30):
secondData = json.loads(route.linejson)["listData"]
if data not in secondData:
route = Route.query.filter(
Route.terminalid == terminalid).order_by(
Route.finish_time.desc()).first()
secondData.append(data)
datas = {"listData": secondData}
route.linejson = json.dumps(datas)
route.finish_time = datetime.datetime.now()
db.session.commit()
data['state'] = '在线'
else:
# print terminalid
st = secondData[-1]['timew'].replace("T", ' ').split('.')[0]
dateSt = datetime.datetime.strptime(st, "%Y-%m-%d %H:%M:%S")
if datetime.datetime.now(
) - route.finish_time > datetime.timedelta(seconds=90):
route = Route.query.filter(
Route.terminalid == terminalid).order_by(
Route.finish_time.desc()).first()
if len(json.loads(route.linejson)["listData"]) != 0:
datas = {"listData": []}
routeadd = Route(linejson=json.dumps(datas),
terminalid=terminalid)
db.session.add(routeadd)
db.session.commit()
data['state'] = '离线'
else:
if datetime.datetime.now(
) - route.create_time <= datetime.timedelta(minutes=31):
lastData = json.loads(route.linejson)["listData"][-1]
else:
lastData = {}
datas = {"listData": [lastData, data]}
routeadd = Route(linejson=json.dumps(datas), terminalid=terminalid)
db.session.add(routeadd)
db.session.commit()
data['state'] = '在线'
data['speed'] = int(data['speed']) / 10
if data['devicecount'] == "4":
data['devicecount'] = "RTK FIX"
elif data['devicecount'] == "1":
data['devicecount'] = "单点定位"
elif data['devicecount'] == "2":
data['devicecount'] = "差分定位"
elif data['devicecount'] == "5":
data['devicecount'] = "RTK FLOAT"
elif data['devicecount'] == "6":
data['devicecount'] = "惯导"
else:
data['devicecount'] = "无效"
return json.dumps(data)
def _parse_response(self, response_dict):
routes = []
for i, route in enumerate(response_dict['routes']):
# filter by transfers
if self._search_request.no_transfers and len(
route['segments']) > 1:
continue
# create route obj
rt = Route(
order=i,
pl_from=self._search_request.req_from,
pl_to=self._search_request.req_to,
from_seg=response_dict['places'][route['depPlace']]
['shortName'],
transfers=len(route['segments']) - 1,
duration=self._parse_duration(route['totalDuration']),
duration_raw=route['totalDuration'],
)
# filter by total price
if not route.get('indicativePrices'):
continue
else:
pr = route['indicativePrices'][-1]
if self._search_request.price_lower_limit and \
int(pr['price']) < self._search_request.price_lower_limit:
continue
if self._search_request.price_upper_limit and \
int(pr['price']) > self._search_request.price_upper_limit:
continue
# parse total price
self._parse_price(route, rt)
# parse segments
segments = []
types_set = set()
for segment in route['segments']:
transport_type = response_dict['vehicles'][
segment['vehicle']]['kind']
types_set.add(transport_type)
if any(not self._names.get(tr) for tr in types_set):
continue
for segment in route['segments']:
# create segment dict
transport_type = response_dict['vehicles'][
segment['vehicle']]['kind']
seg = Segment(
to=self._limit_name(response_dict['places'][
segment['arrPlace']]['shortName']),
to_full=response_dict['places'][segment['arrPlace']]
['shortName'],
transport_type=TransportType(
name=self._names[transport_type],
icon=self._icons[transport_type],
),
)
# parse segment price
if segment.get('indicativePrices'):
self._parse_price(segment, seg)
else:
seg.price = '-'
seg.price_raw = 0
# parsing specific segment type
if segment['segmentKind'] == 'surface':
seg.segment_type = 'surface'
seg.duration = self._parse_duration(
segment['transitDuration'] +
segment['transferDuration'])
seg.duration_raw = segment['transitDuration'] + segment[
'transferDuration']
if segment.get('agencies'):
seg.frequency = self._parse_frequency(
segment['agencies'][0]['frequency'])
links = segment['agencies'][0]['links']
for link in links:
if link['text'] == 'Book at':
seg.book_name = link['displayUrl']
seg.book_url = link['url']
elif link['text'] == 'Schedules at':
seg.schedule_name = link['displayUrl']
seg.schedule_url = link['url']
# end
else:
seg.segment_type = 'air'
if segment.get('outbound'):
duration = 0
leg = segment['outbound'][0]
start_index = leg['hops'][0]['depPlace']
time_start = leg['hops'][0]['depTime']
for hop in leg['hops']:
end_index = hop['arrPlace']
duration += hop['duration']
time_end = hop['arrTime']
seg.airport_start_code = response_dict['places'][
start_index]['code']
seg.airport_start_name = response_dict['places'][
start_index]['shortName']
seg.airport_end_code = response_dict['places'][
end_index]['code']
seg.airport_end_name = response_dict['places'][
end_index]['shortName']
seg.time_start = time_start
seg.time_end = time_end
seg.duration = self._parse_duration(duration)
seg.duration_raw = duration
seg.operating_days = self._parse_days(
leg['operatingDays'])
flights = []
for leg in segment['outbound']:
flight = Flight(operating_days=self._parse_days(
leg['operatingDays']))
if leg.get('indicativePrices'):
self._parse_price(leg, flight)
duration = 0
hops = []
airlines = []
for hop in leg['hops']:
duration += hop['duration']
start_index = hop['depPlace']
end_index = hop['arrPlace']
hp = FlightSegment(
airport_start_code=response_dict['places']
[start_index]['code'],
airport_start_name=response_dict['places']
[start_index]['shortName'],
airport_end_code=response_dict['places']
[end_index]['code'],
airport_end_name=response_dict['places']
[end_index]['shortName'],
time_start=hop['depTime'],
time_end=hop['arrTime'],
duration=self._parse_duration(
hop['duration']),
duration_raw=hop['duration'],
airline_name=response_dict['airlines'][
hop['airline']]['name'],
)
hops.append(hp)
airlines.append(response_dict['airlines'][
hop['airline']]['name'])
flight.flight_segments = hops
flight.duration_raw = duration
flight.duration = self._parse_duration(duration)
flight.airlines = ', '.join(a
for a in set(airlines))
flights.append(flight)
flights_obj = Flights(choices=flights).save()
seg.flights = flights_obj
# end
segments.append(seg)
# end
# parse transport types
transport_types = [
TransportType(
name=self._names[tp],
icon=self._icons[tp],
) for tp in types_set
]
rt.segments = segments
rt.transport_types = transport_types
routes.append(rt)
self._save_best_route(routes)
self._search_request.routes = routes
self._search_request.save()
return self._search_request.result
def seed_routes():
routes = [
Route(
name='Boston Harbor Run',
description=
'Run pleasantly along the Boston harbor. Explore the birthplace of America as you avoid being turned to soup',
userId=1,
startLat=42.35796768090105,
startLong=-71.07336678423798,
endLat=42.369803176648205,
endLong=-71.06982626829688,
distance='1.1 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='Mount Bonnel Stroll',
description=
'Stroll along the Colorado River as you make your way toward the outskirts of Austin. Only there will you find safety from the lava.',
userId=1,
startLat=30.314501765600614,
startLong=-97.76923985630593,
endLat=30.331875472385377,
endLong=-97.77662129559666,
distance='1.6 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='Downtown Austin Walk',
description=
'Explore downtown Austin as you avoid exploding volcanoes around you!',
userId=1,
startLat=30.268589245342792,
startLong=-97.74628014730185,
endLat=30.281301642008884,
endLong=-97.74040074546116,
distance='1.0 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='McKinney Falls State Park',
description=
'Take a lovely stroll around you in a national park. Don\'t worry there\'s not a supervolcano underneath you',
userId=1,
startLat=30.19462327209362,
startLong=-97.71676942263025,
endLat=30.201646920340135,
endLong=-97.76958625647508,
distance='5.2 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='Mount Diablo',
description=
'Yeah, it may be beautiful, but it\'s called Mount Diablo for a reason',
userId=1,
startLat=37.83584869884866,
startLong=-121.97174523353314,
endLat=37.83906849308996,
endLong=-121.99496243433634,
distance='1.8 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='Walnut Creek Shopping Mall',
description=
'If you don\'t get run over by the crazed shoppers, you will get pummelled by the wall of lava. Don\'t worry though, you\'re ending at a hospital',
userId=1,
startLat=37.9037525705202,
startLong=-122.06314604260986,
endLat=37.89191681909042,
endLong=-122.05949823843834,
distance='1.0 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(
name='Antioch Delta Walk',
description=
'Yes, it is low ground. Yes, it is filled with water. But isn\'t that just a pathway to funnel hot lava?',
userId=1,
startLat=38.02166866736312,
startLong=-121.87303309195913,
endLat=38.03869847004751,
endLong=-121.88587581888181,
distance='1.8 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(name='Miami Beach Waterfront',
description='The sun won\'t be the only thing burning you today',
userId=1,
startLat=26.010041969198515,
startLong=-80.13016889489651,
endLat=26.0236559512287,
endLong=-80.11527727098533,
distance='5.0 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(name='Miami Lakefront Homes',
description=
'What will it be: death by alligator or death by hot lava?',
userId=1,
startLat=25.96150673256875,
startLong=-80.39468729802157,
endLat=25.98997878428499,
endLong=-80.3831001557078,
distance='3.4 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
Route(name='A Miami Island Extravaganza',
description='Where will you go when the lava comes rolling in?',
userId=1,
startLat=25.772289026703344,
startLong=-80.13765497573155,
endLat=25.784964407655572,
endLong=-80.13984365793036,
distance='3.4 mi',
createdAt=datetime.now(),
updatedAt=datetime.now()),
]
for i in routes:
db.session.add(i)
db.session.commit()
def seed_routes():
route1 = Route(
name='teahupoo',
userId=1,
description='Great route for a ride',
staticImageURL=
'http://maps.googleapis.com/maps/api/staticmap?size=600x300&zoom=15&markers=color:green|38.91113,-77.04122000000001&path=38.91113,-77.04122000000001|38.911120000000004,-77.03849000000001|38.91113,-77.03689|38.911120000000004,-77.03454|38.91004,-77.03455000000001|38.90793,-77.03456000000001|38.90766,-77.03531000000001|38.907740000000004,-77.03536000000001|38.90778,-77.03538|38.907790000000006,-77.03543&sensor=false&markers=color:red|38.907790000000006,-77.03543&key=AIzaSyDvvUchLC5a-dAif0IQmZu7yP7pvDSZI9c',
requestData=
"{'origin': {'lat': 38.911274095754415, 'lng': -77.04122014552644}, 'destination': {'lat': 38.908001479935045, 'lng': -77.03546948939851}, 'optimizeWaypoints': True, 'travelMode': 'BICYCLING'}",
starting_point="{'lat': 38.911274095754415, 'lng': -77.04122014552644}",
streetBike=False,
routeVisibility=True,
totalDistance="0.6 mi",
totalElevation="18.87ft",
totalDuration="4 mins",
travelingMode="BICYCLING")
route2 = Route(
name='hello my route',
userId=1,
description='Great route for a ride!',
staticImageURL=
"http://maps.googleapis.com/maps/api/staticmap?size=600x300&zoom=15&markers=color:green|38.91113,-77.04122000000001&path=38.91113,-77.04122000000001|38.911120000000004,-77.03849000000001|38.91113,-77.03689|38.911120000000004,-77.03454|38.91004,-77.03455000000001|38.90793,-77.03456000000001|38.90766,-77.03531000000001|38.907740000000004,-77.03536000000001|38.90778,-77.03538|38.907790000000006,-77.03543&sensor=false&markers=color:red|38.907790000000006,-77.03543&key=AIzaSyDvvUchLC5a-dAif0IQmZu7yP7pvDSZI9c",
requestData=
"{'origin': {'lat': 38.911274095754415, 'lng': -77.04122014552644}, 'destination': {'lat': 38.908001479935045, 'lng': -77.03546948939851}, 'optimizeWaypoints': True, 'travelMode': 'BICYCLING'}",
starting_point="{'lat': 38.911274095754415, 'lng': -77.04122014552644}",
streetBike=False,
routeVisibility=True,
totalDistance="0.6 mi",
totalElevation="18.87ft",
totalDuration="4 mins",
travelingMode="BICYCLING")
route3 = Route(
name='awesome route',
userId=2,
description='Great awesome Route!',
staticImageURL=
"http://maps.googleapis.com/maps/api/staticmap?size=600x300&zoom=15&markers=color:green|38.906200000000005,-77.04914000000001&path=38.906200000000005,-77.04914000000001|38.906200000000005,-77.0488|38.9063,-77.0488|38.90644,-77.0488|38.90643,-77.04803000000001|38.90643,-77.048|38.906130000000005,-77.04748000000001|38.905840000000005,-77.04699000000001|38.906220000000005,-77.04664000000001|38.90563,-77.04665|38.904610000000005,-77.04665|38.90375,-77.04664000000001|38.902640000000005,-77.04664000000001|38.902240000000006,-77.04664000000001|38.90155,-77.04665|38.90133,-77.04666|38.901210000000006,-77.04631|38.901030000000006,-77.0458|38.90072,-77.04491|38.900240000000004,-77.04358|38.90019,-77.04339|38.900110000000005,-77.04317&sensor=false&markers=color:red|38.900110000000005,-77.04317&key=AIzaSyDvvUchLC5a-dAif0IQmZu7yP7pvDSZI9c",
requestData=
"{'origin': {'lat': 38.90617587366615, 'lng': -77.04914517865922}, 'destination': {'lat': 38.90003080489828, 'lng': -77.04322286115433}, 'optimizeWaypoints': True, 'travelMode': 'BICYCLING'}",
starting_point="{'lat': 38.90617587366615, 'lng': -77.04914517865922}",
streetBike=True,
routeVisibility=True,
totalDistance="0.7 mi",
totalElevation="29.17 ft",
totalDuration="6 mins",
travelingMode="BICYCLING")
route4 = Route(
name='Central park walk',
userId=2,
description='Great route for a walk!',
staticImageURL=
"http://maps.googleapis.com/maps/api/staticmap?size=600x300&zoom=15&markers=color:green|40.765710000000006,-73.97615&path=40.765710000000006,-73.97615|40.7659,-73.97613000000001|40.76601,-73.97616000000001|40.76606,-73.97621000000001|40.76614,-73.97629|40.76619,-73.97641|40.7663,-73.97661000000001|40.766380000000005,-73.97673|40.76653,-73.97688000000001|40.766670000000005,-73.97696|40.76679,-73.97698000000001|40.76693,-73.97694|40.767120000000006,-73.97676000000001|40.76728000000001,-73.97645|40.767450000000004,-73.97626000000001|40.76756,-73.97616000000001|40.76769,-73.97609|40.767720000000004,-73.97618|40.76766000000001,-73.97622000000001|40.76753,-73.97631000000001|40.76746,-73.97638|40.767300000000006,-73.97658000000001|40.767250000000004,-73.97674|40.767230000000005,-73.97713|40.76728000000001,-73.97748|40.767500000000005,-73.97805000000001|40.76773,-73.97853|40.76765,-73.9786&sensor=false&markers=color:red|40.76765,-73.9786&key=AIzaSyDvvUchLC5a-dAif0IQmZu7yP7pvDSZI9c",
requestData=
"{'origin': {'lat': 40.76567614080248, 'lng': -73.97620371299149}, 'optimizeWaypoints': True, 'waypoints': [{'location': {'lat': 40.76723629031617, 'lng': -73.97641828971268}, 'stopover': False}], 'destination': {'lat': 40.767729749720814, 'lng': -73.97884119931172}, 'travelMode': 'WALKING'}",
starting_point="{'lat': 40.76567614080248, 'lng': -73.97620371299149}",
streetBike=False,
routeVisibility=True,
totalDistance="0.3 mi",
totalElevation="13.92ft",
totalDuration="7 mins",
travelingMode="WALKING")
db.session.add(route1)
db.session.add(route2)
db.session.add(route3)
db.session.add(route4)
db.session.commit()
async def get_route(route_request: RouteRequest) -> Route:
radius = route_request.radius * 1.61 # convert miles to km
# get places dataframe
markers_query = "SELECT marker_id, title, lat, lon, text, text_clean, images, categories, url FROM hmdb_data_table WHERE cty='washington_dc';"
df_markers = pd.read_sql_query(markers_query, con)
print("get_route df_markers.head():")
print(df_markers.head())
# get similarities dataframe
sim_query = "SELECT * FROM similarities_data_table"
df_similarities = pd.read_sql_query(sim_query, con, index_col='marker_id')
print("get_route df_similarities.head():")
print(df_similarities.head())
top_n_id = get_top_locations_close(route_request.start_marker,
df_similarities, 7, df_markers, radius)
print("get_route top_n_id:")
print(top_n_id)
# routing function requires having the start/end point first.
# first entry in top_n_id should always start_marker since it has highest
# similarity with itself
# start with first marker and then add the rest
markers = df_markers[df_markers.marker_id == top_n_id[0]]
markers = markers.append(df_markers[df_markers.marker_id.isin(
top_n_id[1:])])
map_center = [markers.lat.mean(), markers.lon.mean()]
marker_coords = [(x.lon, x.lat) for x in markers.itertuples()]
print(marker_coords)
marker_names = [x.text[:30] for x in markers.itertuples()]
# solve TSP
dist_matrix_response = get_distance_matrix_response(marker_coords)
dist_matrix = dist_matrix_response["durations"]
optimal_coords, marker_order, _ = optimal_route_from_matrix(marker_coords,
marker_names,
dist_matrix,
start=0)
optimal_route, optimal_duration = directions_route_duration(optimal_coords)
optimal_duration = int(optimal_duration)
# reorder marker order to reflect walking tour order
markers = markers.reset_index().reindex(marker_order)
route_str = " ⇨ ".join(markers.title)
# get entities (2 tables due to number of columns)
marker_ids_str = ", ".join(markers.marker_id.values.astype(str))
ent_query_1 = "SELECT * FROM entities_data_table_1 WHERE marker_id IN ({})".format(
marker_ids_str)
df_ent_1 = pd.read_sql_query(ent_query_1, con, index_col='marker_id')
ent_query_2 = "SELECT * FROM entities_data_table_2 WHERE marker_id IN ({})".format(
marker_ids_str)
df_ent_2 = pd.read_sql_query(ent_query_2, con, index_col='marker_id')
df_ent = df_ent_1.merge(df_ent_2, how='outer', on='marker_id')
marker_ents = []
for marker_id in markers.marker_id.values:
raw_one_marker_ents = df_ent.columns[
df_ent.loc[marker_id] != 0].tolist()
one_marker_ents = [
x.split("_")[0].capitalize() for x in raw_one_marker_ents
]
marker_ents.append(one_marker_ents)
markers['marker_ents'] = marker_ents
# decode route to geojson-ready form
route_polylines_flip = optimal_route['features'][0]['geometry'][
'coordinates']
route_polylines = [[y, x] for [x, y] in route_polylines_flip]
# add img_src
img_urls = [x[0] for x in markers['images'].apply(ast.literal_eval)]
img_ids = [x.split("/")[-1][5:-5].zfill(6) for x in img_urls]
marker_id_strs = [str(x).zfill(6) for x in markers['marker_id']]
img_srcs = [
"static/img/{}_{}_small.jpg".format(x, y)
for (x, y) in zip(marker_id_strs, img_ids)
]
markers['img_src'] = img_srcs
marker_objs = [Marker(**marker) for marker in markers.to_dict("records")]
route = Route(markers=marker_objs,
map_center=map_center,
route_polylines=route_polylines,
marker_order=marker_order,
route_str=route_str,
optimal_duration=optimal_duration)
return route