Project

General

Profile

« Previous | Next » 

Revision 10528

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/SpeciesLink/Specimen/: translated multi-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/SpeciesLink/Specimen/postprocess.sql
16 16 
ORDER BY dataprovider
17 17 
*/
18 18 
;
19 

  		




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


  		




  
  21
  
    
$$_alt("dwc_terms_recordNumber", "dwc_terms_fieldNumber")$$)


  		




  
  22
  
    
; -- runtime: 3 min ("Time: 177696.140 ms") @starscream


  		












  

    
      inputs/SpeciesLink/Specimen/map.csv
    
  





  48
  48
  
    
dwc_terms_typeStatus,typeStatus,,


  		




  49
  49
  
    
dwc_terms_catalogNumber,accessionNumber,,


  		




  50
  50
  
    
dwc_terms_occurrenceRemarks,occurrenceRemarks,,


  		




  51
  
  
    
dwc_terms_recordNumber,collectorNumber,/_alt/1,


  		




  
  51
  
    
dwc_terms_recordNumber,collectorNumber_from_recordNumber,,


  		




  52
  52
  
    
dwc_terms_recordedBy,recordedBy,,


  		




  53
  53
  
    
dwc_terms_individualCount,individualCount,,


  		




  54
  54
  
    
dwc_terms_sex,sex,,


  		




  ......




  60
  60
  
    
dwc_terms_year,yearCollected,,


  		




  61
  61
  
    
dwc_terms_month,monthCollected,,


  		




  62
  62
  
    
dwc_terms_day,dayCollected,,


  		




  63
  
  
    
dwc_terms_fieldNumber,collectorNumber,/_alt/2,"Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  63
  
    
dwc_terms_fieldNumber,collectorNumber_from_fieldNumber,,"Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  64
  64
  
    
dwc_terms_continent,continent,,


  		




  65
  65
  
    
dwc_terms_country,country,,


  		




  66
  66
  
    
dwc_terms_stateProvince,stateProvince,,


  		




  ......




  133
  133
  
    
conceptual_darwin_2003_1_0_RelationshipType,relationshipOfResource,,


  		




  134
  134
  
    
conceptual_darwin_2003_1_0_RelatedCatalogItem,relatedResourceID,,


  		




  135
  135
  
    
conceptual_darwin_2003_1_0_Notes,DUPLICATE#of:dwc_terms_occurrenceRemarks#conceptual_darwin_2003_1_0_Notes,,exact duplicate


  		




  
  136
  
    
collectorNumber,collectorNumber,,


  		












  

    
      inputs/SpeciesLink/Specimen/VegBIEN.csv
    
  





  12
  12
  
    
dwc_terms_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",


  		




  13
  13
  
    
dwc_terms_minimumElevationInMeters,/location/elevation_m/_alt/2/_avg/1,


  		




  14
  14
  
    
dwc_terms_maximumElevationInMeters,/location/elevation_m/_alt/2/_avg/2,


  		




  
  15
  
    
collectorNumber,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  15
  16
  
    
dwc_dwcore_GlobalUniqueIdentifier,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  16
  
  
    
dwc_terms_recordNumber,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists/_alt/1",


  		




  17
  
  
    
dwc_terms_fieldNumber,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists/_alt/2","Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  18
  17
  
    
dwc_terms_locality,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1,


  		




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


  		




  20
  19
  
    
conceptual_darwin_2003_1_0_DayIdentified,"/location/locationevent/taxonoccurrence/_if[@name=""if has accepted name""]/then/taxondetermination:[determinationtype=accepted]/determinationdate/_alt/2/_date/day/_nullIf:[null=0,type=float]/value",


  		




  ......




  25
  24
  
    
dwc_terms_year,"/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/2/_date/year/_nullIf:[null=0,type=float]/value",


  		




  26
  25
  
    
dwc_terms_individualCount,/location/locationevent/taxonoccurrence/aggregateoccurrence/count,


  		




  27
  26
  
    
dwc_terms_occurrenceRemarks,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes/_join/1,


  		




  28
  
  
    
dwc_terms_recordNumber,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/authorplantcode/_first/2/_alt/1,


  		




  29
  
  
    
dwc_terms_fieldNumber,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/authorplantcode/_first/2/_alt/2,"Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  27
  
    
collectorNumber,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/authorplantcode/_first/2,


  		




  30
  28
  
    
dwc_terms_sex,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/definedvalue[*_id/userdefined[tablename=plantobservation,userdefinedname=sex]]:[@fkey=tablerecord_id]/definedvalue","Brad: OMIT. Apparently not used by this institution and of uncertain applicability to plants. With other collections, *sometimes* used for phenological information (if specimen in flower, in fruit, sterile, etc.). If so, this is important information we should keep. Bob, Nick, anywhere for phenological information in VB and VX?; Aaron: Keeping it because it's useful for some collections"


  		




  31
  29
  
    
dwc_terms_catalogNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/else",


  		




  32
  30
  
    
dwc_terms_collectionCode,/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


  		




  33
  31
  
    
dwc_terms_collectionID,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectioncode_dwc/_alt/2,


  		




  34
  
  
    
dwc_terms_recordNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/else/_alt/1",


  		




  35
  
  
    
dwc_terms_fieldNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/else/_alt/2","Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  32
  
    
collectorNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/else",


  		




  36
  33
  
    
dwc_dwcore_GlobalUniqueIdentifier,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/institution_id/sourcelist/_if[@name=""if specimen""]/cond/_exists",


  		




  37
  34
  
    
dwc_terms_catalogNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/institution_id/sourcelist/_if[@name=""if specimen""]/cond/_exists",


  		




  38
  35
  
    
dwc_terms_institutionCode,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/institution_id/sourcelist/_if[@name=""if specimen""]/then/name/_first/2",


  		




  39
  36
  
    
dwc_terms_institutionCode,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/institution_id/sourcelist/_if[@name=""if specimen""]/then/sourcename/name/_first/2/_split:[separator=""[,;] *""]/value",


  		




  40
  37
  
    
dwc_dwcore_GlobalUniqueIdentifier,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/sourceaccessioncode,


  		




  41
  
  
    
dwc_terms_recordNumber,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/3/_alt/1",


  		




  42
  
  
    
dwc_terms_fieldNumber,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/3/_alt/2","Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  38
  
    
collectorNumber,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/3",


  		




  43
  39
  
    
dwc_terms_scientificName,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2",


  		




  44
  40
  
    
dwc_terms_recordedBy,/location/locationevent/taxonoccurrence/collector_id/party/fullname,


  		




  
  41
  
    
collectorNumber,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  45
  42
  
    
dwc_dwcore_GlobalUniqueIdentifier,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  46
  
  
    
dwc_terms_recordNumber,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists/_alt/1",


  		




  47
  
  
    
dwc_terms_fieldNumber,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists/_alt/2","Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  48
  43
  
    
dwc_dwcore_GlobalUniqueIdentifier,/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/3,


  		




  49
  44
  
    
dwc_terms_identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/fullname,


  		




  50
  45
  
    
conceptual_darwin_2003_1_0_Subspecies,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=subspecies]/taxonepithet",


  		




  ......




  74
  69
  
    
conceptual_darwin_2003_1_0_MonthIdentified,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/2/_date/month/_nullIf:[null=0,type=float]/value",


  		




  75
  70
  
    
conceptual_darwin_2003_1_0_YearIdentified,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/2/_date/year/_nullIf:[null=0,type=float]/value",


  		




  76
  71
  
    
dwc_terms_catalogNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then",


  		




  77
  
  
    
dwc_terms_recordNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/then/_alt/1",


  		




  78
  
  
    
dwc_terms_fieldNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/then/_alt/2","Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  72
  
    
collectorNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/then",


  		




  79
  73
  
    
dwc_terms_minimumDepthInMeters,/location/locationevent/waterdepth_m/_avg/1,


  		




  80
  74
  
    
dwc_terms_maximumDepthInMeters,/location/locationevent/waterdepth_m/_avg/2,


  		




  81
  75
  
    
dwc_terms_locality,/location/locationnarrative/_merge/1,


  		




  ......




  177
  171
  
    
dwc_terms_basisOfRecord,,** No join mapping for basisOfRecord ** 


  		




  178
  172
  
    
dwc_terms_endDayOfYear,,** No non-empty join mapping for UNUSED#dwc_terms_endDayOfYear ** 


  		




  179
  173
  
    
dwc_terms_eventTime,,** No join mapping for eventTime ** 


  		




  
  174
  
    
dwc_terms_fieldNumber,,"** No join mapping for collectorNumber_from_fieldNumber ** Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  180
  175
  
    
dwc_terms_otherCatalogNumbers,,** No join mapping for otherCatalogNumbers ** 


  		




  181
  176
  
    
dwc_terms_preparations,,** No join mapping for preparations ** 


  		




  
  177
  
    
dwc_terms_recordNumber,,** No join mapping for collectorNumber_from_recordNumber ** 


  		




  182
  178
  
    
dwc_terms_startDayOfYear,,** No non-empty join mapping for UNUSED#dwc_terms_startDayOfYear ** 


  		




  183
  179
  
    
dwc_terms_typeStatus,,** No join mapping for typeStatus ** 


  		




  184
  180
  
    
http__purl_org_dc_terms_modified,,** No join mapping for modified ** 


  		












  

    
      inputs/SpeciesLink/Specimen/test.xml.ref
    
  





  62
  62
  
    
                                    <count>$dwc_terms_individualCount</count>


  		




  63
  63
  
    
                                    <notes>$dwc_terms_occurrenceRemarks</notes>


  		




  64
  64
  
    
                                    <plantobservation>


  		




  65
  
  
    
                                        <authorplantcode>


  		




  66
  
  
    
                                            <_alt>


  		




  67
  
  
    
                                                <1>$dwc_terms_recordNumber</1>


  		




  68
  
  
    
                                                <2>$dwc_terms_fieldNumber</2>


  		




  69
  
  
    
                                            </_alt>


  		




  70
  
  
    
                                        </authorplantcode>


  		




  
  65
  
    
                                        <authorplantcode>$collectorNumber</authorplantcode>


  		




  71
  66
  
    
                                        <definedvalue fkey="tablerecord_id">


  		




  72
  67
  
    
                                            <userdefined_id>


  		




  73
  68
  
    
                                                <userdefined>


  		




  ......




  85
  80
  
    
                                                    <2>$dwc_terms_collectionID</2>


  		




  86
  81
  
    
                                                </_alt>


  		




  87
  82
  
    
                                            </collectioncode_dwc>


  		




  88
  
  
    
                                            <collectionnumber>


  		




  89
  
  
    
                                                <_alt>


  		




  90
  
  
    
                                                    <1>$dwc_terms_recordNumber</1>


  		




  91
  
  
    
                                                    <2>$dwc_terms_fieldNumber</2>


  		




  92
  
  
    
                                                </_alt>


  		




  93
  
  
    
                                            </collectionnumber>


  		




  
  83
  
    
                                            <collectionnumber>$collectorNumber</collectionnumber>


  		




  94
  84
  
    
                                            <institution_id>


  		




  95
  85
  
    
                                                <sourcelist>


  		




  96
  86
  
    
                                                    <name>$dwc_terms_institutionCode</name>


  		












  

    
      inputs/SpeciesLink/Specimen/new_terms.csv
    
  





  45
  45
  
    
dwc_terms_typeStatus,typeStatus,,


  		




  46
  46
  
    
dwc_terms_catalogNumber,accessionNumber,,


  		




  47
  47
  
    
dwc_terms_occurrenceRemarks,occurrenceRemarks,,


  		




  48
  
  
    
dwc_terms_recordNumber,collectorNumber,/_alt/1,


  		




  
  48
  
    
dwc_terms_recordNumber,collectorNumber_from_recordNumber,,


  		




  49
  49
  
    
dwc_terms_recordedBy,recordedBy,,


  		




  50
  50
  
    
dwc_terms_individualCount,individualCount,,


  		




  51
  51
  
    
dwc_terms_sex,sex,,


  		




  ......




  55
  55
  
    
dwc_terms_year,yearCollected,,


  		




  56
  56
  
    
dwc_terms_month,monthCollected,,


  		




  57
  57
  
    
dwc_terms_day,dayCollected,,


  		




  58
  
  
    
dwc_terms_fieldNumber,collectorNumber,/_alt/2,"Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  
  58
  
    
dwc_terms_fieldNumber,collectorNumber_from_fieldNumber,,"Usage inconsistent with DwC definition. This field instead means the same thing as recordNumber, and values are stored under either of the two terms."


  		




  59
  59
  
    
dwc_terms_continent,continent,,


  		




  60
  60
  
    
dwc_terms_country,country,,


  		




  61
  61
  
    
dwc_terms_stateProvince,stateProvince,,


  		












  

    
      inputs/SpeciesLink/Specimen/unmapped_terms.csv
    
  





  2
  2
  
    
modified


  		




  3
  3
  
    
basisOfRecord


  		




  4
  4
  
    
typeStatus


  		




  
  5
  
    
collectorNumber_from_recordNumber


  		




  5
  6
  
    
preparations


  		




  6
  7
  
    
otherCatalogNumbers


  		




  7
  8
  
    
eventTime


  		




  
  9
  
    
collectorNumber_from_fieldNumber


  		




  8
  10
  
    
relationshipOfResource


  		




  9
  11
  
    
relatedResourceID


  		












Also available in:  Unified diff