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)
