search

USFS-PNW / Fia-Biosum-Manager

User interface and main code repository for Biosum
http://biosum.info/
Other
3 stars 3 forks source link

MASTER.COND.TPACURR and related attributes omitting saplings from calculation #253

Closed jsfried closed 2 years ago

jsfried commented 3 years ago

Tpacurr issue in master.cond I built and ran the following query exploring consistency and found it lacking on TPAcurr variables in master.cond. SELECT tree.biosum_cond_id, Sum(tree.tpacurr) AS SumOftpacurr, Sum(tree.tpa_unadj) AS SumOftpa_unadj, cond.tpacurr, cond.condprop_unadj, cond.swd_tpacurr, cond.hwd_tpacurr, cond.ba_ft2_ac, Sum((([dia]/24)^2)3.1415926[tpa_unadj]) AS BA_via_trees FROM tree INNER JOIN cond ON tree.biosum_cond_id = cond.biosum_cond_id WHERE (((tree.statuscd)=1)) GROUP BY tree.biosum_cond_id, cond.tpacurr, cond.condprop_unadj, cond.swd_tpacurr, cond.hwd_tpacurr, cond.ba_ft2_ac; Yielding:

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 -- | -- | -- | -- | -- | -- | -- | -- | -- biosum_cond_id | SumOftpacurr | SumOftpa_unadj | tpacurr | condprop_unadj | swd_tpacurr | hwd_tpacurr | ba_ft2_ac | BA_via_trees 1200506050501500846430001 | 313.1 | 313.1 | 88.2 | 1.00 | 34.1 | 54.2 | 97.2 | 97.2 1200506050501900908540001 | 242.7 | 121.4 | 242.7 | 0.51 | 50.1 | 192.6 | 245.5 | 122.9 1200506050501900908540002 | 1961.1 | 980.6 | 12.0 | 0.49 |   | 12.0 | 52.9 | 26.4 1200506050506900728530001 | 96.3 | 96.3 | 96.3 | 1.00 |   | 96.3 | 51.0 | 51.0 1200506050509300962240001 | 189.0 | 189.0 | 39.1 | 1.00 | 39.1 |   | 58.0 | 58.0 1200506050510700580650001 | 36.1 | 36.1 | 36.1 | 1.00 |   | 36.1 | 22.5 | 22.5 1200606010609300749360001 | 256.5 | 256.5 | 181.5 | 1.00 | 181.5 |   | 241.8 | 241.8 1200606050600700951860001 | 1373.9 | 1373.9 | 174.5 | 1.00 | 101.3 | 73.2 | 153.1 | 153.1 1200606050601500746410002 | 243.5 | 85.2 | 243.5 | 0.38 | 27.9 | 215.7 | 320.9 | 115.6 1200606050602300510620001 | 294.4 | 294.4 | 144.4 | 1.00 |   | 144.4 | 70.5 | 70.5 1200606050602900677270001 | 135.1 | 135.1 | 60.2 | 1.00 | 60.2 |   | 23.8 | 23.8 1200606050603500725230001 | 48.1 | 48.1 | 48.1 | 1.00 | 48.1 |   | 22.3 | 22.3 1200606050603900720950001 | 31.1 | 31.1 | 31.1 | 1.00 | 13.0 | 18.1 | 36.1 | 36.1 1200606050604300535810001 | 48.1 | 48.1 | 48.1 | 1.00 |   | 48.1 | 22.5 | 22.5

I learned that the TREE.TPACURR adjusts for CONDPROP_UNADJ (by dividing by it) to that TPACURR gives estimates that are as if the condition were a full plot, as it should. That is why when CONDPROP_UNADJ <>1, SUM(TPACURR) > SUM(TPA_UNADJ). COND.TPACURR (and the hardwood and software versions of that variable) also accounts for this adjustment (e.g. see cases where CONDPROP_UNAD < 1). However, when there are saplings present (trees <5” DBH) they are oddly omitted from the calculation of COND.TPACURR (and apparently the softwood and hardwood versions of that). This is most easily seen when condprop_unadj=1 such as in rows 1, 5, 7, 8, 10 and 11 in that columns 2 (calculated by summing tree records) and 4 (the cond.tpacurr value) do not match. Interestingly, this issue does not seem to be occurring with the Basal area variables (columns 8 and 9 always match where condprop_unadj=1 and one can see that for the other cases of condprop_unadj, dividing the tree summed BA by it would make them match). I did not check the volume columns.

IF TPACURR is used in BioSum for any analytic purpose, we need to know this and fix the issue with alacrity once we understand what effect it has had. If BioSum does not use it, we still will want to fix the issue at some point so that master.cond does not hold inconsistent data.

lbross commented 3 years ago

Here is the SQL that sets TPACURR during the plot load process. I can update the SQL as you deem appropriate. I'm not sure why @lpotts settled on this implementation. Is there a lower minimum dbh we should use? This could be part of the release for Tyler's code and loading directly from the FIA Datamart.

UPDATE tree t INNER JOIN (cond c INNER JOIN plot p ON c.biosum_plot_id=p.biosum_plot_id) ON t.biosum_cond_id = c.biosum_cond_id SET t.condprop_specific = IIF(c.micrprop_unadj IS NOT NULL AND t.dia < 5,c.micrprop_unadj, IIF(c.subpprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= 5 AND t.dia < p.MACRO_BREAKPOINT_DIA,c.subpprop_unadj,IIF(c.macrprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= p.MACRO_BREAKPOINT_DIA,c.macrprop_unadj))) WHERE t.biosum_status_cd=9

UPDATE tree t SET tpacurr = IIF(t.tpa_unadj IS NOT NULL AND t.condprop_specific IS NOT NULL,t.tpa_unadj / t.condprop_specific,0)

lpotts commented 3 years ago

The SQL below is the implementation that was used by Olaf during his calculation of TPACURR values based on the diameter of the tree. [Forest Service Shield] Larry Potts Information Technology Specialist Forest Service Resource Monitoring and Assessment PNW Research Station Portland Forest Science Lab Forest Inventory and Analysis p: 503-808-2079 @.**@.> 620 SW Main, Suite 502 Portland, OR 97205 www.fs.fed.ushttp://www.fs.fed.us/ [USDA Logo]http://usda.gov/[Forest Service Twitter]https://twitter.com/forestservice[USDA Facebook]https://www.facebook.com/pages/US-Forest-Service/1431984283714112 Caring for the land and serving people

From: Lesley @.> Sent: Tuesday, July 13, 2021 11:51 AM To: USFS-PNW/Fia-Biosum-Manager @.> Cc: Potts, Larry -FS @.>; Mention @.> Subject: Re: [USFS-PNW/Fia-Biosum-Manager] MASTER.COND.TPACURR and related attributes omitting saplings from calculation (#253)

Here is the SQL that sets TPACURR during the plot load process. I can update the SQL as you deem appropriate. I'm not sure why @lpottshttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Flpotts&data=04%7C01%7C%7C366dff092b1945c4c5c508d9462f1fe0%7Ced5b36e701ee4ebc867ee03cfa0d4697%7C0%7C0%7C637617990479405030%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=EXWKHFWlfSglPFlqGmKPfkOGJ7Lsw1JA8Veyc%2Fo4aug%3D&reserved=0 settled on this implementation. Is there a lower minimum dbh we should use? This could be part of the release for Tyler's code and loading directly from the FIA Datamart.

UPDATE tree t INNER JOIN (cond c INNER JOIN plot p ON c.biosum_plot_id=p.biosum_plot_id) ON t.biosum_cond_id = c.biosum_cond_id SET t.condprop_specific = IIF(c.micrprop_unadj IS NOT NULL AND t.dia < 5,c.micrprop_unadj, IIF(c.subpprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= 5 AND t.dia < p.MACRO_BREAKPOINT_DIA,c.subpprop_unadj,IIF(c.macrprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= p.MACRO_BREAKPOINT_DIA,c.macrprop_unadj))) WHERE t.biosum_status_cd=9

UPDATE tree t SET tpacurr = IIF(t.tpa_unadj IS NOT NULL AND t.condprop_specific IS NOT NULL,t.tpa_unadj / t.condprop_specific,0)

- You are receiving this because you were mentioned. 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%2F253%23issuecomment-879319958&data=04%7C01%7C%7C366dff092b1945c4c5c508d9462f1fe0%7Ced5b36e701ee4ebc867ee03cfa0d4697%7C0%7C0%7C637617990479414991%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=FnjTO45E8j8XpSfJ0hb2G8QHQaVJtPInHrNwz3D8laM%3D&reserved=0, or unsubscribehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAEFA7MUFC5QO6JSKILSD5WDTXSDILANCNFSM5AE5ZO5A&data=04%7C01%7C%7C366dff092b1945c4c5c508d9462f1fe0%7Ced5b36e701ee4ebc867ee03cfa0d4697%7C0%7C0%7C637617990479414991%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=p0EoAPqQuzwM%2BOaD8TynJyNaA2NguaB7%2BSf7ePBIFU0%3D&reserved=0.

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.

jsfried commented 3 years ago

Olaf's logic and the code based on it are sound. I remain puzzled by why the tpa represented by the 3 saplings in this stand are not being captured in tpacurr-- I worked through the IFF statement with all the pertinent values from plot and tree in the master versions of these tables and those sub 5 inch trees should be adding to tpacurr, but are not.

Take the case from the CEC project of biosum_condid= 1200506050501500846430001

cond
cond
biosum_cond_id biosum_plot_id invyr condid condprop landclcd fortypcd CECftypcd MgtModeled ground_land_class_pnw owncd owngrpcd fuzzedowner reservcd siteclcd sibase sicond sisp slope aspect stdage stdszcd habtypcd1 adforcd qmd_tot_cm hwd_qmd_tot_cm swd_qmd_tot_cm acres unitcd vol_loc_grp tpacurr hwd_tpacurr swd_tpacurr ba_ft2_ac hwd_ba_ft2_ac swd_ba_ft2_ac vol_ac_grs_stem_ttl_ft3 hwd_vol_ac_grs_stem_ttl_ft3 swd_vol_ac_grs_stem_ttl_ft3 vol_ac_grs_ft3 hwd_vol_ac_grs_ft3 swd_vol_ac_grs_ft3 volcsgrs hwd_volcsgrs swd_volcsgrs gsstkcd alstkcd condprop_unadj micrprop_unadj subpprop_unadj macrprop_unadj cn biosum_status_cd dwm_fuelbed_typcd MODEL_YN
1200506050501500846430001 120050605050150084643000 2005 1 1.02669404517454 1 911 HWD No 120 11 10 10 0 4 100 93 202 65 355 20 2 CD 510 14.2109 8.900365 19.92401 5703.16175850356 1 S26LCA 88.249396 54.162414 34.086982 97.2035727626822 23.4013545106198 73.8022182520624 6383.70208723439 501.499082806373 5882.20300442802 3466.16350270281 176.237472096318 3289.92603060649 3133.56611
3133.56611 3 3 1 1 1 1 13097202010497 1
Y
plot
plot
biosum_plot_id statecd invyr unitcd countycd plot measyear measmon measday elev fvs_variant half_state subplot_count_plot gis_yard_distance_2016 gis_yard_dist_ft num_cond one_cond_yn lat lon macro_breakpoint_dia biosum_status_cd cn fvsloccode
120050605050150084643000 6 2005 1 15 84643 2005 10 25 300 NC CA
396 882.9359738 1 Y 41.585499 -124.058511 24 1 13097194010497 510
The first three trees are not contributing to the TPACURR for unknown reasons tree
tree
biosum_cond_id invyr statecd unitcd countycd subp tree condid statuscd spcd spgrpcd dia diahtcd ht htcd actualht formcl treeclcd cr cclcd cull roughcull decaycd stocking tpacurr wdldstem volcfnet volcfgrs volcsnet volcsgrs volbfnet volbfgrs voltsgrs drybiot drybiom bhage cullbf cullsf totage mist_cl_cd agentcd damtyp1 damsev1 damtyp2 damsev2 tpa_unadj idb_dmg_agent3_cd idb_severity3_cd fvs_dmg_ag1 fvs_dmg_sv1 fvs_dmg_ag2 fvs_dmg_sv2 fvs_dmg_ag3 fvs_dmg_sv3 inc10yr condprop_specific fvs_tree_id idb_alltree_id cn biosum_status_cd
1200506050501500846430001 2005 6 1 15 3 120 1 1 351 45 1.9 1 27 3 27
2 65 4


1.54830002784729 74.965282






0.248612 7.81162

0







74.965282








1 3120
13097249010497 1
1200506050501500846430001 2005 6 1 15 3 119 1 1 351 45 2.1 1 29 3 29
2 85 4


1.78289997577667 74.965282






0.325652 10.196513

0







74.965282








1 3119
13097247010497 1
1200506050501500846430001 2005 6 1 15 2 126 1 1 351 45 4.3 1 28 1 28
2 85 3


9.79549980163574 74.965282






1.253596 45.892847

0







74.965282








1 2126
13097232010497 1
1200506050501500846430001 2005 6 1 15 4 117 1 1 351 45 5.4 1 42 3 42 1 2 60 3 0 0
1.0842000246048 6.018046
1.792928 1.792928



3.001108 100.173427 41.395113
0







6.018046








1 4117
13097226010497 1
1200506050501500846430001 2005 6 1 15 4 106 1 1 351 45 5.7 1 45 1 45 1 2 95 3 0 0
1.17009997367859 6.018046
2.35444 2.35444



3.573521 118.403468 54.359301
0







6.018046








1 4106
13097269010497 1
1200506050501500846430001 2005 6 1 15 4 109 1 1 351 45 6.2 1 39 1 39 1 2 90 4 0 0
0.658699989318848 6.018046
2.642555 2.642555



3.602476 127.904701 61.011311
0







6.018046








1 4109
13097277010497 1
1200506050501500846430001 2005 6 1 15 3 124 1 1 351 45 6.3 1 37 3 37 1 2 80 3 0 0
1.34739995002747 6.018046
2.618035 2.618035



3.521586 127.698394 60.445181
0







6.018046








1 3124
13097260010497 1
1200506050501500846430001 2005 6 1 15 3 125 1 1 351 45 6.4 1 39 3 39 1 2 80 3 0 0
1.37769997119904 6.018046
2.899183 2.899183



3.840869 137.040386 66.936338
0







6.018046








1 3125
13097263010497 1
1200506050501500846430001 2005 6 1 15 4 112 1 1 211 16 6.6 1 43 1 43 1 2 65 4 0 0
0.238600000739098 6.018046
2.412386 2.412386



3.101028 106.700581 54.191828
0

0




6.018046








1 4112
13097286010497 1
1200506050501500846430001 2005 6 1 15 4 116 1 1 351 45 6.7 1 43 3 43 1 2 80 3 0 0
1.4695999622345 6.018046
3.656547 3.656547



4.644476 162.123492 84.422349
0







6.018046








1 4116
13097223010497 1
1200506050501500846430001 2005 6 1 15 4 105 1 1 351 45 7 1 40 1 40 1 2 90 3 0 0
1.56319999694824 6.018046
3.798284 3.798284



4.687978 169.517072 87.694788
0







6.018046








1 4105
13097266010497 1
1200506050501500846430001 2005 6 1 15 4 118 1 1 351 45 7.2 1 44 3 44 1 2 75 3 0 0
1.62660002708435 6.018046
4.521083 4.521083



5.467398 192.585608 104.382755
0







6.018046








1 4118
13097229010497 1
1200506050501500846430001 2005 6 1 15 1 102 1 1 351 45 7.5 1 44 1 44 1 2 95 3 0 0
1.72290003299713 6.018046
5.001778 5.001778



5.929119 210.428495 115.481061
0







6.018046








1 1102
13097301010497 1
1200506050501500846430001 2005 6 1 15 4 115 1 1 211 16 7.7 1 45 1 45 1 2 90 4 0 0
0.30919998884201 6.018046
3.778076 3.778076



4.409643 151.273312 84.870688
0

0




6.018046








1 4115
13097220010497 1
1200506050501500846430001 2005 6 1 15 1 101 1 1 202 10 7.8 1 42 1 42 1 2 95 3 0 0
1.09449994564056 6.018046
5.190284 5.190284



6.037972 244.035264 145.743178 17 0

0




6.018046








1 1101
13097298010497 1
1200506050501500846430001 2005 6 1 15 4 113 1 1 211 16 8.8 1 44 1 44 1 2 70 4 0 0
0.386900007724762 6.018046
5.07207 5.07207



5.648231 192.910102 113.938984 15 0

0




6.018046








1 4113
13097289010497 1
1200506050501500846430001 2005 6 1 15 4 114 1 1 211 16 10.3 1 45 1 45 1 2 90 4 0 0
0.504000008106232 6.018046
7.347482 7.347482 6.361996 6.361996 27.6589 27.6589 7.903263 269.577936 165.053841
0

0




6.018046








1 4114
13097292010497 1
1200506050501500846430001 2005 6 1 15 1 103 1 2 631 47 10.9 1 52 4 14
4



3
6.018046
5.663847 9.932799



18.571467 383.948174 204.98593
0







6.018046








1 1103
13097304010497 1
1200506050501500846430001 2005 6 1 15 1 100 1 1 263 13 29.4 1 114 1 114 1 2 80 2 0 0
1.20840001106262 0.999188
190.625521 190.625521 189.288518 189.288518 1273.85365 1273.85365 197.04095 6051.577937 4995.913658 80 0

0




0.999188








1 1100
13097295010497 1
1200506050501500846430001 2005 6 1 15 4 107 1 1 211 16 33.2 1 113 1 113 1 2 95 2 0 0
1.1956000328064 0.999188
192.21361 192.21361 190.867684 190.867684 1238.581443 1238.581443 198.738258 6836.126693 4317.886526 145 0

0




0.999188








1 4107
13097272010497 1
1200506050501500846430001 2005 6 1 15 4 108 1 2 211 16 33.7 1 146 4 146
4



3
0.999188
253.541765 253.541765



262.140714 9045.613173 5695.562203
0







0.999188








1 4108
13097275010497 1
1200506050501500846430001 2005 6 1 15 4 110 1 1 211 16 45.7 1 165 1 165 1 2 56 2 0 0
2.04530000686646 0.999188
524.321207 524.321207 520.650956 520.650956 3687.611487 3687.611487 541.875515 23306.618197 11778.351596
0

0




0.999188








1 4110
13097280010497 1
1200506050501500846430001 2005 6 1 15 4 111 1 2 211 16 48.7 1 167 4 29
4



4
0.999188
233.163578 602.285985



622.413681 10660.029314 5237.786612
0







0.999188








1 4111
13097284010497 1
1200506050501500846430001 2005 6 1 15 3 123 1 2 211 16 60 1 187 4 6
4



4
0.999188
98.038912 1019.01193



1052.90966 5070.410598 2202.346113
0







0.999188








1 3123
13097258010497 1
1200506050501500846430001 2005 6 1 15 1 104 1 1 211 16 86 1 201 1 201 1 2 65 2 0 0
5.91629981994629 0.999188
2242.091597 2242.091597 2226.396956 2226.396956 15866.294834 15866.294834 2316.335845 91400.235848 50366.345631
0

0




0.999188








1 1104
13097306010497 1
1200506050501500846430001 2005 6 1 15 3 122 1 2 211 16 100 1 233 4 10
4



3
0.999188
766.112071 3493.967917



3609.536275 33920.603539 17209.941572
0







0.999188








1 3122
13097255010497 1
UPDATE tree t INNER JOIN (cond c INNER JOIN plot p ON c.biosum_plot_id=p.biosum_plot_id) ON t.biosum_cond_id = c.biosum_cond_id SET t.condprop_specific = IIF(c.micrprop_unadj IS NOT NULL AND t.dia < 5,c.micrprop_unadj, {case 1: micrprop is assigned, in this case 1} IIF(c.subpprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= 5 AND t.dia < p.MACRO_BREAKPOINT_DIA,c.subpprop_unadj, {case 2: subpprop is assigned, in this case 1} IIF(c.macrprop_unadj IS NOT NULL AND p.MACRO_BREAKPOINT_DIA IS NOT NULL AND t.dia >= p.MACRO_BREAKPOINT_DIA,c.macrprop_unadj))) {case 3: macrprop is assigned, in this case 1} WHERE t.biosum_status_cd=9 {The only thing I cannot check is what the value of biosum_status_cd was at the time this statement executes—it is 1 now} UPDATE tree t SET tpacurr = IIF(t.tpa_unadj IS NOT NULL AND t.condprop_specific IS NOT NULL,t.tpa_unadj / t.condprop_specific,0)
jsfried commented 3 years ago

To clarify, the calculation of tpacurr in master.tree is fine. It is the summing of tpacurr into MASTER.COND.TPACURR that is not working correctly because trees under 5 inches DBH are being excluded. If MASTER.COND.TPACURR is not being used in BIOSUM, then this becomes a back burner issue.

lbross commented 3 years ago

Sorry about that. It looks like we are dropping them out a few lines further down with this SQL statement. If you want to include them in the calculation, we should change it. It's truly a 15 minute fix: INSERT INTO cond_column_updates_work_table (biosum_cond_id,tpacurr) SELECT DISTINCT(a.biosum_cond_id),a.tottpa as tpa
FROM tree t, (SELECT biosum_cond_id, SUM(tpacurr) as tottpa FROM tree WHERE dia >= 5 AND statuscd=1 GROUP BY biosum_cond_id) a WHERE t.biosum_status_cd=9 AND a.biosum_cond_id=t.biosum_cond_id;

There are similar calculations for hwd and swd tpacurr a little further down

jsfried commented 3 years ago

And in the calculations a little further down, they are not omitting trees under 5 inches, right? If so, then the fix here is to simply delete "dia >= 5 AND"

lbross commented 3 years ago

I'm digging into this some more. Tyler and I are both working in the plot loading code and I think it would be good to get this resolved while we're working in this section. I am drilling down on biosum_cond_id 1200506050501500846430001 in the CEC project. Jeremy used this condition above.

As far as I can tell, it looks like the cond level calculations for the tpacurr values should be a simple sum of all the trees in the cond. And yes saplings (dia < 5) are being excluded from ALL 3 tpacurr aggregations (tpacurr, hwd_tpacurr, swd_tpacurr). It's easy to see this because tpa_curr is a super high value for the saplings. You can also confirm this by summing cond.hwd_tpacurr + cond.swd_tpacurr which comes out to cond.tpacurr.

My struggle with this particular condition is that when I write queries to sum these values, I come up with a different answer than what is in the COND table: cond.tpacurr: 98.264194, cond.hwd_tpacurr: 60.18046, cond.hwd_tpacurr: 38.083734. You will see that these 3 values are all slightly higher than what is recorded for these fields in the COND table.

I plan to step through the plot load code step by step and watch the temp tables, but wanted to check-in first to see if my logic is sound.

jsfried commented 3 years ago

Your query is forgetting to drop the dead trees (those with statuscd of anything other than 1). I checked and these account for the difference you are seeing between your query result and the COND table values for the TPACURRs

lbross commented 3 years ago

Thanks Jeremy. Yes adding that status code gives me the right numbers. I can change the sql for the 3 tpacurr cond aggregations and it would include saplings in the calculation. But I want to be sure this is what you want to do and that it doesn't have downstream repercussions. If I made the changes, the tpacurr for the example condition would jump from 88.249396 to 313.145242 which is not insignificant.

As far as I can tell, the only downstream calculation that uses tpacurr at the cond level is qmd_tot_cm. Here is the formula: INSERT INTO cond_column_updates_work_table (biosum_cond_id,qmd_tot_cm) SELECT c.biosum_cond_id, SQR(c.ba_ft2_ac/(.005454154 * c.tpacurr)) as qmd_tot_cm FROM cond c WHERE c.biosum_status_cd=9 AND c.ba_ft2_ac IS NOT NULL AND c.ba_ft2_ac <> 0 AND c.tpacurr IS NOT NULL AND c.tpacurr <> 0;

There are similar calculations for swd_qmd_tot_cm and hwd_qmd_tot_cm.

I found the original condprop design document. Search for 'Code modification plan for CONDPROP issue' in your email. I don't follow it completely but it looks like Olaf advised against using TPACURR for density stand metrics. Maybe this document will help with your decision?

If we do decide to pull the sapling exclusion, I can load a small sample of trees to master.tree and Jeremy can review to see if this throws off any other calculations. I can't help but think that the saplings were filtered out intentionally at some point, but don't have any way to back this up.

lbross commented 3 years ago

From @jsfried: I generated two versions of the FVSIn database for this project—they are identical in STANDinit and Treeinit for this stand, so I can safely say that the change will have no impact on how FVS runs. I did notice that QMD calcs changed quite a lot (because of the updating of TPACURR to include the saplings). Thanks for the fix.

This fix will be deployed in the next BioSum .msi

jsfried commented 2 years ago

Checked a few plots with multiple conditions and plot sizes in an Oregon project created on 1/4 and calculated correctly.