Project

General

Profile

« Previous | Next » 

Revision 10560

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/NY/Ecatalog_all/: 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/NY/Ecatalog_all/postprocess.sql
1 
SELECT util.search_path_append('util');
2 

  		




  
  3
  
    

  		




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


  		




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


  		




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


  		




  
  7
  
    

  		




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


  		




  
  9
  
    
$$_join("Notes", "PlantFungDescription")$$)


  		




  
  10
  
    
; -- runtime: 45 s ("Time: 44831.053 ms") @starscream


  		












  

    
      inputs/NY/Ecatalog_all/map.csv
    
  





  33
  33
  
    
Country,country,,


  		




  34
  34
  
    
StateProvince,stateProvince,,


  		




  35
  35
  
    
County,county,,


  		




  36
  
  
    
Locality,locality,/_join/1,


  		




  
  36
  
    
Locality,locality__main,,


  		




  37
  37
  
    
Longitude,decimalLongitude,,


  		




  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."


  		




  ......




  48
  48
  
    
PreviousCatalogNumber,otherCatalogNumbers,,


  		




  49
  49
  
    
RelationshipType,relationshipOfResource,,


  		




  50
  50
  
    
RelatedCatalogItem,relatedResourceID,,


  		




  51
  
  
    
Notes,occurrenceRemarks,/_join/1,


  		




  
  51
  
    
Notes,occurrenceRemarks__main,,


  		




  52
  52
  
    
Habitat,habitat,,


  		




  53
  
  
    
PlantFungDescription,occurrenceRemarks,/_join/2,"Contains the specimenDescription. PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant."


  		




  54
  
  
    
Substrate,locality,"/_join/2/_label[label=""substrate""]/value",


  		




  55
  
  
    
Vegetation,locality,"/_join/3/_label[label=""vegetation""]/value",


  		




  
  53
  
    
PlantFungDescription,specimenDescription,,"PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant"


  		




  
  54
  
    
Substrate,substrate,,


  		




  
  55
  
    
Vegetation,vegetation,,


  		




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


  		




  
  57
  
    
locality,locality,,


  		




  
  58
  
    
occurrenceRemarks,occurrenceRemarks,,


  		












  

    
      inputs/NY/Ecatalog_all/VegBIEN.csv
    
  





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


  		




  10
  10
  
    
MaximumElevation,/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
  
  
    
Locality,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1/_join/1,


  		




  13
  
  
    
Substrate,"/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1/_join/2/_label[label=""substrate""]/value",


  		




  14
  
  
    
Vegetation,"/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1/_join/3/_label[label=""vegetation""]/value",


  		




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


  		




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


  		




  16
  14
  
    
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",


  		




  17
  15
  
    
MonthIdentified,"/location/locationevent/taxonoccurrence/_if[@name=""if has accepted name""]/then/taxondetermination:[determinationtype=accepted]/determinationdate/_alt/2/_date/month/_nullIf:[null=0,type=float]/value",


  		




  ......




  20
  18
  
    
MonthCollected,"/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/2/_date/month/_nullIf:[null=0,type=float]/value",


  		




  21
  19
  
    
YearCollected,"/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/2/_date/year/_nullIf:[null=0,type=float]/value",


  		




  22
  20
  
    
IndividualCount,/location/locationevent/taxonoccurrence/aggregateoccurrence/count,


  		




  23
  
  
    
Notes,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes/_join/1/_join/1,


  		




  24
  
  
    
PlantFungDescription,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes/_join/1/_join/2,"Contains the specimenDescription. PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant."


  		




  
  21
  
    
occurrenceRemarks,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes/_join/1,


  		




  25
  22
  
    
FieldNumber,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/authorplantcode/_first/2,Usage inconsistent with DwC definition


  		




  26
  23
  
    
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"


  		




  27
  24
  
    
CatalogNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/else",


  		




  ......




  61
  58
  
    
FieldNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/collectionnumber/_if[@name=""if indirect voucher""]/then",Usage inconsistent with DwC definition


  		




  62
  59
  
    
MinimumDepth,/location/locationevent/waterdepth_m/_avg/1,


  		




  63
  60
  
    
MaximumDepth,/location/locationevent/waterdepth_m/_avg/2,


  		




  64
  
  
    
Locality,/location/locationnarrative/_merge/1/_join/1,


  		




  65
  
  
    
Substrate,"/location/locationnarrative/_merge/1/_join/2/_label[label=""substrate""]/value",


  		




  66
  
  
    
Vegetation,"/location/locationnarrative/_merge/1/_join/3/_label[label=""vegetation""]/value",


  		




  
  61
  
    
locality,/location/locationnarrative/_merge/1,


  		




  67
  62
  
    
Habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?"


  		




  68
  63
  
    
CoordinatePrecision,/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value,"Usage inconsistent with DwC definition, which only allows degrees. Assuming units based on the range and precision of values."


  		




  69
  64
  
    
BoundingBox,/location/locationplace/*_id/place/*_id/coordinates/footprintgeometry_dwc,


  		




  ......




  82
  77
  
    
CollectorNumber,,** No non-empty join mapping for UNUSED#CollectorNumber ** 


  		




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


  		




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


  		




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


  		




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


  		




  
  82
  
    
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"


  		




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


  		




  86
  84
  
    
PreviousCatalogNumber,,** No join mapping for otherCatalogNumbers ** 


  		




  87
  85
  
    
RelatedCatalogItem,,** No join mapping for relatedResourceID ** 


  		




  88
  86
  
    
RelationshipType,,** No join mapping for relationshipOfResource ** 


  		




  
  87
  
    
Substrate,,** No join mapping for substrate ** 


  		




  89
  88
  
    
TimeOfDay,,** No join mapping for eventTime ** 


  		




  90
  89
  
    
TypeStatus,,** No join mapping for typeStatus ** 


  		




  91
  90
  
    
UniqueNYInternalRecordNumber,,** No non-empty join mapping for OMIT#UniqueNYInternalRecordNumber ** Sometimes duplicated. Omitted to avoid confusion since this is an internal-only ID.


  		




  
  91
  
    
Vegetation,,** No join mapping for vegetation ** 


  		




  92
  92
  
    
key,,"** No non-empty join mapping for OMIT#key ** CatalogNumber, etc. is a better pkey"


  		




  93
  93
  
    
pkey,,"** No non-empty join mapping for OMIT#pkey ** CatalogNumber, etc. is a better pkey"


  		












  

    
      inputs/NY/Ecatalog_all/test.xml.ref
    
  





  29
  29
  
    
                            <_locationnarrative_is_cultivated>


  		




  30
  30
  
    
                                <locationnarrative>


  		




  31
  31
  
    
                                    <_join>


  		




  32
  
  
    
                                        <1>


  		




  33
  
  
    
                                            <_join>


  		




  34
  
  
    
                                                <1>$Locality</1>


  		




  35
  
  
    
                                                <2>


  		




  36
  
  
    
                                                    <_label>


  		




  37
  
  
    
                                                        <label>substrate</label>


  		




  38
  
  
    
                                                        <value>$Substrate</value>


  		




  39
  
  
    
                                                    </_label>


  		




  40
  
  
    
                                                </2>


  		




  41
  
  
    
                                                <3>


  		




  42
  
  
    
                                                    <_label>


  		




  43
  
  
    
                                                        <label>vegetation</label>


  		




  44
  
  
    
                                                        <value>$Vegetation</value>


  		




  45
  
  
    
                                                    </_label>


  		




  46
  
  
    
                                                </3>


  		




  47
  
  
    
                                            </_join>


  		




  48
  
  
    
                                        </1>


  		




  
  32
  
    
                                        <1>$locality</1>


  		




  49
  33
  
    
                                        <3>$Habitat</3>


  		




  50
  34
  
    
                                    </_join>


  		




  51
  35
  
    
                                </locationnarrative>


  		




  ......




  80
  64
  
    
                                        </_date>


  		




  81
  65
  
    
                                    </collectiondate>


  		




  82
  66
  
    
                                    <count>$IndividualCount</count>


  		




  83
  
  
    
                                    <notes>


  		




  84
  
  
    
                                        <_join>


  		




  85
  
  
    
                                            <1>$Notes</1>


  		




  86
  
  
    
                                            <2>$PlantFungDescription</2>


  		




  87
  
  
    
                                        </_join>


  		




  88
  
  
    
                                    </notes>


  		




  
  67
  
    
                                    <notes>$occurrenceRemarks</notes>


  		




  89
  68
  
    
                                    <plantobservation>


  		




  90
  69
  
    
                                        <authorplantcode>$FieldNumber</authorplantcode>


  		




  91
  70
  
    
                                        <definedvalue fkey="tablerecord_id">


  		




  ......




  258
  237
  
    
                        </locationevent>


  		




  259
  238
  
    
                        <locationnarrative>


  		




  260
  239
  
    
                            <_merge>


  		




  261
  
  
    
                                <1>


  		




  262
  
  
    
                                    <_join>


  		




  263
  
  
    
                                        <1>$Locality</1>


  		




  264
  
  
    
                                        <2>


  		




  265
  
  
    
                                            <_label>


  		




  266
  
  
    
                                                <label>substrate</label>


  		




  267
  
  
    
                                                <value>$Substrate</value>


  		




  268
  
  
    
                                            </_label>


  		




  269
  
  
    
                                        </2>


  		




  270
  
  
    
                                        <3>


  		




  271
  
  
    
                                            <_label>


  		




  272
  
  
    
                                                <label>vegetation</label>


  		




  273
  
  
    
                                                <value>$Vegetation</value>


  		




  274
  
  
    
                                            </_label>


  		




  275
  
  
    
                                        </3>


  		




  276
  
  
    
                                    </_join>


  		




  277
  
  
    
                                </1>


  		




  
  240
  
    
                                <1>$locality</1>


  		




  278
  241
  
    
                                <3>


  		




  279
  242
  
    
                                    <_label>


  		




  280
  243
  
    
                                        <label>habitat</label>


  		












  

    
      inputs/NY/Ecatalog_all/new_terms.csv
    
  





  1
  1
  
    
pkey,OMIT#pkey,,"CatalogNumber, etc. is a better pkey"


  		




  2
  2
  
    
key,OMIT#key,,"CatalogNumber, etc. is a better pkey"


  		




  3
  
  
    
PlantFungDescription,occurrenceRemarks,/_join/2,"Contains the specimenDescription. PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant."


  		




  4
  
  
    
Substrate,locality,"/_join/2/_label[label=""substrate""]/value",


  		




  5
  
  
    
Vegetation,locality,"/_join/3/_label[label=""vegetation""]/value",


  		




  
  3
  
    
PlantFungDescription,specimenDescription,,"PlantFung confusingly refers to the plant/fungus the specimen came from, rather than to a fungus growing on the plant"


  		




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


  		












  

    
      inputs/NY/Ecatalog_all/unmapped_terms.csv
    
  





  3
  3
  
    
typeStatus


  		




  4
  4
  
    
dayOfYear


  		




  5
  5
  
    
eventTime


  		




  
  6
  
    
locality__main


  		




  6
  7
  
    
preparations


  		




  7
  8
  
    
otherCatalogNumbers


  		




  8
  9
  
    
relationshipOfResource


  		




  9
  10
  
    
relatedResourceID


  		




  
  11
  
    
occurrenceRemarks__main


  		




  
  12
  
    
specimenDescription


  		




  
  13
  
    
substrate


  		




  
  14
  
    
vegetation


  		












Also available in:  Unified diff