Project

General

Profile

« Previous | Next » 

Revision 10560

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