FVS: Support the inclusion of seedlings in FVSIn and Out

[lbross]

Seedlings were causing an error when processing volumes and biomasses from FCS 0.3 because the values were null. Jason released an new version that plugs the values with zeroes for seedlings. To improve performance we should update all FCS interfaces with BioSum to NOT send seedling records to FCS.

We need to establish an fvs_tree_id for seedlings when they are added to the master.tree table from the seedling table. The seedling table does not have a tree column which is used to establish the fvs_tree_id. We will test pulling the tree_id from the TREE_INIT table for seedlings. This id is defined as

1st digit=1
2nd-4th digits = SPCD of the seedling record (with leading zeros so always 3 digits)
5th and 6th digit = always zeros
7th digit=subplot to which the seedling record applies

So it should be unique within a condition.

[jsfried]

Yes it is probably best to get from the treeinit. We could potentially make our own from the seedlings table, but I can't say for sure that there is never more than one seedling record for a give species per condition and subplot and if there is, that would make rolling our own more work.

[lbross]

When adding seedlings to the plot load process we will add the SEEDLING table to the tables on the first page of the plot loading screen. We will add a checkbox to the options page that allows the analyst to opt out of loading seedlings. They will be included by default. Check to see if tree_regional_biomass is still being used in processing. If not, replace it with the SEEDLING table on the first screen and remove all references.

Diahtcd is a required field for seedlings that grow and are harvested by FVS. Set this to 2 for woodland species (from tree species table) and 1 for everything else.

[lbross]

We had some discussion regarding removing tree_regional_biomass from the Plot Load screen. It appears that the TRM table is only used in one query:

UPDATE tree t INNER JOIN tree_regional_biomass drb ON t.cn = drb.tre_cn 
SET drybiom = IIF(drb.regional_drybiom IS NOT NULL,drb.regional_drybiom,null),
drybiot = IIF(drb.regional_drybiot IS NOT NULL,drb.regional_drybiot,null)

This supports @lpotts recollection on a recent call. This query sets the drybiom and drybiot values, but they are subsequently overwritten by the FCS process. The only risk of removing this statement would be the possibility of drybiom/drybiot being null if FCS did not return a value. But we would probably want them to be null to indicate a problem. If we decided to stop loading the TRM table, it will need to be removed from the Data Sources infrastructure. Please let me know if you want to move forward with swapping out the TRM table for the seedling table on the plot load screen.

[jsfried]

That makes sense to me! better use of the screen real estate, less confusing to users, and avoids potential difficulties in debugging down the road (hate to have one process overwriting something another process already did-- makes my brain hurt to try to retain such details that would be rendered unncessary by this update).

[lbross]

I have run into trouble trying to copy the fvs_tree_id from the fvs_treeinit_cond table. I get the error 'operation must be an updateable query'. This may be due to fvs_treeinit_cond not having a primary key assigned when it is linked to the MS Access database. @sebbusby has provided an alternative solution that I plan to implement. This also means we don't need to link to fvs_treeinit_cond in the plot load process. "1" & Format([SEEDLING]![SPCD],"000") & "00" & [Seedling].[SUBP] AS fvs_tree_id

What would you like to use for the CN value for seedlings on master.tree? If I pull it directly from the seedling table, it does not have the "S" prepended to it. It does have a leading "S" in fvs_treeinit_cond, but that may not matter if I'm not linking to fvs_treeinit_cond.

[sebbusby]

@lbross some pros and cons here. A major consideration is that by not prepending "S", could seedlings and tree have the same CN in master.tree? Some tinkering around in the FIADB is suggesting that seedling CN is unique from tree CN, but I am not certain.

Case for prepending "S" to SEEDLING.CN to populate master.tree.cn:

• Keeps consistency with how FIA2FVS is doing things in fvs_treeinit_cond
• Lets the user know this is an inventory seedling, although they can also know that from inventory diameter. If modeling seedling growth over a long enough period for it to turn into a sapling/tree in FVS, having the "S" would continue to inform the user that this was once an inventory seedling. It would also tell the user to reference it in FIADB.seedling and not FIADB.tree.

Case for directly populating SEEDLING.CN to master.tree.cn

• If the user wants to reference project loaded seedlings in FIADB.SEEDLING, they don't need to trim the "S" to query.

What do you think @jsfried?

[jsfried]

I don't fully understand that code fragment- is that the workaround from Sebastian?

Is there any reason not to assign a primary key to the fvs_treeinit_cond table?

It would be nice to be able to count on the fact that master.tree.cn will point to only one tree, in the tree or seedling table-- I don't know how to be certain that the CN would not potentially point to both a tree and a seedling, since I don't know how they are generated-- seems like a question for Jason. Thanks for your logic Sebastian-- you slightly beat me to this. If there is any potential for a tree cn having an evil twin in the seedling table, I would vote to continue the S prepending as Sebastian has suggested.

[jsfried]

Jason wrote that [11:39 AM] Brown, Jason - FS we have to as we create ref data on dev that gets loaded to prod. so they have to be unique everywhere as the counters will not be the same

so we can count on there being no "reuse" of CN across tables built on different entities (seedlings and trees in this case). So we don't really need the S. You'll have the differently formatted fvs_tree_id marking it as an original seedling so I don't think we need the S in the CN. @lbross @sebbusby

[lbross]

The code fragment came from @sebbusby. I'm not 100% sure that assigning a primary key to the fvs_treeinit_cond table would solve the Access error, but I do know that Sebastian's approach is more efficient. We can avoid linking to fvs_treeinit_cond altogether with the code fragment. One less DAO call and one less file handle open!

The comment about a differently formatted fvs_tree_id is unclear to me. Although the format is different, what kind of query would you write utilizing the fvs_tree_id to pull seedlings out? If you want a way to easily identify them other than dia = 0.1, I vote for pre-pending the 'S'. I also like Sebastian's comment about being able being able to identify these trees as original seedlings after they grow.

[jsfried]

Differently formatted in that a TREE sourced tree would look like this: 1504 for tree 504 on subplot 1 whereas a seedling would look like 12020000001 if it were a Douglas-fir tree seedling on subplot 1, I think, or do the 000 and 00 get replaced with some kind of sequence number? In any case, it is a wider string in the case of seedlings and that is easy to see (or detect with a LEN() function). I don't know what kind of query we would be using, but it is nice to know that we have the option.

[jsfried]

I don't have strong feelings about CN-- if there is a compelling reason to prepend with an S or it makes it easier to program, I'm fine with that.

[lbross]

Thanks all. I'm leaving the 'S' in the CN since it's already done and we don't have any compelling reason not to.

[lbross]

See attached for screenshot of new checkbox to include seedlings in plot load. This will be enabled by default.

[sebbusby]

Seedlings are correctly loading into master.tree and the FVSin files and BioSum is receiving seedlings from the FVSOut load - everything appears to be working properly.