Project

General

Profile

« Previous | Next » 

Revision 4789

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/functions.sql: Renamed _pct_to_frac() to _percent_to_fraction() and _frac_to_pct() to _fraction_to_percent(), for clarity and for consistency with _percent (which is spelled out), as used by SALVIAS (http://salvias.net/Documents/salvias_data_dictionary.html) and elsewhere

View differences:

inputs/SALVIAS-CSV/Organism/VegBIEN.csv
29 29 
HAVING count(DISTINCT census_date) > 1
30 30 
-----"
31 31 
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."
32 
cover_percent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_pct_to_frac/value,
32 
cover_percent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_percent_to_fraction/value,
33 33 
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."
34 34 
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."
35 35 
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."
inputs/SALVIAS-CSV/Organism/test.xml.ref
17 17 
                    <taxonoccurrence>
18 18 
                        <aggregateoccurrence>
19 19 
                            <count>$no_of_individuals</count>
20 
                            <cover_fraction><_pct_to_frac><value>$cover_percent</value></_pct_to_frac></cover_fraction>
20 
                            <cover_fraction><_percent_to_fraction><value>$cover_percent</value></_percent_to_fraction></cover_fraction>
21 21 
                            <definedvalue fkey="tablerecord_id">
22 22 
                                <userdefined_id>
23 23 
                                    <userdefined>
inputs/CVS/Organism/VegBIEN.csv
7 7 
authorObsCode,"/location/locationevent/_if[@name=""if subplot""]/then/_if[@name=""if event""]/cond/_exists/_first/2",
8 8 
authorObsCode,"/location/locationevent/_if[@name=""if subplot""]/then/_if[@name=""if event""]/then/parent_id/locationevent/authorlocationcode",
9 9 
obsStartDate,/location/locationevent/obsstartdate/_*/date/_alt/1,
10 
%cover,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_pct_to_frac/value,
10 
%cover,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_percent_to_fraction/value,
11 11 
currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/authortaxoncode/_alt/2/_alt/3,
12 12 
currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname,
13 13 
county,/location/locationplace/*_id/placepath/county,
inputs/CVS/Organism/test.xml.ref
26 26 
                    <authoreventcode>$authorObsCode</authoreventcode>
27 27 
                    <obsstartdate><_date><date>$obsStartDate</date></_date></obsstartdate>
28 28 
                    <taxonoccurrence>
29 
                        <aggregateoccurrence><cover_fraction><_pct_to_frac><value>$%cover</value></_pct_to_frac></cover_fraction></aggregateoccurrence>
29 
                        <aggregateoccurrence><cover_fraction><_percent_to_fraction><value>$%cover</value></_percent_to_fraction></cover_fraction></aggregateoccurrence>
30 30 
                        <authortaxoncode>$currentTaxonName sec Weakley 2006</authortaxoncode>
31 31 
                        <taxondetermination><taxonpath_id><taxonpath><scientificname>$currentTaxonName sec Weakley 2006</scientificname></taxonpath></taxonpath_id></taxondetermination>
32 32 
                    </taxonoccurrence>
inputs/SALVIAS/plotObservations/VegBIEN.csv
29 29 
HAVING count(DISTINCT census_date) > 1
30 30 
-----"
31 31 
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."
32 
cover_percent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_pct_to_frac/value,
32 
cover_percent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_percent_to_fraction/value,
33 33 
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."
34 34 
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."
35 35 
Notes,/location/locationevent/taxonoccurrence/aggregateoccurrence/notes,
inputs/SALVIAS/plotObservations/test.xml.ref
17 17 
                    <taxonoccurrence>
18 18 
                        <aggregateoccurrence>
19 19 
                            <count>$NoInd</count>
20 
                            <cover_fraction><_pct_to_frac><value>$cover_percent</value></_pct_to_frac></cover_fraction>
20 
                            <cover_fraction><_percent_to_fraction><value>$cover_percent</value></_percent_to_fraction></cover_fraction>
21 21 
                            <definedvalue fkey="tablerecord_id">
22 22 
                                <userdefined_id>
23 23 
                                    <userdefined>
schemas/vegbien.my.sql
862 862 
--
863 863 

  		




  864
  864
  
    
CREATE VIEW analytical_db_view AS


  		




  865
  
  
    
    SELECT datasource.organizationname AS `dataSourceName`, taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS `taxonAuthor`, taxonpath.variety AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, functions._frac_to_pct(aggregateoccurrence.cover_fraction) AS `pctCover` FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));


  		




  
  865
  
    
    SELECT datasource.organizationname AS `dataSourceName`, taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS `taxonAuthor`, taxonpath.variety AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));


  		




  866
  866
  
    

  		




  867
  867
  
    

  		




  868
  868
  
    
--


  		












  

    
      schemas/functions.sql
    
  





  73
  73
  
    

  		




  74
  74
  
    

  		




  75
  75
  
    
--


  		




  76
  
  
    
-- Name: _frac_to_pct(double precision); Type: FUNCTION; Schema: functions; Owner: -


  		




  
  76
  
    
-- Name: _fraction_to_percent(double precision); Type: FUNCTION; Schema: functions; Owner: -


  		




  77
  77
  
    
--


  		




  78
  78
  
    

  		




  79
  
  
    
CREATE FUNCTION _frac_to_pct(value double precision) RETURNS double precision


  		




  
  79
  
    
CREATE FUNCTION _fraction_to_percent(value double precision) RETURNS double precision


  		




  80
  80
  
    
    LANGUAGE sql IMMUTABLE STRICT


  		




  81
  81
  
    
    AS $_$


  		




  82
  82
  
    
SELECT $1*100.


  		




  ......




  238
  238
  
    

  		




  239
  239
  
    

  		




  240
  240
  
    
--


  		




  241
  
  
    
-- Name: _pct_to_frac(double precision); Type: FUNCTION; Schema: functions; Owner: -


  		




  
  241
  
    
-- Name: _percent_to_fraction(double precision); Type: FUNCTION; Schema: functions; Owner: -


  		




  242
  242
  
    
--


  		




  243
  243
  
    

  		




  244
  
  
    
CREATE FUNCTION _pct_to_frac(value double precision) RETURNS double precision


  		




  
  244
  
    
CREATE FUNCTION _percent_to_fraction(value double precision) RETURNS double precision


  		




  245
  245
  
    
    LANGUAGE sql IMMUTABLE STRICT


  		




  246
  246
  
    
    AS $_$


  		




  247
  247
  
    
SELECT $1/100.


  		




  ......




  249
  249
  
    

  		




  250
  250
  
    

  		




  251
  251
  
    
--


  		




  252
  
  
    
-- Name: _pct_to_frac(text); Type: FUNCTION; Schema: functions; Owner: -


  		




  
  252
  
    
-- Name: _percent_to_fraction(text); Type: FUNCTION; Schema: functions; Owner: -


  		




  253
  253
  
    
--


  		




  254
  254
  
    

  		




  255
  
  
    
CREATE FUNCTION _pct_to_frac(value text) RETURNS double precision


  		




  
  255
  
    
CREATE FUNCTION _percent_to_fraction(value text) RETURNS double precision


  		




  256
  256
  
    
    LANGUAGE sql IMMUTABLE STRICT


  		




  257
  257
  
    
    AS $_$


  		




  258
  
  
    
SELECT functions._pct_to_frac($1::double precision)


  		




  
  258
  
    
SELECT functions._percent_to_fraction($1::double precision)


  		




  259
  259
  
    
$_$;


  		




  260
  260
  
    

  		




  261
  261
  
    

  		












  

    
      schemas/vegbien.sql
    
  





  1142
  1142
  
    
--


  		




  1143
  1143
  
    

  		




  1144
  1144
  
    
CREATE VIEW analytical_db_view AS


  		




  1145
  
  
    
    SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._frac_to_pct(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));


  		




  
  1145
  
    
    SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));


  		




  1146
  1146
  
    

  		




  1147
  1147
  
    

  		




  1148
  1148
  
    
--


  		












  

    
      mappings/VegCore-VegBIEN.csv
    
  





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


  		




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


  		




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


  		




  75
  
  
    
coverPercent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_pct_to_frac/value,


  		




  
  75
  
    
coverPercent,/location/locationevent/taxonoccurrence/aggregateoccurrence/cover_fraction/_percent_to_fraction/value,


  		




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


  		




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


  		




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


  		












  

    
      mappings/for_review/VegCore-VegBIEN.csv
    
  





  72
  72
  
    
monthCollected,//aggregateoccurrence/collectiondate,


  		




  73
  73
  
    
yearCollected,//aggregateoccurrence/collectiondate,


  		




  74
  74
  
    
individualCount,//aggregateoccurrence/count,


  		




  75
  
  
    
coverPercent,//aggregateoccurrence/cover_fraction/_pct_to_frac/value,


  		




  
  75
  
    
coverPercent,//aggregateoccurrence/cover_fraction/_percent_to_fraction/value,


  		




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


  		




  77
  77
  
    
intercept_m,//aggregateoccurrence/linecover_m,


  		




  78
  78
  
    
intercept_cm,//aggregateoccurrence/linecover_m/_cm_to_m/value,


  		












Also available in:  Unified diff