def get_knearest_places(lat, lon, types, m=-1, k=5):
if k < 0:
k = 0
if m <= 0:
pllist = Place.query.filter_by(types=types).all()
return pllist
temp = Place(lat=lat, lon=lon, name="temp_place", types="temp_type")
db.session.add(temp)
db.session.commit()
m = int(m)
try:
placesnearby = (Place.query.filter(
func.ST_DistanceSphere(func.ST_Point(
Place.lat, Place.lon), func.ST_Point(lat, lon)) < m).order_by(
func.ST_DistanceSphere(func.ST_Point(Place.lat, Place.lon),
func.ST_Point(lat, lon))).limit(k))
res = []
for temppl in placesnearby:
if (temppl != temp) and (temppl.types == types):
res.append(temppl)
except Exception as e:
print("exception: ", e)
db.session.delete(temp)
db.session.commit()
else:
db.session.delete(temp)
db.session.commit()
return res
def get_routes_within_radius_count_for_feed(lat, lon, radius, count):
"""Return all routes within a given radius (in meters) of this point."""
geo = func.ST_GeomFromText('POINT({} {})'.format(lon, lat))
return Route.query.filter(
func.ST_DistanceSphere(Route.geo, geo) < radius *
1609.344).order_by(func.ST_DistanceSphere(Route.geo,
geo)).limit(count).all()
async def get(self, request, index):
document_index = get_table(index)
qs = select([document_index])
q = request.args.get('q', None)
if q:
qs = qs.where(document_index.c.body == q)
c = request.args.get('c', None)
if c:
qs = qs.where(document_index.c.content_type.in_(c.split(',')))
p = request.args.get('p', None)
if p:
latlon = p.split(',')
latlon = list(map(float, latlon))
distance = int(request.args.get('r', 25))
geom = func.ST_GeomFromEWKT('SRID=4326;POINT(%s %s)' %
(latlon[1], latlon[0]))
qs = qs.where(
or_(
and_(
document_index.c.geom_point.isnot(None),
func.ST_DistanceSphere(document_index.c.geom_point,
geom) < distance),
and_(
document_index.c.geom_linestring.isnot(None),
func.ST_DistanceSphere(
document_index.c.geom_linestring, geom) <
distance),
and_(document_index.c.geom_polygon.isnot(None),
func.ST_Contains(document_index.c.geom_polygon,
geom))))
try:
rows = await request.app.db.fetch_all(qs)
except UndefinedTableError:
return json({'error': 'ERROR: The index does\'t exist'})
results = []
i = 0
for x in rows:
results.append({
'content_type': x['content_type'],
'object_id': x['object_id'],
'data': x['data']
})
i += 1
return json({'results': results, 'count': i})
def get_places_near_point(self, position, distance):
query = self.session.query(TestPlace)
query = query.filter(
func.ST_DistanceSphere(
func.ST_GeomFromText('POINT({} {} 4326)'.format(
position[0], position[1])),
cast(TestPlace.coord, Geometry),
) <= distance)
query = query.order_by(
func.ST_DistanceSphere(
func.ST_GeomFromText('POINT({} {} 4326)'.format(
position[0], position[1])),
cast(TestPlace.coord, Geometry),
))
places = query.limit(100)
return [place for place in places]
def getDist(coordinate1, coordinate2):
"""
Get the distance between the newest incoming point and the most recent previously recorded
point, distance is reported as meters by default by ST_DistanceSphere.
Parameters
----------
coordinate:1 String
WKT representation of the most recently recorded GPS point coordinates.
coordinate2: String
WKT representation of the incoming GPS point coordinates.
Returns
-------
dist : Float
Distance in meters between the newest and most recent recorded points.
"""
session = Session()
# Geoalchemy ORM expression
res = session.query(
sqlfunc.ST_DistanceSphere(sqlfunc.ST_GeomFromText(coordinate1),
sqlfunc.ST_GeomFromText(coordinate2)))
# coordinate1 = coordinate1
# res = db.session.query(sqlfunc.ST_DistanceSphere(coordinate1,coordinate2))
dist = None
for i in res:
# Make sure the data comes through as a float, not a ORM object
dist = float(i[0])
# Round off number, don't need high precision
dist = round(dist, 1)
session.close()
return dist
def show_user_feed():
b_levels = boulder_levels
s_levels = sport_levels
is_following = [follow.user_being_followed_id for follow in Follows.query.filter(
Follows.user_following_id == current_user.id).all()]
is_project = [project.route_id for project in Project.query.filter(Project.user_id == current_user.id).all()]
is_send = [send.route_id for send in Send.query.filter(Send.user_id == current_user.id).all()]
followed_users = User.query.filter(User.id.in_(is_following)).limit(5).all()
recent_projects = Project.query.filter(or_(Project.user_id.in_(is_following), Project.user_id == current_user.id)).order_by(Project.projected_on.desc()).limit(20).all()
recent_sends = Send.query.filter(or_(Send.user_id.in_(is_following), Send.user_id == current_user.id)).order_by(Send.sent_on.desc()).limit(20).all()
geo = func.ST_GeomFromText('POINT({} {})'.format(current_user.lon, current_user.lat))
nearby_users = User.query.filter(and_(func.ST_DistanceSphere(User.geo, geo) < (50*1609.344), (User.id != current_user.id), (User.id.notin_(is_following)))).order_by(func.ST_DistanceSphere(User.geo, geo)).limit(5).all()
nearby_routes = Route.get_routes_within_radius_count_for_feed(current_user.lat, current_user.lon, 500, 5)
feed = generate_feed_from_users_and_routes(nearby_users, nearby_routes)
is_p = [project.id for project in Project.query.filter(Project.user_id == current_user.id).all()]
is_liked = [like.project_id for like in Likes.query.filter(
Likes.user_id == current_user.id).all()]
kudos = [kudo.send_id for kudo in Kudos.query.filter(
Kudos.user_id == current_user.id).all()]
return render_template(
'user_feed.html',
boulder_levels=b_levels,
sport_levels=s_levels,
feed=feed,
followed_users=followed_users,
is_following=is_following,
is_project=is_project,
is_send=is_send,
recent_projects=recent_projects,
recent_sends=recent_sends,
is_liked=is_liked,
kudos=kudos)
def get_missions(lat, lon, radius, limit, lang, user_id):
try:
location = WKTElement('POINT(' + str(lon) + ' ' + str(lat) + ')',
srid=4326)
no_of_errors = 10
# get already solved error ids
already_solved = db_session.query(api.models.Solution.error_id). \
filter(api.models.Solution.user_id == user_id)
# get nearest neighbors candidates from location
q = db_session.query(api.models.kort_errors.schema, api.models.kort_errors.errorId) \
.filter((~api.models.kort_errors.errorId.in_(already_solved))) \
.order_by(api.models.kort_errors.geom.distance_centroid(location)) \
.limit(limit*no_of_errors).subquery()
# partition by error type
q = db_session.query(api.models.kort_errors, func.row_number().over(
partition_by=api.models.kort_errors.error_type).label("row_number")) \
.filter(tuple_(api.models.kort_errors.schema, api.models.kort_errors.errorId).in_(q))\
.filter(func.ST_DistanceSphere(api.models.kort_errors.geom, location) < radius).subquery()
# set max errors of each type
q = db_session.query(
api.models.kort_errors).select_entity_from(q).filter(
q.c.row_number <= limit / no_of_errors)
except Exception as e:
logger.error(traceback.format_exc())
return [p.dump(lang) for p in q][:limit]
def find_by_coordinates(self, db: Session, *, latitude: float,
longitude: float,
distance: float) -> Optional[List[Post]]:
POINT = f"POINT({longitude} {latitude})"
posts = db.query(self.model).filter(
func.ST_DistanceSphere(
Post.point, func.Geometry((
func.ST_GeographyFromText(POINT)))) < distance).all()
return posts
def get_places_in_rect(self, rect, sort_close_to=None):
query = self.session.query(TestPlace)
query = query.filter(
func.ST_Contains(
func.ST_MakeEnvelope(rect[0], rect[1], rect[2], rect[3], 4326),
cast(TestPlace.coord, Geometry)))
if sort_close_to is not None:
query = query.order_by(
func.ST_DistanceSphere(
func.ST_GeomFromText('POINT({} {} 4326)'.format(
sort_close_to[0], sort_close_to[1])),
cast(TestPlace.coord, Geometry),
))
places = query.limit(100)
return [place for place in places]
def get_all_places(self, city, position=None):
query = self.session.query(TestPlace)
if city:
req_city_id = self.get_city_by_name(city)
query = query.filter_by(city_id=req_city_id)
if position is not None:
query = query.order_by(
func.ST_DistanceSphere(
func.ST_GeomFromText('POINT({} {} 4326)'.format(
position[0], position[1])),
cast(TestPlace.coord, Geometry),
))
places = query.limit(1000)
return [place for place in places]
def get_many(self,
db: Session,
*,
skip: int = 0,
limit: int = 100,
descending: bool = False,
lat: float,
lan: float,
distance: float) -> Optional[List[Post]]:
POINT = "POINT({} {})".format(lan, lat)
posts = db.query(self.model).filter(
func.ST_DistanceSphere(
Post.point, func.Geometry((func.ST_GeographyFromText(POINT))))
< distance).offset(skip).limit(limit).all()
return posts
def search_by_sightings(self, position_query: str) -> Dict[Bird, float]:
matches = dict()
match = re.match('position:([0-9.]+),([0-9.]+);r=([0-9.]+)',
position_query)
if match:
lat = match.group(1)
lon = match.group(2)
radius = float(match.group(3))
result = self.session.query(Bird, func.count(Sighting.bird)) \
.join(Sighting.bird) \
.join(Sighting.position) \
.filter(func.ST_DistanceSphere(func.ST_GeomFromText(func.ST_AsText(Position.point), 4326), func.ST_GeomFromText(f'POINT({lon} {lat})', 4326)) < 1000 * radius) \
.group_by(Bird) \
.all()
total = sum([count for _, count in result])
for bird, count in result:
score = count / total
matches[bird] = score
return matches
def order_by_distance(self, zipcode: "Zipcode") -> "ZipcodeQuery":
return self.order_by(
func.ST_DistanceSphere(Zipcode.coordinates, zipcode.coordinates))
def get_all_vendors_within_radius(geo, radius):
print(geo)
return Vendor.query.filter(
func.ST_DistanceSphere(Vendor.geo, geo) < radius).all()
def submit_search():
"""Submit search to database for routes or users"""
search = request.json.get('q')
option1 = request.json.get('option1')
advanced = request.json.get('advanced')
route_type = request.json.get('route_type')
r_distance = int(request.json.get('r_distance'))
u_distance = int(request.json.get('u_distance'))
if route_type == "Boulder":
low = boulder_levels.index(request.json.get('low'))
high = boulder_levels.index(request.json.get('high'))
else:
low = sport_levels.index(request.json.get('low'))
high = sport_levels.index(request.json.get('high'))
geo = func.ST_GeomFromText('POINT({} {})'.format(current_user.lon,
current_user.lat))
if option1:
if advanced:
if not search:
routes = Route.query.filter(
and_((Route.route_type == route_type),
(func.ST_DistanceSphere(Route.geo, geo) <
(r_distance * 1609.344)), (Route.difficulty.between(
int(low), int(high))))).order_by(
func.ST_DistanceSphere(Route.geo,
geo)).limit(30).all()
else:
routes = Route.query.filter(
and_(Route.name.ilike(f"%{search}%"),
(Route.route_type == route_type),
(func.ST_DistanceSphere(Route.geo, geo) <
(r_distance * 1609.344)), (Route.difficulty.between(
int(low), int(high))))).order_by(
func.ST_DistanceSphere(Route.geo,
geo)).limit(30).all()
all_routes = [route.serialize() for route in routes]
return jsonify(routes=all_routes)
else:
if not search:
routes = Route.query.all()
else:
routes = Route.query.filter(
(Route.name.ilike(f"%{search}%"))).all()
all_routes = [route.serialize() for route in routes]
return jsonify(routes=all_routes)
else:
if advanced:
if not search:
users = User.query.filter(
and_((func.ST_DistanceSphere(User.geo, geo) <
(u_distance * 1609.344)), (User.geo != None),
(User.confirmed == True))).order_by(
func.ST_DistanceSphere(User.geo,
geo)).limit(30).all()
else:
users = User.query.filter(
and_((User.username.ilike(f"%{search}%")),
(User.confirmed == True), (User.geo != None),
(func.ST_DistanceSphere(User.geo, geo) <
(u_distance * 1609.344)))).limit(30).all()
all_users = [user.serialize() for user in users]
return jsonify(users=all_users)
else:
if not search:
users = User.query.filter(
and_((User.confirmed == True),
(User.geo != None))).limit(50).all()
else:
users = User.query.filter(
and_((User.username.ilike(f"%{search}%")),
(User.confirmed == True),
(User.geo != None))).order_by(
func.ST_DistanceSphere(User.geo, geo)).all()
all_users = [user.serialize() for user in users]
return jsonify(users=all_users)
