Something went wrong on our end
structure_SIMO_rslDB_SA.R 4.60 KiB
#####
#
# Restructer the SIMO output in the SQLite database (final "UNIT" table)
#
# !!! ONLY FOR SetAside simulations
#
# 2020-02-19
#
#####
# Define the SQL queries:
# The following queries are based on a SQL-script from K. Eyvindson (see folder params)
# - SQL_Maiju.sql
# They create a final table "UNIT" in the SIMO output database.
# Table UNIT contains the development of indicators over the time under the different management regimes.
## load libraries
library(RSQLite)
# Queries:
create_table_max_v <- 'CREATE TABLE max_v AS
SELECT comp_unit.id AS id,
MAX(comp_unit.V) AS max_v
FROM comp_unit
GROUP BY comp_unit.id'
create_table_UNIT <- 'Create Table UNIT AS SELECT u.*,
(select max(stratum.H_dom) From stratum where stratum.data_id = u.data_id) as H_dom,
(select max(stratum.D_gm) From stratum where stratum.data_id = u.data_id) as D_gm,
(select sum(stratum.N) From stratum where stratum.data_id = u.data_id and D_gm >40) as N_where_D_gt_40,
(select sum(stratum.N) From stratum where stratum.data_id = u.data_id and D_gm <=40 and D_gm > 35) as N_where_D_gt_35_lt_40,
(select sum(stratum.N) From stratum where stratum.data_id = u.data_id and D_gm <=35 and D_gm > 30) as N_where_D_gt_30_lt_35,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and D_gm >40) as V_where_D_gt_40,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and D_gm <=40 and D_gm > 35) as V_where_D_gt_35_lt_40,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and D_gm <=35 and D_gm > 30) as V_where_D_gt_30_lt_35,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and SP = 5) as V_populus,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and SP = 6) as V_Alnus_incana,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and SP = 7) as V_Alnus_glutinosa,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and SP = 8) as V_o_coniferous,
(select sum(stratum.V) From stratum where stratum.data_id = u.data_id and SP = 9) as V_o_decidious,
(select sum(stratum.i_vm3) From stratum where stratum.data_id = u.data_id) as i_Vm3,
l.data_date,
b.branch,
b.branch_desc,
b.branching_group,
0 as income,
0 as cash_flow,
0 as clearcut,
0 as Harvested_V_log,
0 as Harvested_V_pulp,
0 as Harvested_V,
0 as Biomass_ton,
0 as Harvested_V_pulp_under_bark,
0 as Harvested_V_log_under_bark,
m.max_v,
0 as income_log,
0 as income_pulp,
0 as income_log_change,
0 as income_pulp_change,
0 as income_biomass,
0 as Biomass,
0 as THIN
FROM comp_unit u, data_link l left outer join branch_desc b on l.branch = b.branch and l.id = b.id
cross join max_v m on l.id = m.id
WHERE u.data_id=l.data_id and max_v <1000
ORDER BY u.id, l.branch, l.data_date'
# -------------------
# Process: Run queries for SA
# -------------------
# Connect to database
db <- dbConnect(dbDriver("SQLite"),
dbname = paste(inputFolder,
paste0(name, ".db"), sep = "/"))
# If the following tables already exist, for which the query is defined, remove them
tab_to_delete <- c("OPERS2", "OPERS3", "max_v", "UNIT")
# Remove the tables if already exist
for(i in tab_to_delete){
if(dbExistsTable(db, i)) {dbRemoveTable(db, i)}
}
# Run the Queries and create the final table "UNIT",
# which contains the development of all stands and indicators under the simulated management regimes
query_to_run <- c(create_table_max_v, create_table_UNIT)
for(i in query_to_run){
dbExecute(db, i)
}
dbDisconnect(db)