Data audit: Removing erroneous project geometries

[smit1678]

As flagged in #1460, we have some (at least one) project that has project AOI geometries that are in meters. This is an issue from previous versions of TM where it did not prevent uploading incorrect geometries. Since we store and process all geometries in geographic coordinates, these projects that have geometries in meters raise errors, especially for stats work looking at calculating area.

Potential solution

We should drop/remove these projects from the database. We will most likely need to write a query that checks for coordinates that are beyond WGS84 Bounds: -180.0000, -90.0000, 180.0000, 90.0000.

[ramyaragupathy]

Cross posting from #1460

Got to dig the project deletion behaviour further. Recording my observations:

1. Any project manager can delete a project irrespective of who the author is
2. If a project has contributions (tasks mapped or validated) then the deletion throws an error message.
3. Projects could be deleted by reseting task status to ready. Like @russdeffner mentioned this tricks the app by creating new entries in task history. Old entries in the table are preserved though.
   Once reset is done, deletion gets through.
4. However even if all the tasks are reset to READY status, if there has been a chat discussion for the project then the deletion fails with the same Contributions present message.
5. When tasks are reset, number of tasks mapped and validated are updated in the projects table But the counter is not reset for bad imagery tasks.

I feel items 1,4 and 5 behaviour should be fine tuned:

1. Any project manager can delete a project irrespective of who the author is

Either the deletion should be restricted to only admins or the project author. I'd recommend allowing just project authors to delete, so that there is no additional layer of communication with the admin to delete a project

4. However even if all the tasks are reset to READY status, if there has been a chat discussion for the project then the deletion fails with the same Contributions present message.

This fails due to a foreign key constraint:

CRITICAL in project_admin_api [/Users/ramyaragupathy/Github/tasking-manager/server/api/project_admin_api.py:312]:
Project GET - unhandled error: (psycopg2.IntegrityError) update or delete on table "projects" violates foreign key constraint "project_chat_project_id_fkey" on table "project_chat"
DETAIL:  Key (id)=(8) is still referenced from table "project_chat".
 [SQL: 'DELETE FROM projects WHERE projects.id = %(id)s'] [parameters: {'id': 8}]

Should reset be extended to cover all actions associated with a project rather than just task histories? This means we lose all conversations/knowledge sharing that happened within the TM platform

5. When tasks are reset, number of tasks mapped and validated are updated in the projects table But the counter is not reset for bad imagery tasks.

If tasks mapped & validated counts are set to zero on hard reset, then the bad imagery counter should be set to nil as well. Because per current task status after the reset all images are in READY state and there is no point maintaining the counters at old value. Suppose after the reset, a task is marked for bad imagery, then the previous count get added as well leading to escalation in numbers.

On the previous question of fixing invalid geometries,

What do we do with the others? The ones with invalid geometry but already happening has going on. Can we try to fix the AOI?

Agree that we should fix the AOI, but let's try that manually on a case by case basis to ensure none of the project history is affected.

If you'd like to understand the entire DB level update, you can go through this doc where I have logged the table level changes for project creation/deletion and task splitting.

cc @xamanu @ethan-nelson @smit1678

[pantierra]

This has been done some months ago and was confirmed. Closing here.