search

USFS-PNW / Fia-Biosum-Manager

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

DATABASE: Update calculations for vol_ac_grs_stem_ttl_ft3 #258

Closed lbross closed 2 years ago

lbross commented 2 years ago

hwd_vol_ac_grs_stem_ttl_ft3, and swd_vol_ac_grs_stem_ttl_ft3 in the COND table. These fields should use TPA * VOLTSGRS instead of the current hard-coded ratios. Also review calculation for vol_ac_grs_ft3 (and its hw/sw variations) to make sure it is using vol_cf_grs directly.

lbross commented 2 years ago

SQL for tot_volgrsft3

INSERT INTO cond_column_updates_work_table (biosum_cond_id,vol_ac_grs_ft3) SELECT DISTINCT(a.biosum_cond_id),a.tot_volgrsft3 as vol_ac_grs_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(volcfgrs * tpacurr) as tot_volgrsft3 FROM tree WHERE volcfgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd=1 AND dia >=5 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd=9 AND a.biosum_cond_id=t.biosum_cond_id

INSERT INTO cond_column_updates_work_table (biosum_cond_id,hwd_vol_ac_grs_ft3) SELECT DISTINCT(a.biosum_cond_id),a.tot_volgrsft3 as hwd_vol_ac_grs_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(volcfgrs * tpacurr) as tot_volgrsft3 FROM tree WHERE spcd > 299 AND volcfgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd=1 AND dia >=5 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd=9 AND a.biosum_cond_id=t.biosum_cond_id

INSERT INTO cond_column_updates_work_table (biosum_cond_id,swd_vol_ac_grs_ft3) SELECT DISTINCT(a.biosum_cond_id),a.tot_volgrsft3 as swd_vol_ac_grs_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(volcfgrs * tpacurr) as tot_volgrsft3 FROM tree WHERE spcd < 300 AND volcfgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd=1 AND dia >=5 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd=9 AND a.biosum_cond_id=t.biosum_cond_id;"

lbross commented 2 years ago

New SQL for vol_ac_grs_stem_ttl_ft3:

INSERT INTO cond_column_updates_work_table(biosum_cond_id, vol_ac_grs_stem_ttl_ft3) SELECT DISTINCT(a.biosum_cond_id),a.ttl as vol_ac_grs_stem_ttl_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(voltsgrs * tpacurr) as ttl FROM tree WHERE voltsgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd = 1 AND dia >= 1 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd = 9 AND a.biosum_cond_id = t.biosum_cond_id

INSERT INTO cond_column_updates_work_table(biosum_cond_id, hwd_vol_ac_grs_stem_ttl_ft3) SELECT DISTINCT(a.biosum_cond_id),a.ttl as hwd_vol_ac_grs_stem_ttl_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(voltsgrs * tpacurr) as ttl FROM tree WHERE spcd > 299 AND voltsgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd = 1 AND dia >= 1 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd = 9 AND a.biosum_cond_id = t.biosum_cond_id

INSERT INTO cond_column_updates_work_table(biosum_cond_id, swd_vol_ac_grs_stem_ttl_ft3) SELECT DISTINCT(a.biosum_cond_id),a.ttl as swd_vol_ac_grs_stem_ttl_ft3 FROM tree t, (SELECT biosum_cond_id, SUM(voltsgrs * tpacurr) as ttl FROM tree WHERE spcd< 300 AND voltsgrs IS NOT NULL AND tpacurr IS NOT NULL AND statuscd = 1 AND dia >= 1 GROUP BY biosum_cond_id ) a WHERE t.biosum_status_cd = 9 AND a.biosum_cond_id = t.biosum_cond_id

jsfried commented 2 years ago

Checked a random OR plot in a recent project- calcs are perfect