Difference between revisions of "LTA YIELD"

From Agri4castWiki
Jump to: navigation, search
Line 28: Line 28:
 
select
 
select
 
       crop_no,
 
       crop_no,
       ddmm,                              -- day converted to mmdd format
+
       mmdd,                              -- day converted to mmdd format
 
       grid_no,
 
       grid_no,
 
       smu_no,
 
       smu_no,
Line 36: Line 36:
 
from SIMYIELD_EMU                          -- or SIMYIELD_HIS_EMU
 
from SIMYIELD_EMU                          -- or SIMYIELD_HIS_EMU
 
where year between start year and end year -- day converted to yyyy format
 
where year between start year and end year -- day converted to yyyy format
group by crop_no, ddmm, grid_no, smu_no    -- day converted to mmdd format
+
group by crop_no, mmdd, grid_no, smu_no    -- day converted to mmdd format
 
</pre>
 
</pre>
  
Line 62: Line 62:
 
from SIMYIELD_GRID                        -- or SIMYIELD_HIS_GRID
 
from SIMYIELD_GRID                        -- or SIMYIELD_HIS_GRID
 
where year between start year and end year -- day converted to yyyy format
 
where year between start year and end year -- day converted to yyyy format
group by crop_no, ddmm, grid_no            -- day converted to mmdd format
+
group by crop_no, mmdd, grid_no            -- day converted to mmdd format
 
</pre>
 
</pre>
  

Revision as of 21:40, 19 February 2014



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 average values are calculated:

  • 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



Interface

To start the calculation the function 'calculate_lta' is called with 6 arguments.

  1. Theme (lta-yield or lta-crop)
  2. Resolution (emu, grid, nuts). 'emu' can be replaced by 'crop' (crop as a resolution should be regarded as obsolete)
  3. Crop_no (one of the crop_no's of a crop in table CROP_LANDCOVER)
  4. First sowing year that should be included in calculating lta
  5. Last sowing year that should be included in calculating lta
  6. 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;