Project

General

Profile

« Previous | Next » 

Revision 10622

Added by Aaron Marcuse-Kubitza almost 11 years ago

inputs/TEX/Specimen2/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns

View differences:

inputs/TEX/Specimen2/postprocess.sql
1 
SELECT util.search_path_append('util');
2 

  		




  
  3
  
    

  		




  
  4
  
    
SELECT mk_derived_col((:table_str, 'dateCollected'),


  		




  
  5
  
    
$$_alt("EXACT DATE", "GENERAL DATE")$$)


  		




  
  6
  
    
; -- runtime: 0.3 s ("Time: 283.170 ms") @starscream


  		




  
  7
  
    

  		




  
  8
  
    
SELECT mk_derived_col((:table_str, 'recordedBy__primary'),


  		




  
  9
  
    
$$_join_words("COLLECTOR 1 FIRST NAME", "COLLECTOR 1 LAST NAME")$$)


  		




  
  10
  
    
; -- runtime: 0.2 s ("Time: 154.234 ms") @starscream


  		




  
  11
  
    

  		




  
  12
  
    
SELECT mk_derived_col((:table_str, 'recordedBy__additional__1'),


  		




  
  13
  
    
$$_join_words("COLLECTOR 2 FIRST NAME", "COLLECTOR 2 LAST NAME")$$)


  		




  
  14
  
    
; -- runtime: 0.1 s ("Time: 135.107 ms") @starscream


  		




  
  15
  
    

  		




  
  16
  
    
SELECT mk_derived_col((:table_str, 'recordedBy__additional__2'),


  		




  
  17
  
    
$$_join_words("COLLECTOR 3 FIRST NAME", "COLLECTOR 3 LAST NAME")$$)


  		




  
  18
  
    
; -- runtime: 0.1 s ("Time: 133.670 ms") @starscream


  		




  
  19
  
    

  		




  
  20
  
    
SELECT mk_derived_col((:table_str, 'recordedBy__additional__3'),


  		




  
  21
  
    
$$_join_words("COLLECTOR 4 FIRST NAME", "COLLECTOR 4 LAST NAME")$$)


  		




  
  22
  
    
; -- runtime: 0.1 s ("Time: 132.596 ms") @starscream


  		




  
  23
  
    

  		




  
  24
  
    
SELECT mk_derived_col((:table_str, 'recordedBy__additional__4'),


  		




  
  25
  
    
$$_join_words("COLLECTOR 5 FIRST NAME", "COLLECTOR 5 LAST NAME")$$)


  		




  
  26
  
    
; -- runtime: 0.1 s ("Time: 132.078 ms") @starscream


  		




  
  27
  
    

  		




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


  		




  
  29
  
    
$$_join("recordedBy__primary", "recordedBy__additional__1", "recordedBy__additional__2", "recordedBy__additional__3", "recordedBy__additional__4")$$)


  		




  
  30
  
    
; -- runtime: 0.1 s ("Time: 141.355 ms") @starscream


  		












  

    
      inputs/TEX/Specimen2/map.csv
    
  





  18
  18
  
    
ELEVATION,verbatimElevation,,


  		




  19
  19
  
    
LATITUDE,latitude_DMS,,


  		




  20
  20
  
    
LONGITUDE,longitude_DMS,,


  		




  21
  
  
    
EXACT DATE,dateCollected,/_alt/1,


  		




  
  21
  
    
EXACT DATE,dateCollected__scrubbed,,


  		




  22
  22
  
    
END DATE,UNUSED#END DATE,,


  		




  23
  
  
    
GENERAL DATE,dateCollected,/_alt/2,


  		




  24
  
  
    
COLLECTOR 1 FIRST NAME,recordedBy,/_join/1/_join_words/1,


  		




  25
  
  
    
COLLECTOR 1 LAST NAME,recordedBy,/_join/1/_join_words/2,


  		




  
  23
  
    
GENERAL DATE,dateCollected__verbatim,,


  		




  
  24
  
    
COLLECTOR 1 FIRST NAME,recordedBy__primary__first,,


  		




  
  25
  
    
COLLECTOR 1 LAST NAME,recordedBy__primary__last,,


  		




  26
  26
  
    
COLLECTOR NUMBER,collectorNumber,,


  		




  27
  
  
    
COLLECTOR 2 FIRST NAME,recordedBy,/_join/2/_join_words/1,


  		




  28
  
  
    
COLLECTOR 2 LAST NAME,recordedBy,/_join/2/_join_words/2,


  		




  29
  
  
    
COLLECTOR 3 FIRST NAME,recordedBy,/_join/3/_join_words/1,


  		




  30
  
  
    
COLLECTOR 3 LAST NAME,recordedBy,/_join/3/_join_words/2,


  		




  31
  
  
    
COLLECTOR 4 FIRST NAME,recordedBy,/_join/4/_join_words/1,


  		




  32
  
  
    
COLLECTOR 4 LAST NAME,recordedBy,/_join/4/_join_words/2,


  		




  33
  
  
    
COLLECTOR 5 FIRST NAME,recordedBy,/_join/5/_join_words/1,


  		




  34
  
  
    
COLLECTOR 5 LAST NAME,recordedBy,/_join/5/_join_words/2,


  		




  
  27
  
    
COLLECTOR 2 FIRST NAME,recordedBy__additional__1__first,,


  		




  
  28
  
    
COLLECTOR 2 LAST NAME,recordedBy__additional__1__last,,


  		




  
  29
  
    
COLLECTOR 3 FIRST NAME,recordedBy__additional__2__first,,


  		




  
  30
  
    
COLLECTOR 3 LAST NAME,recordedBy__additional__2__last,,


  		




  
  31
  
    
COLLECTOR 4 FIRST NAME,recordedBy__additional__3__first,,


  		




  
  32
  
    
COLLECTOR 4 LAST NAME,recordedBy__additional__3__last,,


  		




  
  33
  
    
COLLECTOR 5 FIRST NAME,recordedBy__additional__4__first,,


  		




  
  34
  
    
COLLECTOR 5 LAST NAME,recordedBy__additional__4__last,,


  		




  35
  35
  
    
LATLONG SOURCE,*LATLONG SOURCE,,


  		




  36
  36
  
    
LATLONG DATUM,*LATLONG DATUM,,


  		




  37
  37
  
    
DATABASED BY,*DATABASED BY,,


  		




  ......




  44
  44
  
    
DET DATE,dateIdentified,,


  		




  45
  45
  
    
DETERMINER INSTITUTION,*DETERMINER INSTITUTION,,


  		




  46
  46
  
    
DET NOTES,identificationRemarks,,


  		




  
  47
  
    
dateCollected,dateCollected,,


  		




  
  48
  
    
recordedBy__primary,recordedBy__primary,,


  		




  
  49
  
    
recordedBy__additional__1,recordedBy__additional__1,,


  		




  
  50
  
    
recordedBy__additional__2,recordedBy__additional__2,,


  		




  
  51
  
    
recordedBy__additional__3,recordedBy__additional__3,,


  		




  
  52
  
    
recordedBy__additional__4,recordedBy__additional__4,,


  		




  
  53
  
    
recordedBy,recordedBy,,


  		












  

    
      inputs/TEX/Specimen2/VegBIEN.csv
    
  





  5
  5
  
    
LOCALITY,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1,


  		




  6
  6
  
    
VEGETATION,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/3,


  		




  7
  7
  
    
DET DATE,"/location/locationevent/taxonoccurrence/_if[@name=""if has accepted name""]/then/taxondetermination:[determinationtype=accepted]/determinationdate/_alt/1/_dateRangeStart/value",


  		




  8
  
  
    
EXACT DATE,/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/1/_alt/1,


  		




  9
  
  
    
GENERAL DATE,/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/1/_alt/2,


  		




  
  8
  
    
dateCollected,/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/1,


  		




  10
  9
  
    
PLANT DATA,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes/_join/1, = specimenDescription


  		




  11
  10
  
    
COLLECTOR NUMBER,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/authorplantcode/_first/2,


  		




  12
  11
  
    
PHENOLOGY,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/reproductivecondition,


  		




  13
  12
  
    
HERBARIUM,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectioncode_dwc/_alt/1,Brad: Not sure if mapping correct. DwC element; code for entire collection; often same as InstitutionCode; Aaron: Combining with InstitutionCode to create collection name


  		




  14
  13
  
    
COLLECTOR NUMBER,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/else",


  		




  15
  14
  
    
COLLECTOR NUMBER,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/3",


  		




  16
  
  
    
COLLECTOR 1 FIRST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/1/_join_words/1,


  		




  17
  
  
    
COLLECTOR 1 LAST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/1/_join_words/2,


  		




  18
  
  
    
COLLECTOR 2 FIRST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/2/_join_words/1,


  		




  19
  
  
    
COLLECTOR 2 LAST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/2/_join_words/2,


  		




  20
  
  
    
COLLECTOR 3 FIRST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/3/_join_words/1,


  		




  21
  
  
    
COLLECTOR 3 LAST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/3/_join_words/2,


  		




  22
  
  
    
COLLECTOR 4 FIRST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/4/_join_words/1,


  		




  23
  
  
    
COLLECTOR 4 LAST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/4/_join_words/2,


  		




  24
  
  
    
COLLECTOR 5 FIRST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/5/_join_words/1,


  		




  25
  
  
    
COLLECTOR 5 LAST NAME,/location/locationevent/taxonoccurrence/collector_id/party/fullname/_join/5/_join_words/2,


  		




  
  15
  
    
recordedBy,/location/locationevent/taxonoccurrence/collector_id/party/fullname,


  		




  26
  16
  
    
COLLECTOR NUMBER,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  27
  17
  
    
ORIGIN,"/location/locationevent/taxonoccurrence/iscultivated/_alt/2/_map:[cultivated=t,wild=f,*=]/value",


  		




  28
  18
  
    
ORIGIN,"/location/locationevent/taxonoccurrence/isnative/_map:[native=t,exotic=f,*=]/value",


  		




  ......




  60
  50
  
    
STATE,/location/locationplace/*_id/place/stateprovince,


  		




  61
  51
  
    
ELEVATION,/location/verbatimelevation,


  		




  62
  52
  
    
BARCODE,,** No join mapping for barcode ** 


  		




  
  53
  
    
COLLECTOR 1 FIRST NAME,,** No join mapping for recordedBy__primary__first ** 


  		




  
  54
  
    
COLLECTOR 1 LAST NAME,,** No join mapping for recordedBy__primary__last ** 


  		




  
  55
  
    
COLLECTOR 2 FIRST NAME,,** No join mapping for recordedBy__additional__1__first ** 


  		




  
  56
  
    
COLLECTOR 2 LAST NAME,,** No join mapping for recordedBy__additional__1__last ** 


  		




  
  57
  
    
COLLECTOR 3 FIRST NAME,,** No join mapping for recordedBy__additional__2__first ** 


  		




  
  58
  
    
COLLECTOR 3 LAST NAME,,** No join mapping for recordedBy__additional__2__last ** 


  		




  
  59
  
    
COLLECTOR 4 FIRST NAME,,** No join mapping for recordedBy__additional__3__first ** 


  		




  
  60
  
    
COLLECTOR 4 LAST NAME,,** No join mapping for recordedBy__additional__3__last ** 


  		




  
  61
  
    
COLLECTOR 5 FIRST NAME,,** No join mapping for recordedBy__additional__4__first ** 


  		




  
  62
  
    
COLLECTOR 5 LAST NAME,,** No join mapping for recordedBy__additional__4__last ** 


  		




  63
  63
  
    
DATABASED BY,,** No join mapping for *DATABASED BY ** 


  		




  64
  64
  
    
DET NOTES,,** No join mapping for identificationRemarks ** 


  		




  65
  65
  
    
DETERMINER INSTITUTION,,** No join mapping for *DETERMINER INSTITUTION ** 


  		




  66
  66
  
    
END DATE,,** No non-empty join mapping for UNUSED#END DATE ** 


  		




  
  67
  
    
EXACT DATE,,** No join mapping for dateCollected__scrubbed ** 


  		




  
  68
  
    
GENERAL DATE,,** No join mapping for dateCollected__verbatim ** 


  		




  67
  69
  
    
LATLONG DATUM,,** No join mapping for *LATLONG DATUM ** 


  		




  68
  70
  
    
LATLONG SOURCE,,** No join mapping for *LATLONG SOURCE ** 


  		




  69
  71
  
    
SUBSPECIES AUTHOR,,** No join mapping for *SUBSPECIES AUTHOR ** 


  		




  70
  72
  
    
UTM,,** No join mapping for *UTM ** 


  		




  71
  73
  
    
VARIETY AUTHOR,,** No join mapping for *VARIETY AUTHOR ** 


  		




  
  74
  
    
recordedBy__additional__1,,** No join mapping for recordedBy__additional__1 ** 


  		




  
  75
  
    
recordedBy__additional__2,,** No join mapping for recordedBy__additional__2 ** 


  		




  
  76
  
    
recordedBy__additional__3,,** No join mapping for recordedBy__additional__3 ** 


  		




  
  77
  
    
recordedBy__additional__4,,** No join mapping for recordedBy__additional__4 ** 


  		




  
  78
  
    
recordedBy__primary,,** No join mapping for recordedBy__primary ** 


  		




  72
  79
  
    
row_num,,** No join mapping for *row_num ** 


  		












  

    
      inputs/TEX/Specimen2/test.xml.ref
    
  





  27
  27
  
    
                        <locationevent>


  		




  28
  28
  
    
                            <taxonoccurrence>


  		




  29
  29
  
    
                                <aggregateoccurrence>


  		




  30
  
  
    
                                    <collectiondate>


  		




  31
  
  
    
                                        <_alt>


  		




  32
  
  
    
                                            <1>$EXACT DATE</1>


  		




  33
  
  
    
                                            <2>$GENERAL DATE</2>


  		




  34
  
  
    
                                        </_alt>


  		




  35
  
  
    
                                    </collectiondate>


  		




  
  30
  
    
                                    <collectiondate>$dateCollected</collectiondate>


  		




  36
  31
  
    
                                    <notes>$PLANT DATA</notes>


  		




  37
  32
  
    
                                    <plantobservation>


  		




  38
  33
  
    
                                        <authorplantcode>$COLLECTOR NUMBER</authorplantcode>


  		




  ......




  43
  38
  
    
                                        </specimenreplicate>


  		




  44
  39
  
    
                                    </plantobservation>


  		




  45
  40
  
    
                                </aggregateoccurrence>


  		




  46
  
  
    
                                <collector_id>


  		




  47
  
  
    
                                    <party>


  		




  48
  
  
    
                                        <fullname>


  		




  49
  
  
    
                                            <_join>


  		




  50
  
  
    
                                                <1>


  		




  51
  
  
    
                                                    <_join_words>


  		




  52
  
  
    
                                                        <1>$COLLECTOR 1 FIRST NAME</1>


  		




  53
  
  
    
                                                        <2>$COLLECTOR 1 LAST NAME</2>


  		




  54
  
  
    
                                                    </_join_words>


  		




  55
  
  
    
                                                </1>


  		




  56
  
  
    
                                                <2>


  		




  57
  
  
    
                                                    <_join_words>


  		




  58
  
  
    
                                                        <1>$COLLECTOR 2 FIRST NAME</1>


  		




  59
  
  
    
                                                        <2>$COLLECTOR 2 LAST NAME</2>


  		




  60
  
  
    
                                                    </_join_words>


  		




  61
  
  
    
                                                </2>


  		




  62
  
  
    
                                                <3>


  		




  63
  
  
    
                                                    <_join_words>


  		




  64
  
  
    
                                                        <1>$COLLECTOR 3 FIRST NAME</1>


  		




  65
  
  
    
                                                        <2>$COLLECTOR 3 LAST NAME</2>


  		




  66
  
  
    
                                                    </_join_words>


  		




  67
  
  
    
                                                </3>


  		




  68
  
  
    
                                                <4>


  		




  69
  
  
    
                                                    <_join_words>


  		




  70
  
  
    
                                                        <1>$COLLECTOR 4 FIRST NAME</1>


  		




  71
  
  
    
                                                        <2>$COLLECTOR 4 LAST NAME</2>


  		




  72
  
  
    
                                                    </_join_words>


  		




  73
  
  
    
                                                </4>


  		




  74
  
  
    
                                                <5>


  		




  75
  
  
    
                                                    <_join_words>


  		




  76
  
  
    
                                                        <1>$COLLECTOR 5 FIRST NAME</1>


  		




  77
  
  
    
                                                        <2>$COLLECTOR 5 LAST NAME</2>


  		




  78
  
  
    
                                                    </_join_words>


  		




  79
  
  
    
                                                </5>


  		




  80
  
  
    
                                            </_join>


  		




  81
  
  
    
                                        </fullname>


  		




  82
  
  
    
                                    </party>


  		




  83
  
  
    
                                </collector_id>


  		




  
  41
  
    
                                <collector_id><party><fullname>$recordedBy</fullname></party></collector_id>


  		




  84
  42
  
    
                                <iscultivated>


  		




  85
  43
  
    
                                    <_map>


  		




  86
  44
  
    
                                        <cultivated>t</cultivated>


  		












  

    
      inputs/TEX/Specimen2/new_terms.csv
    
  





  2
  2
  
    
SPECIES AUTHORITY,scientificNameAuthorship,,


  		




  3
  3
  
    
VEGETATION,habitat,,


  		




  4
  4
  
    
PLANT DATA,occurrenceRemarks,, = specimenDescription


  		




  5
  
  
    
EXACT DATE,dateCollected,/_alt/1,


  		




  6
  
  
    
GENERAL DATE,dateCollected,/_alt/2,


  		




  7
  
  
    
COLLECTOR 1 FIRST NAME,recordedBy,/_join/1/_join_words/1,


  		




  8
  
  
    
COLLECTOR 1 LAST NAME,recordedBy,/_join/1/_join_words/2,


  		




  9
  
  
    
COLLECTOR 2 FIRST NAME,recordedBy,/_join/2/_join_words/1,


  		




  10
  
  
    
COLLECTOR 2 LAST NAME,recordedBy,/_join/2/_join_words/2,


  		




  11
  
  
    
COLLECTOR 3 FIRST NAME,recordedBy,/_join/3/_join_words/1,


  		




  12
  
  
    
COLLECTOR 3 LAST NAME,recordedBy,/_join/3/_join_words/2,


  		




  13
  
  
    
COLLECTOR 4 FIRST NAME,recordedBy,/_join/4/_join_words/1,


  		




  14
  
  
    
COLLECTOR 4 LAST NAME,recordedBy,/_join/4/_join_words/2,


  		




  15
  
  
    
COLLECTOR 5 FIRST NAME,recordedBy,/_join/5/_join_words/1,


  		




  16
  
  
    
COLLECTOR 5 LAST NAME,recordedBy,/_join/5/_join_words/2,


  		




  
  5
  
    
EXACT DATE,dateCollected__scrubbed,,


  		




  
  6
  
    
GENERAL DATE,dateCollected__verbatim,,


  		




  
  7
  
    
COLLECTOR 1 FIRST NAME,recordedBy__primary__first,,


  		




  
  8
  
    
COLLECTOR 1 LAST NAME,recordedBy__primary__last,,


  		




  
  9
  
    
COLLECTOR 2 FIRST NAME,recordedBy__additional__1__first,,


  		




  
  10
  
    
COLLECTOR 2 LAST NAME,recordedBy__additional__1__last,,


  		




  
  11
  
    
COLLECTOR 3 FIRST NAME,recordedBy__additional__2__first,,


  		




  
  12
  
    
COLLECTOR 3 LAST NAME,recordedBy__additional__2__last,,


  		




  
  13
  
    
COLLECTOR 4 FIRST NAME,recordedBy__additional__3__first,,


  		




  
  14
  
    
COLLECTOR 4 LAST NAME,recordedBy__additional__3__last,,


  		




  
  15
  
    
COLLECTOR 5 FIRST NAME,recordedBy__additional__4__first,,


  		




  
  16
  
    
COLLECTOR 5 LAST NAME,recordedBy__additional__4__last,,


  		




  17
  17
  
    
MISC NOTES,locationRemarks,,


  		




  18
  18
  
    
ORIGIN,establishmentMeans,,


  		




  19
  19
  
    
DETERMINER FIRST NAME,identifiedBy,,


  		












  

    
      inputs/TEX/Specimen2/unmapped_terms.csv
    
  





  2
  2
  
    
barcode


  		




  3
  3
  
    
*SUBSPECIES AUTHOR


  		




  4
  4
  
    
*VARIETY AUTHOR


  		




  
  5
  
    
dateCollected__scrubbed


  		




  
  6
  
    
dateCollected__verbatim


  		




  
  7
  
    
recordedBy__primary__first


  		




  
  8
  
    
recordedBy__primary__last


  		




  
  9
  
    
recordedBy__additional__1__first


  		




  
  10
  
    
recordedBy__additional__1__last


  		




  
  11
  
    
recordedBy__additional__2__first


  		




  
  12
  
    
recordedBy__additional__2__last


  		




  
  13
  
    
recordedBy__additional__3__first


  		




  
  14
  
    
recordedBy__additional__3__last


  		




  
  15
  
    
recordedBy__additional__4__first


  		




  
  16
  
    
recordedBy__additional__4__last


  		




  5
  17
  
    
*LATLONG SOURCE


  		




  6
  18
  
    
*LATLONG DATUM


  		




  7
  19
  
    
*DATABASED BY


  		




  8
  20
  
    
*UTM


  		




  9
  21
  
    
*DETERMINER INSTITUTION


  		




  10
  22
  
    
identificationRemarks


  		




  
  23
  
    
recordedBy__primary


  		




  
  24
  
    
recordedBy__additional__1


  		




  
  25
  
    
recordedBy__additional__2


  		




  
  26
  
    
recordedBy__additional__3


  		




  
  27
  
    
recordedBy__additional__4


  		












Also available in:  Unified diff