Mask "Experiment" to support metrics for AOIs and VBs

[philipbaileynar]

Fundamental Need

Users need the ability to create analyses using AOIs, valley bottoms and other polygon layers. Currently we only support doing this for sample frame polygons.

AOIs might be single polygons (catchment polygons delineated usung stream stats) or multi polygons (fish extent polygons scattered across a HUC10).

Users want the full analysis experience for these polygons. They want to hoover GIS or manually enter metric values for these "big polygons" just like they currently do with small DGO polygons. If they import VBET level path polygons they might want to enter metric values for each level path, or for the entire watershed valley bottom.

Conceptual Solution

A bit like when we genericized data capture event features using the "3 feature class experiment", we are going to genericise storing of input layer polygons in a single feature class.

Sample Frames, AOIs, Valley bottoms and any other layers for which we want to perform analyses, will have their features stored in a single "MaskPolygons" feature class. The rows in this feature class table will join to the MetricValues table (much like sample frames currently do) allowing for storing metrics for any of the mask types.

"Masks" and "MaskFeatures" will be terms that only appear in the database. Sample Frames, AOIs etc will retain their current names in the user interface and their position in the QRiS project tree will not change. In other words, what's changing is just the data storage. It's presentation to the user will remain unchanged.

drawing

[KellyMWhitehead]

Tasks:

1. Migration (careful to follow order of operations here):

• There is an existing masks table that contains the AOI objects. Copy these to a temporary table and delete.
• There is an existing mask_features fc that was previously used, but not currently used. These features should have been moved to the sample_frame_features fc, so this should be safe to remove.
• There is an existing lkp_mask_types table. Lets repurpose this. remove "Regular Mask" and "Directional Mask". Add label field and add new values.
• Migrate the Masks from masks (aois), valley_bottoms', and sample_frames`, pay attention to mapping existing attributes to appropriate fields and/or metadata.
• Create mask_polygons multipolygon fc with appropriate fields
• Write a python function (using ogr) to migrate features, pay attention to mapping existing attributes to appropriate fields/metadata.
• Make sure to include the gpkg_contents and gpkg_geometry_columns entries.
• Delete aoi_features, mask_features, sample_frame_features, valley_bottom_features, masks, valley_bottoms, sample_frames tables (and make sure the entries are removed from gpkg_contents)
• Safe handling for exceptions.

** Check if there are other tables with column references or foreign keys that will need to be rebuilt.

2. Model Updates

• update the model classes to use the new gpkg tables
• update any lingering UI references

Stop short of Metric, analysis, climate engine enhancements. These will come next in a separate ticket(s).

[philipbaileynar]

Research

• Does existing DB migration drop triggers so we can alter feature classes?
• If not, then explore changing the db migration code to use ogr.ExecuteSQL()
• If this is a pain, then we should explore "spatially enabling" the pure sqlite3 connection.

Revised DB Migration Workflow

1. Alter SampleFrames table to support AOIs (and any other polygon layers)
2. Alter SampleFrameFeatures table to support AOI features
3. Copy Masks (AOIs) to SampleFrames
4. Copy MaskFeatures (AOIs) to SampleFrameFeatures
5. Drop Masks and MaskFeatures

INSERT INTO SampleFrames (Title, Attribute1, MetaData) SELECT (Title, Attribute1, encapsulate primary key in JSON) FROM Masks;

For the MaskFeatures, we need to join using the old MaskID, but then insert the new sample frame ID.

INSERT INTO SampleFrameFeatures (SampleFrameID, geom, attribute1) SELECT
(
    SELECT SF.SampleFrameID, geom, attribute1) FROM MaskFeatures MF INNER JOIN
    SampleFrames SF ON MF.MaskID = SF.json_extract_value("MaskID")
)