Project

General

Profile

« Previous | Next » 

Revision 10386

Added by Aaron Marcuse-Kubitza almost 11 years ago

inputs/BRIT/: switched to new-style import, using the steps at wiki.vegpath.org/Adding_new-style_import_to_a_datasource

View differences:

postprocess.sql
1 1 
SELECT util.search_path_append('util');
2 2 

  		




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


  		




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


  		




  4
  4
  
    

  		




  5
  5
  
    

  		




  6
  6
  
    
-- map_nulls() derived cols


  		




  ......




  14
  14
  
    
  LANGUAGE sql IMMUTABLE


  		




  15
  15
  
    
  COST 100;


  		




  16
  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


  		




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


  		




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


  		




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


  		




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


  		




  21
  21
  
    

  		




  22
  22
  
    

  		




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


  		




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


  		




  
  24
  
    
$$CASE WHEN "locality__area__main" IS NOT NULL THEN "occurrenceRemarks_verbatim" END$$)


  		




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


  		




  26
  26
  
    

  		




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


  		




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


  		




  
  27
  
    
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("diameterBreastHeight_min_cm_verbatim", "diameterBreastHeight_max_cm_verbatim")$$); -- runtime: 0.3 s ("Time: 326.615 ms") @starscream


  		




  
  28
  
    
SELECT mk_derived_col((:table_str, 'height_m'),                $$_avg("height_min_m_verbatim"               , "height_max_m_verbatim"               )$$); -- runtime: 0.3 s ("Time: 346.548 ms") @starscream


  		




  29
  29
  
    

  		




  30
  30
  
    
SELECT mk_derived_col((:table_str, 'recordedBy'),


  		




  31
  
  
    
$$_join("Collector_Abbrev_Name", "Collection_Team")$$)


  		




  
  31
  
    
$$_join("recordedBy_primary", "recordedBy_additional")$$)


  		




  32
  32
  
    
; -- runtime: 0.3 s ("Time: 281.817 ms") @starscream


  		




  33
  33
  
    
SELECT mk_derived_col((:table_str, 'locality'),


  		




  34
  
  
    
$$_join("Locality_Summary", "Locality_Description", "Locality_Site_Specific")$$)


  		




  
  34
  
    
$$_join("locality__area__prefix", "locality__area__main", "locality__spot")$$)


  		




  35
  35
  
    
; -- runtime: 0.3 s ("Time: 254.771 ms") @starscream


  		












Also available in:  Unified diff