def timezone_at_coordinate(session, geom):
area = session.execute(
select(TimezoneArea.tzid).where(
func.ST_Contains(TimezoneArea.geom, geom))).scalar_one_or_none()
if area:
return area.tzid
return None
def can_moderate_at(session, user_id, shape):
"""
Returns True if the user_id can moderate a given geo-shape (i.e., if the shape is contained in any Node that the user is an admin of)
"""
cluster_ids = list(
session.query(Cluster.id).join(
Node, Node.id == Cluster.parent_node_id).filter(
Cluster.is_official_cluster).filter(
func.ST_Contains(Node.geom, shape)).all())
return _can_moderate_any_cluster(session, user_id, cluster_ids)
def get_parent_node_at_location(session, shape):
"""
Finds the smallest node containing the shape.
Shape can be any PostGIS geo object, e.g. output from create_coordinate
"""
# Fin the lowest Node (in the Node tree) that contains the shape. By construction of nodes, the area of a sub-node
# must always be less than its parent Node, so no need to actually traverse the tree!
return session.query(Node).filter(func.ST_Contains(Node.geom, shape)).order_by(func.ST_Area(Node.geom)).first()
def can_moderate_at(session, user_id, shape):
"""
Returns True if the user_id can moderate a given geo-shape (i.e., if the shape is contained in any Node that the user is an admin of)
"""
cluster_ids = [
cluster_id for (cluster_id, ) in session.execute(
select(Cluster.id).join(Node, Node.id == Cluster.parent_node_id).
where(Cluster.is_official_cluster).where(
func.ST_Contains(Node.geom, shape))).all()
]
return _can_moderate_any_cluster(session, user_id, cluster_ids)
def enforce_community_memberships_for_user(session, user):
"""
Adds a given user to all the communities they belong in based on their location.
"""
nodes = session.execute(
select(Node).where(func.ST_Contains(Node.geom,
user.geom))).scalars().all()
for node in nodes:
node.official_cluster.cluster_subscriptions.append(
ClusterSubscription(
user=user,
role=ClusterRole.member,
))
session.commit()
def enforce_community_memberships():
"""
Go through all communities and make sure every user in the polygon is also a member
"""
with session_scope() as session:
for node in session.execute(select(Node)).scalars().all():
existing_users = select(ClusterSubscription.user_id).where(
ClusterSubscription.cluster == node.official_cluster)
users_needing_adding = (session.execute(
select(User).where(User.is_visible).where(
func.ST_Contains(node.geom, User.geom)).where(
~User.id.in_(existing_users))).scalars().all())
for user in users_needing_adding:
node.official_cluster.cluster_subscriptions.append(
ClusterSubscription(
user=user,
role=ClusterRole.member,
))
session.commit()
def ListNearbyUsers(self, request, context):
with session_scope() as session:
page_size = min(MAX_PAGINATION_LENGTH, request.page_size or MAX_PAGINATION_LENGTH)
next_nearby_id = int(request.page_token) if request.page_token else 0
node = session.execute(select(Node).where(Node.id == request.community_id)).scalar_one_or_none()
if not node:
context.abort(grpc.StatusCode.NOT_FOUND, errors.COMMUNITY_NOT_FOUND)
nearbys = (
session.execute(
select(User)
.where_users_visible(context)
.where(func.ST_Contains(node.geom, User.geom))
.where(User.id >= next_nearby_id)
.order_by(User.id)
.limit(page_size + 1)
)
.scalars()
.all()
)
return communities_pb2.ListNearbyUsersRes(
nearby_user_ids=[nearby.id for nearby in nearbys[:page_size]],
next_page_token=str(nearbys[-1].id) if len(nearbys) > page_size else None,
)
def st_contains(self, x, y):
query = self.__session.query(NdtPose).filter(
func.ST_Contains(NdtPose.geom,
WKTElement('POINT(%f %f)' % (x, y))))
return self.__get_files(query)
def UserSearch(self, request, context):
with session_scope() as session:
query = session.query(User).filter(~User.is_banned)
if request.HasField("query"):
if request.query_name_only:
query = query.filter(
or_(User.name.ilike(f"%{request.query.value}%"),
User.username.ilike(f"%{request.query.value}%")))
else:
query = query.filter(
or_(
User.name.ilike(f"%{request.query.value}%"),
User.username.ilike(f"%{request.query.value}%"),
User.city.ilike(f"%{request.query.value}%"),
User.hometown.ilike(f"%{request.query.value}%"),
User.about_me.ilike(f"%{request.query.value}%"),
User.my_travels.ilike(f"%{request.query.value}%"),
User.things_i_like.ilike(
f"%{request.query.value}%"),
User.about_place.ilike(f"%{request.query.value}%"),
User.additional_information.ilike(
f"%{request.query.value}%"),
))
if request.HasField("last_active"):
raw_dt = to_aware_datetime(request.last_active)
coarsened_dt = raw_dt.replace(minute=(raw_dt.minute // 15) *
15,
second=0,
microsecond=0)
query = query.filter(User.last_active >= coarsened_dt)
if request.HasField("gender"):
query = query.filter(
User.gender.ilike(f"%{request.gender.value}%"))
if len(request.hosting_status_filter) > 0:
query = query.filter(
User.hosting_status.in_([
hostingstatus2sql[status]
for status in request.hosting_status_filter
]))
if len(request.smoking_location_filter) > 0:
query = query.filter(
User.smoking_allowed.in_([
smokinglocation2sql[loc]
for loc in request.smoking_location_filter
]))
if len(request.sleeping_arrangement_filter) > 0:
query = query.filter(
User.sleeping_arrangement.in_([
sleepingarrangement2sql[arr]
for arr in request.sleeping_arrangement_filter
]))
if len(request.parking_details_filter) > 0:
query = query.filter(
User.parking_details.in_([
parkingdetails2sql[det]
for det in request.parking_details_filter
]))
if request.HasField("guests"):
query = query.filter(User.max_guests >= request.guests.value)
if request.HasField("last_minute"):
query = query.filter(User.last_minute == last_minute.value)
if request.HasField("has_pets"):
query = query.filter(User.has_pets == has_pets.value)
if request.HasField("accepts_pets"):
query = query.filter(User.accepts_pets == accepts_pets.value)
if request.HasField("has_kids"):
query = query.filter(User.has_kids == has_kids.value)
if request.HasField("accepts_kids"):
query = query.filter(User.accepts_kids == accepts_kids.value)
if request.HasField("has_housemates"):
query = query.filter(
User.has_housemates == has_housemates.value)
if request.HasField("wheelchair_accessible"):
query = query.filter(
User.wheelchair_accessible == wheelchair_accessible.value)
if request.HasField("smokes_at_home"):
query = query.filter(
User.smokes_at_home == smokes_at_home.value)
if request.HasField("drinking_allowed"):
query = query.filter(
User.drinking_allowed == drinking_allowed.value)
if request.HasField("drinks_at_home"):
query = query.filter(
User.drinks_at_home == drinks_at_home.value)
if request.HasField("parking"):
query = query.filter(User.parking == parking.value)
if request.HasField("camping_ok"):
query = query.filter(User.camping_ok == camping_ok.value)
if request.HasField("search_in_area"):
# EPSG4326 measures distance in decimal degress
# we want to check whether two circles overlap, so check if the distance between their centers is less
# than the sum of their radii, divided by 111111 m ~= 1 degree (at the equator)
search_point = create_coordinate(request.search_in_area.lat,
request.search_in_area.lng)
query = query.filter(
func.ST_DWithin(
User.geom, search_point,
(User.geom_radius + request.search_in_area.radius) /
111111))
if request.HasField("search_in_community_id"):
# could do a join here as well, but this is just simpler
node = session.query(Node).filter(
Node.id == request.search_in_community_id).one_or_none()
if not node:
context.abort(grpc.StatusCode.NOT_FOUND,
errors.COMMUNITY_NOT_FOUND)
query = query.filter(func.ST_Contains(node.geom, User.geom))
if request.only_with_references:
query = query.join(Reference, Reference.to_user_id == User.id)
# TODO:
# google.protobuf.StringValue language = 11;
# bool friends_only = 13;
# google.protobuf.UInt32Value age_min = 14;
# google.protobuf.UInt32Value age_max = 15;
page_size = min(MAX_PAGINATION_LENGTH, request.page_size
or MAX_PAGINATION_LENGTH)
next_user_id = int(request.page_token) if request.page_token else 0
users = query.filter(User.id >= next_user_id).order_by(
User.id).limit(page_size + 1).all()
return search_pb2.UserSearchRes(
results=[
search_pb2.Result(
rank=1,
user=user_model_to_pb(user, session, context),
) for user in users[:page_size]
],
next_page_token=str(users[-1].id)
if len(users) > page_size else None,
)
def get_observation_score():
"""
.. :quickref: Profiles;
Check an observation with the related profile
Return alert when the observation do not match the profile
"""
data = request.get_json()
try:
cd_ref = data["cd_ref"]
except KeyError:
raise BadRequest("No cd_ref provided")
# Récupération du profil du cd_ref
result = {}
profile = (DB.session.query(VmValidProfiles).filter(
VmValidProfiles.cd_ref == cd_ref).one_or_none())
if not profile:
raise NotFound("No profile for this cd_ref")
check_life_stage = profile.active_life_stage
result = {
"valid_distribution": True,
"valid_altitude": True,
"valid_phenology": True,
"valid_life_stage": None,
"life_stage_accepted": [],
"errors": [],
"profil": profile.as_dict(),
"check_life_stage": check_life_stage
}
# Calcul de la période correspondant à la date
if data.get("date_min") and data.get("date_max"):
date_min = datetime.datetime.strptime(data["date_min"], "%Y-%m-%d")
date_max = datetime.datetime.strptime(data["date_max"], "%Y-%m-%d")
# Calcul du numéro du jour pour les dates min et max
doy_min = date_min.timetuple().tm_yday
doy_max = date_max.timetuple().tm_yday
else:
raise BadRequest("Missing date min or date max")
# Récupération des altitudes
if data.get("altitude_min") and data.get("altitude_max"):
altitude_min = data["altitude_min"]
altitude_max = data["altitude_max"]
else:
raise BadRequest('Missing altitude_min or altitude_max')
# Check de la répartition
if "geom" in data:
query = DB.session.query(
func.ST_Contains(
func.ST_Transform(profile.valid_distribution, 4326),
func.ST_SetSRID(
func.ST_GeomFromGeoJSON(json.dumps(data["geom"])), 4326),
))
check_geom = query.one_or_none()
if not check_geom:
result["valid_distribution"] = False
result["errors"].append({
"type":
"geometry",
"value":
"Erreur lors du calcul de la géométrie valide"
})
if check_geom[0] is False:
result["valid_distribution"] = False
result["errors"].append({
"type":
"geom",
"value":
f"Le taxon n'a jamais été observé dans cette zone géographique"
})
else:
result["valid_distribution"] = True
# check de la periode
q_pheno = DB.session.query(
VmCorTaxonPhenology.id_nomenclature_life_stage).distinct()
q_pheno = q_pheno.filter(VmCorTaxonPhenology.cd_ref == cd_ref)
q_pheno = q_pheno.filter(
VmCorTaxonPhenology.doy_min <= doy_min).filter(
VmCorTaxonPhenology.doy_max >= doy_max)
period_result = q_pheno.all()
if len(period_result) == 0:
result["valid_phenology"] = False
result["errors"].append({
"type":
"period",
"value":
"Le taxon n'a jamais été observé à cette periode"
})
# check de l'altitude
if altitude_max > profile.altitude_max or altitude_min < profile.altitude_min:
result["valid_altitude"] = False
result["errors"].append({
"type":
"altitude",
"value":
f"Le taxon n'a jamais été observé à cette altitude ({altitude_min}-{altitude_max}m)"
})
# check de l'altitude pour la période donnée
if len(period_result) > 0:
peridod_and_altitude = q_pheno.filter(
VmCorTaxonPhenology.calculated_altitude_min <= altitude_min)
peridod_and_altitude = peridod_and_altitude.filter(
VmCorTaxonPhenology.calculated_altitude_max >= altitude_max)
peridod_and_altitude_r = peridod_and_altitude.all()
if len(peridod_and_altitude_r) > 0:
result["valid_altitude"] = True
result["valid_phenology"] = True
for row in peridod_and_altitude_r:
# Construction de la liste des stade de vie potentielle
if row.id_nomenclature_life_stage:
result["life_stage_accepted"].append(
row.id_nomenclature_life_stage)
else:
result["valid_altitude"] = False
result["valid_phenology"] = False
if altitude_max <= profile.altitude_max and altitude_min >= altitude_min:
result["errors"].append({
"type":
"period",
"value":
f"Le taxon a déjà été observé à cette altitude ({altitude_min}-{altitude_max}m), mais pas à cette periode de l'année"
})
if result["valid_phenology"]:
result["errors"].append({
"type":
"period",
"value":
f"Le taxon a déjà été observé à cette periode de l'année, mais pas à cette altitude ({altitude_min}-{altitude_max}m)"
})
# check du stade de vie pour la periode donnée
if check_life_stage and "life_stages" in data:
if type(data["life_stages"]) is not list:
raise BadRequest("life_stages must be a list")
for life_stage in data["life_stages"]:
life_stage_value = TNomenclatures.query.get(life_stage)
q = q_pheno.filter(VmCorTaxonPhenology.
id_nomenclature_life_stage == life_stage)
r_life_stage = q.all()
if len(r_life_stage) == 0:
result["valid_life_stage"] = False
result["valid_phenology"] = False
result["errors"].append({
"type":
"life_stage",
"value":
f"Le taxon n'a jamais été observé à cette periode pour le stade de vie {life_stage_value.label_default}"
})
# check du stade de vie pour la période et l'altitude
else:
if altitude_min and altitude_max:
q = q.filter(VmCorTaxonPhenology.
calculated_altitude_min <= altitude_min)
q = q.filter(VmCorTaxonPhenology.
calculated_altitude_max >= altitude_max)
r_life_stage_altitude = q.all()
if len(r_life_stage_altitude) == 0:
result["valid_life_stage"] = False
result["valid_altitude"] = False
result["valid_phenology"] = False
result["errors"].append({
"type":
"life_stage",
"value":
f"""
Le taxon n'a jamais été observé à cette periode et à cette altitude ({altitude_min}-{altitude_max}m)
pour le stade de vie {life_stage_value.label_default}"""
})
return result