示例#1
0
    def get_project_stats(self) -> ProjectStatsDTO:
        """ Create Project Stats model for postgis project object"""
        project_stats = ProjectStatsDTO()
        project_stats.project_id = self.id
        project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
        project_area_result = db.engine.execute(text(project_area_sql),
                                                id=self.id)

        project_stats.area = project_area_result.fetchone()["area"]
        project_stats.total_mappers = (db.session.query(User).filter(
            User.projects_mapped.any(self.id)).count())
        project_stats.total_tasks = self.total_tasks
        project_stats.total_comments = (db.session.query(ProjectChat).filter(
            ProjectChat.project_id == self.id).count())
        project_stats.percent_mapped = Project.calculate_tasks_percent(
            "mapped",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_validated = Project.calculate_tasks_percent(
            "validated",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
            "bad_imagery",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
        project_stats.aoi_centroid = geojson.loads(centroid_geojson)
        unique_mappers = (TaskHistory.query.filter(
            TaskHistory.action == "LOCKED_FOR_MAPPING",
            TaskHistory.project_id == self.id,
        ).distinct(TaskHistory.user_id).count())
        unique_validators = (TaskHistory.query.filter(
            TaskHistory.action == "LOCKED_FOR_VALIDATION",
            TaskHistory.project_id == self.id,
        ).distinct(TaskHistory.user_id).count())
        project_stats.total_time_spent = 0
        project_stats.total_mapping_time = 0
        project_stats.total_validation_time = 0
        project_stats.average_mapping_time = 0
        project_stats.average_validation_time = 0

        query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
                   WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING')
                   and project_id = :project_id;"""
        total_mapping_time = db.engine.execute(text(query), project_id=self.id)
        for row in total_mapping_time:
            total_mapping_time = row[0]
            if total_mapping_time:
                total_mapping_seconds = total_mapping_time.total_seconds()
                project_stats.total_mapping_time = total_mapping_seconds
                project_stats.total_time_spent += project_stats.total_mapping_time
                if unique_mappers:
                    average_mapping_time = total_mapping_seconds / unique_mappers
                    project_stats.average_mapping_time = average_mapping_time

        query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
                   WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION')
                   and project_id = :project_id;"""
        total_validation_time = db.engine.execute(text(query),
                                                  project_id=self.id)
        for row in total_validation_time:
            total_validation_time = row[0]
            if total_validation_time:
                total_validation_seconds = total_validation_time.total_seconds(
                )
                project_stats.total_validation_time = total_validation_seconds
                project_stats.total_time_spent += project_stats.total_validation_time
                if unique_validators:
                    average_validation_time = (total_validation_seconds /
                                               unique_validators)
                    project_stats.average_validation_time = average_validation_time

        return project_stats
示例#2
0
    def get_project_stats(self) -> ProjectStatsDTO:
        """ Create Project Stats model for postgis project object"""
        project_stats = ProjectStatsDTO()
        project_stats.project_id = self.id
        project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
        project_area_result = db.engine.execute(text(project_area_sql),
                                                id=self.id)

        project_stats.area = project_area_result.fetchone()["area"]
        project_stats.total_mappers = (db.session.query(User).filter(
            User.projects_mapped.any(self.id)).count())
        project_stats.total_tasks = self.total_tasks
        project_stats.total_comments = (db.session.query(ProjectChat).filter(
            ProjectChat.project_id == self.id).count())
        project_stats.percent_mapped = Project.calculate_tasks_percent(
            "mapped",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_validated = Project.calculate_tasks_percent(
            "validated",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
            "bad_imagery",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
        project_stats.aoi_centroid = geojson.loads(centroid_geojson)
        project_stats.total_time_spent = 0
        project_stats.total_mapping_time = 0
        project_stats.total_validation_time = 0
        project_stats.average_mapping_time = 0
        project_stats.average_validation_time = 0

        total_mapping_time, total_mapping_tasks = (db.session.query(
            func.sum(
                cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
                     Time)),
            func.count(TaskHistory.action),
        ).filter(
            or_(
                TaskHistory.action == "LOCKED_FOR_MAPPING",
                TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
            )).filter(TaskHistory.project_id == self.id).one())

        if total_mapping_tasks > 0:
            total_mapping_time = total_mapping_time.total_seconds()
            project_stats.total_mapping_time = total_mapping_time
            project_stats.average_mapping_time = (total_mapping_time /
                                                  total_mapping_tasks)
            project_stats.total_time_spent += total_mapping_time

        total_validation_time, total_validation_tasks = (db.session.query(
            func.sum(
                cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
                     Time)),
            func.count(TaskHistory.action),
        ).filter(
            or_(
                TaskHistory.action == "LOCKED_FOR_VALIDATION",
                TaskHistory.action == "AUTO_UNLOCKED_FOR_VALIDATION",
            )).filter(TaskHistory.project_id == self.id).one())

        if total_validation_tasks > 0:
            total_validation_time = total_validation_time.total_seconds()
            project_stats.total_validation_time = total_validation_time
            project_stats.average_validation_time = (total_validation_time /
                                                     total_validation_tasks)
            project_stats.total_time_spent += total_validation_time

        actions = []
        if project_stats.average_mapping_time <= 0:
            actions.append(TaskStatus.LOCKED_FOR_MAPPING.name)
        if project_stats.average_validation_time <= 0:
            actions.append(TaskStatus.LOCKED_FOR_VALIDATION.name)

        zoom_levels = []
        # Check that averages are non-zero.
        if len(actions) != 0:
            zoom_levels = (Task.query.with_entities(
                Task.zoom.distinct()).filter(Task.project_id == self.id).all())
            zoom_levels = [z[0] for z in zoom_levels]

        # Validate project has arbitrary tasks.
        is_square = True
        if None in zoom_levels:
            is_square = False
        sq = (TaskHistory.query.with_entities(
            Task.zoom,
            TaskHistory.action,
            (cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
                  Time)).label("ts"),
        ).filter(Task.is_square == is_square).filter(
            TaskHistory.project_id == Task.project_id).filter(
                TaskHistory.task_id == Task.id).filter(
                    TaskHistory.action.in_(actions)))
        if is_square is True:
            sq = sq.filter(Task.zoom.in_(zoom_levels))

        sq = sq.subquery()

        nz = (db.session.query(sq.c.zoom, sq.c.action, sq.c.ts).filter(
            sq.c.ts > datetime.time(0)).limit(10000).subquery())

        if project_stats.average_mapping_time <= 0:
            mapped_avg = (db.session.query(nz.c.zoom, (func.avg(
                nz.c.ts)).label("avg")).filter(
                    nz.c.action ==
                    TaskStatus.LOCKED_FOR_MAPPING.name).group_by(
                        nz.c.zoom).all())
            mapping_time = sum([t.avg.total_seconds()
                                for t in mapped_avg]) / len(mapped_avg)
            project_stats.average_mapping_time = mapping_time

        if project_stats.average_validation_time <= 0:
            val_avg = (db.session.query(nz.c.zoom, (func.avg(
                nz.c.ts)).label("avg")).filter(
                    nz.c.action ==
                    TaskStatus.LOCKED_FOR_VALIDATION.name).group_by(
                        nz.c.zoom).all())
            validation_time = sum([t.avg.total_seconds()
                                   for t in val_avg]) / len(val_avg)
            project_stats.average_validation_time = validation_time

        time_to_finish_mapping = (
            self.total_tasks -
            (self.tasks_mapped + self.tasks_bad_imagery +
             self.tasks_validated)) * project_stats.average_mapping_time
        project_stats.time_to_finish_mapping = time_to_finish_mapping
        project_stats.time_to_finish_validating = (
            self.total_tasks - (self.tasks_validated + self.tasks_bad_imagery)
        ) * project_stats.average_validation_time + time_to_finish_mapping

        return project_stats
示例#3
0
    def get_project_stats(self) -> ProjectStatsDTO:
        """ Create Project Stats model for postgis project object"""
        project_stats = ProjectStatsDTO()
        project_stats.project_id = self.id
        project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
        project_area_result = db.engine.execute(text(project_area_sql), id=self.id)

        project_stats.area = project_area_result.fetchone()["area"]
        project_stats.total_mappers = (
            db.session.query(User).filter(User.projects_mapped.any(self.id)).count()
        )
        project_stats.total_tasks = self.total_tasks
        project_stats.total_comments = (
            db.session.query(ProjectChat)
            .filter(ProjectChat.project_id == self.id)
            .count()
        )
        project_stats.percent_mapped = Project.calculate_tasks_percent(
            "mapped",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_validated = Project.calculate_tasks_percent(
            "validated",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
            "bad_imagery",
            self.total_tasks,
            self.tasks_mapped,
            self.tasks_validated,
            self.tasks_bad_imagery,
        )
        centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
        project_stats.aoi_centroid = geojson.loads(centroid_geojson)
        unique_mappers = (
            TaskHistory.query.filter(
                TaskHistory.action == "LOCKED_FOR_MAPPING",
                TaskHistory.project_id == self.id,
            )
            .distinct(TaskHistory.user_id)
            .count()
        )
        unique_validators = (
            TaskHistory.query.filter(
                TaskHistory.action == "LOCKED_FOR_VALIDATION",
                TaskHistory.project_id == self.id,
            )
            .distinct(TaskHistory.user_id)
            .count()
        )
        project_stats.total_time_spent = 0
        project_stats.total_mapping_time = 0
        project_stats.total_validation_time = 0
        project_stats.average_mapping_time = 0
        project_stats.average_validation_time = 0

        total_mapping_time = (
            db.session.query(
                func.sum(
                    cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
                )
            )
            .filter(
                or_(
                    TaskHistory.action == "LOCKED_FOR_MAPPING",
                    TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
                )
            )
            .filter(TaskHistory.project_id == self.id)
        )
        for row in total_mapping_time:
            total_mapping_time = row[0]
            if total_mapping_time:
                total_mapping_seconds = total_mapping_time.total_seconds()
                project_stats.total_mapping_time = total_mapping_seconds
                project_stats.total_time_spent += project_stats.total_mapping_time
                if unique_mappers:
                    average_mapping_time = total_mapping_seconds / unique_mappers
                    project_stats.average_mapping_time = average_mapping_time

        query = (
            TaskHistory.query.with_entities(
                func.date_trunc("minute", TaskHistory.action_date).label("trn"),
                func.max(TaskHistory.action_text).label("tm"),
            )
            .filter(TaskHistory.project_id == self.id)
            .filter(TaskHistory.action == "LOCKED_FOR_VALIDATION")
            .group_by("trn")
            .subquery()
        )
        total_validation_time = db.session.query(
            func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"), Time))
        ).all()

        for row in total_validation_time:
            total_validation_time = row[0]
            if total_validation_time:
                total_validation_seconds = total_validation_time.total_seconds()
                project_stats.total_validation_time = total_validation_seconds
                project_stats.total_time_spent += project_stats.total_validation_time
                if unique_validators:
                    average_validation_time = (
                        total_validation_seconds / unique_validators
                    )
                    project_stats.average_validation_time = average_validation_time

        return project_stats