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:

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


  		












Also available in:  Unified diff