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)