Revision 11601
Added by Aaron Marcuse-Kubitza about 11 years ago
| inputs/VegBank/plot_/postprocess.sql | ||
|---|---|---|
| 1 |
SELECT util.search_path_append('util');
|
|
| 2 |
|
|
| 3 |
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("locationID")$$); -- runtime: 0.2 s ("Time: 238.474 ms") @starscream
|
|
| 4 |
|
|
| 5 |
|
|
| 6 |
-- remove institutions that we have direct data for |
|
| 7 |
|
|
| 8 |
-- CVS |
|
| 9 |
DELETE FROM :table |
|
| 10 |
WHERE "locationName" IN (SELECT "authorPlotCode" FROM "CVS".plot) |
|
| 11 |
-- removes 2323 of 7079 CVS plots (75340 - 73017) |
|
| 12 |
; -- runtime: 0.1 s ("109.790 ms") @starscream
|
|
| 13 |
|
|
| 14 |
|
|
| 15 |
-- map_*() derived cols |
|
| 16 |
-- runtime: 2 s @starscream |
|
| 17 |
|
|
| 18 |
CREATE OR REPLACE FUNCTION map_access_rights(value integer) |
|
| 19 |
RETURNS text AS |
|
| 20 |
$BODY$ |
|
| 21 |
/* |
|
| 22 |
"closed List of Values: |
|
| 23 |
Value Description Sorting |
|
| 24 |
0 Public 1 |
|
| 25 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 26 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 27 |
3 100 km radius (nearest degree) 4 |
|
| 28 |
4 Location embargo 5 |
|
| 29 |
5 Public embargo on data 6 |
|
| 30 |
6 Full embargo on data 7 |
|
| 31 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 32 |
*/ |
|
| 33 |
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
|
|
| 34 |
$BODY$ |
|
| 35 |
LANGUAGE sql IMMUTABLE |
|
| 36 |
COST 100; |
|
| 37 |
|
|
| 38 |
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1167.547 ms") @starscream
|
|
| 39 |
|
|
| 40 |
CREATE OR REPLACE FUNCTION map_fuzzing(value integer) |
|
| 41 |
RETURNS double precision AS |
|
| 42 |
$BODY$ |
|
| 43 |
/* |
|
| 44 |
"closed List of Values: |
|
| 45 |
Value Description Sorting |
|
| 46 |
0 Public 1 |
|
| 47 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 48 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 49 |
3 100 km radius (nearest degree) 4 |
|
| 50 |
4 Location embargo 5 |
|
| 51 |
5 Public embargo on data 6 |
|
| 52 |
6 Full embargo on data 7 |
|
| 53 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 54 |
*/ |
|
| 55 |
SELECT public._km_to_m(util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision)
|
|
| 56 |
$BODY$ |
|
| 57 |
LANGUAGE sql IMMUTABLE |
|
| 58 |
COST 100; |
|
| 59 |
|
|
| 60 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1064.189 ms") @starscream
|
|
| 61 |
|
|
| 62 |
|
|
| 63 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'), |
|
| 64 |
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "coordinateUncertaintyInMeters__from_coords")$$)
|
|
| 65 |
; -- runtime: 0.5 s ("Time: 586.453 ms") @starscream
|
|
| inputs/VegBank/import_order.txt | ||
|---|---|---|
| 1 | 1 |
Source |
| 2 |
plot |
|
| 2 | 3 |
plot_ |
| 3 | 4 |
observation_ |
| 4 | 5 |
stratumtype |
| inputs/VegBank/plantconcept_/new_terms.csv | ||
|---|---|---|
| 1 |
plantconcept_reference_id,*plantconcept_--plantconcept_reference_id,, |
|
| 2 |
plantconcept_plantname,scientificName__for_nearest_taxon_concept,, |
|
| 3 |
plantcode,*plantconcept_--plantcode,, |
|
| 4 |
plantdescription,*plantconcept_--plantdescription,, |
|
| 5 |
d_obscount,*plantconcept_--d_obscount,, |
|
| 6 |
d_currentaccepted,*plantconcept_--d_currentaccepted,, |
|
| 7 |
plantconcept_accessioncode,*plantconcept_--plantconcept_accessioncode,, |
|
| inputs/VegBank/taxon_observation.**/header.csv | ||
|---|---|---|
| 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,*taxonobservation_--party_id,OMIT#authorplantname,*taxonobservation_--taxonobservation_reference_id,*taxonobservation_--taxoninferencearea,*taxonobservation_--emb_taxonobservation,OMIT#int_origplantconcept_id,OMIT#int_origplantscifull,OMIT#int_origplantscinamenoauth,OMIT#int_origplantcommon,OMIT#int_origplantcode,OMIT#int_currplantconcept_id,OMIT#int_currplantscifull,OMIT#int_currplantscinamenoauth,OMIT#int_currplantcommon,OMIT#int_currplantcode,*taxonobservation_--taxonobservation_accessioncode,identificationID,*taxonobservation_--stemlocation_id,*taxonobservation_--taxoninterpretation_plantname_id,*taxonobservation_--role_id,identificationType,*taxonobservation_--reference_id,taxon_determination__is_original,taxon_determination__is_current,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,*taxonobservation--party--salutation,identifiedBy__first,identifiedBy__middle,identifiedBy__last,*taxonobservation--party--organizationname,*taxonobservation--party--currentname_id,*taxonobservation--party--contactinstructions,*taxonobservation--party--email,*taxonobservation--party--partytype,*taxonobservation--party--partypublic,*taxonobservation--party--party_d_obscount,*taxonobservation--party--accessioncode,*plantconcept_--plantconcept_reference_id,scientificName,*plantconcept_--plantcode,*plantconcept_--plantdescription,*plantconcept_--d_obscount,*plantconcept_--d_currentaccepted,*plantconcept_--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,identifiedBy,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_--hasobservationsynonym,*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 |
|
| 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,*taxonobservation_--party_id,OMIT#authorplantname,*taxonobservation_--taxonobservation_reference_id,*taxonobservation_--taxoninferencearea,*taxonobservation_--emb_taxonobservation,OMIT#int_origplantconcept_id,OMIT#int_origplantscifull,OMIT#int_origplantscinamenoauth,OMIT#int_origplantcommon,OMIT#int_origplantcode,OMIT#int_currplantconcept_id,OMIT#int_currplantscifull,OMIT#int_currplantscinamenoauth,OMIT#int_currplantcommon,OMIT#int_currplantcode,*taxonobservation_--taxonobservation_accessioncode,identificationID,*taxonobservation_--stemlocation_id,*taxonobservation_--taxoninterpretation_plantname_id,*taxonobservation_--role_id,identificationType,*taxonobservation_--reference_id,taxon_determination__is_original,taxon_determination__is_current,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,*taxonobservation--party--salutation,identifiedBy__first,identifiedBy__middle,identifiedBy__last,*taxonobservation--party--organizationname,*taxonobservation--party--currentname_id,*taxonobservation--party--contactinstructions,*taxonobservation--party--email,*taxonobservation--party--partytype,*taxonobservation--party--partypublic,*taxonobservation--party--party_d_obscount,*taxonobservation--party--accessioncode,*plantconcept_--plantconcept_reference_id,scientificName,*plantconcept_--plantcode,*plantconcept_--plantdescription,*plantconcept_--d_obscount,*plantconcept_--d_currentaccepted,*plantconcept_--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,identifiedBy,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_--hasobservationsynonym,*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,accessRights,coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters,continent,DUPLICATE#of:country#area|country|territory,DUPLICATE#of:stateprovince#region|state|province,county,*plot_--quadrangle,*plot_--Geographic Name,taxonOccurrenceID |
|
| inputs/VegBank/taxon_observation.**/map.csv | ||
|---|---|---|
| 241 | 241 |
*observation_--soildescription,*observation_--soildescription,, |
| 242 | 242 |
*observation_--emb_soilobs,*observation_--emb_soilobs,, |
| 243 | 243 |
locationName,locationName,, |
| 244 |
*plot_--reference_id,*plot_--reference_id,,
|
|
| 244 |
*plot--reference_id,*plot--reference_id,,
|
|
| 245 | 245 |
parentLocationID,parentLocationID,, |
| 246 | 246 |
coordinateUncertaintyInMeters__from_coords,coordinateUncertaintyInMeters__from_coords,, |
| 247 | 247 |
accessRights_verbatim,accessRights_verbatim,, |
| 248 |
*plot_--confidentialityreason,*plot_--confidentialityreason,,
|
|
| 248 |
*plot--confidentialityreason,*plot--confidentialityreason,,
|
|
| 249 | 249 |
decimalLatitude,decimalLatitude,, |
| 250 | 250 |
decimalLongitude,decimalLongitude,, |
| 251 |
*plot_--authore,*plot_--authore,,
|
|
| 252 |
*plot_--authorn,*plot_--authorn,,
|
|
| 253 |
*plot_--authorzone,*plot_--authorzone,,
|
|
| 254 |
*plot_--authordatum,*plot_--authordatum,,
|
|
| 255 |
*plot_--authorlocation,*plot_--authorlocation,,
|
|
| 251 |
*plot--authore,*plot--authore,,
|
|
| 252 |
*plot--authorn,*plot--authorn,,
|
|
| 253 |
*plot--authorzone,*plot--authorzone,,
|
|
| 254 |
*plot--authordatum,*plot--authordatum,,
|
|
| 255 |
*plot--authorlocation,*plot--authorlocation,,
|
|
| 256 | 256 |
locality,locality,, |
| 257 |
*plot_--azimuth,*plot_--azimuth,,
|
|
| 258 |
*plot_--dsgpoly,*plot_--dsgpoly,,
|
|
| 257 |
*plot--azimuth,*plot--azimuth,,
|
|
| 258 |
*plot--dsgpoly,*plot--dsgpoly,,
|
|
| 259 | 259 |
footprintWKT,footprintWKT,, |
| 260 | 260 |
plotArea_m2,plotArea_m2,, |
| 261 |
*plot_--standsize,*plot_--standsize,,
|
|
| 262 |
*plot_--placementmethod,*plot_--placementmethod,,
|
|
| 263 |
*plot_--permanence,*plot_--permanence,,
|
|
| 264 |
*plot_--layoutnarrative,*plot_--layoutnarrative,,
|
|
| 261 |
*plot--standsize,*plot--standsize,,
|
|
| 262 |
*plot--placementmethod,*plot--placementmethod,,
|
|
| 263 |
*plot--permanence,*plot--permanence,,
|
|
| 264 |
*plot--layoutnarrative,*plot--layoutnarrative,,
|
|
| 265 | 265 |
elevationInMeters,elevationInMeters,, |
| 266 |
*plot_--elevationaccuracy,*plot_--elevationaccuracy,,
|
|
| 266 |
*plot--elevationaccuracy,*plot--elevationaccuracy,,
|
|
| 267 | 267 |
UNUSED#elevationrange,UNUSED#elevationrange,, |
| 268 | 268 |
slopeAspect,slopeAspect,, |
| 269 | 269 |
minSlopeAspect,minSlopeAspect,, |
| ... | ... | |
| 271 | 271 |
slopeGradient,slopeGradient,, |
| 272 | 272 |
minSlopeGradient,minSlopeGradient,, |
| 273 | 273 |
maxSlopeGradient,maxSlopeGradient,, |
| 274 |
*plot_--topoposition,*plot_--topoposition,,
|
|
| 274 |
*plot--topoposition,*plot--topoposition,,
|
|
| 275 | 275 |
landform,landform,, |
| 276 |
*plot_--surficialdeposits,*plot_--surficialdeposits,,
|
|
| 277 |
*plot_--rocktype,*plot_--rocktype,,
|
|
| 276 |
*plot--surficialdeposits,*plot--surficialdeposits,,
|
|
| 277 |
*plot--rocktype,*plot--rocktype,,
|
|
| 278 | 278 |
stateProvince,stateProvince,, |
| 279 | 279 |
country,country,, |
| 280 |
*plot_--submitter_surname,*plot_--submitter_surname,, |
|
| 281 |
*plot_--submitter_givenname,*plot_--submitter_givenname,, |
|
| 282 |
*plot_--submitter_email,*plot_--submitter_email,, |
|
| 283 |
*plot_--notespublic,*plot_--notespublic,, |
|
| 284 |
*plot_--notesmgt,*plot_--notesmgt,, |
|
| 285 |
*plot_--revisions,*plot_--revisions,, |
|
| 286 |
*plot_--dateentered,*plot_--dateentered,, |
|
| 287 |
*plot_--emb_plot,*plot_--emb_plot,, |
|
| 288 |
*plot_--plotrationalenarrative,*plot_--plotrationalenarrative,, |
|
| 289 |
*plot_--accessioncode,*plot_--accessioncode,, |
|
| 280 |
*plot--submitter_surname,*plot--submitter_surname,, |
|
| 281 |
*plot--submitter_givenname,*plot--submitter_givenname,, |
|
| 282 |
*plot--submitter_email,*plot--submitter_email,, |
|
| 283 |
*plot--notespublic,*plot--notespublic,, |
|
| 284 |
*plot--notesmgt,*plot--notesmgt,, |
|
| 285 |
*plot--revisions,*plot--revisions,, |
|
| 286 |
*plot--dateentered,*plot--dateentered,, |
|
| 287 |
*plot--emb_plot,*plot--emb_plot,, |
|
| 288 |
*plot--plotrationalenarrative,*plot--plotrationalenarrative,, |
|
| 289 |
*plot--accessioncode,*plot--accessioncode,, |
|
| 290 |
accessRights,accessRights,, |
|
| 291 |
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters__from_fuzzing,, |
|
| 292 |
coordinateUncertaintyInMeters,coordinateUncertaintyInMeters,, |
|
| 290 | 293 |
continent,continent,, |
| 291 | 294 |
DUPLICATE#of:country#area|country|territory,DUPLICATE#of:country#area|country|territory,, |
| 292 | 295 |
DUPLICATE#of:stateprovince#region|state|province,DUPLICATE#of:stateprovince#region|state|province,, |
| 293 | 296 |
county,county,, |
| 294 | 297 |
*plot_--quadrangle,*plot_--quadrangle,, |
| 295 | 298 |
*plot_--Geographic Name,*plot_--Geographic Name,, |
| 296 |
accessRights,accessRights,, |
|
| 297 |
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters__from_fuzzing,, |
|
| 298 |
coordinateUncertaintyInMeters,coordinateUncertaintyInMeters,, |
|
| 299 | 299 |
taxonOccurrenceID,taxonOccurrenceID,, |
| inputs/VegBank/taxon_observation.**/unmapped_terms.csv | ||
|---|---|---|
| 172 | 172 |
*observation_--soilcoarse |
| 173 | 173 |
*observation_--soildescription |
| 174 | 174 |
*observation_--emb_soilobs |
| 175 |
*plot_--reference_id
|
|
| 175 |
*plot--reference_id |
|
| 176 | 176 |
coordinateUncertaintyInMeters__from_coords |
| 177 | 177 |
accessRights_verbatim |
| 178 |
*plot_--confidentialityreason |
|
| 179 |
*plot_--authore |
|
| 180 |
*plot_--authorn |
|
| 181 |
*plot_--authorzone |
|
| 182 |
*plot_--authordatum |
|
| 183 |
*plot_--authorlocation |
|
| 184 |
*plot_--azimuth |
|
| 185 |
*plot_--dsgpoly |
|
| 186 |
*plot_--standsize |
|
| 187 |
*plot_--placementmethod |
|
| 188 |
*plot_--permanence |
|
| 189 |
*plot_--layoutnarrative |
|
| 190 |
*plot_--elevationaccuracy |
|
| 191 |
*plot_--topoposition |
|
| 192 |
*plot_--surficialdeposits |
|
| 193 |
*plot_--rocktype |
|
| 194 |
*plot_--submitter_surname |
|
| 195 |
*plot_--submitter_givenname |
|
| 196 |
*plot_--submitter_email |
|
| 197 |
*plot_--notespublic |
|
| 198 |
*plot_--notesmgt |
|
| 199 |
*plot_--revisions |
|
| 200 |
*plot_--dateentered |
|
| 201 |
*plot_--emb_plot |
|
| 202 |
*plot_--plotrationalenarrative |
|
| 203 |
*plot_--accessioncode |
|
| 178 |
*plot--confidentialityreason |
|
| 179 |
*plot--authore |
|
| 180 |
*plot--authorn |
|
| 181 |
*plot--authorzone |
|
| 182 |
*plot--authordatum |
|
| 183 |
*plot--authorlocation |
|
| 184 |
*plot--azimuth |
|
| 185 |
*plot--dsgpoly |
|
| 186 |
*plot--standsize |
|
| 187 |
*plot--placementmethod |
|
| 188 |
*plot--permanence |
|
| 189 |
*plot--layoutnarrative |
|
| 190 |
*plot--elevationaccuracy |
|
| 191 |
*plot--topoposition |
|
| 192 |
*plot--surficialdeposits |
|
| 193 |
*plot--rocktype |
|
| 194 |
*plot--submitter_surname |
|
| 195 |
*plot--submitter_givenname |
|
| 196 |
*plot--submitter_email |
|
| 197 |
*plot--notespublic |
|
| 198 |
*plot--notesmgt |
|
| 199 |
*plot--revisions |
|
| 200 |
*plot--dateentered |
|
| 201 |
*plot--emb_plot |
|
| 202 |
*plot--plotrationalenarrative |
|
| 203 |
*plot--accessioncode |
|
| 204 |
coordinateUncertaintyInMeters__from_fuzzing |
|
| 204 | 205 |
*plot_--quadrangle |
| 205 | 206 |
*plot_--Geographic Name |
| 206 |
coordinateUncertaintyInMeters__from_fuzzing |
|
| inputs/VegBank/plot_/create.sql | ||
|---|---|---|
| 4 | 4 |
FROM place |
| 5 | 5 |
JOIN namedplace USING (namedplace_id) |
| 6 | 6 |
WHERE |
| 7 |
place.plot_id = plot.plot_id
|
|
| 7 |
place.plot_id = plot."locationID"
|
|
| 8 | 8 |
AND placesystem = 'continent' |
| 9 | 9 |
LIMIT 1 |
| 10 | 10 |
) AS continent |
| ... | ... | |
| 12 | 12 |
FROM place |
| 13 | 13 |
JOIN namedplace USING (namedplace_id) |
| 14 | 14 |
WHERE |
| 15 |
place.plot_id = plot.plot_id
|
|
| 15 |
place.plot_id = plot."locationID"
|
|
| 16 | 16 |
AND placesystem = 'area|country|territory' |
| 17 | 17 |
LIMIT 1 |
| 18 | 18 |
) AS "area|country|territory" |
| ... | ... | |
| 20 | 20 |
FROM place |
| 21 | 21 |
JOIN namedplace USING (namedplace_id) |
| 22 | 22 |
WHERE |
| 23 |
place.plot_id = plot.plot_id
|
|
| 23 |
place.plot_id = plot."locationID"
|
|
| 24 | 24 |
AND placesystem = 'region|state|province' |
| 25 | 25 |
LIMIT 1 |
| 26 | 26 |
) AS "region|state|province" |
| ... | ... | |
| 28 | 28 |
FROM place |
| 29 | 29 |
JOIN namedplace USING (namedplace_id) |
| 30 | 30 |
WHERE |
| 31 |
place.plot_id = plot.plot_id
|
|
| 31 |
place.plot_id = plot."locationID"
|
|
| 32 | 32 |
AND placesystem = 'county' |
| 33 | 33 |
LIMIT 1 |
| 34 | 34 |
) AS county |
| ... | ... | |
| 36 | 36 |
FROM place |
| 37 | 37 |
JOIN namedplace USING (namedplace_id) |
| 38 | 38 |
WHERE |
| 39 |
place.plot_id = plot.plot_id
|
|
| 39 |
place.plot_id = plot."locationID"
|
|
| 40 | 40 |
AND placesystem = 'quadrangle' |
| 41 | 41 |
LIMIT 1 |
| 42 | 42 |
) AS quadrangle |
| ... | ... | |
| 44 | 44 |
FROM place |
| 45 | 45 |
JOIN namedplace USING (namedplace_id) |
| 46 | 46 |
WHERE |
| 47 |
place.plot_id = plot.plot_id
|
|
| 47 |
place.plot_id = plot."locationID"
|
|
| 48 | 48 |
AND placesystem = 'Geographic Name' |
| 49 | 49 |
LIMIT 1 |
| 50 | 50 |
) AS "Geographic Name" |
| inputs/VegBank/plot_/header.csv | ||
|---|---|---|
| 1 |
plot_id,authorplotcode,reference_id,parent_id,locationaccuracy,confidentialitystatus,confidentialityreason,latitude,longitude,authore,authorn,authorzone,authordatum,authorlocation,locationnarrative,azimuth,dsgpoly,shape,area,standsize,placementmethod,permanence,layoutnarrative,elevation,elevationaccuracy,elevationrange,slopeaspect,minslopeaspect,maxslopeaspect,slopegradient,minslopegradient,maxslopegradient,topoposition,landform,surficialdeposits,rocktype,stateprovince,country,submitter_surname,submitter_givenname,submitter_email,notespublic,notesmgt,revisions,dateentered,emb_plot,plotrationalenarrative,accessioncode,continent,area|country|territory,region|state|province,county,quadrangle,Geographic Name |
|
| 1 |
locationID,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,accessRights,coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters,continent,area|country|territory,region|state|province,county,quadrangle,Geographic Name |
|
| inputs/VegBank/plot_/map.csv | ||
|---|---|---|
| 1 | 1 |
VegBank,VegCore,Filter,Comments |
| 2 |
plot_id,locationID,,
|
|
| 3 |
authorplotcode,locationName,,
|
|
| 4 |
reference_id,*plot_--reference_id,,
|
|
| 5 |
parent_id,parentLocationID,,
|
|
| 6 |
locationaccuracy,coordinateUncertaintyInMeters__from_coords,,
|
|
| 7 |
confidentialitystatus,accessRights_verbatim,,
|
|
| 8 |
confidentialityreason,*plot_--confidentialityreason,,
|
|
| 9 |
latitude,decimalLatitude,,
|
|
| 10 |
longitude,decimalLongitude,,
|
|
| 11 |
authore,*plot_--authore,,
|
|
| 12 |
authorn,*plot_--authorn,,
|
|
| 13 |
authorzone,*plot_--authorzone,,
|
|
| 14 |
authordatum,*plot_--authordatum,,
|
|
| 15 |
authorlocation,*plot_--authorlocation,,
|
|
| 16 |
locationnarrative,locality,,
|
|
| 17 |
azimuth,*plot_--azimuth,,
|
|
| 18 |
dsgpoly,*plot_--dsgpoly,,
|
|
| 19 |
shape,footprintWKT,,
|
|
| 20 |
area,plotArea_m2,,"""Total area of the plot in m2"" (area definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)"
|
|
| 21 |
standsize,*plot_--standsize,,
|
|
| 22 |
placementmethod,*plot_--placementmethod,,
|
|
| 23 |
permanence,*plot_--permanence,,
|
|
| 24 |
layoutnarrative,*plot_--layoutnarrative,,
|
|
| 25 |
elevation,elevationInMeters,,"has only 5 decimal places of precision, with only 9s and random #s after that. ""The elevation of the plot origin in meters above sea level"" (elevation definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)"
|
|
| 26 |
elevationaccuracy,*plot_--elevationaccuracy,,
|
|
| 27 |
elevationrange,UNUSED#elevationrange,, |
|
| 28 |
slopeaspect,slopeAspect,,
|
|
| 29 |
minslopeaspect,minSlopeAspect,,
|
|
| 30 |
maxslopeaspect,maxSlopeAspect,,
|
|
| 31 |
slopegradient,slopeGradient,,
|
|
| 32 |
minslopegradient,minSlopeGradient,,
|
|
| 33 |
maxslopegradient,maxSlopeGradient,,
|
|
| 34 |
topoposition,*plot_--topoposition,,
|
|
| 2 |
locationID,locationID,,
|
|
| 3 |
locationName,locationName,,
|
|
| 4 |
*plot--reference_id,*plot--reference_id,,
|
|
| 5 |
parentLocationID,parentLocationID,,
|
|
| 6 |
coordinateUncertaintyInMeters__from_coords,coordinateUncertaintyInMeters__from_coords,,
|
|
| 7 |
accessRights_verbatim,accessRights_verbatim,,
|
|
| 8 |
*plot--confidentialityreason,*plot--confidentialityreason,,
|
|
| 9 |
decimalLatitude,decimalLatitude,,
|
|
| 10 |
decimalLongitude,decimalLongitude,,
|
|
| 11 |
*plot--authore,*plot--authore,,
|
|
| 12 |
*plot--authorn,*plot--authorn,,
|
|
| 13 |
*plot--authorzone,*plot--authorzone,,
|
|
| 14 |
*plot--authordatum,*plot--authordatum,,
|
|
| 15 |
*plot--authorlocation,*plot--authorlocation,,
|
|
| 16 |
locality,locality,,
|
|
| 17 |
*plot--azimuth,*plot--azimuth,,
|
|
| 18 |
*plot--dsgpoly,*plot--dsgpoly,,
|
|
| 19 |
footprintWKT,footprintWKT,,
|
|
| 20 |
plotArea_m2,plotArea_m2,,
|
|
| 21 |
*plot--standsize,*plot--standsize,,
|
|
| 22 |
*plot--placementmethod,*plot--placementmethod,,
|
|
| 23 |
*plot--permanence,*plot--permanence,,
|
|
| 24 |
*plot--layoutnarrative,*plot--layoutnarrative,,
|
|
| 25 |
elevationInMeters,elevationInMeters,,
|
|
| 26 |
*plot--elevationaccuracy,*plot--elevationaccuracy,,
|
|
| 27 |
UNUSED#elevationrange,UNUSED#elevationrange,,
|
|
| 28 |
slopeAspect,slopeAspect,,
|
|
| 29 |
minSlopeAspect,minSlopeAspect,,
|
|
| 30 |
maxSlopeAspect,maxSlopeAspect,,
|
|
| 31 |
slopeGradient,slopeGradient,,
|
|
| 32 |
minSlopeGradient,minSlopeGradient,,
|
|
| 33 |
maxSlopeGradient,maxSlopeGradient,,
|
|
| 34 |
*plot--topoposition,*plot--topoposition,,
|
|
| 35 | 35 |
landform,landform,, |
| 36 |
surficialdeposits,*plot_--surficialdeposits,,
|
|
| 37 |
rocktype,*plot_--rocktype,,
|
|
| 38 |
stateprovince,stateProvince,,
|
|
| 36 |
*plot--surficialdeposits,*plot--surficialdeposits,,
|
|
| 37 |
*plot--rocktype,*plot--rocktype,,
|
|
| 38 |
stateProvince,stateProvince,,
|
|
| 39 | 39 |
country,country,, |
| 40 |
submitter_surname,*plot_--submitter_surname,, |
|
| 41 |
submitter_givenname,*plot_--submitter_givenname,, |
|
| 42 |
submitter_email,*plot_--submitter_email,, |
|
| 43 |
notespublic,*plot_--notespublic,, |
|
| 44 |
notesmgt,*plot_--notesmgt,, |
|
| 45 |
revisions,*plot_--revisions,, |
|
| 46 |
dateentered,*plot_--dateentered,, |
|
| 47 |
emb_plot,*plot_--emb_plot,, |
|
| 48 |
plotrationalenarrative,*plot_--plotrationalenarrative,, |
|
| 49 |
accessioncode,*plot_--accessioncode,, |
|
| 40 |
*plot--submitter_surname,*plot--submitter_surname,, |
|
| 41 |
*plot--submitter_givenname,*plot--submitter_givenname,, |
|
| 42 |
*plot--submitter_email,*plot--submitter_email,, |
|
| 43 |
*plot--notespublic,*plot--notespublic,, |
|
| 44 |
*plot--notesmgt,*plot--notesmgt,, |
|
| 45 |
*plot--revisions,*plot--revisions,, |
|
| 46 |
*plot--dateentered,*plot--dateentered,, |
|
| 47 |
*plot--emb_plot,*plot--emb_plot,, |
|
| 48 |
*plot--plotrationalenarrative,*plot--plotrationalenarrative,, |
|
| 49 |
*plot--accessioncode,*plot--accessioncode,, |
|
| 50 |
accessRights,accessRights,, |
|
| 51 |
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters__from_fuzzing,, |
|
| 52 |
coordinateUncertaintyInMeters,coordinateUncertaintyInMeters,, |
|
| 50 | 53 |
continent,continent,, |
| 51 | 54 |
area|country|territory,DUPLICATE#of:country#area|country|territory,, |
| 52 | 55 |
region|state|province,DUPLICATE#of:stateprovince#region|state|province,, |
| inputs/VegBank/plot_/new_terms.csv | ||
|---|---|---|
| 1 |
reference_id,*plot_--reference_id,, |
|
| 2 |
parent_id,parentLocationID,, |
|
| 3 |
locationaccuracy,coordinateUncertaintyInMeters__from_coords,, |
|
| 4 |
confidentialitystatus,accessRights_verbatim,, |
|
| 5 |
confidentialityreason,*plot_--confidentialityreason,, |
|
| 6 |
authore,*plot_--authore,, |
|
| 7 |
authorn,*plot_--authorn,, |
|
| 8 |
authorzone,*plot_--authorzone,, |
|
| 9 |
authordatum,*plot_--authordatum,, |
|
| 10 |
authorlocation,*plot_--authorlocation,, |
|
| 11 |
azimuth,*plot_--azimuth,, |
|
| 12 |
dsgpoly,*plot_--dsgpoly,, |
|
| 13 |
shape,footprintWKT,, |
|
| 14 |
standsize,*plot_--standsize,, |
|
| 15 |
placementmethod,*plot_--placementmethod,, |
|
| 16 |
permanence,*plot_--permanence,, |
|
| 17 |
layoutnarrative,*plot_--layoutnarrative,, |
|
| 18 |
elevationaccuracy,*plot_--elevationaccuracy,, |
|
| 19 |
topoposition,*plot_--topoposition,, |
|
| 20 |
surficialdeposits,*plot_--surficialdeposits,, |
|
| 21 |
rocktype,*plot_--rocktype,, |
|
| 22 |
submitter_surname,*plot_--submitter_surname,, |
|
| 23 |
submitter_givenname,*plot_--submitter_givenname,, |
|
| 24 |
submitter_email,*plot_--submitter_email,, |
|
| 25 |
notespublic,*plot_--notespublic,, |
|
| 26 |
notesmgt,*plot_--notesmgt,, |
|
| 27 |
revisions,*plot_--revisions,, |
|
| 28 |
dateentered,*plot_--dateentered,, |
|
| 29 |
emb_plot,*plot_--emb_plot,, |
|
| 30 |
plotrationalenarrative,*plot_--plotrationalenarrative,, |
|
| 31 |
accessioncode,*plot_--accessioncode,, |
|
| 32 | 1 |
area|country|territory,DUPLICATE#of:country#area|country|territory,, |
| 33 | 2 |
region|state|province,DUPLICATE#of:stateprovince#region|state|province,, |
| 34 | 3 |
quadrangle,*plot_--quadrangle,, |
| inputs/VegBank/plot_/unmapped_terms.csv | ||
|---|---|---|
| 1 |
*plot_--reference_id
|
|
| 1 |
*plot--reference_id |
|
| 2 | 2 |
coordinateUncertaintyInMeters__from_coords |
| 3 | 3 |
accessRights_verbatim |
| 4 |
*plot_--confidentialityreason |
|
| 5 |
*plot_--authore |
|
| 6 |
*plot_--authorn |
|
| 7 |
*plot_--authorzone |
|
| 8 |
*plot_--authordatum |
|
| 9 |
*plot_--authorlocation |
|
| 10 |
*plot_--azimuth |
|
| 11 |
*plot_--dsgpoly |
|
| 12 |
*plot_--standsize |
|
| 13 |
*plot_--placementmethod |
|
| 14 |
*plot_--permanence |
|
| 15 |
*plot_--layoutnarrative |
|
| 16 |
*plot_--elevationaccuracy |
|
| 17 |
*plot_--topoposition |
|
| 18 |
*plot_--surficialdeposits |
|
| 19 |
*plot_--rocktype |
|
| 20 |
*plot_--submitter_surname |
|
| 21 |
*plot_--submitter_givenname |
|
| 22 |
*plot_--submitter_email |
|
| 23 |
*plot_--notespublic |
|
| 24 |
*plot_--notesmgt |
|
| 25 |
*plot_--revisions |
|
| 26 |
*plot_--dateentered |
|
| 27 |
*plot_--emb_plot |
|
| 28 |
*plot_--plotrationalenarrative |
|
| 29 |
*plot_--accessioncode |
|
| 4 |
*plot--confidentialityreason |
|
| 5 |
*plot--authore |
|
| 6 |
*plot--authorn |
|
| 7 |
*plot--authorzone |
|
| 8 |
*plot--authordatum |
|
| 9 |
*plot--authorlocation |
|
| 10 |
*plot--azimuth |
|
| 11 |
*plot--dsgpoly |
|
| 12 |
*plot--standsize |
|
| 13 |
*plot--placementmethod |
|
| 14 |
*plot--permanence |
|
| 15 |
*plot--layoutnarrative |
|
| 16 |
*plot--elevationaccuracy |
|
| 17 |
*plot--topoposition |
|
| 18 |
*plot--surficialdeposits |
|
| 19 |
*plot--rocktype |
|
| 20 |
*plot--submitter_surname |
|
| 21 |
*plot--submitter_givenname |
|
| 22 |
*plot--submitter_email |
|
| 23 |
*plot--notespublic |
|
| 24 |
*plot--notesmgt |
|
| 25 |
*plot--revisions |
|
| 26 |
*plot--dateentered |
|
| 27 |
*plot--emb_plot |
|
| 28 |
*plot--plotrationalenarrative |
|
| 29 |
*plot--accessioncode |
|
| 30 |
coordinateUncertaintyInMeters__from_fuzzing |
|
| 30 | 31 |
*plot_--quadrangle |
| 31 | 32 |
*plot_--Geographic Name |
| inputs/VegBank/plot/postprocess.sql | ||
|---|---|---|
| 1 |
SELECT util.search_path_append('util');
|
|
| 2 |
|
|
| 3 |
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("locationID")$$); -- runtime: 0.2 s ("Time: 238.474 ms") @starscream
|
|
| 4 |
|
|
| 5 |
|
|
| 6 |
-- remove institutions that we have direct data for |
|
| 7 |
|
|
| 8 |
-- CVS |
|
| 9 |
DELETE FROM :table |
|
| 10 |
WHERE "locationName" IN (SELECT "authorPlotCode" FROM "CVS".plot) |
|
| 11 |
-- removes 2323 of 7079 CVS plots (75340 - 73017) |
|
| 12 |
; -- runtime: 1 min ("69478.847 ms") @starscream
|
|
| 13 |
|
|
| 14 |
|
|
| 15 |
-- map_*() derived cols |
|
| 16 |
-- runtime: 2 s @starscream |
|
| 17 |
|
|
| 18 |
CREATE OR REPLACE FUNCTION map_access_rights(value integer) |
|
| 19 |
RETURNS text AS |
|
| 20 |
$BODY$ |
|
| 21 |
/* |
|
| 22 |
"closed List of Values: |
|
| 23 |
Value Description Sorting |
|
| 24 |
0 Public 1 |
|
| 25 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 26 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 27 |
3 100 km radius (nearest degree) 4 |
|
| 28 |
4 Location embargo 5 |
|
| 29 |
5 Public embargo on data 6 |
|
| 30 |
6 Full embargo on data 7 |
|
| 31 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 32 |
*/ |
|
| 33 |
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
|
|
| 34 |
$BODY$ |
|
| 35 |
LANGUAGE sql IMMUTABLE |
|
| 36 |
COST 100; |
|
| 37 |
|
|
| 38 |
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1167.547 ms") @starscream
|
|
| 39 |
|
|
| 40 |
CREATE OR REPLACE FUNCTION map_fuzzing(value integer) |
|
| 41 |
RETURNS double precision AS |
|
| 42 |
$BODY$ |
|
| 43 |
/* |
|
| 44 |
"closed List of Values: |
|
| 45 |
Value Description Sorting |
|
| 46 |
0 Public 1 |
|
| 47 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 48 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 49 |
3 100 km radius (nearest degree) 4 |
|
| 50 |
4 Location embargo 5 |
|
| 51 |
5 Public embargo on data 6 |
|
| 52 |
6 Full embargo on data 7 |
|
| 53 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 54 |
*/ |
|
| 55 |
SELECT public._km_to_m(util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision)
|
|
| 56 |
$BODY$ |
|
| 57 |
LANGUAGE sql IMMUTABLE |
|
| 58 |
COST 100; |
|
| 59 |
|
|
| 60 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1064.189 ms") @starscream
|
|
| 61 |
|
|
| 62 |
|
|
| 63 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'), |
|
| 64 |
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "coordinateUncertaintyInMeters__from_coords")$$)
|
|
| 65 |
; -- runtime: 0.5 s ("Time: 586.453 ms") @starscream
|
|
| inputs/VegBank/plot/header.csv | ||
|---|---|---|
| 1 |
plot_id,authorplotcode,reference_id,parent_id,locationaccuracy,confidentialitystatus,confidentialityreason,latitude,longitude,authore,authorn,authorzone,authordatum,authorlocation,locationnarrative,azimuth,dsgpoly,shape,area,standsize,placementmethod,permanence,layoutnarrative,elevation,elevationaccuracy,elevationrange,slopeaspect,minslopeaspect,maxslopeaspect,slopegradient,minslopegradient,maxslopegradient,topoposition,landform,surficialdeposits,rocktype,stateprovince,country,submitter_surname,submitter_givenname,submitter_email,notespublic,notesmgt,revisions,dateentered,emb_plot,plotrationalenarrative,accessioncode |
|
| inputs/VegBank/plot/run | ||
|---|---|---|
| 1 |
#!/bin/bash -e |
|
| 2 |
. "$(dirname "${BASH_SOURCE[0]}")"/../table.run
|
|
| 0 | 3 | |
| inputs/VegBank/plot/map.csv | ||
|---|---|---|
| 1 |
VegBank,VegCore,Filter,Comments |
|
| 2 |
plot_id,locationID,, |
|
| 3 |
authorplotcode,locationName,, |
|
| 4 |
reference_id,*plot--reference_id,, |
|
| 5 |
parent_id,parentLocationID,, |
|
| 6 |
locationaccuracy,coordinateUncertaintyInMeters__from_coords,, |
|
| 7 |
confidentialitystatus,accessRights_verbatim,, |
|
| 8 |
confidentialityreason,*plot--confidentialityreason,, |
|
| 9 |
latitude,decimalLatitude,, |
|
| 10 |
longitude,decimalLongitude,, |
|
| 11 |
authore,*plot--authore,, |
|
| 12 |
authorn,*plot--authorn,, |
|
| 13 |
authorzone,*plot--authorzone,, |
|
| 14 |
authordatum,*plot--authordatum,, |
|
| 15 |
authorlocation,*plot--authorlocation,, |
|
| 16 |
locationnarrative,locality,, |
|
| 17 |
azimuth,*plot--azimuth,, |
|
| 18 |
dsgpoly,*plot--dsgpoly,, |
|
| 19 |
shape,footprintWKT,, |
|
| 20 |
area,plotArea_m2,,"""Total area of the plot in m2"" (area definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)" |
|
| 21 |
standsize,*plot--standsize,, |
|
| 22 |
placementmethod,*plot--placementmethod,, |
|
| 23 |
permanence,*plot--permanence,, |
|
| 24 |
layoutnarrative,*plot--layoutnarrative,, |
|
| 25 |
elevation,elevationInMeters,,"has only 5 decimal places of precision, with only 9s and random #s after that. ""The elevation of the plot origin in meters above sea level"" (elevation definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)" |
|
| 26 |
elevationaccuracy,*plot--elevationaccuracy,, |
|
| 27 |
elevationrange,UNUSED#elevationrange,, |
|
| 28 |
slopeaspect,slopeAspect,, |
|
| 29 |
minslopeaspect,minSlopeAspect,, |
|
| 30 |
maxslopeaspect,maxSlopeAspect,, |
|
| 31 |
slopegradient,slopeGradient,, |
|
| 32 |
minslopegradient,minSlopeGradient,, |
|
| 33 |
maxslopegradient,maxSlopeGradient,, |
|
| 34 |
topoposition,*plot--topoposition,, |
|
| 35 |
landform,landform,, |
|
| 36 |
surficialdeposits,*plot--surficialdeposits,, |
|
| 37 |
rocktype,*plot--rocktype,, |
|
| 38 |
stateprovince,stateProvince,, |
|
| 39 |
country,country,, |
|
| 40 |
submitter_surname,*plot--submitter_surname,, |
|
| 41 |
submitter_givenname,*plot--submitter_givenname,, |
|
| 42 |
submitter_email,*plot--submitter_email,, |
|
| 43 |
notespublic,*plot--notespublic,, |
|
| 44 |
notesmgt,*plot--notesmgt,, |
|
| 45 |
revisions,*plot--revisions,, |
|
| 46 |
dateentered,*plot--dateentered,, |
|
| 47 |
emb_plot,*plot--emb_plot,, |
|
| 48 |
plotrationalenarrative,*plot--plotrationalenarrative,, |
|
| 49 |
accessioncode,*plot--accessioncode,, |
|
| inputs/VegBank/plot/VegBIEN.csv | ||
|---|---|---|
| 1 |
link ../../../mappings/VegCore-VegBIEN.csv |
|
| 0 | 2 | |
| inputs/VegBank/plot/test.xml.ref | ||
|---|---|---|
| 1 |
<!--put template--> |
|
| 2 |
<VegBIEN> |
|
| 3 |
<_setDefault id="-1"> |
|
| 4 |
<source_id><source><shortname>VegBank.new</shortname></source></source_id> |
|
| 5 |
<path> |
|
| 6 |
<_simplifyPath> |
|
| 7 |
<next>parent_id</next> |
|
| 8 |
<path> |
|
| 9 |
<location> |
|
| 10 |
<authorlocationcode>$locationName</authorlocationcode> |
|
| 11 |
<sourceaccessioncode>$locationID</sourceaccessioncode> |
|
| 12 |
<accesslevel>$accessRights</accesslevel> |
|
| 13 |
<area_m2>$plotArea_m2</area_m2> |
|
| 14 |
<elevation_m>$elevationInMeters</elevation_m> |
|
| 15 |
<iscultivated><_locationnarrative_is_cultivated><locationnarrative>$locality</locationnarrative></_locationnarrative_is_cultivated></iscultivated> |
|
| 16 |
<landform>$landform</landform> |
|
| 17 |
<locationnarrative>$locality</locationnarrative> |
|
| 18 |
<locationplace> |
|
| 19 |
<place_id> |
|
| 20 |
<place> |
|
| 21 |
<coordinates_id> |
|
| 22 |
<coordinates> |
|
| 23 |
<coordsaccuracy_m><_noCV><value>$coordinateUncertaintyInMeters</value></_noCV></coordsaccuracy_m> |
|
| 24 |
<footprintgeometry_dwc>$footprintWKT</footprintgeometry_dwc> |
|
| 25 |
<latitude_deg> |
|
| 26 |
<_nullIf> |
|
| 27 |
<null>0</null> |
|
| 28 |
<type>float</type> |
|
| 29 |
<value>$decimalLatitude</value> |
|
| 30 |
</_nullIf> |
|
| 31 |
</latitude_deg> |
|
| 32 |
<longitude_deg> |
|
| 33 |
<_nullIf> |
|
| 34 |
<null>0</null> |
|
| 35 |
<type>float</type> |
|
| 36 |
<value>$decimalLongitude</value> |
|
| 37 |
</_nullIf> |
|
| 38 |
</longitude_deg> |
|
| 39 |
</coordinates> |
|
| 40 |
</coordinates_id> |
|
| 41 |
<country>$country</country> |
|
| 42 |
<matched_place_id> |
|
| 43 |
<place> |
|
| 44 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 45 |
<coordinates_id> |
|
| 46 |
<coordinates> |
|
| 47 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 48 |
<latitude_deg> |
|
| 49 |
<_nullIf> |
|
| 50 |
<null>0</null> |
|
| 51 |
<type>float</type> |
|
| 52 |
<value>$decimalLatitude</value> |
|
| 53 |
</_nullIf> |
|
| 54 |
</latitude_deg> |
|
| 55 |
<longitude_deg> |
|
| 56 |
<_nullIf> |
|
| 57 |
<null>0</null> |
|
| 58 |
<type>float</type> |
|
| 59 |
<value>$decimalLongitude</value> |
|
| 60 |
</_nullIf> |
|
| 61 |
</longitude_deg> |
|
| 62 |
</coordinates> |
|
| 63 |
</coordinates_id> |
|
| 64 |
<country>$country</country> |
|
| 65 |
<stateprovince>$stateProvince</stateprovince> |
|
| 66 |
</place> |
|
| 67 |
</matched_place_id> |
|
| 68 |
<stateprovince>$stateProvince</stateprovince> |
|
| 69 |
</place> |
|
| 70 |
</place_id> |
|
| 71 |
</locationplace> |
|
| 72 |
<parent_id><location><sourceaccessioncode>$parentLocationID</sourceaccessioncode></location></parent_id> |
|
| 73 |
<slopeaspect_deg> |
|
| 74 |
<_alt> |
|
| 75 |
<1><_compass><value>$slopeAspect</value></_compass></1> |
|
| 76 |
<2> |
|
| 77 |
<_avg> |
|
| 78 |
<1>$minSlopeAspect</1> |
|
| 79 |
<2>$maxSlopeAspect</2> |
|
| 80 |
</_avg> |
|
| 81 |
</2> |
|
| 82 |
</_alt> |
|
| 83 |
</slopeaspect_deg> |
|
| 84 |
<slopegradient_fraction> |
|
| 85 |
<_alt> |
|
| 86 |
<1>$slopeGradient</1> |
|
| 87 |
<2> |
|
| 88 |
<_avg> |
|
| 89 |
<1>$minSlopeGradient</1> |
|
| 90 |
<2>$maxSlopeGradient</2> |
|
| 91 |
</_avg> |
|
| 92 |
</2> |
|
| 93 |
</_alt> |
|
| 94 |
</slopegradient_fraction> |
|
| 95 |
</location> |
|
| 96 |
</path> |
|
| 97 |
</_simplifyPath> |
|
| 98 |
</path> |
|
| 99 |
</_setDefault> |
|
| 100 |
</VegBIEN> |
|
| 101 |
Inserted 14 new rows into database |
|
| inputs/VegBank/plot/unmapped_terms.csv | ||
|---|---|---|
| 1 |
*plot--reference_id |
|
| 2 |
coordinateUncertaintyInMeters__from_coords |
|
| 3 |
accessRights_verbatim |
|
| 4 |
*plot--confidentialityreason |
|
| 5 |
*plot--authore |
|
| 6 |
*plot--authorn |
|
| 7 |
*plot--authorzone |
|
| 8 |
*plot--authordatum |
|
| 9 |
*plot--authorlocation |
|
| 10 |
*plot--azimuth |
|
| 11 |
*plot--dsgpoly |
|
| 12 |
*plot--standsize |
|
| 13 |
*plot--placementmethod |
|
| 14 |
*plot--permanence |
|
| 15 |
*plot--layoutnarrative |
|
| 16 |
*plot--elevationaccuracy |
|
| 17 |
*plot--topoposition |
|
| 18 |
*plot--surficialdeposits |
|
| 19 |
*plot--rocktype |
|
| 20 |
*plot--submitter_surname |
|
| 21 |
*plot--submitter_givenname |
|
| 22 |
*plot--submitter_email |
|
| 23 |
*plot--notespublic |
|
| 24 |
*plot--notesmgt |
|
| 25 |
*plot--revisions |
|
| 26 |
*plot--dateentered |
|
| 27 |
*plot--emb_plot |
|
| 28 |
*plot--plotrationalenarrative |
|
| 29 |
*plot--accessioncode |
|
| inputs/VegBank/plot/new_terms.csv | ||
|---|---|---|
| 1 |
reference_id,*plot--reference_id,, |
|
| 2 |
parent_id,parentLocationID,, |
|
| 3 |
locationaccuracy,coordinateUncertaintyInMeters__from_coords,, |
|
| 4 |
confidentialitystatus,accessRights_verbatim,, |
|
| 5 |
confidentialityreason,*plot--confidentialityreason,, |
|
| 6 |
authore,*plot--authore,, |
|
| 7 |
authorn,*plot--authorn,, |
|
| 8 |
authorzone,*plot--authorzone,, |
|
| 9 |
authordatum,*plot--authordatum,, |
|
| 10 |
authorlocation,*plot--authorlocation,, |
|
| 11 |
azimuth,*plot--azimuth,, |
|
| 12 |
dsgpoly,*plot--dsgpoly,, |
|
| 13 |
shape,footprintWKT,, |
|
| 14 |
standsize,*plot--standsize,, |
|
| 15 |
placementmethod,*plot--placementmethod,, |
|
| 16 |
permanence,*plot--permanence,, |
|
| 17 |
layoutnarrative,*plot--layoutnarrative,, |
|
| 18 |
elevationaccuracy,*plot--elevationaccuracy,, |
|
| 19 |
topoposition,*plot--topoposition,, |
|
| 20 |
surficialdeposits,*plot--surficialdeposits,, |
|
| 21 |
rocktype,*plot--rocktype,, |
|
| 22 |
submitter_surname,*plot--submitter_surname,, |
|
| 23 |
submitter_givenname,*plot--submitter_givenname,, |
|
| 24 |
submitter_email,*plot--submitter_email,, |
|
| 25 |
notespublic,*plot--notespublic,, |
|
| 26 |
notesmgt,*plot--notesmgt,, |
|
| 27 |
revisions,*plot--revisions,, |
|
| 28 |
dateentered,*plot--dateentered,, |
|
| 29 |
emb_plot,*plot--emb_plot,, |
|
| 30 |
plotrationalenarrative,*plot--plotrationalenarrative,, |
|
| 31 |
accessioncode,*plot--accessioncode,, |
|
Also available in: Unified diff
bugfix: inputs/VegBank/: need to remove inter-datasource duplicates from plot instead of the left-joined plot_ table, because the fkeys needed to do the cascading deletes are all to the plot table. this requires doing the column-renaming and postprocessing on plot before it's left-joined.