LTA YIELD
From Agri4castWiki
The calculation of long term averages and other statistics such as minimum, maximum, standard deviation and count, is done for each crop indicator and for each individual geographical feature on a 10 daily base. For the following basic parameters statistics are calculated for data of:
- Potential above ground biomass
- Water limited above ground biomass
- Potential storage organs biomass
- Water limited storage organs biomass
- Potential Leaf Area Index
- Water limited Leaf Area Index
- Crop development stage (DVS)
- Relative soil moisture
- Total water consumption
- Total water requirement
- volumetric soil moisture content in rooted zone (FSM)
- volumetric soil moisture content in not rooted zone (FSMUR)
- Leaves died by cold
- Run off
- Soil evaporation
- Loss to subsoil
| pseudo code |
|---|
|
SIMYIELD_EMU_LTA and SIMYIELD_HIS_EMU_LTA:
select
crop_no,
mmdd, -- day converted to mmdd format
grid_no,
smu_no,
average(indicator_1),
average(indicator_2),
...
from SIMYIELD_EMU -- or SIMYIELD_HIS_EMU
where year between start year and end year -- day converted to yyyy format
group by crop_no, mmdd, grid_no, smu_no -- day converted to mmdd format
SIMYIELD_GRID_LTA and SIMYIELD_HIS_GRID_LTA:
select
crop_no,
mmdd, -- day converted to mmdd format
grid_no,
average(indicator_1),
average(indicator_2),
...
min(indicator_1),
min(indicator_2),
...
max(indicator_1),
max(indicator_2),
...
stddev(indicator_1),
stddev(indicator_2),
...
count(indicator_1),
count(indicator_2),
...
from SIMYIELD_GRID -- or SIMYIELD_HIS_GRID
where year between start year and end year -- day converted to yyyy format
group by crop_no, mmdd, grid_no -- day converted to mmdd format
select
crop_no,
mmdd, -- day converted to mmdd format
nuts_code,
average(indicator_1),
average(indicator_2),
...
min(indicator_1),
min(indicator_2),
...
max(indicator_1),
max(indicator_2),
...
stddev(indicator_1),
stddev(indicator_2),
...
count(indicator_1),
count(indicator_2),
...
from SIMYIELD_REGION -- or SIMYIELD_HIS_REGION
where year between start year and end year -- day converted to yyyy format
group by crop_no, mmdd, nuts_code -- day converted to mmdd format
|
Interface
To start the calculation the function 'calculate_lta' in package LTA_YIELD is called with 6 arguments.
- Theme (lta-yield or lta-crop)
- Resolution (emu, grid, nuts). 'emu' can be replaced by 'crop' (crop as a resolution should be regarded as obsolete)
- Crop_no (one of the crop_no's of a crop in table CROP_LANDCOVER)
- First sowing year that should be included in calculating lta
- Last sowing year that should be included in calculating lta
- A query to get a subset of regions to be included in the processing of LTA. Both nuts_codes and grid_no's are regarded as regions. For emu, grid: select epn.grid_no from emu_plus_nuts epn where epn.nuts_code like 'PL%'. For nuts: select ........
Example
In the example below, the function is called for each combination of crop and resolution separately. In this way the progress can be easily monitored and controlled.
set schema=... set tns=... set pw=... sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 1 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 2 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 3 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 6 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 7 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 8 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 10 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 11 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 50 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 51 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 76 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield emu 77 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 1 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 2 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 3 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 6 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 7 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 8 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 10 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 11 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 50 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 51 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 76 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield grid 77 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 1 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 2 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 3 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 6 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 7 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 8 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 10 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 11 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 50 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 51 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 76 1975 2010 null sqlplus %schema%/%pw%@%tns% @lta_yield.sql lta-yield nuts 77 1975 2010 null
where lta_yield.sql contains:
set serverout on
exec lta_yield.calculate_lta('&1','&2',&3,&4,&5,'&6');
/
exit
