Linking specific methods with associated variables in BETYdb

[NewcombMaria]

List of data-sets and variables uploaded to betydb that need to be linked with specific methods. This issue is connected with #61 .

• [x] Method: Harvest yield estimates Maricopa sorghum season1 hand harvest

• Linked with the following traits that I uploaded on 2 July 2017 and on 8 July 2017

  1. aboveground_fresh_biomass_handharvest (2 July upload)
  2. aboveground_dry_biomass_handharvest (2 July upload)
  3. aboveground_biomass_moisture_handharvest (2 July upload)
  4. harvest_plant_count (8 July upload)
  5. sum_of_gaps_greater20cm (8 July upload)

• [x] Method: In-season leaf area and biomass estimates by destructive sampling

• Linked with the following traits that I uploaded on 9 July 2017 around 5:00pm Arizona time

  1. number_plants_destructive_sample
  2. number_tillers_destructive_sample
  3. aboveground_fresh_weight_1meter_destructive_sample
  4. aboveground_fresh_weight_5plant_destructive_sample
  5. fresh_weight_stems
  6. fresh_weight_leaves
  7. dry_weight_stems
  8. dry_weight_leaves
  9. leaf_area_index_AreaMeter
  10. biomass_dry_stems
  11. biomass_dry_leaves
  12. SLA
  13. dry_matter_fraction

[dlebauer]

Part 1:

-- check if any methods exist
select distinct method_id from traits 
  where variable_id in (select id from variables 
    where name in ('aboveground_fresh_biomass_handharvest', 
                   'aboveground_dry_biomass_handharvest',
                   'aboveground_biomass_moisture_handharvest',
                   'harvest_plant_count', 
                   'sum_of_gaps_greater20cm'))
-- all of these are null

-- at methods to traits
update traits 
   set method_id = (select id from methods where name = 'Harvest yield estimates Maricopa sorghum season1 hand harvest') 
   where 
     (variable_id in (select id from variables 
       where name in ('aboveground_fresh_biomass_handharvest', 
                      'aboveground_dry_biomass_handharvest',
                      'aboveground_biomass_moisture_handharvest')) 
         and date_trunc('day', created_at) = (date '2017-07-02'))
     or 
       (variable_id in (select id from variables 
          where name in ('harvest_plant_count', 
                         'sum_of_gaps_greater20cm') ) 
            and date_trunc('day', created_at) = (date '2017-07-08'))

[dlebauer]

@NewcombMaria for the data uploaded on July 9, we need to split out the variable and method.

E.g.

• leaf_area_index can have a method that describes the use of a leaf area meter
• aboveground_fresh_weight can have two methods, one that uses a 1 m section of the row and another that uses a subsample of five plants

Similarly, the number_tillers and number_plants variable (trait) name should be independent from how it was sampled (though it isn't clear why the method of counting the number of tillers / plants would require a destructive sample).. I will go ahead and update the methods for these, and we can go back later and update the names / methods after they are decided and available in the database.

[dlebauer]

update traits 
   set method_id = (select id from methods where name = 'In-season leaf area and biomass estimates by destructive sampling') 
   where 
     (variable_id in (select id from variables 
       where name in ('number_plants_destructive_sample',
'number_tillers_destructive_sample',
'aboveground_fresh_weight_1meter_destructive_sample',
'aboveground_fresh_weight_5plant_destructive_sample',
'fresh_weight_stems',
'fresh_weight_leaves',
'dry_weight_stems',
'dry_weight_leaves',
'leaf_area_index_AreaMeter',
'biomass_dry_stems',
'biomass_dry_leaves',
'SLA',
'dry_matter_fraction')) 
         and date_trunc('day', created_at) = (date '2017-07-10'))

[NewcombMaria]

Thanks @dlebauer. Let me know if there are steps that I need to take. I've written Methods for mechanical harvest sorghum season1 and mechanical harvest sorghum season2 (different equipment, different methods). Should I make a list of the harvest-associated traits to be linked with each season, similar to above?

[NewcombMaria]

@dlebauer the following is a new updated list of data-sets and variables recently uploaded to betydb that need to be linked with specific methods.

• [x] Method: Visual classification of sorghum growth stages to determine estimates of developmental timing by accession

• Linked with the following traits that I uploaded on July 22 around 2:30 PM Arizona time:

  1. flag_leaf_emergence_time
  2. heading_time
  3. flowering_time
  4. grain_milk_stage_time
  5. grain_soft_dough_stage_time
  6. grain_hard_dough_stage_time

• [x] Method: Grain near-infrared spectroscopy (NIRS) analysis

• Linked with the following traits that I uploaded on July 22 around 4:45 PM Arizona time:

  1. grain_ash_content
  2. grain_fat_content
  3. grain_fiber_content
  4. grain_moisture_content
  5. grain_protein_content
  6. grain_starch_content
  7. grain_stage_at_harvest

[NewcombMaria]

@dlebauer I uploaded a small data-set for ground-based canopy cover estimates for the purpose of validating the canopy cover estimates from the stereoTop RGB cameras, and the data need to be linked with the appropriate Method. The method/protocol I followed is described in 'Canopy cover estimation from ground-based conventional photographs': https://terraref.ncsa.illinois.edu/bety/methods/6000000024 .

• [x] The Method: ground-based canopy cover (6000000024)

• Linked to canopy_cover traits uploaded on October 13 around 7:00 AM Arizona time

[dlebauer]

updated the ground-based canopy cover

UPDATE traits 
    SET method_id = (SELECT ID FROM methods NAME = 
      'Canopy cover estimation from ground-based conventional photographs')
 WHERE 
    DATE (traits.created_at) = '2017-10-13'
   AND variable_id = (SELECT    ID FROM variables WHERE NAME = 'canopy_cover');

[dlebauer]

Visual classification of sorghum growth stages to determine estimates of developmental timing by accession

UPDATE traits SET method_id = (
    SELECT ID FROM methods WHERE NAME = 
                 'Visual classification of sorghum growth stages to determine estimates of developmental timing by accession')
WHERE DATE (traits.created_at) = '2017-07-22'
    AND variable_id in (SELECT ID FROM variables WHERE
        NAME in ( 'flag_leaf_emergence_time','heading_time', 
                                 'flowering_time', 
                                 'grain_milk_stage_time',
                                 'grain_soft_dough_stage_time',
                                 'grain_hard_dough_stage_time'));

Grain near-infrared spectroscopy (NIRS) analysis

UPDATE traits SET method_id = (
    SELECT ID FROM methods WHERE NAME = 
                'Grain near-infrared spectroscopy (NIRS) analysis')
WHERE DATE (traits.created_at) = '2017-07-22'
    AND variable_id in (SELECT ID FROM variables WHERE
        NAME in ('grain_ash_content',
                                'grain_fat_content',
                                'grain_fiber_content',
                                'grain_moisture_content',
                                'grain_protein_content',
                                'grain_starch_content',
                                'grain_stage_at_harvest'));

[dlebauer]

@NewcombMaria I've associated methods with all of the traits in this issue. I am going to close it in order to keep it from getting too long - please open new ones as needed.