def get_places_by_distance_postgresql(db: Session, lati: float, longi: float,
radi: float):
tomiles = radi * 1000
box = func.earth_box(func.ll_to_earth(lati, longi), tomiles)
para = func.ll_to_earth(models.Place.latitude, models.Place.longitude)
all = db.query(models.Place).filter(box.op("@>")(para)).all()
return 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_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_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 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 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!
def get_using_postgres():
try:
lat = float(request.args.get('latitude'))
lon = float(request.args.get('longitude'))
except:
return 'Please provide latitude and longitude'
req_loc = func.earth_box(func.ll_to_earth(lat, lon), 5000)
loc_company = func.ll_to_earth(Mapping.latitude, Mapping.longitude)
result = Mapping.query.filter(req_loc.op("@>")(loc_company))
mp_schema = MappingSchema(many=True)
output = mp_schema.dump(result).data
return jsonify(output)
def get_using_postgres():
try:
lat = float(request.args.get('latitude'))
long = float(request.args.get('longitude'))
except:
return "Please send Lat and Long"
# SELECT * FROM public.places WHERE earth_box( ll_to_earth(106.332, 106.331), 5000) @> ll_to_earth(places.latitude, places.longitude);
loc_given = func.earth_box(func.ll_to_earth(lat, long ), 5000)
loc_test = func.ll_to_earth(Places.latitude, Places.longitude)
result = Places.query.filter(loc_given.op("@>")(loc_test))
place_schema = PlacesSchema(many=True)
output = place_schema.dump(result).data
output = json.dumps({'place': output}, default=alchemyencoder)
return (json.dumps(json.loads(output), indent=2))
class Location(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80))
lat = db.Column(db.Float())
lng = db.Column(db.Float())
# for faster calculation of Distance Difference
Index('dist', func.ll_to_earth(lat, lng), postgresql_using='gist')
def __init__(self, name, lat, lng):
self.name = name
self.lat = lat
self.lng = lng
def get_location():
try:
lat1 = float(request.args.get('latitude')) # 28.616700
lon1 = float(request.args.get('longitude')) # 77.216700
except:
return 'Must send Latitude and Longitude'
loc_amsterdam = func.earth_box(func.ll_to_earth(lat1, lon1), 5000)
loc_company = func.ll_to_earth(User.latitude, User.longitude)
print loc_company
result = User.query.filter(loc_amsterdam.op("@>")(loc_company))
print result
# Resultset is no longer list of Company, but a list of tuples.
user_schema = UserSchema(many=True)
print user_schema
output = user_schema.dump(result).data
print output
output = json.dumps({'user': output}, default=alchemyencoder)
j = output.replace('"[', '[').replace(']"', ']')
return (json.dumps(json.loads(j), indent=2))
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
import sys
import os
CURRENT_PATH = os.path.abspath(os.path.dirname(__file__))
PROJECT_ROOT = os.path.normpath(os.path.join(CURRENT_PATH, '../'))
sys.path.insert(0, PROJECT_ROOT)
from sqlalchemy import func, Index
from app.taxi.models import Taxi
Index('taxi_distance', func.ll_to_earth(Taxi.lat, Taxi.lon), postgresql_using='gist')
from database import db
from sqlalchemy.dialects.postgresql import JSON, REAL, INTEGER
from sqlalchemy import func, Index
class Result(db.Model):
__tablename__ = 'results'
id = db.Column(db.Integer, primary_key=True)
lat=db.Column(REAL)
lng=db.Column(REAL)
rank=db.Column(INTEGER)
result = db.Column(JSON)
def __init__(self,lat,lng,rank,result):
self.lat=lat
self.lng=lng
self.rank=rank
self.result=result
def __repr__(self):
return '<id {}>'.format(self.id)
Index('place_distance', func.ll_to_earth(Result.lat, Result.lng), postgresql_using='gist')
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