Project

General

Profile

« Previous | Next » 

Revision 11095

bugfix: inputs/VegBank/: taxonOccurrenceID: include the aggregateOrganismObservationID in this so that there is one taxonoccurrence for each stratum's taxonImportance. this allows the different strata to have separate taxonoccurrences that are associated with the stratum-specific locationevents, rather than all being lumped into one taxonoccurrence, with only one locationevent.

View differences:

inputs/VegBank/stemcount_/postprocess.sql
1 1
SELECT util.search_path_append('util');
2 2

  
3
SELECT create_if_not_exists($$CREATE INDEX $$||:table_str||%'__parent'||$$ ON $$||:table_str||$$ ("taxonOccurrenceID")$$); -- runtime: 2 s ("Time: 1766.315 ms") @starscream
3
SELECT create_if_not_exists($$CREATE INDEX $$||:table_str||%'__parent'||$$ ON $$||:table_str||$$ ("taxonOccurrenceID__overall_plot")$$); -- runtime: 2 s ("Time: 1766.315 ms") @starscream
4 4

  
5 5

  
6 6
SELECT mk_derived_col((:table_str, 'aggregateOrganismObservationID'),
inputs/VegBank/stemcount_/map.csv
1 1
VegBank,VegCore,Filter,Comments
2 2
taxonimportance_id,aggregateOrganismObservationID__taxon_presence,,
3
taxonobservation_id,taxonOccurrenceID,,"Note that taxonimportance is not 1:1 with taxonobservation [1].
3
taxonobservation_id,taxonOccurrenceID__overall_plot,,"Note that taxonimportance is not 1:1 with taxonobservation [1].
4 4

  
5 5
[1] The following query returns rows:
6 6
-----
inputs/VegBank/stemcount_/test.xml.ref
20 20
                                    </plantobservation>
21 21
                                    <sourceaccessioncode>$aggregateOrganismObservationID</sourceaccessioncode>
22 22
                                </aggregateoccurrence>
23
                                <sourceaccessioncode>$taxonOccurrenceID</sourceaccessioncode>
24 23
                            </taxonoccurrence>
25 24
                        </locationevent>
26 25
                    </location>
......
29 28
        </path>
30 29
    </_setDefault>
31 30
</VegBIEN>
32
Inserted 7 new rows into database
31
Inserted 5 new rows into database
inputs/VegBank/stemcount_/unmapped_terms.csv
1 1
aggregateOrganismObservationID__taxon_presence
2
taxonOccurrenceID__overall_plot
2 3
*stratum_id
3 4
*stemcount_--basalarea
4 5
*stemcount_--biomass
inputs/VegBank/taxonobservation_/map.csv
1 1
VegBank,VegCore,Filter,Comments
2 2
plantname_id,*taxonobservation_--plantname_id,,
3 3
plantconcept_id,*taxonobservation_--plantconcept_id,,
4
taxonobservation_id,taxonOccurrenceID,,
4
taxonobservation_id,taxonOccurrenceID__overall_plot,,
5 5
taxoninterpretation_id,identificationID,,
6 6
stemlocation_id,*taxonobservation_--stemlocation_id,,
7 7
taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_plantname_id,,
inputs/VegBank/taxonobservation_/test.xml.ref
22 22
                                    </plantobservation>
23 23
                                </aggregateoccurrence>
24 24
                                <authortaxoncode>$collectorNumber</authortaxoncode>
25
                                <sourceaccessioncode>$taxonOccurrenceID</sourceaccessioncode>
26 25
                                <taxondetermination>
27 26
                                    <taxonverbatim_id>
28 27
                                        <taxonverbatim>
......
137 136
        </path>
138 137
    </_setDefault>
139 138
</VegBIEN>
140
Inserted 17 new rows into database
139
Inserted 13 new rows into database
inputs/VegBank/taxonobservation_/unmapped_terms.csv
1 1
*taxonobservation_--plantname_id
2 2
*taxonobservation_--plantconcept_id
3
taxonOccurrenceID__overall_plot
3 4
identificationID
4 5
*taxonobservation_--stemlocation_id
5 6
*taxonobservation_--taxoninterpretation_plantname_id
inputs/VegBank/taxon_observation.**/header.csv
1
sort_col,locationID,eventID,*stratumtype_id,*stratum_id,aggregateOrganismObservationID__taxon_presence,aggregateOrganismObservationID__individual_count,aggregateOrganismObservationID,taxonOccurrenceID,*taxonobservation_--plantname_id,*taxonobservation_--plantconcept_id,identificationID,*taxonobservation_--stemlocation_id,*taxonobservation_--taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_party_id,*taxonobservation_--role_id,identificationType,*taxonobservation_--reference_id,*taxonobservation_--originalinterpretation,*taxonobservation_--currentinterpretation,identificationQualifier,identificationVerificationStatus,UNUSED#collector_id,collectorNumber,*taxonobservation_--museum_id,accessionNumber,*taxonobservation_--grouptype,occurrenceRemarks,*taxonobservation_--notespublic,*taxonobservation_--notesmgt,*taxonobservation_--revisions,dateIdentified,dateCollected,*taxonobservation_--emb_taxoninterpretation,*taxonobservation_--taxoninterpretation_accessioncode,originalScientificName,*taxonobservation_--taxonobservation_reference_id,*taxonobservation_--taxoninferencearea,*taxonobservation_--emb_taxonobservation,*taxonobservation_--int_origplantconcept_id,EQUIV#to:authorplantname#int_origplantscifull,originalTaxonName,originalVernacularName,*taxonobservation_--int_origplantcode,*taxonobservation_--int_currplantconcept_id,scientificName_verbatim,taxonName,vernacularName,*taxonobservation_--int_currplantcode,*taxonobservation_--taxonobservation_accessioncode,*taxonobservation_--plantconcept_reference_id,scientificName__for_nearest_taxon_concept,*taxonobservation_--plantcode,*taxonobservation_--plantdescription,*taxonobservation_--d_obscount,*taxonobservation_--d_currentaccepted,*taxonobservation_--plantconcept_accessioncode,kingdom,*Subkingdom,*Superdivision,*Division,*Subdivision,class,*Subclass,order,family,genus,EQUIV#to:plantname#Species,EQUIV#to:plantname#Subspecies,EQUIV#to:plantname#Variety,EQUIV#to:plantname#Forma,DUPLICATE#of:plantconcept_plantname#plantname,*taxonobservation_--plantname_reference_id,*taxonobservation_--dateentered,observationGranularity,scientificName,coverPercent,*stemcount_--basalarea,*stemcount_--biomass,*stemcount_--inferencearea,*stemcount_--stratumbase,*stemcount_--stratumheight,*stemcount_--emb_taxonimportance,*stemcount_--covercode,diameterBreastHeight_cm,*stemcount_--stemdiameteraccuracy,height_m,*stemcount_--stemheightaccuracy,individualCount,*stemcount_--stemtaxonarea,*stemcount_--emb_stemcount,stemID,UNUSED#stemcode,UNUSED#stemxposition,UNUSED#stemyposition,*stemlocation_--stemhealth,*stemlocation_--emb_stemlocation,OMIT#stemdiameter,OMIT#stemdiameteraccuracy,OMIT#stemheight,OMIT#stemheightaccuracy,OMIT#stemcount,OMIT#stemtaxonarea,OMIT#emb_stemcount,individualID,*stratum--observation_id,*stratum--stratummethod_id,*stratum--stratumname,*stratum--stratumheight,*stratum--stratumbase,*stratum--stratumcover,*stratum--stratumdescription,*stratumtype--stratummethod_id,*stratumtype--stratumindex,stratum__name,*stratumtype--stratumdescription,*observation_--previousobs_id,projectID,authorEventCode,*observation_--dateaccuracy,*observation_--covermethod_id,*observation_--coverdispersion,*observation_--autotaxoncover,*observation_--stratummethod_id,samplingProtocol,*observation_--taxonobservationarea,*observation_--stemsizelimit,*observation_--stemobservationarea,*observation_--stemsamplemethod,*observation_--originaldata,*observation_--effortlevel,*observation_--plotvalidationlevel,*observation_--floristicquality,*observation_--bryophytequality,*observation_--lichenquality,*observation_--observationnarrative,*observation_--landscapenarrative,*observation_--homogeneity,*observation_--phenologicaspect,*observation_--representativeness,*observation_--standmaturity,*observation_--successionalstatus,*observation_--basalarea,*observation_--hydrologicregime,*observation_--soilmoistureregime,*observation_--soildrainage,*observation_--watersalinity,*observation_--waterdepth,*observation_--shoredistance,*observation_--soildepth,*observation_--organicdepth,*observation_--soiltaxon_id,*observation_--soiltaxonsrc,*observation_--percentbedrock,*observation_--percentrockgravel,*observation_--percentwood,*observation_--percentlitter,*observation_--percentbaresoil,*observation_--percentwater,*observation_--percentother,*observation_--nameother,*observation_--treeht,*observation_--shrubht,*observation_--fieldht,*observation_--nonvascularht,*observation_--submergedht,*observation_--treecover,*observation_--shrubcover,*observation_--fieldcover,*observation_--nonvascularcover,*observation_--floatingcover,*observation_--submergedcover,*observation_--dominantstratum,*observation_--growthform1type,*observation_--growthform2type,*observation_--growthform3type,*observation_--growthform1cover,*observation_--growthform2cover,*observation_--growthform3cover,*observation_--totalcover,*observation_--notespublic,*observation_--notesmgt,*observation_--revisions,startDate,endDate,*observation_--dateentered,*observation_--emb_observation,*observation_--interp_orig_ci_id,*observation_--interp_orig_cc_id,*observation_--interp_orig_sciname,*observation_--interp_orig_code,*observation_--interp_orig_party_id,*observation_--interp_orig_partyname,*observation_--interp_current_ci_id,*observation_--interp_current_cc_id,*observation_--interp_current_sciname,*observation_--interp_current_code,*observation_--interp_current_party_id,*observation_--interp_current_partyname,*observation_--interp_bestfit_ci_id,*observation_--interp_bestfit_cc_id,*observation_--interp_bestfit_sciname,*observation_--interp_bestfit_code,*observation_--interp_bestfit_party_id,*observation_--interp_bestfit_partyname,*observation_--toptaxon1name,*observation_--toptaxon2name,*observation_--toptaxon3name,*observation_--toptaxon4name,*observation_--toptaxon5name,*observation_--numberoftaxa,*observation_--accessioncode,*observation_--soilobs_id,*observation_--soilhorizon,*observation_--soildepthtop,*observation_--soildepthbottom,*observation_--soilcolor,organic_percent,texture,sand_percent,silt_percent,clay_percent,*observation_--soilcoarse,ph,cationExchangeCapacity_cmol_kg,baseSaturation_percent,*observation_--soildescription,*observation_--emb_soilobs,locationName,*plot_--reference_id,parentLocationID,coordinateUncertaintyInMeters__from_coords,accessRights_verbatim,*plot_--confidentialityreason,decimalLatitude,decimalLongitude,*plot_--authore,*plot_--authorn,*plot_--authorzone,*plot_--authordatum,*plot_--authorlocation,locality,*plot_--azimuth,*plot_--dsgpoly,footprintWKT,plotArea_m2,*plot_--standsize,*plot_--placementmethod,*plot_--permanence,*plot_--layoutnarrative,elevationInMeters,*plot_--elevationaccuracy,UNUSED#elevationrange,slopeAspect,minSlopeAspect,maxSlopeAspect,slopeGradient,minSlopeGradient,maxSlopeGradient,*plot_--topoposition,landform,*plot_--surficialdeposits,*plot_--rocktype,stateProvince,country,*plot_--submitter_surname,*plot_--submitter_givenname,*plot_--submitter_email,*plot_--notespublic,*plot_--notesmgt,*plot_--revisions,*plot_--dateentered,*plot_--emb_plot,*plot_--plotrationalenarrative,*plot_--accessioncode,continent,DUPLICATE#of:country#area|country|territory,DUPLICATE#of:stateprovince#region|state|province,county,*plot_--quadrangle,*plot_--Geographic Name,accessRights,coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters
1
sort_col,locationID,eventID,*stratumtype_id,*stratum_id,aggregateOrganismObservationID__taxon_presence,aggregateOrganismObservationID__individual_count,aggregateOrganismObservationID,taxonOccurrenceID__overall_plot,*taxonobservation_--plantname_id,*taxonobservation_--plantconcept_id,identificationID,*taxonobservation_--stemlocation_id,*taxonobservation_--taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_party_id,*taxonobservation_--role_id,identificationType,*taxonobservation_--reference_id,*taxonobservation_--originalinterpretation,*taxonobservation_--currentinterpretation,identificationQualifier,identificationVerificationStatus,UNUSED#collector_id,collectorNumber,*taxonobservation_--museum_id,accessionNumber,*taxonobservation_--grouptype,occurrenceRemarks,*taxonobservation_--notespublic,*taxonobservation_--notesmgt,*taxonobservation_--revisions,dateIdentified,dateCollected,*taxonobservation_--emb_taxoninterpretation,*taxonobservation_--taxoninterpretation_accessioncode,originalScientificName,*taxonobservation_--taxonobservation_reference_id,*taxonobservation_--taxoninferencearea,*taxonobservation_--emb_taxonobservation,*taxonobservation_--int_origplantconcept_id,EQUIV#to:authorplantname#int_origplantscifull,originalTaxonName,originalVernacularName,*taxonobservation_--int_origplantcode,*taxonobservation_--int_currplantconcept_id,scientificName_verbatim,taxonName,vernacularName,*taxonobservation_--int_currplantcode,*taxonobservation_--taxonobservation_accessioncode,*taxonobservation_--plantconcept_reference_id,scientificName__for_nearest_taxon_concept,*taxonobservation_--plantcode,*taxonobservation_--plantdescription,*taxonobservation_--d_obscount,*taxonobservation_--d_currentaccepted,*taxonobservation_--plantconcept_accessioncode,kingdom,*Subkingdom,*Superdivision,*Division,*Subdivision,class,*Subclass,order,family,genus,EQUIV#to:plantname#Species,EQUIV#to:plantname#Subspecies,EQUIV#to:plantname#Variety,EQUIV#to:plantname#Forma,DUPLICATE#of:plantconcept_plantname#plantname,*taxonobservation_--plantname_reference_id,*taxonobservation_--dateentered,observationGranularity,scientificName,coverPercent,*stemcount_--basalarea,*stemcount_--biomass,*stemcount_--inferencearea,*stemcount_--stratumbase,*stemcount_--stratumheight,*stemcount_--emb_taxonimportance,*stemcount_--covercode,diameterBreastHeight_cm,*stemcount_--stemdiameteraccuracy,height_m,*stemcount_--stemheightaccuracy,individualCount,*stemcount_--stemtaxonarea,*stemcount_--emb_stemcount,stemID,UNUSED#stemcode,UNUSED#stemxposition,UNUSED#stemyposition,*stemlocation_--stemhealth,*stemlocation_--emb_stemlocation,OMIT#stemdiameter,OMIT#stemdiameteraccuracy,OMIT#stemheight,OMIT#stemheightaccuracy,OMIT#stemcount,OMIT#stemtaxonarea,OMIT#emb_stemcount,individualID,*stratum--observation_id,*stratum--stratummethod_id,*stratum--stratumname,*stratum--stratumheight,*stratum--stratumbase,*stratum--stratumcover,*stratum--stratumdescription,*stratumtype--stratummethod_id,*stratumtype--stratumindex,stratum__name,*stratumtype--stratumdescription,*observation_--previousobs_id,projectID,authorEventCode,*observation_--dateaccuracy,*observation_--covermethod_id,*observation_--coverdispersion,*observation_--autotaxoncover,*observation_--stratummethod_id,samplingProtocol,*observation_--taxonobservationarea,*observation_--stemsizelimit,*observation_--stemobservationarea,*observation_--stemsamplemethod,*observation_--originaldata,*observation_--effortlevel,*observation_--plotvalidationlevel,*observation_--floristicquality,*observation_--bryophytequality,*observation_--lichenquality,*observation_--observationnarrative,*observation_--landscapenarrative,*observation_--homogeneity,*observation_--phenologicaspect,*observation_--representativeness,*observation_--standmaturity,*observation_--successionalstatus,*observation_--basalarea,*observation_--hydrologicregime,*observation_--soilmoistureregime,*observation_--soildrainage,*observation_--watersalinity,*observation_--waterdepth,*observation_--shoredistance,*observation_--soildepth,*observation_--organicdepth,*observation_--soiltaxon_id,*observation_--soiltaxonsrc,*observation_--percentbedrock,*observation_--percentrockgravel,*observation_--percentwood,*observation_--percentlitter,*observation_--percentbaresoil,*observation_--percentwater,*observation_--percentother,*observation_--nameother,*observation_--treeht,*observation_--shrubht,*observation_--fieldht,*observation_--nonvascularht,*observation_--submergedht,*observation_--treecover,*observation_--shrubcover,*observation_--fieldcover,*observation_--nonvascularcover,*observation_--floatingcover,*observation_--submergedcover,*observation_--dominantstratum,*observation_--growthform1type,*observation_--growthform2type,*observation_--growthform3type,*observation_--growthform1cover,*observation_--growthform2cover,*observation_--growthform3cover,*observation_--totalcover,*observation_--notespublic,*observation_--notesmgt,*observation_--revisions,startDate,endDate,*observation_--dateentered,*observation_--emb_observation,*observation_--interp_orig_ci_id,*observation_--interp_orig_cc_id,*observation_--interp_orig_sciname,*observation_--interp_orig_code,*observation_--interp_orig_party_id,*observation_--interp_orig_partyname,*observation_--interp_current_ci_id,*observation_--interp_current_cc_id,*observation_--interp_current_sciname,*observation_--interp_current_code,*observation_--interp_current_party_id,*observation_--interp_current_partyname,*observation_--interp_bestfit_ci_id,*observation_--interp_bestfit_cc_id,*observation_--interp_bestfit_sciname,*observation_--interp_bestfit_code,*observation_--interp_bestfit_party_id,*observation_--interp_bestfit_partyname,*observation_--toptaxon1name,*observation_--toptaxon2name,*observation_--toptaxon3name,*observation_--toptaxon4name,*observation_--toptaxon5name,*observation_--numberoftaxa,*observation_--accessioncode,*observation_--soilobs_id,*observation_--soilhorizon,*observation_--soildepthtop,*observation_--soildepthbottom,*observation_--soilcolor,organic_percent,texture,sand_percent,silt_percent,clay_percent,*observation_--soilcoarse,ph,cationExchangeCapacity_cmol_kg,baseSaturation_percent,*observation_--soildescription,*observation_--emb_soilobs,locationName,*plot_--reference_id,parentLocationID,coordinateUncertaintyInMeters__from_coords,accessRights_verbatim,*plot_--confidentialityreason,decimalLatitude,decimalLongitude,*plot_--authore,*plot_--authorn,*plot_--authorzone,*plot_--authordatum,*plot_--authorlocation,locality,*plot_--azimuth,*plot_--dsgpoly,footprintWKT,plotArea_m2,*plot_--standsize,*plot_--placementmethod,*plot_--permanence,*plot_--layoutnarrative,elevationInMeters,*plot_--elevationaccuracy,UNUSED#elevationrange,slopeAspect,minSlopeAspect,maxSlopeAspect,slopeGradient,minSlopeGradient,maxSlopeGradient,*plot_--topoposition,landform,*plot_--surficialdeposits,*plot_--rocktype,stateProvince,country,*plot_--submitter_surname,*plot_--submitter_givenname,*plot_--submitter_email,*plot_--notespublic,*plot_--notesmgt,*plot_--revisions,*plot_--dateentered,*plot_--emb_plot,*plot_--plotrationalenarrative,*plot_--accessioncode,continent,DUPLICATE#of:country#area|country|territory,DUPLICATE#of:stateprovince#region|state|province,county,*plot_--quadrangle,*plot_--Geographic Name,accessRights,coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters,taxonOccurrenceID
inputs/VegBank/taxon_observation.**/postprocess.sql
4 4
/* no row_num because left-join to stemcount_, stemlocation_ adds rows to each
5 5
taxonobservation_ */
6 6
SELECT "identificationID" AS sort_col, *
7
, _join("taxonOccurrenceID__overall_plot"::text, "aggregateOrganismObservationID") AS "taxonOccurrenceID"
7 8
FROM              taxonobservation_
8 9
NATURAL LEFT JOIN stemcount_
9 10
NATURAL LEFT JOIN stemlocation_
inputs/VegBank/taxon_observation.**/map.csv
7 7
aggregateOrganismObservationID__taxon_presence,aggregateOrganismObservationID__taxon_presence,,
8 8
aggregateOrganismObservationID__individual_count,aggregateOrganismObservationID__individual_count,,
9 9
aggregateOrganismObservationID,aggregateOrganismObservationID,,
10
taxonOccurrenceID,taxonOccurrenceID,,
10
taxonOccurrenceID__overall_plot,taxonOccurrenceID__overall_plot,,
11 11
*taxonobservation_--plantname_id,*taxonobservation_--plantname_id,,
12 12
*taxonobservation_--plantconcept_id,*taxonobservation_--plantconcept_id,,
13 13
identificationID,identificationID,,
......
283 283
accessRights,accessRights,,
284 284
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters__from_fuzzing,,
285 285
coordinateUncertaintyInMeters,coordinateUncertaintyInMeters,,
286
taxonOccurrenceID,taxonOccurrenceID,,
inputs/VegBank/taxon_observation.**/test.xml.ref
250 250
        </path>
251 251
    </_setDefault>
252 252
</VegBIEN>
253
Inserted 22 new rows into database
253
Inserted 26 new rows into database
inputs/VegBank/taxon_observation.**/unmapped_terms.csv
3 3
*stratum_id
4 4
aggregateOrganismObservationID__taxon_presence
5 5
aggregateOrganismObservationID__individual_count
6
taxonOccurrenceID__overall_plot
6 7
*taxonobservation_--plantname_id
7 8
*taxonobservation_--plantconcept_id
8 9
identificationID

Also available in: Unified diff