def get_clusters_per_point_query(self, session, gridpoints, riskdate): days_prev = self.dycast_parameters.temporal_domain enddate = riskdate startdate = riskdate - datetime.timedelta(days=(days_prev)) points_query = self.get_points_query_from_grid(gridpoints) # close_space_and_time_query = self.get_close_space_and_time_query(session) # case_subquery = session.query(Case).subquery() # daily_case_query = session.query(Case) \ # .join(points_query, literal(True)) \ # .filter(Case.report_date >= startdate, # Case.report_date <= enddate) return session.query(func.array_agg( func.json_build_object( Case.id, Case.location )).label('case_array'), points_query.c.point.geom.label('point')) \ .join(points_query, literal(True)) \ .filter(Case.report_date >= startdate, Case.report_date <= enddate, func.ST_DWithin(Case.location, points_query.c.point.geom, self.dycast_parameters.spatial_domain)) \ .group_by(points_query.c.point.geom)
def get_poi_along_path(cls, lines, radius=5, filters=None): if filters is None: filters = {} """ SELECT park.* FROM park WHERE ST_DWithin(line.geom, park.geom, radius) ORDER BY ST_Line_Locate_Point(line.geom, park.geom), ST_Distance(line.geom, park.geom); :param lines: an array of locations :param radius: the search radius :param filters: filters for searching :return: list of all POI's found """ linestring = RoutePaths(paths=lines) q = db.session.query(Park).join(Ride).filter(func.ST_DWithin(linestring.locations, Park.location, radius)) \ .order_by( desc(func.ST_Line_Locate_Point(linestring.locations, Park.location)), desc(func.ST_Distance(linestring.locations, Park.location))) final = _combine_filters(q, filters) return final.all()
def chunk(pickup_file): session = db_connection.SESSIONMAKER() with open(pickup_file, 'r') as pickup_file: pickups = csv.DictReader(pickup_file) count = 0 ditched = 0 start = datetime.datetime.now() for pickup in pickups: pickup_loc = 'SRID=4326;POINT({} {})'.format( pickup['Lon'], pickup['Lat']) pickup_geo = func.ST_GeogFromText(pickup_loc) nearest = session.query(db_model.Intersection.id, func.ST_Distance(pickup_geo, db_model.Intersection.location)).\ filter(func.ST_DWithin(db_model.Intersection.location, pickup_geo, 200)).\ order_by(func.ST_Distance(pickup_geo, db_model.Intersection.location).asc()).first() if nearest is None: ditched += 1 continue nearest = nearest[0] pickup_time = datetime.datetime.strptime(pickup['Date/Time'], "%m/%d/%Y %H:%M:%S") session.add( db_model.Pickup(time=pickup_time, base=pickup['Base'], location=pickup_geo, intersection=nearest, week_chunk=week_chunk(pickup_time), month_chunk=month_chunk(pickup_time))) if count % 10000 == 0: stop = datetime.datetime.now() print((stop - start).total_seconds(), count, ditched) start = datetime.datetime.now() count += 1 session.commit() session.close()
def get_close_space_only_old(self, cases_in_cluster_query): subquery = cases_in_cluster_query.subquery() query = cases_in_cluster_query.join(subquery, literal(True)) \ .filter(func.ST_DWithin(Case.location, subquery.c.location, self.dycast_parameters.close_in_space), Case.id < subquery.c.id) return database_service.get_count_for_query(query)
def get(self): """Get all publications.""" params = publication_parser.parse_args() has_lat = params.latitude is not None has_lon = params.longitude is not None has_dist = params.max_distance is not None if any((has_lat, has_lon, has_dist)) and not all( (has_lat, has_lon, has_dist)): raise DistanceFilterMissingParameters query = Publication.query # noqa: E712 for filter_name, filter_op in params.items(): if not isinstance(filter_op, FilterParam): if filter_op is None: continue for i in publication_parser.args: if i.name == filter_name: filter_op = i.type(filter_op) break if not isinstance(filter_op, FilterParam): continue query = filter_op.apply(query, Publication) if params.max_distance: point = func.ST_GeographyFromText( f"POINT({params.latitude} {params.longitude})", srid=4326) query = query.filter( func.ST_DWithin(Publication.loc, point, params.max_distance * 1000)) return query.all()
def nearby(): try: lat = float(request.args.get('lat')) long = float(request.args.get('long')) except (TypeError, ValueError): logging.info('Missing latitude and longitude params') return 'Missing latitude and longitude params', 400 dist = int(request.args.get('distance') or 350) current_pos = 'POINT(%f %f)' % (long, lat) places_raw = db.session \ .query( Place, func.ST_Distance(current_pos, Place.position).label('distance'), func.count(Place.guides) ).filter(func.ST_DWithin( Place.position, current_pos, dist) ).outerjoin(Place.guides) \ .group_by(Place.id) \ .order_by('distance') \ .all() places = [] for p_raw in places_raw: p = to_object(p_raw[0], excluded=['position']) p['numGuides'] = p_raw[2] p['distance'] = p_raw[1] places.append(p) logging.info('Returning %d results' % len(places)) return jsonify(places)
def get_cst_subquery_baked(self, main_query): subquery = main_query.session.query(Case) \ .filter(Case.id.op('IN')(bindparam('case_id_array'))) \ .subquery() return main_query.join(subquery, literal(True)) \ .filter(func.ST_DWithin(Case.location, subquery.c.location, self.dycast_parameters.close_in_space), func.abs(Case.report_date - subquery.c.report_date) <= self.dycast_parameters.close_in_time, Case.id < subquery.c.id)
def get_close_space_and_time(self, cases_in_cluster_query): subquery = cases_in_cluster_query.subquery() query = cases_in_cluster_query.join(subquery, literal(True)) \ .filter(func.ST_DWithin(Case.location, subquery.c.location, self.dycast_parameters.close_in_space), func.abs(Case.report_date - subquery.c.report_date) <= self.dycast_parameters.close_in_time, Case.id < subquery.c.id) return database_service.get_count_for_query(query)
def get_results_for_boundary(boundary, charge_filter, max_results): """Returns the results of land charges contained in a extent :param boundary: Extent to search for land charges within :param charge_filter: String indicating whether to filter out cancelled charges :param max_results: Max number of land charges to be returned. Defaults to 1000 :return: Json representation of the results """ try: extent_shape = asShape(boundary) geo_extent_shape = shape.from_shape(unary_union(extent_shape), srid=27700) subquery = db.session.query(GeometryFeature.local_land_charge_id, GeometryFeature.geometry) \ .distinct(GeometryFeature.local_land_charge_id) \ .filter(func.ST_DWithin(GeometryFeature.geometry, geo_extent_shape, 0)) \ .subquery() if charge_filter: charge_query = LocalLandCharge.query \ .filter(LocalLandCharge.id == subquery.c.local_land_charge_id) \ .filter(or_(~func.ST_Touches(subquery.c.geometry, geo_extent_shape), ~LocalLandCharge.llc_item.contains( {'charge-sub-category': 'Conditional planning consent'}))) \ .filter(LocalLandCharge.cancelled.isnot(True)) \ .order_by(LocalLandCharge.llc_item[SORT_BY_FIELD].desc()) else: charge_query = LocalLandCharge.query \ .filter(LocalLandCharge.id == subquery.c.local_land_charge_id) \ .filter(or_(~func.ST_Touches(subquery.c.geometry, geo_extent_shape), ~LocalLandCharge.llc_item.contains( {'charge-sub-category': 'Conditional planning consent'}))) \ .order_by(LocalLandCharge.llc_item[SORT_BY_FIELD].desc()) num_results = charge_query.count() if num_results > max_results: current_app.logger.info("Search-area: {0}, " "Number-of-charges: {1}, " "Normal-limit: {2}, " "Too many charges returned".format( boundary, num_results, max_results)) raise ApplicationError("Too many charges, search a smaller area", 507, 507) llc_result = charge_query.all() if llc_result and len(llc_result) > 0: current_app.logger.info("Returning local land charges") return json.dumps( model_mappers.map_llc_result_to_dictionary_list( llc_result)), 200, { 'Content-Type': 'application/json' } else: raise ApplicationError("No land charges found", 404, 404) except (ValueError, TypeError) as err: raise ApplicationError("Unprocessable Entity. {}".format(err), 422, 422)
def test_within_query(geospatial_db_session): nearby_places = geospatial_db_session\ .query(Place)\ .filter(func.ST_DWithin( Place.position, REF_POINT, 300) ).all() assert len(nearby_places) == 2
def within_distance_func(cls, position, distance): """ Creates the geoalchemy function that determines if a point is in range < distance from the position item :param position: the position to check distance with :param distance: the maximum distance in meters :return: function to apply to query """ point = func.ST_GeomFromText('POINT({0} {1})'.format(*position), srid=Position.SRS_WGS_84) return func.ST_DWithin(cast(Position.location, Geography(srid=Position.SRS_WGS_84)), point, distance)
def get_close_space_and_time_query(self, session): case_query = session.query(Case.id) subquery = session.query(Case).subquery() return case_query.join(subquery, literal(True)) \ .filter(func.ST_DWithin(Case.location, subquery.c.location, self.dycast_parameters.close_in_space), func.abs(Case.report_date - subquery.c.report_date) <= self.dycast_parameters.close_in_time, Case.id < subquery.c.id) \ .subquery()
def get_close_space_and_time_new(self, session, cluster): cases_in_cluster = session.query(Case).filter(Case.id.in_(cluster.case_id_array)) subquery = cases_in_cluster.subquery() close_in_space_and_time = cases_in_cluster.join(subquery, literal(True)) \ .filter(func.ST_DWithin(Case.location, subquery.c.location, self.dycast_parameters.close_in_space), func.abs(Case.report_date - subquery.c.report_date) <= self.dycast_parameters.close_in_time, Case.id < subquery.c.id) return database_service.get_count_for_query(close_in_space_and_time)
def near_noisy_transport(lat, lng): point = WKTElement(f"SRID=4326;POINT({lng} {lat})") query = session.query(Transport).filter( func.ST_DWithin(Transport.geom, point, 0.0004) ) result = False if query.count() == 0 else True session.close() return result
def find_adjacent_events(cls, point, events): '''Find any event within settings.ALERT_DISTANCE of point.''' adjacent_events = [] for event in events: within = session.execute( func.ST_DWithin(event.point, point, settings.ALERT_DISTANCE)).first()[0] if within: adjacent_events.append(event) return adjacent_events
def test_nearby_with_distances_query(geospatial_db_session): nearby_places = geospatial_db_session\ .query(Place, func.ST_Distance(REF_POINT, Place.position).label('distance'))\ .filter(func.ST_DWithin( Place.position, REF_POINT, 300) ).order_by('distance')\ .all() assert len(nearby_places) == 2 assert nearby_places[1][0].name == 'Galleria Vittorio Emanuele' assert nearby_places[0][0].name == 'Piazza Duomo'
def get(self): parser = reqparse.RequestParser() parser.add_argument('lng', type=float, required=True, help='longitude is required') parser.add_argument('lat', type=float, required=True, help='latitude is required') parser.add_argument('radius', type=float, required=True, help='radius is required') args = parser.parse_args() point = WKTElement('POINT({0} {1})'.format(args['lng'], args['lat']), srid=4326) stations = db.session.query(Station).filter( func.ST_DWithin(Station.position, point, args['radius']) ).all() return stations
def fetch_atms_by_coords(coords): """Fetch ATM's by coordinates from Google maps.""" latitude, longitude = coords.split(',') point = 'POINT({} {})'.format(longitude, latitude) atms = db.session.query(ATM).filter( func.ST_DWithin(ATM.location, point, 5000)).all() if atms: atms = deserialize_atms(atms) else: # Fetch ATM's from google maps and add them to our database atms = fetch_atms_from_google(latitude, longitude) atms = transform_google_results(atms) atms = create_atms(atms) atms = deserialize_atms(atms) return jsonify(atms)
def within(id_malette, id_sensors, n: hug.types.number, response): """return all the sensors within {n} meters to sensors(id_sensors, id_malette)""" logger.debug("Call to within with : id_malette: %r, id_sensors: %r, distance: %r ", id_malette, id_sensors, n) schm = schema.SensorsSchema() ids = {"id_malette": id_malette, "id_sensors": id_sensors} try: inst = schm.get_instance(ids) except SQLAlchemyError as err: logger.error("Error when getting sensor from db : %r", err) schm.Meta.sqla_session.rollback() inst = None if not inst: response.status = HTTP_400 logger.debug("Returning 400 status") return "Can't find sensors" geom = json.dumps(inst.gps_pos) try: logger.debug("Searching sensors in the defined zone (distance %r, around %r)", n, geom) insts = schm.Meta.sqla_session.query(models.Sensors).filter( func.ST_DWithin( func.ST_GeomFromGeoJSON(geom), models.Sensors.gps_pos, n) ) except SQLAlchemyError as err: logger.error("Error when requesting sensor in the disance zone : %r", err) schm.Meta.sqla_session.rollback() insts = None d = schm.dump(insts, many=True).data logger.debug("Returning theses sensors : %r", d) return d
bbox[0], (bbox[1][0], bbox[0][1]), bbox[1], (bbox[0][0], bbox[1][1]) ] bbox.append(bbox[0]) else: bbox = None query_location = Issue.location if bbox: user_location = f'POLYGON(({", ".join(" ".join(map(str, i)) for i in bbox)}))' user_location = func.ST_GeomFromEWKT(f'SRID=4326;{user_location}') _filter = func.ST_Within(cast(Issue.location, Geometry), user_location) else: user_location = f'POINT({lat} {lon})' _filter = func.ST_DWithin(query_location, user_location, distance) filtered = session.query(Issue).filter(_filter) if pollution_types: filtered = filtered.filter( Issue.pollution_category_id.in_(pollution_types)) if state_types: filtered = filtered.filter(Issue.state_id.in_(state_types)) issues = filtered.order_by( Issue.pollution_rating.desc()).limit(count).all() return issues def get_issues(self, ids): session = self.session_maker() issues = session.query(Issue).filter(Issue.id.in_(ids)).all() return issues
def get_users_within_diameter(): ''' make sure person condition and location both are present otherwise return error get the geocoordinates of all the people in close proximity error-> 302: redirect when no login, otherwise success : 200 ''' print('before post method') if request.method == 'POST': request_data = request.get_json(force=True) print('inside post') try: main_user_latitude = request_data["userLatitude"] main_user_longitude = request_data["userLongitude"] main_user_condition = request_data["personCondition"] temp_identity = get_jwt_identity() current_user_id = temp_identity['id'] # print('{} {}'.format(current_user_id, type(current_user_id))) # Also check if active temp_lat = Decimal(main_user_latitude) temp_lon = Decimal(main_user_longitude) point_wkt = WKTElement('SRID=4326;POINT({} {})'.format(temp_lon, temp_lat), srid=4326) # print(point_string) # update UserHealth with users health user_health_instance = db.session.query(UserHealth).filter(UserHealth.person_id == current_user_id).first() # update_health_with_celery(user_health_instance, # main_user_condition, # current_user_id) # update_health_with_celery.delay(user_health_instance, # main_user_condition, # current_user_id) if(user_health_instance): user_health_instance.user_health = main_user_condition db.session.add(user_health_instance) db.session.commit() else: db.session.add(UserHealth(main_user_condition, current_user_id)) db.session.commit() # update LastLocationGis with users last location last_loc_instance = db.session.query(LastLocationPostGis).filter(LastLocationPostGis.person_id == current_user_id).first() print('last location is {}'.format(last_loc_instance)) # update_location_with_celery(last_loc_instance, # point_wkt, # current_user_id) # update_location_with_celery.delay(last_loc_instance, # point_wkt, # current_user_id) if(last_loc_instance): print('instance found') last_loc_instance.latest_point = point_wkt last_loc_instance.last_modified = dt.datetime.utcnow() db.session.add(last_loc_instance) db.session.commit() else: print('instance not found') db.session.add(LastLocationPostGis(point_wkt, current_user_id)) db.session.commit() # working # Find all points and remove our own point # wkb_element = from_shape(point) # print(point_wkt) # wkb_point = WKBSpatialElement(buffer(point.wkb ), 4326 ) list_of_users_filter = func.ST_DWithin( LastLocationPostGis.latest_point, point_wkt, 100) print('before within') list_of_users = db.session.query(LastLocationPostGis).filter(LastLocationPostGis.active==True).filter(list_of_users_filter).order_by(LastLocationPostGis.person_id).all() print('after within') print(list_of_users) if len(list_of_users) > 0: print('len(list_of_users)') temp_list_user_ids = [] # temp_list_user_ids_parallel=[] # temp_list_user_ids_parallel = Parallel(n_jobs=2)(delayed(i) for i in range(10)) # print(temp_list_user_ids_parallel) with Parallel(n_jobs=1): for every_user in list_of_users: temp_list_user_ids.append(every_user.person_id) temp_list_users_conditions = db.session.query(UserHealth).filter(UserHealth.person_id.in_(temp_list_user_ids)).order_by(UserHealth.person_id).all() list_of_user_location_and_health = [] with Parallel(n_jobs=1): for i in range(len(list_of_users)): temp_obj = UserLocationAndHealth(str(to_shape(list_of_users[i].latest_point).y), str(to_shape(list_of_users[i].latest_point).x), list_of_users[i].person_id, temp_list_users_conditions[i].user_health) list_of_user_location_and_health.append(temp_obj) user_location_health_return = [e.serialize() for e in list_of_user_location_and_health] return jsonify( message=str("fetching complete ....."), list_of_user_location_and_health=(user_location_health_return), # prev_lat=main_user_latitude, # prev_lng=main_user_longitude, # prev_health=main_user_condition ), 200 elif len(list_of_users) == 0: print('len(list_of_users) == 0') print('here') list_of_user_location_and_health = [] temp_obj = UserLocationAndHealth(main_user_latitude, main_user_longitude, current_user_id, main_user_condition) print(temp_obj) list_of_user_location_and_health.append(temp_obj) user_location_health_return = [e.serialize() for e in list_of_user_location_and_health] print(user_location_health_return) return jsonify( message=str("fetching complete ....."), list_of_user_location_and_health=(user_location_health_return), ), 200 else: return jsonify(message=str("in side post"), list_of_users=[]), 200 except KeyError as err: print(err) print('returning key error') return jsonify({"message": str(err)}), 200 return jsonify({"message": str("function ends")}), 401
def get_cases_in_cluster_query(self, daily_cases_query, point): # wkt_point = geography_service.get_point_from_lat_long(point.y, point.x, self.system_coordinate_system) return daily_cases_query.filter( func.ST_DWithin(Case.location, point, self.dycast_parameters.spatial_domain))
def index(self): if "lat" in request.params: lat = float( request.params["lat"] ) else: return { "error": True, "message": "No \"lat\" parameter was found." } if "lon" in request.params: lon = float( request.params["lon"] ) else: return { "error": True, "message": "No \"lon\" parameter was found." } if "zoom" in request.params: zoom = int( request.params["zoom"] ) else: return { "error": True, "message": "No \"zoom\" parameter was found." } is_mobile = False if "mobile" in request.params: if request.params["mobile"] == "true": is_mobile = True point = Point(lon, lat) wkb_point = WKBSpatialElement( buffer( point.wkb ), 4326 ) meters_to_search = 1.8 if is_mobile: meters_to_search = 2.1 distance_meters = pow( meters_to_search, ( 20 - zoom ) ) tolerance = metersToDegrees( distance_meters, lat ) features = [] # # Query points first # # # These layers aren't visible until we hit zoom 9 # if zoom >= 9: # # Light Rail Stop query # lightRailFilter = func.ST_DWithin( wkb_point, LightRail.geometry_column(), tolerance ) lightRailQuery = Session.query( LightRail ).filter( lightRailFilter ) for row in lightRailQuery: feature = row.toFeature() feature.properties["feature_type"] = "Light Rail Stop" features.append( feature ) if len( features ) > 0: return FeatureCollection(features) if zoom >= 16: # # These layers aren't visible until we hit zoom 16 # # # Bar/Pub query # barPubFilter = func.ST_DWithin( wkb_point, BarPub.geometry_column(), tolerance ) barPubQuery = Session.query( BarPub ).filter ( barPubFilter ) for row in barPubQuery: feature = row.toFeature() feature.properties["feature_type"] = "Bar/Pub" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) # # Cafe query # cafeFilter = func.ST_DWithin( wkb_point, Cafe.geometry_column(), tolerance ) cafeQuery = Session.query( Cafe ).filter ( cafeFilter ) for row in cafeQuery: feature = row.toFeature() feature.properties["feature_type"] = "Cafe" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) # # Restaurant query # restaurantFilter = func.ST_DWithin( wkb_point, Restaurant.geometry_column(), tolerance ) restaurantQuery = Session.query( Restaurant ).filter ( restaurantFilter ) for row in restaurantQuery: feature = row.toFeature() feature.properties["feature_type"] = "Restaurant" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) # # Bicycle Rental query # bicycleRentalFilter = func.ST_DWithin( wkb_point, BicycleRental.geometry_column(), tolerance ) bicycleRentalQuery = Session.query( BicycleRental ).filter ( bicycleRentalFilter ) for row in bicycleRentalQuery: feature = row.toFeature() feature.properties["feature_type"] = "Bicycle Rental" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) # # If no points, query lines # # # Light Rail Line query # lightRailLineFilter = func.ST_DWithin( wkb_point, LightRailLine.geometry_column(), tolerance ) lightRailLineQuery = Session.query( LightRailLine ).filter( lightRailLineFilter ) for row in lightRailLineQuery: feature = row.toFeature() feature.properties["feature_type"] = "Light Rail Line" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) # # Free Bus query # freeBusFilter = func.ST_DWithin( wkb_point, FreeBus.geometry_column(), tolerance ) freeBusQuery = Session.query( FreeBus ).filter( freeBusFilter ) for row in freeBusQuery: feature = row.toFeature() feature.properties["feature_type"] = "Free Bus" features.append( feature ) if len( features ) > 0: return FeatureCollection( features ) return FeatureCollection( features )
def _get_near_flights(flight, location, time, max_distance=1000): # calculate max_distance in degrees at the earth's sphere (approximate, # cutoff at +-85 deg) max_distance_deg = (max_distance / METERS_PER_DEGREE) / math.cos( math.radians(min(abs(location.latitude), 85))) # the distance filter is geometric only, so max_distance must be given in # SRID units (which is degrees for WGS84). The filter will be more and more # inaccurate further to the poles. But it's a lot faster than the geograpic # filter... result = Flight.query() \ .options(undefer_group('path')) \ .filter(Flight.id != flight.id) \ .filter(Flight.takeoff_time <= time) \ .filter(Flight.landing_time >= time) \ .filter(func.ST_DWithin(Flight.locations, location.to_wkt_element(), max_distance_deg)) result = _patch_query(result) flights = [] for flight in result: # find point closest to given time closest = min(range(len(flight.timestamps)), key=lambda x: abs( (flight.timestamps[x] - time).total_seconds())) trace = to_shape(flight.locations).coords if closest == 0 or closest == len(trace) - 1: point = trace[closest] else: # interpolate flight trace between two fixes next_smaller = closest if flight.timestamps[ closest] < time else closest - 1 next_larger = closest if flight.timestamps[ closest] > time else closest + 1 dx = (time - flight.timestamps[next_smaller]).total_seconds() / \ (flight.timestamps[next_larger] - flight.timestamps[next_smaller]).total_seconds() point_next = trace[closest] point_prev = trace[closest] point = [ point_prev[0] + (point_next[0] - point_prev[0]) * dx, point_prev[1] + (point_next[1] - point_prev[1]) * dx ] point_distance = location.geographic_distance( Location(latitude=point[1], longitude=point[0])) if point_distance > max_distance: continue flights.append(flight) # limit to 5 flights if len(flights) == 5: break return flights
def get_all_rooms(cls, db, date_from, date_to, longitude, latitude, people, types, owner_uuid, min_price, max_price, allow_blocked, only_blocked, ids): partial_query = db.query(Room) # Date query if ((date_from is not None) and (date_to is not None) and (validate_date_format(date_from)) and (validate_date_format(date_to)) and (datetime.datetime.strptime(date_from, '%Y-%m-%d') <= datetime.datetime.strptime(date_to, '%Y-%m-%d'))): date_from = datetime.datetime.strptime(date_from, '%Y-%m-%d') date_to = datetime.datetime.strptime(date_to, '%Y-%m-%d') # Get the list of room ids that are booked between the dates received book_list = db.query(RoomBooking) \ .filter(((RoomBooking.date_from <= date_to) & (RoomBooking.date_to >= date_from))) \ .distinct(RoomBooking.room_id) \ .with_entities(RoomBooking.room_id) \ .all() # Turn the list of tuples into a list for i in range(len(book_list)): book_list[i] = book_list[i][0] # Filter the rooms that are not booked in the range partial_query = partial_query.filter(~Room.id.in_(book_list)) # Location query if ((longitude is not None) and (latitude is not None) and (-180 < longitude < 180) and (-90 < latitude < 90)): point = WKTElement(f'POINT({longitude} {latitude})', srid=4326) partial_query = partial_query.filter( func.ST_DWithin(Room.coordinates, point, RADIUS)) # People capacity query if (people is not None) and (people >= 0): partial_query = partial_query.filter(Room.capacity >= people) # Owner uuid query if owner_uuid is not None: partial_query = partial_query.filter(Room.owner_uuid == owner_uuid) # Min price query if min_price is not None: partial_query = partial_query.filter( Room.price_per_day >= min_price) # Max price query if max_price is not None: partial_query = partial_query.filter( Room.price_per_day <= max_price) if types is not None: partial_query = partial_query.filter(Room.type.in_(types)) if allow_blocked is False: partial_query = partial_query.filter(Room.blocked == False) if only_blocked is True: partial_query = partial_query.filter(Room.blocked == True) if ids is not None: partial_query = partial_query.filter(Room.id.in_(ids)) rooms_list = partial_query.all() serialized_list = [] for room in rooms_list: serialized_list.append(room.serialize()) return serialized_list
def get_near_location(cls, session, lat, lon, max_distance): geometry = geo.make_point_geometry(lat, lon) threads = session.query(cls).filter( func.ST_DWithin(cls.location, geometry, max_distance)).all() return threads
def get_within_radius(session, lat, lng, radius): geom_var = WKTElement('POINT({0} {1})'.format(lng, lat), srid=4326) return session.query(Point).filter( func.ST_DWithin(Point.geom, geom_var, radius)).all()
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None): tbl = dbcon.getTable('argo2', 'oceanobs') #first create a subquery to quickly discard argo profiles subqry = select([tbl]) if tspan: subqry = subqry.where( func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1])) # Apply initial geospatial constraints if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box subqry = subqry.where( func.ST_DWithin( literal_column('ST_Envelope(geom::geometry)::geography'), func.ST_GeogFromText(geoWKT), withinDmeter)) else: subqry = subqry.where( func.ST_Intersects(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT, 4326))) #we need to assign an alias to this subquery in order to work with it subqry = subqry.alias("ar") #expand the arrays and points int he subquery qry = select([ subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter, func.unnest(subqry.c.mode).label('mode'), func.unnest(subqry.c.ascend).label('ascend'), func.unnest(subqry.c.tlocation).label('tlocation'), func.unnest(subqry.c.cycle).label('cycle'), func.unnest(subqry.c.iprof).label('iprof'), ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom') ]) #additional spatial constraints finalqry = qry qry = qry.alias("arex") if tspan: finalqry = select([qry]).where( between(qry.c.tlocation, tspan[0], tspan[1])) if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box finalqry = finalqry.where( func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT), withinDmeter)) else: finalqry = finalqry.where( func.ST_Within(literal_column("arex.geom"), func.ST_GeomFromText(geoWKT, 4326))) if tsort: finalqry = finalqry.order_by(qry.c.tlocation) return dbcon.dbeng.execute(finalqry)