FVS: Update load for FVS_InForestTree table with option to load trees for ALL FVS years

[lbross]

This will be a long-running process so it should be split off into its own pick list item so it doesn't devastate performance for FVS Out. Revisit the schema for FVS_ResidTree to make sure it still makes sense. Trees not associated with an FVS pre/post cycle will have null for rxcycle. Is there any value in leaving the existing load in place for analysts that don't need the full set of ResidTree data?

[jsfried]

Will the (full, all years, resid tree) table still write to the same db that it writes to now? Can it be enabled with a checkbox in FVSOUT processing so that it can be generated if the user wants it but have its generation turned off by default (saving the generation of another interface to process the FVSOUT as a separate process)? Or is it better to completely separate. If so, I would say we still only want to generate a partial resid tree until we have that separate standalone process set up for all resid_tree time points. At that point, I would drop the partial resid_tree table generation in the main FVSOUT load to save time.

[lbross]

Where will this large table live? : We can keep it in the current FVSOUT_TREE_LIST.db or put it in a new .db. Whatever makes the most sense to the analyst. The caveat to this is if having a large table in the same database as the CutList will impact Processor performance. We won't know for sure until we try.

Recommended updates to the UI? : Because this shares a screen with a few other FVS Out tasks, there isn't space to set up a special checkbox. None of the other items have a checkbox. My recommendation is to add it as another (optional) and completely separate step.

What to do with existing partial load?: If you see value to some users for this partial load, I would leave it as-is in the current main FVSOut process. It's no work to do so. If there is no value, then yes, we should pull it when the full load is available

Table schema: @jsfried should review the current table schema to make sure it is still valid for the ResidTree table. There are several fields that are pulled from FIADB because they don't exist in FVS. Some of these are required by FCS, some are for our reference and/or were included because we wanted to keep the schema the same as FVS_CutTree. There may not be a point to retaining/populating these fields if they are going to be the same for all 14 years. It makes the dataset larger.

[lbross]

Rename integer year field to simYear. Add text rxYear back into table to link with FVS_CutTree table.

[jsfried]

Create/Replace FVS_InForest does nothing when selected for execution in the Douglas 510 project. I had package 3 selected and even tried reloading the package entirely but it made no difference when I went to create/replace FVS_InForest. No table is created. No error is generated.

Related but different issues:

1. All the pick list options with BaseYr in their name don’t make sense for any table except for POTFIRE (the only one that baseyr can be implemented). I don’t think it makes any sense to have base yr options showing in the picklist for any table. Better to have only NoBY options (and then you don’t need to label them as NoBY). We can provide instructions for how to mod the Seqnums for a run that does use baseyear for the POTFIRE table.
2. What does the list of packages in the packages field signify? Are these packages for which seqnum has been assigned or is it always a list of all packages because we now require that all packages use the same seqnum assignments? If packages were added to the project after seqnums were assigned, would those packages be missing from this list? Perhaps the field should be enlarged and/or be given a scroll bar
3. Idea: put a separator in the pick list (---Optional---) the selecting of which has no effect. Then add the options below that with no step numbers (so short enough to show in the picklist): Write FVSOUT_BioSum.db Write FVS_InForest to FVSOUT_TreeList.db

[lbross]

Create/Replace FVS_InForest does nothing because there is no FVS_TreeList table in the Douglas project. At least there is no FVS_TreeList in my version of the Douglas project. I added a pop-up message that notifies of this condition. Unfortunately it's behind the progress indicator and I'm not sure how to re-arrange that, but it's better than nothing.

1. I'll address the sequence number comments on the sequence number issue (issue #282)
2. See above
3. I reformatted the FVS Out pick list to accommodate this request

[jsfried]

The Fvs_inforest_tree table works. Not sure if it is an issue, but the tmpCutTree is not automatically deleted when BioSum closes. It may have been hanging around from a previous load though.

[lbross]

I found that tmpCutTree is part of the post-processing audit. It was not cleaning up after itself, but it is now. The table was most likely hanging around from a previous run of the audit. Any chance you could run the audit to double-check?

[jsfried]

The message telling me that there are no trees to write to the tree table pops up under but you cannot get to it-- you only know that it exists because when you hover mouse over BioSum icon in task bar you can see that there is a message window (in minature) but you cannot read it or execute its OK button unless you first cancel the operation (which then allows the error message window to appear on your screen).

[lbross]

Added call FVSRecordsFinished() if no fvs_tree table to clean up progress indicator

[jsfried]

I loaded FVS output into a 4FRI project and ran the post-load audit and there was no tmp table, so this seems to be resolved. Also successfully ran the make InForest_Tree_List table generation for this 4/10/23 vintage 4FRI project and, after setting indexes in FVSOut.db as follows (in SQLite Studio):

CREATE INDEX IDX_StandId ON FVS_SUMMARY (StandId);

CREATE INDEX IDX_RunTitle ON FVS_CASES (RunTitle);

it generated the InForest tree table in about 2 hours (vs no success after 15 hours without setting those indexes).

This issue is ready to be closed once the table design for FVSOut.DB has those indexes set by the BioSum code (hopefully that is possible). @lbross

[lbross]

The best place to do this (right now) is at the beginning of the Create .mdbs step. The index on the RunTitle might help that process run faster too. But I will also add it to the FVS out process for legacy projects that may not run the Create .mdbs step again. You will just need to remember to load at least one package in FVS Out before trying to create the InForest table. Normally this would be the ordinary course of events ...

[jsfried]

that would be the normal workflow-- it seems like these changes should cover it then. Outstanding!

[lbross]

Just learned that index names have to be unique within the entire SQLite db. You can't use the same index name on > 1 table. Thus, the index names should be: IDX_Summary_StandId (Instead of IDX_StandId) IDX_Cases_RunTitle (Instead of IDX_RunTitle) This way you can index on these same field names across multiple tables. The indexes you added yesterday to test should be fine, but we will use these new names in the system-applied indexes.

[lbross]

From Dan: Here are my validation notes regarding Issue #287 (it’s fixed)! That issue can now be checked off the list on the validation spreadsheet on Box.