Project

General

Profile

« Previous | Next » 

Revision 10382

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/BRIT/specimen_flat/: DBH_*, Height_*: mapped NULL-equivalent values, using the steps at wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns

View differences:

inputs/BRIT/specimen_flat/postprocess.sql
2 2 

  		




  3
  3
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY (specimen_id)$$);


  		




  4
  4
  
    

  		




  
  5
  
    

  		




  
  6
  
    
-- map_nulls() derived cols


  		




  
  7
  
    
-- runtime: 1 s @starscream


  		




  
  8
  
    

  		




  
  9
  
    
CREATE OR REPLACE FUNCTION map_nulls__nonzero(value anyelement)


  		




  
  10
  
    
  RETURNS anyelement AS


  		




  
  11
  
    
$BODY$


  		




  
  12
  
    
SELECT NULLIF($1, 0)


  		




  
  13
  
    
$BODY$


  		




  
  14
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  15
  
    
  COST 100;


  		




  
  16
  
    

  		




  
  17
  
    
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_min_cm'), $$map_nulls__nonzero("DBH_Min"   )$$); -- runtime: 0.2 s ("Time: 172.094 ms") @starscream


  		




  
  18
  
    
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_max_cm'), $$map_nulls__nonzero("DBH_Max"   )$$); -- runtime: 0.2 s ("Time: 161.774 ms") @starscream


  		




  
  19
  
    
SELECT mk_derived_col((:table_str, 'height_min_m'               ), $$map_nulls__nonzero("Height_Min")$$); -- runtime: 0.2 s ("Time: 153.984 ms") @starscream


  		




  
  20
  
    
SELECT mk_derived_col((:table_str, 'height_max_m'               ), $$map_nulls__nonzero("Height_Max")$$); -- runtime: 0.2 s ("Time: 207.515 ms") @starscream


  		




  
  21
  
    

  		




  
  22
  
    

  		




  5
  23
  
    
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),


  		




  6
  24
  
    
$$CASE WHEN "Locality_Description" IS NOT NULL THEN "Notes_Plant" END$$)


  		




  7
  25
  
    
; -- runtime: 0.3 s ("Time: 311.190 ms") @starscream


  		




  8
  26
  
    

  		




  9
  
  
    
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("DBH_Min"   , "DBH_Max"   )$$); -- runtime:  s ("Time:  ms") @starscream


  		




  10
  
  
    
SELECT mk_derived_col((:table_str, 'height_m'),                $$_avg("Height_Min", "Height_Max")$$); -- runtime:  s ("Time:  ms") @starscream


  		




  
  27
  
    
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("DBH_Min"   , "DBH_Max"   )$$); -- runtime: 0.3 s ("Time: 392.412 ms") @starscream


  		




  
  28
  
    
SELECT mk_derived_col((:table_str, 'height_m'),                $$_avg("Height_Min", "Height_Max")$$); -- runtime: 0.3 s ("Time: 344.442 ms") @starscream


  		












  

    
      inputs/BRIT/specimen_flat/map.csv
    
  





  38
  38
  
    
Soil_Description,*Soil_Description,,


  		




  39
  39
  
    
Notes_Plant,occurrenceRemarks_verbatim,,


  		




  40
  40
  
    
Phenology_Stage,reproductiveCondition,,


  		




  41
  
  
    
Height_Min,height_min_m,,Assuming units based on the range and precision of values


  		




  42
  
  
    
Height_Max,height_max_m,,Assuming units based on the range and precision of values


  		




  43
  
  
    
DBH_Min,diameterBreastHeight_min_cm,,Assuming units based on the range and precision of values


  		




  44
  
  
    
DBH_Max,diameterBreastHeight_max_cm,,Assuming units based on the range and precision of values


  		




  
  41
  
    
Height_Min,height_min_m_verbatim,,Assuming units based on the range and precision of values


  		




  
  42
  
    
Height_Max,height_max_m_verbatim,,Assuming units based on the range and precision of values


  		




  
  43
  
    
DBH_Min,diameterBreastHeight_min_cm_verbatim,,Assuming units based on the range and precision of values


  		




  
  44
  
    
DBH_Max,diameterBreastHeight_max_cm_verbatim,,Assuming units based on the range and precision of values


  		




  45
  45
  
    
Latitude,decimalLatitude,,


  		




  46
  46
  
    
Longitude,decimalLongitude,,


  		




  47
  47
  
    
Coordinate_Uncertainity,coordinateUncertaintyInMeters,,Assuming units based on the range and precision of values


  		




  ......




  59
  59
  
    
Year_Identified,yearIdentified,,


  		




  60
  60
  
    
Month_Identified,monthIdentified,,


  		




  61
  61
  
    
Day_Identified,dayIdentified,,


  		




  
  62
  
    
diameterBreastHeight_min_cm,diameterBreastHeight_min_cm,,


  		




  
  63
  
    
diameterBreastHeight_max_cm,diameterBreastHeight_max_cm,,


  		




  
  64
  
    
height_min_m,height_min_m,,


  		




  
  65
  
    
height_max_m,height_max_m,,


  		




  62
  66
  
    
occurrenceRemarks,occurrenceRemarks,,


  		




  63
  67
  
    
diameterBreastHeight_cm,diameterBreastHeight_cm,,


  		




  64
  68
  
    
height_m,height_m,,


  		












  

    
      inputs/BRIT/specimen_flat/VegBIEN.csv
    
  





  92
  92
  
    
Auxilirary_Collections,,** No non-empty join mapping for UNUSED#Auxilirary_Collections ** 


  		




  93
  93
  
    
Basis_Of_Specimen,,** No join mapping for basisOfRecord ** 


  		




  94
  94
  
    
Closest_Town,,** No non-empty join mapping for UNUSED#Closest_Town ** 


  		




  95
  
  
    
DBH_Max,,** No join mapping for diameterBreastHeight_max_cm ** Assuming units based on the range and precision of values


  		




  96
  
  
    
DBH_Min,,** No join mapping for diameterBreastHeight_min_cm ** Assuming units based on the range and precision of values


  		




  
  95
  
    
DBH_Max,,** No join mapping for diameterBreastHeight_max_cm_verbatim ** Assuming units based on the range and precision of values


  		




  
  96
  
    
DBH_Min,,** No join mapping for diameterBreastHeight_min_cm_verbatim ** Assuming units based on the range and precision of values


  		




  97
  97
  
    
Dubpliate_Herbarium,,** No non-empty join mapping for UNUSED#Dubpliate_Herbarium ** 


  		




  98
  98
  
    
Duplicates,,** No join mapping for *Duplicates ** # of duplicates


  		




  99
  99
  
    
Global_Unique_Identifier,,** No non-empty join mapping for UNUSED#Global_Unique_Identifier ** 


  		




  100
  
  
    
Height_Max,,** No join mapping for height_max_m ** Assuming units based on the range and precision of values


  		




  101
  
  
    
Height_Min,,** No join mapping for height_min_m ** Assuming units based on the range and precision of values


  		




  
  100
  
    
Height_Max,,** No join mapping for height_max_m_verbatim ** Assuming units based on the range and precision of values


  		




  
  101
  
    
Height_Min,,** No join mapping for height_min_m_verbatim ** Assuming units based on the range and precision of values


  		




  102
  102
  
    
Infra_Rank,,** No join mapping for infraspecificRank ** 


  		




  103
  103
  
    
Local_Name,,** No join mapping for vernacularName ** 


  		




  104
  104
  
    
Notes_Plant,,** No join mapping for occurrenceRemarks_verbatim ** 


  		




  ......




  107
  107
  
    
Subclass_Name,,** No join mapping for *subclass ** 


  		




  108
  108
  
    
Type_Specimen,,** No non-empty join mapping for UNUSED#Type_Specimen ** 


  		




  109
  109
  
    
Updated_date,,** No join mapping for modified ** 


  		




  
  110
  
    
diameterBreastHeight_max_cm,,** No join mapping for diameterBreastHeight_max_cm ** 


  		




  
  111
  
    
diameterBreastHeight_min_cm,,** No join mapping for diameterBreastHeight_min_cm ** 


  		




  
  112
  
    
height_max_m,,** No join mapping for height_max_m ** 


  		




  
  113
  
    
height_min_m,,** No join mapping for height_min_m ** 


  		












  

    
      inputs/BRIT/specimen_flat/new_terms.csv
    
  





  23
  23
  
    
Habitat_Summary,habitat,/_join/2,


  		




  24
  24
  
    
Notes_Plant,occurrenceRemarks_verbatim,,


  		




  25
  25
  
    
Phenology_Stage,reproductiveCondition,,


  		




  26
  
  
    
Height_Min,height_min_m,,Assuming units based on the range and precision of values


  		




  27
  
  
    
Height_Max,height_max_m,,Assuming units based on the range and precision of values


  		




  28
  
  
    
DBH_Min,diameterBreastHeight_min_cm,,Assuming units based on the range and precision of values


  		




  29
  
  
    
DBH_Max,diameterBreastHeight_max_cm,,Assuming units based on the range and precision of values


  		




  
  26
  
    
Height_Min,height_min_m_verbatim,,Assuming units based on the range and precision of values


  		




  
  27
  
    
Height_Max,height_max_m_verbatim,,Assuming units based on the range and precision of values


  		




  
  28
  
    
DBH_Min,diameterBreastHeight_min_cm_verbatim,,Assuming units based on the range and precision of values


  		




  
  29
  
    
DBH_Max,diameterBreastHeight_max_cm_verbatim,,Assuming units based on the range and precision of values


  		




  30
  30
  
    
Coordinate_Uncertainity,coordinateUncertaintyInMeters,,Assuming units based on the range and precision of values


  		




  31
  31
  
    
Elevation_Min,minimumElevationInMeters,,Assuming units based on the range and precision of values


  		




  32
  32
  
    
Elevation_Max,maximumElevationInMeters,,Assuming units based on the range and precision of values


  		












  

    
      inputs/BRIT/specimen_flat/unmapped_terms.csv
    
  





  4
  4
  
    
*Research_Site_Name


  		




  5
  5
  
    
*Soil_Description


  		




  6
  6
  
    
occurrenceRemarks_verbatim


  		




  7
  
  
    
height_min_m


  		




  8
  
  
    
height_max_m


  		




  9
  
  
    
diameterBreastHeight_min_cm


  		




  10
  
  
    
diameterBreastHeight_max_cm


  		




  
  7
  
    
height_min_m_verbatim


  		




  
  8
  
    
height_max_m_verbatim


  		




  
  9
  
    
diameterBreastHeight_min_cm_verbatim


  		




  
  10
  
    
diameterBreastHeight_max_cm_verbatim


  		




  11
  11
  
    
basisOfRecord


  		




  12
  12
  
    
*Duplicates


  		




  13
  13
  
    
modified


  		




  
  14
  
    
diameterBreastHeight_min_cm


  		




  
  15
  
    
diameterBreastHeight_max_cm


  		




  
  16
  
    
height_min_m


  		




  
  17
  
    
height_max_m


  		












Also available in:  Unified diff