Locality description,/location/locationevent/commclass/commdetermination/*_id/commconcept/commdescription,Often begins with the text in Vegetation type

First evaluation,/location/locationevent/obsenddate/_*/date/_alt/2/_dateRangeEnd/value,

First evaluation,/location/locationevent/obsstartdate/_*/date/_alt/2/_dateRangeStart/value,

CIC (ISRIC 11–cmolc/Kg–0.05),/location/locationevent/soilobs/cationexchangecapacity_cmol_kg,

Conductividad electrica (ASPT 6–µS/cm– 1 - 10000),/location/locationevent/soilobs/conductivity,

pH acuoso (ISRIC 4–– 1 - 14),/location/locationevent/soilobs/ph,

Textural class,/location/locationevent/soilobs/texture,

Locality,/location/locationnarrative/_merge/1,

Observaciones,/location/notespublic,

Direction,/location/slopeaspect_deg/_alt/1/_compass/value/_alt/1,Part files provide either this field or Direction

Orientación/exposicion,/location/slopeaspect_deg/_alt/1/_compass/value/_alt/2,Part files provide either this field or Orientación

Collaborators,,"** No join mapping for Collaborators ** Need a way to store a verbatim list of people, or a function to parse it and insert it iteratively into party"

Coordinates,,** No join mapping for Coordinates ** Concatenation of Latitude (DMS) and Longitude (DMS)

Installed and evaluated by,,"** No join mapping for Installed and evaluated by ** Need a way to store a verbatim list of people, or a function to parse it and insert it iteratively into party"

                    <obsenddate><_date><date><_dateRangeEnd><value>$First evaluation</value></_dateRangeEnd></date></_date></obsenddate>

                    <obsstartdate><_date><date><_dateRangeStart><value>$First evaluation</value></_dateRangeStart></date></_date></obsstartdate>

                    <soilobs>

                        <cationexchangecapacity_cmol_kg>$CIC (ISRIC 11&#8211;cmolc/Kg&#8211;0.05)</cationexchangecapacity_cmol_kg>

                        <conductivity>$Conductividad electrica (ASPT 6&#8211;&#181;S/cm&#8211; 1 - 10000)</conductivity>

                        <ph>$pH acuoso (ISRIC 4&#8211;&#8211; 1 - 14)</ph>

                        <texture>$Textural class</texture>

                    </soilobs>

                </locationevent>

                        </value>

                    </_compass>

                </slopeaspect_deg>

            </location>

        </path>

    </_simplifyPath>

HAVING count(DISTINCT census_date) > 1

-----"

no_of_individuals,/location/locationevent/taxonoccurrence/aggregateoccurrence/count,"Brad: This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

census_no,"/location/locationevent/taxonoccurrence/aggregateoccurrence/definedvalue[*_id/userdefined[tablename=aggregateoccurrence,userdefinedname=censusNo]]:[@fkey=tablerecord_id]/definedvalue","Brad: Assigned by SALVIAS. 1 for first plot, then 2, 3, etc. I can't recall if we even have repeat censuses in SALVIAS. Probably not."

intercept_cm,/location/locationevent/taxonoccurrence/aggregateoccurrence/linecover_m/_cm_to_m/value,"Brad: This is an aggregate observation. Used in line-intercept methodology only, describes the point along centerline at which an individual intercepts the center line of the plot. Used to determin relative abundance."

individual_code,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/collectionnumber,"Brad: Code, if any, used by the data provider to indicate an individual tree. Scope is unknown, although typically this value is unique only within plot, or sometimes only within subplot."

                    <taxonoccurrence>

                        <aggregateoccurrence>

                            <count>$no_of_individuals</count>

                            <definedvalue fkey="tablerecord_id">

                                <userdefined_id>

                                    <userdefined>

date_finish,/location/locationevent/obsenddate/_*/date/_alt/1,

date_start,/location/locationevent/obsstartdate/_*/date/_alt/1,

precip_mm,/location/locationevent/precipitation_m,

soil_cation_cap,/location/locationevent/soilobs/cationexchangecapacity_cmol_kg,

soil_conductivity,/location/locationevent/soilobs/conductivity,

pH,/location/locationevent/soilobs/ph,"Brad: For all these soil variables, how do we store information on method, units. Seems like an ontological structure would be more appropriate (measurementName, measurementValue, measurementUnits, measurementMethod). Same as BIEN traits table. Something to discuss."

soil_texture,/location/locationevent/soilobs/texture,

temp_c,/location/locationevent/temperature_c,

locality_description,/location/locationnarrative/_merge/1,"Brad: No; this is free text description of locality where plot was situated, same as for DwC. Not a description of vegetation. Most likely='namedPlace.placeDescription' in VB; not sure about VX, again need to ask Nick where locality descriptions live in VX"

pol2,/location/locationplace/*_id/placepath/county,Brad: No pol2=countyParish

pol1,/location/locationplace/*_id/placepath/stateprovince,Brad: No; pol1=stateProvince

slope_aspect,/location/slopeaspect_deg/_alt/1/_compass/value,

observation_type,,"** No join mapping for observation_type ** Brad: SALVIAS internal metadata indicating whether the record represents an individual or aggregate observation. Rather than storing, use to decide where to store in VegX.; Aaron: VegX aggregateOrganismObservation table is missing many fields available in individualOrganismObservation, so we're mapping to individualOrganismObservation regardless of observation type"

recensused,,"** No join mapping for recensused ** Brad: This is a 0/1 value, internal to SALVIAS. 1 indicates that a  plot has >1 set of values, from different census events.; Aaron: Different censuses are distinguished in organisms data by different census_no values"

                    <obsstartdate><_date><date>$date_start</date></_date></obsstartdate>

                    <precipitation_m>$precip_mm</precipitation_m>

                    <soilobs>

                            <_units>

                                <default>%</default>

                                <to></to>

                                    </_map>

                                </value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_base</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Ca</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_C</value>

                            </_units>

                        <cationexchangecapacity_cmol_kg>$soil_cation_cap</cationexchangecapacity_cmol_kg>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$clay_percent</value>

                            </_units>

                        <conductivity>$soil_conductivity</conductivity>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Mg</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_N</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$organic_percent</value>

                            </_units>

                        <ph>$pH</ph>

                            <_units>

                                <default>%</default>

                                <to></to>

                                    </_map>

                                </value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_K</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$sand_percent</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$silt_percent</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Na</value>

                            </_units>

                        <texture>$soil_texture</texture>

                    </soilobs>

                    <temperature_c>$temp_c</temperature_c>

                    </placepath_id>

                </locationplace>

                <slopeaspect_deg><_compass><value>$slope_aspect</value></_compass></slopeaspect_deg>

            </location>

        </path>

    </_simplifyPath>

authorObsCode,"/location/locationevent/_if[@name=""if subplot""]/then/_if[@name=""if event""]/cond/_exists/_first/2",

authorObsCode,"/location/locationevent/_if[@name=""if subplot""]/then/_if[@name=""if event""]/then/parent_id/locationevent/authorlocationcode",

obsStartDate,/location/locationevent/obsstartdate/_*/date/_alt/1,

currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/authortaxoncode/_alt/2/_alt/3,

currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname,

county,/location/locationplace/*_id/placepath/county,

                    <authoreventcode>$authorObsCode</authoreventcode>

                    <obsstartdate><_date><date>$obsStartDate</date></_date></obsstartdate>

                    <taxonoccurrence>

                        <authortaxoncode>$currentTaxonName sec Weakley 2006</authortaxoncode>

                        <taxondetermination><taxonpath_id><taxonpath><scientificname>$currentTaxonName sec Weakley 2006</scientificname></taxonpath></taxonpath_id></taxondetermination>

                    </taxonoccurrence>

date_finish,/location/locationevent/obsenddate/_*/date/_alt/1,

date_start,/location/locationevent/obsstartdate/_*/date/_alt/1,

Precip,/location/locationevent/precipitation_m,

soil_cation_cap,/location/locationevent/soilobs/cationexchangecapacity_cmol_kg,

soil_conductivity,/location/locationevent/soilobs/conductivity,

pH,/location/locationevent/soilobs/ph,"Brad: For all these soil variables, how do we store information on method, units. Seems like an ontological structure would be more appropriate (measurementName, measurementValue, measurementUnits, measurementMethod). Same as BIEN traits table. Something to discuss."

soil_texture,"/location/locationevent/soilobs/texture/_units:[default=""%"",to=]/value",

Temp,/location/locationevent/temperature_c,

Locality_Description,/location/locationnarrative/_merge/1,"Brad: No; this is free text description of locality where plot was situated, same as for DwC. Not a description of vegetation. Most likely='namedPlace.placeDescription' in VB; not sure about VX, again need to ask Nick where locality descriptions live in VX"

PolDiv1,/location/locationplace/*_id/placepath/stateprovince,Brad: No; pol1=stateProvince

plot_notes,/location/notespublic,

slope_aspect,/location/slopeaspect_deg/_alt/1/_compass/value,

AccessCode,,** No join mapping for AccessCode ** 

Country_index,,** No join mapping for Country_index ** 

ElevSource,,** No join mapping for ElevSource ** 

                    <obsstartdate><_date><date>$date_start</date></_date></obsstartdate>

                    <precipitation_m>$Precip</precipitation_m>

                    <soilobs>

                            <_units>

                                <default>%</default>

                                <to></to>

                                    </_map>

                                </value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_base</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Ca</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_C</value>

                            </_units>

                        <cationexchangecapacity_cmol_kg>$soil_cation_cap</cationexchangecapacity_cmol_kg>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$clay_percent</value>

                            </_units>

                        <conductivity>$soil_conductivity</conductivity>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Mg</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_N</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$organic_percent</value>

                            </_units>

                        <ph>$pH</ph>

                            <_units>

                                <default>%</default>

                                <to></to>

                                    </_map>

                                </value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_K</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$sand_percent</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$silt_percent</value>

                            </_units>

                            <_units>

                                <default>%</default>

                                <to></to>

                                <value>$soil_Na</value>

                            </_units>

                        <texture>

                            <_units>

                                <default>%</default>

                </locationplace>

                <notespublic>$plot_notes</notespublic>

                <slopeaspect_deg><_compass><value>$slope_aspect</value></_compass></slopeaspect_deg>

            </location>

        </path>

    </_simplifyPath>

HAVING count(DISTINCT census_date) > 1

-----"

NoInd,/location/locationevent/taxonoccurrence/aggregateoccurrence/count,"Brad: This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

census_no,"/location/locationevent/taxonoccurrence/aggregateoccurrence/definedvalue[*_id/userdefined[tablename=aggregateoccurrence,userdefinedname=censusNo]]:[@fkey=tablerecord_id]/definedvalue","Brad: Assigned by SALVIAS. 1 for first plot, then 2, 3, etc. I can't recall if we even have repeat censuses in SALVIAS. Probably not."

intercept_cm,/location/locationevent/taxonoccurrence/aggregateoccurrence/linecover_m/_cm_to_m/value,"Brad: This is an aggregate observation. Used in line-intercept methodology only, describes the point along centerline at which an individual intercepts the center line of the plot. Used to determin relative abundance."

Notes,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes,

                    <taxonoccurrence>

                        <aggregateoccurrence>

                            <count>$NoInd</count>

                            <definedvalue fkey="tablerecord_id">

                                <userdefined_id>

                                    <userdefined>

observation_id,"/location/locationevent/_if[@name=""if subplot""]/then/_if[@name=""if event""]/then/parent_id/locationevent/sourceaccessioncode",

obsenddate,/location/locationevent/obsenddate/_*/date/_alt/1,

obsstartdate,/location/locationevent/obsstartdate/_*/date/_alt/1,

exchangecapacity,/location/locationevent/soilobs/cationexchangecapacity_cmol_kg,

soilph,/location/locationevent/soilobs/ph,

soiltexture,/location/locationevent/soilobs/texture,

accessioncode,,** No join mapping for accessioncode ** 

autotaxoncover,,** No join mapping for autotaxoncover ** 

                    <obsenddate><_date><date>$obsenddate</date></_date></obsenddate>

                    <obsstartdate><_date><date>$obsstartdate</date></_date></obsstartdate>

                    <soilobs>

                        <cationexchangecapacity_cmol_kg>$exchangecapacity</cationexchangecapacity_cmol_kg>

                        <ph>$soilph</ph>

                        <texture>$soiltexture</texture>

                    </soilobs>

                </locationevent>

slopeaspect,/location/slopeaspect_deg/_alt/1/_compass/value,

maxslopeaspect,/location/slopeaspect_deg/_alt/2/_avg/max,

minslopeaspect,/location/slopeaspect_deg/_alt/2/_avg/min,

Geographic Name,,** No join mapping for Geographic Name ** 

accessioncode,,** No join mapping for accessioncode ** 

authordatum,,** No join mapping for authordatum ** 

                        </2>

                    </_alt>

                </slopeaspect_deg>

                    <_alt>

                        <1>$slopegradient</1>

                        <2>

                            </_avg>

                        </2>

                    </_alt>

            </location>

        </path>

    </_simplifyPath>

    sourceaccessioncode text,

    taxonoccurrence_id integer NOT NULL,

    collectiondate timestamp with time zone,

    linecover_m double precision,

    basalarea_m2 double precision,

    biomass_kg_m2 double precision,

    slopeaspect_deg double precision,

    minslopeaspect_deg double precision,

    maxslopeaspect_deg double precision,

    topoposition text,

    landform text,

    surficialdeposits text,

--

CREATE VIEW analytical_db_view AS

--

    depthbottom_m double precision,

    color text,

    texture text,

    ph double precision,

    cationexchangecapacity_cmol_kg double precision,

    conductivity double precision,

);

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

startDate,/location/locationevent/obsstartdate/_*/date/_alt/1,

eventDate,/location/locationevent/obsstartdate/_*/date/_alt/2/_dateRangeStart/value,

precipitation,/location/locationevent/precipitation_m,

cationExchangeCapacity,/location/locationevent/soilobs/cationexchangecapacity_cmol_kg,

conductivity,/location/locationevent/soilobs/conductivity,

ph,/location/locationevent/soilobs/ph,

texture,/location/locationevent/soilobs/texture,

dateCollected,/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/1/_*/date,

dayCollected,"/location/locationevent/taxonoccurrence/aggregateoccurrence/collectiondate/_alt/2/_date/day/_nullIf:[null=0,type=float]/value",

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

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

individualCount,/location/locationevent/taxonoccurrence/aggregateoccurrence/count,

censusNumber,"/location/locationevent/taxonoccurrence/aggregateoccurrence/definedvalue[*_id/userdefined[tablename=aggregateoccurrence,userdefinedname=censusNo]]:[@fkey=tablerecord_id]/definedvalue",

intercept_m,/location/locationevent/taxonoccurrence/aggregateoccurrence/linecover_m,

intercept_cm,/location/locationevent/taxonoccurrence/aggregateoccurrence/linecover_m/_cm_to_m/value,

slopeAspect,/location/slopeaspect_deg/_alt/1/_compass/value,

maxSlopeAspect,/location/slopeaspect_deg/_alt/2/_avg/max,

minSlopeAspect,/location/slopeaspect_deg/_alt/2/_avg/min,

subplotID,/location/sourceaccessioncode,

subplotX,/location/sublocationxposition_m,

subplotY,/location/sublocationyposition_m,

startDate,//locationevent/obsstartdate/_*/date,

eventDate,//locationevent/obsstartdate/_*/date,

precipitation,//locationevent/precipitation_m,

cationExchangeCapacity,//soilobs/cationexchangecapacity_cmol_kg,

conductivity,//soilobs/conductivity,

ph,//soilobs/ph,

texture,//soilobs/texture,

dateCollected,//aggregateoccurrence/collectiondate/_*/date,

dayCollected,//aggregateoccurrence/collectiondate,

monthCollected,//aggregateoccurrence/collectiondate,

yearCollected,//aggregateoccurrence/collectiondate,

individualCount,//aggregateoccurrence/count,

censusNumber,"//aggregateoccurrence/definedvalue[*_id/userdefined[tablename=aggregateoccurrence,userdefinedname=censusNo]]/definedvalue",

intercept_m,//aggregateoccurrence/linecover_m,

intercept_cm,//aggregateoccurrence/linecover_m,

slopeAspect,//location/slopeaspect_deg,

maxSlopeAspect,//location/slopeaspect_deg,

minSlopeAspect,//location/slopeaspect_deg,

subplotID,//location/sourceaccessioncode,

subplotX,//location/sublocationxposition_m,

subplotY,//location/sublocationyposition_m,