FVS: Update FVS Out process to consume data from FVSOn* SQLite output

[lbross]

This is a new database we will create that assigns MS Access friendly datatypes to fields in the original FVSOut.db.

[lbross]

Stop generating PREPOSTxxx.accdb's for CUTLIST, TREELIST, and ATRTLIST as these tables don't generate pre/post tables. This may be a moot point if the redesign includes moving all of the pre/post tables into an SQLite .db.

[jsfried]

Evan Patrick verified that OpCost is using SQLite and throwing no errors for OpCost data storage on 6/14/24

[jsfried]

When defining a processor scenario, we got this error message when clicking on the Rule definitions tab:

This did not happen with a new project which had not run processor before (but was still started as a 5.10.1). However we had a slug of other errors-- first had to update location of Rscript and of OpCost.R, then got error log showing that RSQLite could not be installed:

Loading required package: RSQLite Warning message: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE, : there is no package called 'RSQLite' Warning in install.packages(packages, type = "binary", repos = "http://cran.r-project.org", : 'lib = "C:/Program Files/R/R-4.4.0/library"' is not writable Error in install.packages(packages, type = "binary", repos = "http://cran.r-project.org", : unable to install packages Execution halted

Opening cmd.exe in a special directory, it is possible to start R and run the package.install command found in the OpCost R code-- takes about 20 minutes to install 60-70 packages (lots of dependencies). Then processor works and seems to produce viable output in the SQLite database,

[lbross]

Regarding the processor scenario error: I discovered this late last week and fixed it. It's related to the issues I mentioned after the DEV call when you move a project but don't open it in v5.10.1 before opening it in v5.11.0. With the fix, opening first in v5.10.1 is best practice, but opening first in v5.11.0 will no longer result in this error. I'm not sure why this wasn't caught in DEV testing because we ran these scenarios multiple times. Regarding RSQLite: Yes. You do have install the RSQLite package before running Processor in v5.11.0. This information is in the Release Notes that I distributed with the initial v5.11.0 announcement on May 15. I know, hard to keep track of everything. It shouldn't take any longer than it used to take to install the RODBC package using the RGui. The OpCost R code tries to install RSQLite if it can't find it. Unfortunately you need elevated privileges for this script to run successfully which will never be the case for USFS computers. BioSum isn't usually run with elevated privileges. Might it make sense to take this out of the OpCost R script to avoid future confusion? And maybe just inform that RSQLite is missing? I feel your pain. It took me a couple of hours to figure out this was my problem when validating Sebastian's work. I'm not sure why you lost the location for Opcost.R.

[jsfried]

The processor mods seem to be successful. One exception or anomaly is that a scenario that I started with using a scenario based harvest method and then later switched to the Rx based harvest method shows a populated row for scenario_harvest_method and no rows populated for scenario_rx_harvest_method. Based on the values in the OpCost inputs table, it is using the right harvest system (the one specified in the Rx, not the one specified in, but grayed out as inactive, in the processor scenario harvest system screen). And those grayed out values do show up in the scenario_harvest_method table in the scenario rule definition. Don't know if that is intended or not. not sure where the rx level harvest method selections are stored-- perhaps only in the rx info in fvs_master and linked into a work table when Processor runs? @lbross

[lbross]

I remember this being the case with previous versions. You can double-check me by testing on v5.10.1. The rx-level harvest method selections are stored in fvsmaster.mdb\rx. There are 2 columns for regular and steep slopes. It actually happens in memory, but yes, if you choose an rx-based harvest method, Processor looks it up in the rx table rather than using the scenario_harvest_method field.

[jsfried]

So if the table in the rule defs is never populated, perhaps it can be dropped?

[jsfried]

GIS load worked like a charm except that columns populate backwards:

It would be REALLY nice though if STATE, COUNTY and CITY and Milll Type displayed on the selection screen in Optimizer. One could sort on them and quickly find candidates for your analysis area. @lbross

[lbross]

Regarding the table in scenario_processor_rule_definitions.db: It's not that it's never populated but it's only populated when someone chooses a scenario-based harvest method. We can't drop the table because it's embedded in the logic that loads a Processor scenario. The Harvest Methods screen loads whatever is in the scenario_processor_rule_definitions.db and when a Processor scenario is saved or run, whatever is on this screen is saved to the database. If this is truly an issue, we could blank out the harvest method selections when someone chooses the rx-based method after choosing a scenario-based method. Then when the scenario is run/saved, you would lose the harvest method details and the screen would come up blank the next time. This, however, would be outside the scope of the current v5.11.0 that we are trying to get released. There may be some value in leaving the previous scenario-based information there if someone wants to switch back and forth between scenario and rx because you'd have a record of the scenario-based rx.

[lbross]

Regarding the psites table: The order of the columns in the project gis tables is the same as it has always been. I double-checked against an older MS Access table. I can look into rewriting the 'create table' scripts if this is going to be an ongoing issue. I'm not sure how often analysts look at the project tables. If you'd like to specify the column order for the table, please add it to this issue. It sounds like you are requesting additional updates to the Optimizer psites screen. I think that's outside the scope of this v5.11.0 release and recommend opening a new issue for @druepdx if you'd like to proceed.

[jsfried]

It is not the column order that is a concern - it is that county is populating in the Mill_type column and Mill type in the County column when I loaded from the master travel times zip that was on Box.

[jsfried]

Sorry if message was confusing. It is the scenario_rx_harvest_method table that is NEVER getting populated in Rule Defs whether I choose to use a scenario based or rx based harvest method. It is an empty table every time I look at it.

And as for looking at project tables, we do look at the psites and travel times in the GIS travel times database when trying to sort things out. It would be nice if column names matched their content.

The wish list for additional columns in the psites view in optimizer is definitely not in scope for 5.11.0 but a worthy addition for a future release. Without it, even to do testing, one has to look at some separate database to see what mills are reasonable picks to include- why not have at least the location (in human interpretable form rather than just lat lon) there to view while working in BioSum?

[lbross]

Thanks for the clarification on the psites table load. Yes. That is definitely a concern. I will fix for the next .msi. I will also check to make sure the same thing isn't happening in the version migration logic.

[jsfried]

optimizer implementation of SQLite looks good for config and outputs. Will wait on new msi to confirm functioning of scenario copying.

[jsfried]

GIS data load from Drue's Oregon example master database worked successfully for two projects and put the mill type and county data in the correct fields now, so that portion of the issue is resolved.

[lbross]

Closing with official release of v5.11.0!