New rscript variable in Calc 2.1-a3 on plot level

Dear Hans,

I want to add more elements to the answer above. it is actually possible, but with a trick.

If your inventory doesnt contain a lot of data, you can use a separate workspace (linked with same collect data) only for plot and cluster results (Don't set up a Sampling Design strategy in Calc).

Here the steps to follow:

1. Create a workspace
2. Import collect data
3. Add the qty variables you want to calculate in Calculation section (e.g. volume, basal area etc..) and select the type of aggregation you wish to apply (e.g. sum, avg etc..)
4. Edit the formulas for the variables at step 3 in RStudio (BUT this time the unit should be U/ha eg. m3/ha. i.e. if you are editing volume, you first calculate volume in m3 for each tree and then divide the value for the area of the plot they where measured in).
5. Execute the processing chain (click source in RStudio, with calc.R as file to execute)
6. Click play in Calculation section
7. Access Saiku

You should be able to slice your results for the entire hierarchy of the survey in Saiku.

Please let me know if you need more info,

Thank you,

Mino

Dear Hans,

That depends on how the survey is structured.

Usually, we create code lists for such variables, (cluster ids, plot ids, ... ) so that once the survey is imported into Calc (the Collect code lists get converted into Calc categories), they are available in Saiku together with the results without any additional operation.

If you haven't created code lists, then you can create a category in Calc.

Please let me know if that helps or you need more info.

Thank you, Mino

Dear Mino and Hans, I just tested an alternative method that is more simple for me: I created an alternative aoi file with three levels (Country, Cluster, Subplot). I am using cluster sampling and like to get results per cluster and per subplot. I set the area (level_3_area) to 1 ha for all subplots. In Saiku I can now aggregate to all three levels (which are under the dimension "Administrative Unit"). This works fine for me.

In general: The way how results are aggregated in Calc is sometimes difficult for me. Usually we are estimating means and total per ha (and plot) and also like to see these values before expanding this by any area later (might be a forest type or an administrative area). However, Calc is estimating totals for the entire area first. Lutz

Dear Lutz,

Thanks for posting your solution; the results per ha are correct, probably not totals (like volume in m3) for the Country if the sum of the area (in the aoi file you uploaded) doesn't match with the area of the country.

Calc does aggregates first at the base unit level, producing per ha results; they are just not presented in Saiku; we encountered performance issues with big surveys.

It then produces totals, by multiplying the, so called, expansion factor (which is calculated as Area of Admin unit / Sum of the base unit weights) with the plot results.

The means are then calculated as Total / Area on the fly in Saiku for the combination of categories you select.

Plot level results are accessible, at the moment, only through direct access to Postgres (e.g. with PgAdmin). The aggregated table names are produced as _{entity}_{level}_results , e.g. _tree_plot_results and are created in the database schema with the name of the workspace.

Aggregated results for cluster sampling are not produced at the moment, Calc goes from plots to aoi; there are ways to play with base unit weight in combination with 'apply weight method' to calculate results using cluster sampling.

I will implment the cluster sampling soon, and will think a way to show plot and cluster level results also (Probably in the data section of Calc).

If you have any suggestions, would like to know more, questions, please let me know!

Thank you,

Mino

Hi Hans,

For some technical constraints , at the moment only Categorical variables are allowed for the base unit. That's why you see that error, you are adding a quantitative variable to the base unit ( your plot ) entity.

Could i know more information about the variable you want to calculate?

Thank you,

Mino

Dear Hans,

Those are common tree variables. they are just aggregated at plot level; therefore if you want to calculate them, you need to add them to the tree entity.

If you set up a Sampling Design strategy in Calc, Results in Saiku are aggregated only at the estimation unit level (e.g. area of interest, country, region,..). We tried to add plot and cluster level values in Saiku, but for big inventories, performances were terrible and left this feature out.

If you want to access plot and or cluster level results, you can still access them, but through PGAdmin (i.e. access directly the database).

Thank you,

Mino

Thanks Mino, Hans and Lutz, this discussion answers main questions I had with aggregation (with newly gained knowledge on R not exceeding simple expressions to edit variables). I hope really that cluster and plot level can be included in Saiku so that i can see/check what really happens while aggregating without going to Postgres. This to encourage Mino's efforts to show cluster and plot levels soon! Further it would be advisable to extend a bit on this in the Calc manual. Thanks Joseph.

Hi,

you are right that we need to get plot and cluster level results out from Calc (or Saiku). In order to get plot results (per hectare), I have solved this by writing a R script into a calculation module at the very end of my processing chain. The plot results are written into file C:\Temp\Plot_Results.csv.

This script works also in cases where plots/subplots are split into land cover/use sections, and base unit is plot section (or stand). I show this only for entities 'tree' and 'fallen_deadwood', although in the full script there are 'sapling','seedling' and some other entities too.

###########################
# select plot_id_ and plot variables that you want to report
plot2 <- dbGetQuery(conn=connection, statement="SELECT 
 plot_id_,cluster_no,plot_no,stratum,accessibility FROM plot_view")

###########################
## TREE DATA ##
tree2 <- sqldf("SELECT * FROM tree") 
tree2$Number_trees_ha <- tree2$tree_count / tree2$plot_area
tree2$Basal_area_ha <- tree2$tree_basal_area / tree2$plot_area
tree2$AGB_ha <- tree2$tree_ag_biomass / tree2$plot_area
tree2$BGB_ha <- tree2$tree_bg_biomass / tree2$plot_area
tree2$Biomass_ha <- tree2$tree_biomass / tree2$plot_area
tree2$AGC_ha <- tree2$tree_ag_carbon / tree2$plot_area
tree2$BGC_ha <- tree2$tree_bg_carbon / tree2$plot_area
tree2$Carbon_ha <- tree2$tree_carbon / tree2$plot_area

queryTree <- sqldf("SELECT plot_id_, SUM(Number_trees_ha) AS Number_trees_ha,
 SUM(Basal_area_ha) AS Basal_area_ha,
 SUM(AGB_ha) AS AGB_ha, SUM(BGB_ha) AS BGB_ha, SUM(Biomass_ha) AS Biomass_ha, SUM(AGC_ha) AS AGC_ha,
 SUM(BGC_ha) AS BGC_ha, SUM(Carbon_ha) AS Carbon_ha 
 FROM tree2
 GROUP BY plot_id_ ")

plot2 <- sqldf("SELECT * FROM plot2 LEFT JOIN queryTree USING (plot_id_)")

###########################
## DEADWOOD DATA ##
dw2 <- sqldf("SELECT * FROM fallen_deadwood") 
dw2$Volume_ha  <- dw2$dw_volume / dw2$plot_area
dw2$Biomass_ha <- dw2$dw_biomass/ dw2$plot_area
dw2$Carbon_ha  <- dw2$dw_carbon / dw2$plot_area

queryDW <- sqldf("SELECT plot_id_, SUM(Volume_ha) AS DW_Volume_ha,
 SUM(Biomass_ha) AS DW_Biomass_ha, SUM(Carbon_ha) AS DW_Carbon_ha 
 FROM dw2
 GROUP BY plot_id_ ")

plot2 <- sqldf("SELECT * FROM plot2 LEFT JOIN queryDW USING(plot_id_)")

# remove plot_id_
plot2$plot_id_ <- NULL
# change NAs to zero
plot2[is.na(plot2)] <- 0

# sort data
plot2 <- sqldf("SELECT * FROM plot2 ORDER BY cluster_no,plot_no")
# write results to CSV file
write.csv(plot2, "C:/Temp/Plot_Results.csv" )

# Remove unneeded dataframes
rm(tree2)
rm(queryTree)
rm(dw2)
rm(queryDW)
rm(plot2)
#################

Regards, Lauri