#####
#
# Restructer the SIMO output in the SQLite database (final "UNIT" table)
# CB
# 2019-07-15
#
#####
# Define the SQL queries:
# The following queries are based on a SQL-script from K. Eyvindson (see folder params)
# - SQL_FBE.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_OPERS2 <- 'CREATE TABLE OPERS2 AS SELECT t.id, t.branch, t.op_date, br.branching_group, case when t.op_name = "clearcut" OR t.op_name = "clearcut_nature_scene" then 1 else 0 end as bool,t.op_name,
(SELECT sum(op_res.income) FROM op_res WHERE t.op_id = op_res.op_id )/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS income,
(SELECT sum(op_res.cash_flow) FROM op_res WHERE t.op_id = op_res.op_id )/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS cash_flow,
(SELECT sum(op_res.income) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1 )/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS income_log,
(SELECT sum(op_res.income) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2 )/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS income_pulp,
(SELECT sum(op_res.income_biomass) FROM op_res WHERE t.op_id = op_res.op_id )/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS income_biomass,
(SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_log,
(SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_pulp,
(SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V,
(SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1 and op_res.sp = 1),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_log_pine,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2 and op_res.sp = 1),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_pulp_pine,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1 and op_res.sp = 2),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_log_spruce,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2 and op_res.sp = 2),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_pulp_spruce,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1 and (op_res.sp = 3 or op_res.sp = 4)),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_log_birch,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2 and (op_res.sp = 3 or op_res.sp = 4)),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_pulp_birch,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and (op_res.assortment =2 or op_res.assortment =1) and op_res.sp > 4),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_others,
case when t.op_name = "thinning" OR t.op_name = "first_thinning" then t.op_date else 0 end as THIN,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =1 and op_res.sp > 4),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_log_others,
ifnull((SELECT sum(op_res.Volume) FROM op_res WHERE t.op_id = op_res.op_id and op_res.assortment =2 and op_res.sp > 4),0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Harvested_V_pulp_others,
ifnull((SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id and op_res.sp = 1)*0.420,0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass_Ton_pine,
ifnull((SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id and op_res.sp = 2)*0.380,0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass_Ton_spruce,
ifnull((SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id and op_res.sp = 3)*0.510,0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass_Ton_birch_pub,
ifnull((SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id and op_res.sp = 4)*0.485,0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass_Ton_birch_pen,
ifnull((SELECT sum(op_res.Biomass) FROM op_res WHERE t.op_id = op_res.op_id and op_res.sp > 4)*0.420,0)/((select count(*) from (select * from op_res where t.op_id = op_res.op_id) x)/(select count(*) from (select distinct * from op_res where t.op_id = op_res.op_id ) x) ) AS Biomass_Ton_others,
(CASE WHEN t.op_name is "clearcut" THEN 55 WHEN t.op_name is "clearcut_nature_scene" THEN 55 WHEN t.op_name is "first_thinning" THEN 40 WHEN t.op_name is "thinning" THEN 50 WHEN t.op_name is "selection_cut" THEN 50 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 50 WHEN t.op_name is "remove_seedtrees" THEN 55 WHEN t.op_name is "seedtree_position" THEN 55 WHEN t.op_name is "seedtree_position_nature_scene" THEN 55 ELSE 9999 END) as P_PINE_LOG,
(CASE WHEN t.op_name is "clearcut" THEN 17 WHEN t.op_name is "clearcut_nature_scene" THEN 17 WHEN t.op_name is "first_thinning" THEN 11 WHEN t.op_name is "thinning" THEN 13 WHEN t.op_name is "selection_cut" THEN 13 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 13 WHEN t.op_name is "remove_seedtrees" THEN 17 WHEN t.op_name is "seedtree_position" THEN 17 WHEN t.op_name is "seedtree_position_nature_scene" THEN 17 ELSE 9999 END) as P_PINE_PULP,
(CASE WHEN t.op_name is "clearcut" THEN 55 WHEN t.op_name is "clearcut_nature_scene" THEN 55 WHEN t.op_name is "first_thinning" THEN 42 WHEN t.op_name is "thinning" THEN 50 WHEN t.op_name is "selection_cut" THEN 50 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 50 WHEN t.op_name is "remove_seedtrees" THEN 55 WHEN t.op_name is "seedtree_position" THEN 55 WHEN t.op_name is "seedtree_position_nature_scene" THEN 55 ELSE 9999 END) as P_SPRUCE_LOG,
(CASE WHEN t.op_name is "clearcut" THEN 25 WHEN t.op_name is "clearcut_nature_scene" THEN 25 WHEN t.op_name is "first_thinning" THEN 19 WHEN t.op_name is "thinning" THEN 21 WHEN t.op_name is "selection_cut" THEN 21 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 21 WHEN t.op_name is "remove_seedtrees" THEN 25 WHEN t.op_name is "seedtree_position" THEN 25 WHEN t.op_name is "seedtree_position_nature_scene" THEN 25 ELSE 9999 END) as P_SPRUCE_PULP,
(CASE WHEN t.op_name is "clearcut" THEN 43 WHEN t.op_name is "clearcut_nature_scene" THEN 43 WHEN t.op_name is "first_thinning" THEN 35 WHEN t.op_name is "thinning" THEN 38 WHEN t.op_name is "selection_cut" THEN 38 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 38 WHEN t.op_name is "remove_seedtrees" THEN 43 WHEN t.op_name is "seedtree_position" THEN 43 WHEN t.op_name is "seedtree_position_nature_scene" THEN 43 ELSE 9999 END) as P_BIRCH_LOG,
(CASE WHEN t.op_name is "clearcut" THEN 15 WHEN t.op_name is "clearcut_nature_scene" THEN 15 WHEN t.op_name is "first_thinning" THEN 10 WHEN t.op_name is "thinning" THEN 11 WHEN t.op_name is "selection_cut" THEN 11 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 11 WHEN t.op_name is "remove_seedtrees" THEN 15 WHEN t.op_name is "seedtree_position" THEN 15 WHEN t.op_name is "seedtree_position_nature_scene" THEN 15 ELSE 9999 END) as P_BIRCH_PULP,
(CASE WHEN t.op_name is "clearcut" THEN 10 WHEN t.op_name is "clearcut_nature_scene" THEN 10 WHEN t.op_name is "first_thinning" THEN 7 WHEN t.op_name is "thinning" THEN 8 WHEN t.op_name is "selection_cut" THEN 8 WHEN t.op_name is "selection_cut_with_little_open_areas" THEN 8 WHEN t.op_name is "remove_seedtrees" THEN 10 WHEN t.op_name is "seedtree_position" THEN 10 WHEN t.op_name is "seedtree_position_nature_scene" THEN 10 ELSE 9999 END) as P_OTHER,
(CASE WHEN t.op_group is "soil_preparation" THEN 1 WHEN t.op_group is "harrowing" THEN 1 WHEN t.op_group is "mounding" THEN 1 ELSE 0 END) as SCAR,
(CASE WHEN t.op_name is "planting" THEN 1 ELSE 0 END) as PLANTING,
(CASE WHEN t.op_name is "seedtree_position" THEN 1 WHEN t.op_group is "seedtree_position_nature_scene" THEN 1 WHEN t.op_group is "natural_regeneration" THEN 1 ELSE 0 END) as SEEDING
FROM op_link t
LEFT OUTER JOIN branch_desc br ON br.branch = t.branch AND t.id = br.id
WHERE cash_flow is not null'
create_table_OPERS3 <- 'CREATE TABLE OPERS3 AS SELECT distinct id, branch, op_date, branching_group, sum(income) as income,
sum(Harvested_V_pulp) as Harvested_V_pulp, sum(Harvested_V_log) as Harvested_V_log,
sum(Harvested_V) as Harvested_V, case when sum(bool) > 0 then 1 else 0 end as clearcut,
sum(income_log) as income_log, sum(income_pulp) as income_pulp,
sum(income_biomass) as income_biomass, sum(Biomass) as Biomass,
sum(Harvested_V_log_pine* P_PINE_LOG + Harvested_V_log_spruce* P_SPRUCE_LOG+ Harvested_V_log_birch* P_BIRCH_LOG) as income_log_change,
sum(Harvested_V_pulp_pine* P_PINE_PULP+ Harvested_V_pulp_spruce* P_SPRUCE_PULP+ Harvested_V_pulp_birch* P_BIRCH_PULP+ Harvested_v_others* P_OTHER)as income_pulp_change,
sum(cash_flow) as cash_flow, thin,
SCAR, PLANTING,SEEDING,
sum(Harvested_V_log_pine*0.88 +Harvested_V_log_spruce*0.898 +Harvested_V_log_birch*0.885+Harvested_V_log_others*0.874) as Harvested_V_log_under_bark,
sum(Harvested_V_pulp_pine*0.863 +Harvested_V_pulp_spruce*0.864 +Harvested_V_pulp_birch*0.862+Harvested_V_pulp_others*0.862) as Harvested_V_pulp_under_bark,
sum(Biomass_Ton_pine +Biomass_Ton_spruce+ Biomass_Ton_birch_pub+ Biomass_Ton_birch_pen+Biomass_Ton_others) as Harvested_biomass_Ton
FROM OPERS2
group by id, branch, op_date'
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,
l.data_date, b.branch, b.branch_desc, b.branching_group, o.income/u.AREA as income, o.cash_flow/u.AREA as cash_flow, o.clearcut, o.Harvested_V_log/u.AREA as Harvested_V_log,
o.Harvested_V_pulp/u.AREA as Harvested_V_pulp, o.Harvested_V/u.AREA as Harvested_V, m.max_v, o.income_log/u.AREA as income_log, o.income_pulp/u.AREA as income_pulp,
o.income_log_change/u.AREA as income_log_change,o.income_pulp_change/u.AREA as income_pulp_change, o.income_biomass, o.Biomass/u.AREA as Biomass, o.thin,
o.Harvested_biomass_Ton/u.AREA as Biomass_ton,
o.Harvested_V_pulp_under_bark/u.AREA as Harvested_V_pulp_under_bark,
o.Harvested_V_log_under_bark/u.AREA as Harvested_V_log_under_bark,
(CASE WHEN u.age >= 100 THEN 1 ELSE 0 END) as OLD_FOREST,
(CASE WHEN u.age <= 40 THEN 1 ELSE 0 END) as YOUNG_FOREST,
(CASE WHEN u.Main_sp is 1 THEN 1 ELSE 0 END) as SP_DOM,
(CASE WHEN u.Main_sp is 2 THEN 1 ELSE 0 END) as Ns_DOM,
(CASE WHEN u.Main_sp is 3 THEN 1 ELSE 0 END) as Bpend_DOM,
(CASE WHEN u.Main_sp is 4 THEN 1 ELSE 0 END) as Bpub_DOM,
(CASE WHEN u.Main_sp is 6 THEN 1 ELSE 0 END) as Alnus_DOM,
o.SCAR,o.PLANTING,o.SEEDING
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
left outer join OPERS3 o on o.branch = b.branch and o.id = b.id and o.op_date= l.data_date
WHERE u.data_id=l.data_id
ORDER BY u.id, l.branch, l.data_date'
# -------------------
# Process: Run queries for all regimes
# -------------------
# 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_OPERS2,
create_table_OPERS3,
create_table_UNIT)
for(i in query_to_run){
dbExecute(db, i)
}
dbDisconnect(db)
# REMOVE THIS!!!
# for (name in db_names){
# print(name)
#
# #name = "simulated_without_MV_Korsnas_Wind_NO_286-300"
# # 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")
#
# 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_OPERS2,
# create_table_OPERS3,
# create_table_UNIT)
#
# for(i in query_to_run){
#
# dbExecute(db, i)
# }
#
# dbDisconnect(db)
#
# #rm(query_to_run, tab_to_delete, db)
# }
#rm(create_table_max_v, create_table_OPERS2, create_table_OPERS3, create_table_UNIT)
##################################################################
##################################################################
# # First extraction of wind simulated Korsnas data for MSc
#
# sim_variant <- "without"
# db_names <- c("MV_Pori_Wind_1", "MV_Pori_Wind_2", "MV_Pori_Wind_3") #
#
#
#
# for (name in db_names){
# # Connect to the database
#
# db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/withoutCC_wind/simulated_", sim_variant,"_" ,name , ".db"))
#
# # If the following tables already exist, for which the query is defined, remove them
# tab_to_delete <- c("OPERS2", "OPERS3", "max_v", "UNIT")
#
# 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_OPERS2, create_table_OPERS3, create_table_UNIT)
#
# for(i in query_to_run){
# dbExecute(db, i)
# }
#
# dbDisconnect(db)
#
# rm(query_to_run, tab_to_delete, db)
# }
#
# rm(create_table_max_v, create_table_OPERS2, create_table_OPERS3, create_table_UNIT)