Project

General

Profile

« Previous | Next » 

Revision 10561

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/$dest/$subdir/: translated single-column filters to postprocessing derived columns, using the steps at wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns

View differences:

inputs/NY/Ecatalog_all/postprocess.sql
1 1 
SELECT util.search_path_append('util');
2 2 

  		




  3
  3
  
    

  		




  
  4
  
    
-- map_nulls() derived cols


  		




  
  5
  
    
-- runtime: 1.5 min ("76.61 real") @starscream


  		




  
  6
  
    

  		




  
  7
  
    
CREATE OR REPLACE FUNCTION map_nulls__num(value text)


  		




  
  8
  
    
  RETURNS double precision AS


  		




  
  9
  
    
$BODY$


  		




  
  10
  
    
SELECT util.map_nulls('{.}', $1)::double precision


  		




  
  11
  
    
$BODY$


  		




  
  12
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  13
  
    
  COST 100;


  		




  
  14
  
    

  		




  
  15
  
    
SELECT mk_derived_col((:table_str, 'minimumElevationInMeters'), $$map_nulls__num("MinimumElevation")$$); -- runtime: 40 s ("Time: 37588.218 ms") @starscream


  		




  
  16
  
    
SELECT mk_derived_col((:table_str, 'maximumElevationInMeters'), $$map_nulls__num("MaximumElevation")$$); -- runtime: 40 s ("Time: 38766.181 ms") @starscream


  		




  
  17
  
    

  		




  
  18
  
    

  		




  4
  19
  
    
SELECT mk_derived_col((:table_str, 'locality'),


  		




  5
  20
  
    
$$_join("Locality", _label('substrate', "Substrate"), _label('vegetation', "Vegetation"))$$)


  		




  6
  21
  
    
; -- runtime: 45 s ("Time: 47353.897 ms") @starscream


  		












  

    
      inputs/NY/Ecatalog_all/map.csv
    
  





  38
  38
  
    
Latitude,decimalLatitude,,


  		




  39
  39
  
    
CoordinatePrecision,coordinateUncertaintyInMeters,,"Usage inconsistent with DwC definition, which only allows degrees. Assuming units based on the range and precision of values."


  		




  40
  40
  
    
BoundingBox,footprintWKT,,


  		




  41
  
  
    
MinimumElevation,minimumElevationInMeters,/_nullIf:[null=.]/value,


  		




  42
  
  
    
MaximumElevation,maximumElevationInMeters,,


  		




  
  41
  
    
MinimumElevation,minimumElevationInMeters_verbatim,,


  		




  
  42
  
    
MaximumElevation,maximumElevationInMeters_verbatim,,


  		




  43
  43
  
    
MinimumDepth,minimumDepthInMeters,,


  		




  44
  44
  
    
MaximumDepth,maximumDepthInMeters,,


  		




  45
  45
  
    
Sex,sex,,


  		




  ......




  56
  56
  
    
UniqueNYInternalRecordNumber,OMIT#UniqueNYInternalRecordNumber,,Sometimes duplicated. Omitted to avoid confusion since this is an internal-only ID.


  		




  57
  57
  
    
locality,locality,,


  		




  58
  58
  
    
occurrenceRemarks,occurrenceRemarks,,


  		




  
  59
  
    
minimumElevationInMeters,minimumElevationInMeters,,


  		




  
  60
  
    
maximumElevationInMeters,maximumElevationInMeters,,


  		












  

    
      inputs/NY/Ecatalog_all/VegBIEN.csv
    
  





  6
  6
  
    
InstitutionCode,"/location/_if[@name=""if subplot""]/else/authorlocationcode/_first/3/_if[@name=""if plot""]/else/_alt/2/_if[@name=""if catalogNumber""]/then/_join/1/_if[@name=""if specimen""]/then/_first/2",


  		




  7
  7
  
    
CollectionCode,"/location/_if[@name=""if subplot""]/else/authorlocationcode/_first/3/_if[@name=""if plot""]/else/_alt/2/_if[@name=""if catalogNumber""]/then/_join/2/_join/1",


  		




  8
  8
  
    
CatalogNumber,"/location/_if[@name=""if subplot""]/else/authorlocationcode/_first/3/_if[@name=""if plot""]/else/_alt/2/_if[@name=""if catalogNumber""]/then/_join/3/_if[@name=""if indirect voucher""]/else",


  		




  9
  
  
    
MinimumElevation,/location/elevation_m/_alt/2/_avg/1/_nullIf:[null=.]/value,


  		




  10
  
  
    
MaximumElevation,/location/elevation_m/_alt/2/_avg/2,


  		




  
  9
  
    
minimumElevationInMeters,/location/elevation_m/_alt/2/_avg/1,


  		




  
  10
  
    
maximumElevationInMeters,/location/elevation_m/_alt/2/_avg/2,


  		




  11
  11
  
    
FieldNumber,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",Usage inconsistent with DwC definition


  		




  12
  12
  
    
locality,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1,


  		




  13
  13
  
    
Habitat,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/3,


  		




  ......




  78
  78
  
    
DateLastModified,,** No join mapping for modified ** 


  		




  79
  79
  
    
JulianDay,,** No join mapping for dayOfYear ** 


  		




  80
  80
  
    
Locality,,** No join mapping for locality__main ** 


  		




  
  81
  
    
MaximumElevation,,** No join mapping for maximumElevationInMeters_verbatim ** 


  		




  
  82
  
    
MinimumElevation,,** No join mapping for minimumElevationInMeters_verbatim ** 


  		




  81
  83
  
    
Notes,,** No join mapping for occurrenceRemarks__main ** 


  		




  82
  84
  
    
PlantFungDescription,,"** No join mapping for specimenDescription ** PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant"


  		




  83
  85
  
    
PreparationType,,** No join mapping for preparations ** 


  		












  

    
      inputs/NY/Ecatalog_all/test.xml.ref
    
  





  16
  16
  
    
                        </authorlocationcode>


  		




  17
  17
  
    
                        <elevation_m>


  		




  18
  18
  
    
                            <_avg>


  		




  19
  
  
    
                                <1>


  		




  20
  
  
    
                                    <_nullIf>


  		




  21
  
  
    
                                        <null>.</null>


  		




  22
  
  
    
                                        <value>$MinimumElevation</value>


  		




  23
  
  
    
                                    </_nullIf>


  		




  24
  
  
    
                                </1>


  		




  25
  
  
    
                                <2>$MaximumElevation</2>


  		




  
  19
  
    
                                <1>$minimumElevationInMeters</1>


  		




  
  20
  
    
                                <2>$maximumElevationInMeters</2>


  		




  26
  21
  
    
                            </_avg>


  		




  27
  22
  
    
                        </elevation_m>


  		




  28
  23
  
    
                        <iscultivated>


  		












  

    
      inputs/NY/Ecatalog_all/unmapped_terms.csv
    
  





  4
  4
  
    
dayOfYear


  		




  5
  5
  
    
eventTime


  		




  6
  6
  
    
locality__main


  		




  
  7
  
    
minimumElevationInMeters_verbatim


  		




  
  8
  
    
maximumElevationInMeters_verbatim


  		




  7
  9
  
    
preparations


  		




  8
  10
  
    
otherCatalogNumbers


  		




  9
  11
  
    
relationshipOfResource


  		












Also available in:  Unified diff