-
Notifications
You must be signed in to change notification settings - Fork 0
/
fiadb_sqlite_volume_per_acre.R
116 lines (104 loc) · 4.59 KB
/
fiadb_sqlite_volume_per_acre.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
# make some edits
# load packages
library(RSQLite)
# set working directory
setwd("D:/GroupWork/FIA Data")
# connect to FIA database and query for trees, acres, and volume -----
# by forest type and county
fia <- dbConnect(SQLite(), 'D:/GroupWork/FIA DataFIADB.db')
trees_fortyp <- dbGetQuery(fia, "SELECT peg.eval_grp,
Sum([EXPVOL]*[TPA_UNADJ]*
CASE WHEN t.dia IS NULL
THEN ps.adj_expvol_subp
WHEN t.dia < 5
THEN ps.adj_expvol_micr
WHEN ps.macro_breakpoint_dia IS NULL
THEN ps.adj_expvol_subp
WHEN t.dia < ps.macro_breakpoint_dia
THEN ps.adj_expvol_subp
ELSE ps.adj_expvol_macr
END) trees,
peg.statecd,
ps.countycd,
c.fortypcd,
c.owngrpcd
FROM pop_eval_grp peg,
plotsnap ps,
cond c,
tree t
WHERE ps.cn = c.plt_cn
AND c.plt_cn = t.plt_cn
AND c.condid = t.condid
AND peg.cn = ps.eval_grp_cn
AND c.cond_status_cd = 1
AND t.treeclcd = 2
AND t.statuscd = 1
AND t.dia >= 1
AND (c.siteclcd=1 Or c.siteclcd=2 or c.siteclcd=3 or
c.siteclcd=4 or c.siteclcd=5 or c.siteclcd=6)
AND c.reservcd=0
GROUP BY peg.eval_grp,
peg.statecd,
ps.countycd,
c.fortypcd,
c.owngrpcd")
acres_fortyp <- dbGetQuery(fia, "SELECT ps.EVAL_GRP,
Sum([EXPCURR]*[CONDPROP_UNADJ]*[ADJ_EXPCURR])
AS [total_timberland_acres],
peg.STATECD,
ps.COUNTYCD,
c.OWNGRPCD,
c.FORTYPCD
FROM pop_eval_grp peg,
plotsnap ps,
cond c
WHERE ps.cn = c.plt_cn
AND peg.cn = ps.eval_grp_cn
AND c.cond_status_cd = 1
AND (c.siteclcd=1 Or c.siteclcd=2 or c.siteclcd=3 or
c.siteclcd=4 or c.siteclcd=5 or c.siteclcd=6)
AND c.reservcd=0
GROUP BY ps.eval_grp,
peg.STATECD,
ps.COUNTYCD,
c.OWNGRPCD,
c.FORTYPCD")
volume_fortyp <- dbGetQuery(fia, "SELECT peg.eval_grp, Sum(ps.expvol*t.volcfnet*t.tpa_unadj*
CASE WHEN t.dia IS NULL
THEN ps.adj_expvol_subp
WHEN t.dia < 5
THEN ps.adj_expvol_micr
WHEN ps.macro_breakpoint_dia IS NULL
THEN ps.adj_expvol_subp
WHEN t.dia < ps.macro_breakpoint_dia
THEN ps.adj_expvol_subp
ELSE ps.adj_expvol_macr
END) AS volume,
peg.statecd,
ps.countycd,
c.fortypcd,
c.owngrpcd
FROM pop_eval_grp peg,
plotsnap ps,
cond c,
tree t
WHERE ps.cn = c.plt_cn
AND c.plt_cn = t.plt_cn
AND c.condid = t.condid
AND peg.cn = ps.eval_grp_cn
AND t.treeclcd = 2
AND c.cond_status_cd = 1
AND t.statuscd = 1
AND c.reservcd = 0
AND (c.siteclcd=1 Or c.siteclcd=2 or c.siteclcd=3 or
c.siteclcd=4 or c.siteclcd=5 or c.siteclcd=6)
GROUP BY peg.eval_grp,
peg.statecd,
ps.countycd,
c.fortypcd,
c.owngrpcd")
dbDisconnect(fia)
# join acres, trees, and volume-------------
d <- volume_fortyp %>%
inner_join(acres_fortyp,
by = c('EVAL_GRP', 'STATECD', 'COUNTYCD', 'OWNGRPCD', 'FORTYPCD'))