site index info from fvsin.accdb to db when missing

[jsfried]

I have learned that the FVS_STANDINIT_COND table in FIADB is missing thousands of site index values for unknown reasons. We may want to populate site index, when null in the FVSIn.db.FVS_STANDINIT_COND.SiteIndex (and associated site species and SI Base values) with the values calculated internally by BioSum for ORCAWA since we have calculated them anyway and those values should be entirely valid for these three states and are far better than FVS assuming an average site index. Maybe we will get to the bottom of the FIADB FVS table issues and maybe we won't and even if we do, it may or may not be resolved for a while. In the interim, if needing these values, you can write database queries that link the access and sqlite copies of STANDINIT and, where SI is missing from the SQLIte StandInit, and is populated in the Access StandInit, copy over the site index and site species and if available base year values to the SQLite Standinit table. @lbross @sebbusby

[lbross]

This is not hard to do and we could potentially include it in the next .msi if the requirements are defined quickly. If you have the SQL statements you are currently using as a workaround, that would help.

[jsfried]

Best to build these today while still relatively fresh in my head (after beating on it over the weekend-- what better way to spend a holiday?). Kind of a lucky thing that we kept around our legacy code, entangled though it may be. In an ideal world, I would not be repeatedly stumbling into issues in FIADB data-- it is making me testy!

[lbross]

One point of clarification is whether we want to apply this by fvs variant or by state code. I can see it working either way. It's generous of you to do so much QA on the FIADB data :-)

[lbross]

From @jsfried: Lets populate as you describe (Site_Index and Site_Species) into the FVSIn.DB from the FVSIn.Accdb when Site_Index is null in FVSIn.DB. Then overwrite the SITE_INDEX_BASE_AG with null so that we’ll know which cases these are should we need to determine site index sourcing. There will still be stands without a Site_Index in either FVSIn unfortunately and those will go to the FVS default for the variant.

[jsfried]

BioSum validation of site index substitution For 432 conditions in our SO variant portion of the FCOM grm (2001-2010 INVYRs being used to look at fire resistance trend) project I compared site index, site species and base age in an FVSIn.db created before this change with the respective values after this change (that includes substitutions, when SI is null, from the ACCDB version of FVSIn created with BioSum’s legacy site index calculations). I also linked in the SI, SISP and BASEAGE from the project’s COND table and the site index value calculated for the first site tree from the site tree table that links to these conditions (if any). All 432 conditions have a site class code (1-7, in COND). 13 of the conditions had no SI info via old or new methods. For two of these, there was a SI in master.cond, but no site trees in the site tree table. 117 additional conditions were missing a site index via the pre-change workflow (presumably owing to lack of SI in FVS_STANDINIT_COND) and now have a site index because of the workflow change. Under the new workflow (that subs in for missing SI), the Site Species on conditions where a value was filled in from our legacy site index calculation workflow (the ACCDB), it is filled in as a 2 character alpha value rather than a numeric species code. As long as FVS is equally happy to see alpha characters (which the documentation suggest it should be), all should be good. Jeremy had suggested overwriting the BaseAge field with null to signal which site index values got ported over from the legacy workflow. This was an inane idea, as it removes potentially useful information and is not needed because the presence of site species as an alpha code rather than numeric accomplished the same thing. Given my druthers, I would drop the overwriting of the SITE_INDEX_BASE_AG field with nulls. @lbross @sebbusby

[lbross]

Thanks for the validation details. I just pulled the update to SITE_INDEX_BASE_AG in FVSIn.db. This will be in the next .msi which will hopefully be posted shortly.

[lbross]

Closing. Jeremy validated in July.