def get(self, *args, **kwargs):
lat = request.args['lat']
lng = request.args['lng']
video_id = request.args['video_id']
rq_id = request.args.get('rq_id')
if rq_id:
rq = RoadQuality.query.filter_by(id=rq_id).first()
frame = Frames.query.filter(Frames.video_id == video_id).order_by(
func.st_distance(
Frames.point, func.st_setsrid(func.st_makepoint(lng, lat),
4326)))
frame = frame.first()
return jsonify({
'url':
'/photo/{}/frame_{}.jpg'.format(frame.video_id, frame.id),
'position':
frame.l,
'frame':
frame.frame,
'defects':
rq.defects if rq_id else 'дефекты отсутствуют',
'score':
rq.score if rq_id else '5',
})
def intersect():
"""
Find the closest parcelle to the point and set it as
new geography
"""
# Input dataset
basol_geog_merged = Dataset("etl", "basol_geog_merged")
cadastre = Dataset("etl", "cadastre")
# Output dataset
basol_intersected = Dataset("etl", "basol_intersected")
dtype = basol_geog_merged.read_dtype()
basol_intersected.write_dtype(dtype)
Cadastre = cadastre.reflect()
BasolGeogMerged = basol_geog_merged.reflect()
session = basol_geog_merged.get_session()
stmt = session.query(Cadastre.geog) \
.filter(func.st_dwithin(
Cadastre.geog,
BasolGeogMerged.geog,
0.0001)) \
.order_by(func.st_distance(
Cadastre.geog,
BasolGeogMerged.geog)) \
.limit(1) \
.label("nearest")
q = session.query(BasolGeogMerged, stmt).all()
with basol_intersected.get_writer() as writer:
for (row, cadastre_geog) in q:
if cadastre_geog is not None:
row.geog = cadastre_geog
writer.write_row_dict(row2dict(row))
session.close()
def _run_one(self, missed):
start = local_time_to_time(missed)
with self._config.db.session_context() as s:
ajournal = self._get_source(s, start)
with Timestamp(owner=self.owner_out,
source=ajournal).on_success(s):
if ajournal.route_edt:
for sector_group in s.query(SectorGroup). \
filter(func.st_distance(SectorGroup.centre, ajournal.centre)
< SectorGroup.radius). \
all():
log.info(
f'Finding climbs for activity journal {ajournal.id} / sector group {sector_group.id}'
)
try:
self.__find_big_climbs(s, sector_group, ajournal)
except Exception as e:
log.warning(
f'Climb detection failed with {e} for activity journal {ajournal.id} '
f'and sector group {sector_group.id}')
log_current_exception(True)
s.rollback()
def add_parcelle():
"""
Projette les points géométriques sur la parcelle la plus proche.
On se limite à un voisinage d'environ 10m = 0.0001 degré.
Si aucune parcelle n'a été trouvée dans ce voisinage on conserve
le point d'origine.
La requête SQL Alchemy est équivalente à
SELECT *,
(SELECT geog
FROM kelrisks.cadastre AS c
WHERE st_dwithin(ic.geog, c.geog, 0.0001)
ORDER BY st_distance(ic.geog, c.geog)
LIMIT 1) nearest
FROM etl.s3ic_geog_merged ic
On utilise la table cadastre du schéma kelrisks
et non la table du schéma etl car il n'y a pas assez
de stockage sur le serveur pour avoir 4 tables cadastre
(preprod `etl`, preprod `kelrisks`, prod `etl`, prod `kelrisks`).
On est donc obligé de supprimer les tables cadastres
du schéma `etl` après les avoir copié dans le schéma `kelrisks`.
La table `etl.cadastre` n'existe donc pas forcément au moment
où ce DAG est executé.
"""
# Input dataset
s3ic_geog_merged = Dataset("etl", "s3ic_geog_merged")
cadastre = Dataset("kelrisks", "cadastre")
# Output dataset
s3ic_with_parcelle = Dataset("etl", "s3ic_with_parcelle")
dtype = s3ic_geog_merged.read_dtype()
s3ic_with_parcelle.write_dtype(dtype)
Cadastre = cadastre.reflect()
S3icGeogMerged = s3ic_geog_merged.reflect()
session = s3ic_geog_merged.get_session()
stmt = session.query(Cadastre.geog) \
.filter(func.st_dwithin(
Cadastre.geog,
S3icGeogMerged.geog,
0.0001)) \
.order_by(func.st_distance(
Cadastre.geog,
S3icGeogMerged.geog)) \
.limit(1) \
.label("nearest")
q = session.query(S3icGeogMerged, stmt).yield_per(500)
with s3ic_with_parcelle.get_writer() as writer:
for (row, cadastre_geog) in q:
if cadastre_geog is not None \
and row.geog_precision != precisions.MUNICIPALITY:
row.geog = cadastre_geog
writer.write_row_dict(row2dict(row))
session.close()
def get_distance(object_a, object_b):
"""Calculates the distance between two geometries."""
assert object_a.geom is not None
assert object_b.geom is not None
return session.query(func.st_distance(object_a.geom, object_b.geom))[0][0]
def extend_matching_pair(stroke_ref, stroke_target, junction_ref,
junction_target, tolerance_distance):
"""Extends the input delimited strokes with strokes that have good continuity at input junction,
until a good match is found or if no match is possible. Stroke_ref and stroke_target are both lists of delimited
strokes."""
# create local variables of which stroke to extend and which to compare to when a new stroke is added
if get_length(stroke_ref) < get_length(stroke_target):
stroke_to_extend = stroke_ref
stroke_to_compare = stroke_target
junction_to_extend = junction_ref
junction_to_compare = junction_target
else:
stroke_to_extend = stroke_target
stroke_to_compare = stroke_ref
junction_to_extend = junction_target
junction_to_compare = junction_ref
new_stroke = None
# if the junction where the next stroke is added is a W-junction (type 1), select the stroke of the outer road
# sections to be added
if junction_to_extend.type_k3 == 1:
if angle_at_junction(
stroke_to_extend[-1],
junction_to_extend) != junction_to_extend.angle_k3:
for road_section in junction_to_extend.road_sections:
if road_section.delimited_stroke != stroke_to_extend[-1] and junction_to_extend.angle_k3 != \
angle_at_junction(road_section, junction_to_extend):
new_stroke = road_section.delimited_stroke
# for other junctions, select the stroke that has good continuity
if junction_to_extend.degree > 1:
for road_section in junction_to_extend.road_sections:
if road_section.delimited_stroke != stroke_to_extend[
-1] and has_good_continuity(road_section,
stroke_to_extend[-1],
junction_to_extend):
new_stroke = road_section.delimited_stroke
if new_stroke and (new_stroke.begin_junction == junction_to_extend
or new_stroke.end_junction == junction_to_extend):
stroke_to_extend.append(new_stroke)
junction_to_extend = other_junction(stroke_to_extend[-1],
junction_to_extend)
point_distance = session.query(
func.st_distance(junction_to_extend.geom,
junction_to_compare.geom))[0][0]
if point_distance < tolerance_distance:
return Match(stroke_ref, stroke_target)
elif session.query(func.st_distance(junction_to_extend.geom, stroke_to_compare[-1].geom))[0][0] < tolerance_distance or \
session.query(func.st_distance(junction_to_compare.geom, stroke_to_extend[-1].geom))[0][0] < tolerance_distance:
if stroke_to_extend == stroke_ref:
return extend_matching_pair(stroke_ref, stroke_target,
junction_to_extend,
junction_target,
tolerance_distance)
elif stroke_to_extend == stroke_target:
return extend_matching_pair(stroke_ref, stroke_target,
junction_ref, junction_to_extend,
tolerance_distance)
else:
print('Error: wrong stroke')
return None