Table of content:

• Data storage
  • DB structure of nfi_esta
    • v2.2.0
    • v2.5.X
  • Inventory data
  • Analytical part

Data storage

DB structure of nfi_esta

Figure 2 shows relational structure of the nFIESTA database. It is a detailed representation of the lower part of the functional schema shown in Figure 1.

v2.2.0

ERD is also available in other formats: yED graphml, yED pdf, yED svg, pgModeler dbm, pgModeler svg, pgModeler png.

v2.5.X

ERD is also available in other formats: yED graphml, yED png, yED svg

~/nfiesta/nfiesta_pg$ git grep -n -i 'alter\|create table [^w]\|drop table [^w]' nfiesta--2.[2345].*
nfiesta--2.2.6--2.2.7.sql:16:ALTER TABLE @extschema@.t_total_estimate_conf ALTER COLUMN total_estimate_conf TYPE varchar(200);
nfiesta--2.3.2--2.4.0.sql:15:alter table @extschema@.f_a_cell drop column cell_id;
nfiesta--2.4.10--2.4.11.sql:15:alter table @extschema@.t_auxiliary_data add column value_inserted       timestamp with time zone default now();
nfiesta--2.4.10--2.4.11.sql:16:alter table @extschema@.t_target_data            add column value_inserted       timestamp with time zone default now();
nfiesta--2.4.10--2.4.11.sql:17:alter table @extschema@.t_aux_total              add column aux_total_inserted   timestamp with time zone default now();
nfiesta--2.4.11--2.5.0.sql:17:CREATE TABLE @extschema@.t_cell_hierarchy
nfiesta--2.4.11--2.5.0.sql:57:CREATE TABLE @extschema@.t_variable_hierarchy
nfiesta--2.4.2--2.4.3.sql:15:alter table @extschema@.t_result add column min_ssize double precision;
nfiesta--2.4.2--2.4.3.sql:16:alter table @extschema@.t_result add column act_ssize bigint;
nfiesta--2.4.2--2.4.3.sql:18:ALTER TYPE @extschema@.estimate_result ADD ATTRIBUTE min_ssize double precision;
nfiesta--2.4.2--2.4.3.sql:19:ALTER TYPE @extschema@.estimate_result ADD ATTRIBUTE act_ssize bigint;
nfiesta--2.4.6--2.4.7.sql:15:alter table @extschema@.c_area_domain alter COLUMN label type character varying(200) ;
nfiesta--2.4.6--2.4.7.sql:16:alter table @extschema@.c_sub_population alter COLUMN label type character varying(250) ;
nfiesta--2.4.9--2.4.10.sql:15:alter table @extschema@.c_area_domain_category rename COLUMN domain TO area_domain;
nfiesta--2.4.9--2.4.10.sql:19:ALTER TABLE @extschema@.t_target_data ADD COLUMN variable integer;
nfiesta--2.4.9--2.4.10.sql:38:ALTER TABLE @extschema@.t_target_data
nfiesta--2.4.9--2.4.10.sql:41:alter table @extschema@.t_target_data alter column reference_year_set set not null; -- no reason to be NULL
nfiesta--2.4.9--2.4.10.sql:42:alter table @extschema@.t_target_data alter column variable set not null;
nfiesta--2.4.9--2.4.10.sql:45:alter table @extschema@.t_target_data drop column target_variable;
nfiesta--2.4.9--2.4.10.sql:46:alter table @extschema@.t_target_data drop column sub_population_category;
nfiesta--2.4.9--2.4.10.sql:47:alter table @extschema@.t_target_data drop column area_domain_category;
nfiesta--2.4.9--2.4.10.sql:52:ALTER TABLE @extschema@.t_auxiliary_data ADD COLUMN variable integer;
nfiesta--2.4.9--2.4.10.sql:63:ALTER TABLE @extschema@.t_auxiliary_data
nfiesta--2.4.9--2.4.10.sql:66:alter table @extschema@.t_auxiliary_data alter column variable set not null;
nfiesta--2.4.9--2.4.10.sql:68:alter table @extschema@.t_auxiliary_data drop column auxiliary_variable_category;
nfiesta--2.4.9--2.4.10.sql:69:ALTER TABLE @extschema@.t_auxiliary_data ADD CONSTRAINT uidx__t_auxiliary_data__plot_variable UNIQUE (plot, variable);<Paste>

The Data storage schema uses different colors to highlight certain sets of database tables according to their meaning. The meaning of the colors is the following:

• Red – code lists (or lookup tables) defining the key categorical variables recognised by the system e.g. list of countries (analytical.c_country) or the list of target variables (analytical.c_target_variable). Data provided to the system refer to categories found in these code lists. This way semantic meaning as well as a proper structure of inventory data being uploaded can be enforced. These code lists are predefined by the nFIESTA user (administrator).
• Yellow – metadata describing spatial aspects of the particular NFI sampling and survey design.
• Orange – metadata describing temporal aspects of the particular NFI sampling and survey design.
• Blue – actual data provided by NFIs at the plot level. The data must be linked (enforcement by foreign keys) to the code lists (red tables) as well as design and survey metadata provided by the respective NFI (country).
• Green – mapping tables which are used to model more complex (N:N) relationships between tables. These are mostly found between tables describing the particular NFI design but a good example is the analytical.cm_plot2cell_mapping, which makes it possible to link one plot to several (spatially overlapping) estimation cells.
• Violet – these tables contain spatial representation (geometries) of estimation cells (study areas for which estimates need to be produced (analytical.f_a_cell) and of parametrisation regions (.f_a_param_area) formed as unions of available estimation cells (in order to preserve the spatial linkage to plots via the linkage between plots and cells established by NFIs).
• Gray – used for tables in the Analytical part of the nFIESTA Data storage. These tables are filled during the production of estimates based on the content of the Inventory data part.

Inventory data

The left part of the data storage schema, labeled as Inventory data, was designed to contain data provided by National Forest Inventories i.e. field plot data and all necessary design and survey metadata. In addition the Inventory data part also stores the linkage of inventory plots to (predefined) estimation cells as well as auxiliary values determined at (exact) inventory plot positions. The reason behind is that many countries are not allowed or prefer not to disclose exact coordinates of their (usually permanent) National Forest Inventory plots.

The meaning and intended use of particular data tables of the Inventory data part (left part of the Data storage schema in Figure 2 can be found in the technical report by lanz_et_al_2018b.

Analytical part

The right part of the schema, called Analytical part, is used by nFIESTA to configure, produce and save estimates of the requested target parameters.

Analytical part can be broken down into three subparts:

1. Results – to the two tables of this subpart all the estimates produced by nFIESTA and their metadata are saved. Through the metadata the estimates keep a link to the their configuration so any time later the user can see how the particular estimates were produced. Results are written by the process described in node No. 6 of the functional schema in Figure 1.
2. G_Beta – this subpart contains only one table in which the precalculated \boldsymbol{\tilde{G}_{\beta_{t+}}} matrices are saved. The beta-matrices can be used to estimate new target variable applying a set of auxiliaries and working model which had been previously used for one or more other target variables. Further information on this matrices can be found under the description of node No. 4 of the schema in Figure 1.
3. Estimates configuration – here all metadata concerning the estimates configuration are stored. The nFIESTA Estimates calculation functionality accesses this subpart in order to steer the estimation process (prepare the right data, auxiliaries and run the appropriate estimation procedure). The process of estimates configuration is briefly described in nodes No. 1 to 3. of the functional schema in Figure 1.