python-mapswipe-workers
python-mapswipe-workers copied to clipboard
Move group aggregated columns calculation logic
Related to: https://github.com/mapswipe/python-mapswipe-workers/pull/780#discussion_r1227792502
Currently, we are calculating the group's total area and max time allowed using the aggregated module as it is only used by it introduced in PR#780. For more consistency, we will have to move this to the project->creation pipeline.
Basic Query needed:
groups_data AS (
SELECT
T.project_id,
T.group_id,
SUM( -- sqkm
ST_Area(T.geom::geography(GEOMETRY,4326)) / 1000000
) as total_task_group_area,
(
CASE
-- Using 95_percent value of existing data for each project_type
WHEN P.project_type = {Project.Type.BUILD_AREA.value} THEN 1.4
WHEN P.project_type = {Project.Type.COMPLETENESS.value} THEN 1.4
WHEN P.project_type = {Project.Type.CHANGE_DETECTION.value} THEN 11.2
-- FOOTPRINT: Not calculated right now
WHEN P.project_type = {Project.Type.FOOTPRINT.value} THEN 6.1
ELSE 1
END
) * COUNT(*) as time_spent_max_allowed
FROM tasks T
INNER JOIN projects P USING (project_id)
WHERE T.project_id = %(project_id)s
GROUP BY project_id, P.project_type, group_id
)
UPDATE groups G
SET
total_area = GD.total_task_group_area,
time_spent_max_allowed = GD.time_spent_max_allowed
FROM groups_data GD
WHERE
G.project_id = GD.project_id AND
G.group_id = GD.group_id;
### Tasks
- [ ] Move logic to project->group pipeline
- [ ] Define test cases
cc: @Hagellach37 @ElJocho