def get_place_using_postgres(db: Session, lat: float, lon: float, lim: int):
db_list = db.query(models.Places).filter((func.earth_distance(
func.ll_to_earth(models.Places.latitude, models.Places.longitude),
func.ll_to_earth(lat, lon)) / 1000) < 1).order_by(
func.earth_distance(
func.ll_to_earth(models.Places.latitude,
models.Places.longitude),
func.ll_to_earth(lat, lon)) / 1000).all()
print(db_list)
return db_list
def get_geof(db: Session, lat: float, lon: float):
llist = db.query(models.Geof.id).filter((func.earth_distance(
func.ll_to_earth(models.Geof.latitude, models.Geof.longitude),
func.ll_to_earth(lat, lon)) / 1000) < 1).order_by(
func.earth_distance(
func.ll_to_earth(models.Geof.latitude, models.Geof.longitude),
func.ll_to_earth(lat, lon)) / 1000).all()
print(llist)
if len(llist) < 1:
return False
else:
return db.query(models.Geof.place_name).filter(
models.Geof.id == llist[0][0]).all()
def get_closest(lat_user,lng_user):
#Calculating each company's distance from Amsterdam.
loc_user = func.ll_to_earth(lat_user, lng_user)
loc_place = func.ll_to_earth(Result.lat, Result.lng)
distance_func = func.earth_distance(loc_user, loc_place)
query = db.session.query(Result, distance_func).filter(Result.rank==1).order_by(distance_func).limit(5)
#Resultset is no longer list of Company, but a list of tuples.
result = query.all()
return result
def get_using_postgres(lat, lng):
loc_current = func.ll_to_earth(lat, lng)
loc_locations = func.ll_to_earth(Location.lat, Location.lng)
distance_func = func.earth_distance(loc_current, loc_locations)
query = db.session.query(Location, distance_func).filter(distance_func < 5000).order_by(distance_func)
result = query.all()
mapped = []
for row in result:
# print(row[0].__dict__)
mapped.append({"name": row[0].__dict__["name"]})
return jsonify(mapped)
def taxi_order_submit(order_id, customer_id, lat, lon):
o = Order.query.get(order_id)
if not o or o.is_canceled:
return
loc_customer = func.ll_to_earth(lat, lon)
loc_taxi = func.ll_to_earth(Taxi.lat, Taxi.lon)
distance_func = func.earth_distance(loc_customer, loc_taxi)
while True:
found_taxi_data = db.session.query(
Taxi,
distance_func
).filter(
Taxi.is_busy == False
).order_by(
distance_func
).first()
if not found_taxi_data:
logging.critical(CARS_FINISHED_ERROR)
time.sleep(MAX_SLEEP)
continue
found_taxi = found_taxi_data[0]
if Taxi.query.filter(
Taxi.id == found_taxi.id,
Taxi.is_busy == False
).update(
{
'is_busy': True
},
synchronize_session=False
):
o = Order.query.get(order_id)
o.driver_id = found_taxi.driver_id
db.session.add(o)
db.session.commit()
logging.debug('''
{order_id} JOB FINISHED for customer with id:{customer_id} lat:{lat} lon:{lon}
successful - driver:{driver_id} lat:{d_lat}, lon:{d_lon}
'''.format(
order_id=order_id,
customer_id=customer_id,
lat=lat,
lon=lon,
driver_id=found_taxi.driver_id,
d_lat=found_taxi.lat,
d_lon=found_taxi.lon
))
break
else:
logging.critical('Selected driver already busy, try one more time')
time.sleep(MIN_SLEEP)
#TODO mark driver as free or drop, ask customer before!
async def contractor_list(request): # noqa: C901 (ignore complexity)
sort_val = request.query.get('sort')
sort_col = SORT_OPTIONS.get(sort_val, SORT_OPTIONS['last_updated'])
pagination, offset = get_pagination(request, 100, 100)
company = request['company']
options = company.options or {}
fields = (
c.id,
c.first_name,
c.last_name,
c.tag_line,
c.primary_description,
c.town,
c.country,
c.photo_hash,
)
show_labels = options.get('show_labels')
if show_labels:
fields += (c.labels, )
show_stars = options.get('show_stars')
if show_stars:
fields += (c.review_rating, )
show_hours_reviewed = options.get('show_hours_reviewed')
if show_hours_reviewed:
fields += (c.review_duration, )
where = (c.company == company.id, )
subject_filter = get_arg(request, 'subject')
qual_level_filter = get_arg(request, 'qual_level')
select_from = None
if subject_filter or qual_level_filter:
select_from = sa_contractors.join(sa_con_skills)
if subject_filter:
select_from = select_from.join(sa_subjects)
where += (sa_subjects.c.id == subject_filter, )
if qual_level_filter:
select_from = select_from.join(sa_qual_levels)
where += (sa_qual_levels.c.id == qual_level_filter, )
labels_filter = request.query.getall('label', [])
labels_exclude_filter = request.query.getall('label_exclude', [])
if labels_filter:
where += (c.labels.contains(cast(labels_filter, ARRAY(String(255)))), )
if labels_exclude_filter:
where += (or_(
~c.labels.overlap(cast(labels_exclude_filter, ARRAY(String(255)))),
c.labels.is_(None)), )
location = await geocode(request)
inc_distance = None
if location:
if location.get('error'):
return json_response(
request,
location=location,
results=[],
count=0,
)
max_distance = get_arg(request, 'max_distance', default=80_000)
inc_distance = True
request_loc = func.ll_to_earth(location['lat'], location['lng'])
con_loc = func.ll_to_earth(c.latitude, c.longitude)
distance_func = func.earth_distance(request_loc, con_loc)
where += (distance_func < max_distance, )
fields += (distance_func.label('distance'), )
sort_col = distance_func
distinct_cols = sort_col, c.id
if sort_col == c.review_rating:
sort_on = c.review_rating.desc().nullslast(), c.review_duration.desc(
).nullslast(), c.id
distinct_cols = c.review_rating, c.review_duration, c.id
elif sort_col == c.last_updated:
sort_on = sort_col.desc(), c.id
else:
sort_on = sort_col.asc(), c.id
q_iter = (select(fields).where(and_(*where)).order_by(*sort_on).distinct(
*distinct_cols).offset(offset).limit(pagination))
q_count = select([sql_f.count(distinct(c.id))]).where(and_(*where))
if select_from is not None:
q_iter = q_iter.select_from(select_from)
q_count = q_count.select_from(select_from)
results = []
name_display = company.name_display
conn = await request['conn_manager'].get_connection()
async for row in conn.execute(q_iter):
name = _get_name(name_display, row)
con = dict(
id=row.id,
url=route_url(request,
'contractor-get',
company=company.public_key,
id=row.id),
link=f'{row.id}-{slugify(name)}',
name=name,
tag_line=row.tag_line,
primary_description=row.primary_description,
town=row.town,
country=row.country,
photo=_photo_url(request, row, True),
distance=inc_distance and int(row.distance),
)
if show_labels:
con['labels'] = row.labels or []
if show_stars:
con['review_rating'] = row.review_rating
if show_hours_reviewed:
con['review_duration'] = row.review_duration
results.append(con)
cur_count = await conn.execute(q_count)
return json_response(
request,
location=location,
results=results,
count=(await cur_count.first())[0],
)
def within(cls, l1, l2, dist):
me = func.ll_to_earth(cls.lat, cls.lng)
you = func.ll_to_earth(l1, l2)
return (func.earth_distance(me, you) < dist)
def within(self, l1, l2, dist):
me = func.ll_to_earth(self.lat, self.lng)
you = func.ll_to_earth(l1, l2)
return func.earth_distance(me, you) < dist
def loc_check(db: Session, lat: float, lon: float):
llist = db.query(models.Places).filter((func.earth_distance(
func.ll_to_earth(models.Places.latitude, models.Places.longitude),
func.ll_to_earth(lat, lon)) / 1000) < 1).all()
if len(llist) > 0:
return 1