FVS: New Resid_Tree table with calculated volumes and biomass attributes

[lbross]

Can we populate volume and biomass attributes for every tree in the FVS_Treelist output in the FVSOUT databases? This will be extremely valuable to the analyst in characterizing the carbon outcomes of modeled management. The structure of FVS_Treelist is identical to FVS_CUTLIST so @sorgtyler believes that the same code he’s worked on for getting vol/bio for FVS cutlist trees can be applied to treelist trees by parameterizing these 1000+ lines of code to read and write to a different table when computing for treelist data vs cutlist data. This would produce a 2nd table in the CUTLIST databases in BiosumCalc folders alongside FVS_TREE—perhaps to be called FVS_LIVETREE. Ultimately, we would want all these FVS_LIVETREE records collated into one table in one SQLIte database. If programming resources are too scarce, this last step could be achieved by an analyst with an R script to process all those CUTLIST databases. We won’t work on this until other priorities are first completed.

Lower priority than current compatibility issues.

[lbross]

I researched adding the FVS_Treelist as an input to the Tree Troubleshooter. We can't do the calculations with this table directly from the FVS output mdb because it is lacking many columns that the Tree Troubleshooter requires to do the calculations. But if we had the FVS_LIVETREE table in the CUTLIST databases (with the same schema as FVS_TREE) it could serve as an input table for the Troubleshooter. Another argument for starting to generate the FVS_LIVETREE table.

[lbross]

Reminder to revisit issue #59 if this request is implemented. There is only value for @jsfried in exporting Tree Troubleshooter content if it contains live trees.

[lbross]

Some thoughts/questions as I am looking into this. I recommend writing this output to an SQLite database rather than MS Access to expedite our transition to SQLite. It looks like there is a LONG function called RunAppend_UpdateFVSTreeTable() that encapsulates most of the required logic. A copy of this function could be created that uses SQLite.

It sounds like the preference is to have this data in a single table in a single SQLite database. Is this one database per variant? Or one database per project? Either way, since we'd essentially be starting from scratch I don't think it would add much time.

Is there any analysis value to processing the cut list trees the same way? We would leave the existing cut list tables/databases where they are, so this would be duplicate data. It could be an interim step toward converting the downstream BioSum processes (Processor) to SQLite. Again, it wouldn't be too much incremental work as the algorithm seems to be the same for both tables.

Update: The new RunAppend_UpdateFVSTreeTable() can use the source tables directly from FVSOut.db as a first step towards cutting out the MS Access interim databases. The FVSOut.db tables use SQLite data types (TEXT, REAL). It would probably be best to use MS Access data types to maintain compatibility.

This code has references to FVS-created compact trees (CM). I think Sebastian reported that this is obsolete. There is also reference to FVS-created seedling trees that start with ES. Is this still correct?

I plan to add the new fields that the FCS jar is calculating. No point in not doing this since it's a new table: volcfsnd, drybio_bole, drybio_top, drybio_sapling, drybio_wdld_spp. These fields could also be added to the cut list table in this new sqlite database, if we elect to start creating a second cut list table.

[lbross]

I will summarize these questions in the agenda for this week's DEV call. In the meantime, here is another process I don't know that we want to continue. The following lines of code run to create a temporary work table. Once for each cycle.

INSERT INTO fvs_tree_TCuFt (biosum_cond_id, rxpackage,rx,rxcycle,rxyear,fvs_variant,fvs_tree_id,TCuFt) 
SELECT a.biosum_cond_id, a.rxpackage,a.rx,a.rxcycle,a.rxyear,a.fvs_variant,a.fvs_tree_id,a.TCuFt 
FROM cutlist_fia_trees_work_table a,(SELECT standid,year 
FROM FVS_CUTLIST_PREPOST_SEQNUM_MATRIX_TableLink 
WHERE CYCLE1_PRE_YN='Y')  b 
WHERE TRIM(a.biosum_cond_id)=TRIM(b.standid) AND CINT(a.rxyear)=b.year

Later, this worktable is used to populate volcfnet if it is null. It runs after the FCS process has updated fvs_tree. It seems to me that we only want volcfnet to come from FCS and we want to know if the FCS result is null for some reason.

UPDATE fvs_tree a 
INNER JOIN fvs_tree_TCuFt b ON a.biosum_cond_id=b.biosum_cond_id 
AND a.rxpackage=b.rxpackage AND a.rx=b.rx AND a.rxcycle=b.rxcycle AND a.rxyear=b.rxyear AND a.fvs_tree_id=b.fvs_tree_id 
SET a.volcfnet=b.tcuft WHERE a.volcfnet IS NULL;

Also, Sebastian confirmed that the TreeId for FVS-created trees through FVSOn* still starts with 'ES' so that part of the algorithm should remain intact.

[jsfried]

This is great progress, Lesley!

wrt the May 4th entry regarding tree volume and biomass computation for residual trees, I think 1 SQLite per project, stored in the fvs/db folder, would be great. The CUTLIST table could be stored in the same SQLite database or in its own.

I am unclear on what this means: "The FVSOut.db tables use SQLite data types (TEXT, REAL)." That is how they come out of FVS? Then we'll need to change datatypes when writring to our BioSum .db tables?

Yes, by all means, let's add the new fields and lets keep tree and cutlist tables consistent. @lbross @sebbusby

[jsfried]

Yes, I agree that we don't want to use TCuFt if it can be avoided. I am guessing that was a fail-safe that Larry put in there so that tree volume would not be null (which probably causes bad things to happen in later calculations, or at the very least leads to underestimates of harvested volume). If we have confidence that the Java program will always calculate a non null volume, then we should have confidence, but still a check to make sure it isn't null at some point in the work flow and report when it is so that we can diagnose the issue. @lbross

[lbross]

1. We will design the new SQLite database to contain both the FVS_Tree and FVS_LiveTree tables. There's no point in propagating additional databases if we don't need them. We will have 2 copies on the CUTLIST table (SQLite and MS Access) until we have the resources/time to switch Processor over to use the new table.
2. Yes. FVSOut.db comes out of FVS with native SQLite data types (INT, TEXT, and REAL). It sounds like I should re-use the translator that John Bertini wrote to convert these to (LONG, TEXT(255), and DOUBLE). We are currently doing this translation when we create the MS Access .accdb's from FVSOut.db.
3. Upon close examination yesterday, the current FVS_Tree CUTLIST table has the new fields and we will continue to carry these through to the new tables.
4. I will not include the TCuFt code in the algorithm that generates this new database and tables. The post-processor audit from the FVS Out screen will detect these critical missing values so we need to document that it should be run following an FVS Out run.

[lbross]

Can you review this code for treeclcd? This happens before the trees are sent to FCS.

1. Build a temporary table that calculates totalcull

   SELECT id, IIF(cull IS NOT NULL AND roughcull IS NOT NULL, cull + roughcull, 
   IIF(cull IS NOT NULL,cull, IIF(roughcull IS NOT NULL, roughcull,0))) AS totalcull 
   INTO cull_total_work_table FROM biosum_volumes_input

2. Set treeclcd using work table

   UPDATE biosum_volumes_input a INNER JOIN cull_total_work_table b ON a.id=b.id
   SET a.treeclcd= IIF(a.SpCd IN (62,65,66,106,133,138,304,321,322,475,756,758,990),3,
   IIF(a.StatusCd=2,3, IIF(b.totalcull < 75,2, IIF(a.roughcull > 37.5,3,4))))

3. Set treeclcd for species not covered in step 2

   UPDATE biosum_volumes_input a INNER JOIN cull_total_work_table b ON a.id=b.id
   SET a.treeclcd=IIF(a.DecayCd > 1,4,IIF(a.dbh < 9 AND a.SpCd < 300,4,a.treeclcd)) 
   WHERE a.treeclcd=3 AND a.statuscd=2 
   AND a.SpCd NOT IN (62,65,66,106,133,138,304,321,322,475,756,758,990)

[jsfried]

The temp table seems fine. Step 2 and Step 3 seem to be at variance with what is specified in the FIADB guide with respect to a couple of items. One is the species codes that automatically place the tree in rough cull (code 3). The complete list of species that should do so based on the FIADB documentation is attached. treeclcd3_spp.xlsx treeclcd.pdf

code snippet 2 seems to put all dead trees to treeclcd 3, so the default assumption is that the dead tree is salvable dead. These are then parsed in code snipet 3 to make them rotten if they are beyond decaycd 1 (which would make them non-salvable, so that makes sense). However, the last bit (assigning rotten to small conifers but not to small hardwoods) does not seem to be described in the database guide. I don't understand what the species list is doing in this 3rd code snipet. It seems to prevent the possibility of class 4 for these species-- I don't have a rationale for that and will consult Jason to see if he knows why this particular coding might have been implemented (perhaps it is part of NIMS and just not reflected in the NIMS documentation).

[lbross]

Some notes from today's DEV call

1. The new table will be called FVS_ResidTree instead of FVS_LiveTree
2. We will not make any changes to the above algorithm for setting treeclcd (based on cull and species code) with the current implementation of FCS. There may be changes required when we move to CRM2.
3. The cut_leave field will be removed from the tables in the SQLite .db. This field was always set to a 'C' in FVS_Tree and serves no purpose that we can see.

[lbross]

The database will be called FVSOUT_TREE_LIST.db. The two tables will be called Cut_Tree and Resid_Tree. Cut_Tree is from the cut list and is identical to the FVS_Tree table currently used by Processor

[lbross]

Adding these fields to both FVS_Resid_Tree and FVS_Cut_Tree: TREEVAL, MORTPA, MDEFECT, BAPCTILE, DG, HTG, and STANDING_DEAD_CD. Start populating STANDING_DEAD_CD from master.tree to FCS input file. Actually we need to do this in plot load and troubleshooter too if we are not already. Outstanding question on if we should add statuscd to the FVS_Resid_Tree and FVS_Cut_Tree tables?

[jsfried]

The standing dead cd will only work for FIADB generated trees. For tree records in CUTLIST, which are harvested, technically they could be from originally live or originally dead trees (which would have status values of 1 or 2 in the FIADB). If some way into the simulation, a tree record represents both live and dead trees, that would get really tricky. Perhaps it is best to assume that only the live tree TPA is available for cutting out of a tree record. Then statuscd will be 1 for all tree in CUT and will be 1 for all trees in Resid except for those that have a 0 TPA (because all the TPA has moved over to MORTPA) and those will have a statuscd of 2. There really doesn't seem to be an elegant way to interface between FIADB trees and FVS trees, even though we've been attempting this for deacades. We've only been successful by limiting the scope to live trees so far. That may be a delineation/demarcation that we want to continue, leaving to FVS to track snags and down wood by its own internal math.

From: Lesley @.> Sent: Friday, June 17, 2022 2:02 PM To: USFS-PNW/Fia-Biosum-Manager @.> Cc: Fried, Jeremy - FS @.>; Mention @.> Subject: Re: [USFS-PNW/Fia-Biosum-Manager] FVS: New Resid_Tree table with calculated volumes and biomass attributes (#257)

Adding these fields to both FVS_Resid_Tree and FVS_Cut_Tree: TREEVAL, MORTPA, MDEFECT, BAPCTILE, DG, HTG, and STANDING_DEAD_CD. Start populating STANDING_DEAD_CD from master.tree to FCS input file. Actually we need to do this in plot load and troubleshooter too if we are not already. Outstanding question on if we should add statuscd to the FVS_Resid_Tree and FVS_Cut_Tree tables?

- Reply to this email directly, view it on GitHubhttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FUSFS-PNW%2FFia-Biosum-Manager%2Fissues%2F257%23issuecomment-1159224128&data=05%7C01%7C%7C84a6f620daf04149d05f08da50a49f44%7Ced5b36e701ee4ebc867ee03cfa0d4697%7C0%7C0%7C637910965231529057%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=LUJPR9Psoj7NrYCTB6S2%2FSJOM5GzK0thrvJyd8zXpmw%3D&reserved=0, or unsubscribehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAEFM5IEITUZFAUVM4OBQQTLVPTRUPANCNFSM5D2FCANA&data=05%7C01%7C%7C84a6f620daf04149d05f08da50a49f44%7Ced5b36e701ee4ebc867ee03cfa0d4697%7C0%7C0%7C637910965231529057%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=6%2Bb0pn%2FQDUmqkWX9bKbigtDrHRWknQr6MKX7dmXTBGo%3D&reserved=0. You are receiving this because you were mentioned.Message ID: @.**@.>>

This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.

[lbross]

FVS_ResidTree will be retired when FVS_InForest tree is implemented (issue #287). Per @jsfried: We aren't having trouble with cuttree loading. have received no errors about biosumcalc directory are appearing when loading FVS output. FICS values are now retained. it is not longer throwing tree cut twice errors. troubleshooter no longer requires any FVS tables to exist.