Before entering data, it is first necessary to add and select the citation that is the source of the data. It is also necessary for each data point to be associated with a Site, Treatment, and Species. Cultivar information is also required when available, but it is only relevant for domesticated species. Fields with an asterisk (*) are required.

Each experiment is conducted at a unique site. In the context of BETY, the term 'site' refers to a specific location and it is common for many sites to be located within the same experimental station. By creating distinct records for multiple sites, it is possible to differentiate among independent studies.

1. Before adding a site, search to make sure that site is not already

   entered in the database.

2. Search for the site given latitude and longitude

   • If an institution name or city and state are given, try to

     locate the site on Google Maps

   • If a site name is given, try to locate the site using a

     combination of Google and Google Maps

   • If latitude and longitude are given in the paper, search by lat

     and lon, which will return all sites within $\pm$ 1 degree lat and

     long.

   • If an existing site is plausibly the same site as the one

     mentioned in the paper, it will be necessary to check other

     papers linked to the existing site.

     • Use the same site if the previous study uses the *exact same

       location* and experimental setup.

     • Create a new site if the study was conducted in a different

       field (i.e., not the exact same location).

     • Create a new site if one study was conducted in a greenhouse

       and another was conducted in a field.

     • Do not use distinct sites for seed source in a common garden

       experiment (see ’When not to enter a new site’ below)

3. To use an existing site, click Edit for the site, and then select

   current citation under Add Citation Relationships

4. If site does not exist, add a new site.

Interface for adding a new site:

Attributes of a site record

1. Do not enter a new site When plants (or seeds) are collected from multiple locations and then grown in the same location, this is called 'common garden experiment'. In this case, the location of the study is included as site information. Information about the seed source can be entered as a distinct cultivar.

Site Location

Points can be added via the web interface; spatial geometries, e.g. a plot, site, or country boundary, must be entered via the PostgreSQL command line.

Point Locations

If latitude and longitude coordinates are not available, it is often possible to determine the site location based on the site name, city, and other information. One way to do this would be to look up a location name in Google Maps and then locate it on the embedded map. Google Maps can provide decimal degrees if the LatLng feature is enabled, which can be done here. Google Earth can be particularly useful in locating sites, along with their coordinates and elevation. Alternatively, the site website or address might be found through an internet search (e.g. Google).

Use Table \ref{tab:location_accuracy} to determine the number of significant digits to indicate the level of precision with which a study location is known.

Table \ref{tab:location_accuracy} \label{tab:location_accuracy} Level of accuracy to record in lat and lon fields.

Boundaries

A vector boundary must be obtained. Here is one way to obtain a site boundary using R:

A rectangular plot (with bounding box)

Here I set the bounding box for a plot by specifying the plot corners and elevation. Notice that it is necessary to specify the first point twice, once at the beginning and once at the end.

UPDATE sites
SET geometry = ST_Geomfromtext('POLYGON((-76.116081 42.794448 415, 
                                         -76.116679 42.794448 415, 
                                         -76.116679 42.79231 415, 
                                         -76.116081 42.79231 415,
                                         -76.116081 42.794448 415))', 4326)
WHERE
    ID = 1123;

A country boundary:

library(prevR)# for `create.boundary` function
library(sp)
library(rgeos)

UK_boundary <- create.boundary('United Kingdom')
writeLines(
  paste("insert into sites (country, sitename, geometry) values ('UK', 'United Kingdom', ST_GEOMFromText('",
         writeWKT(UK_boundary), "',4326)) ;"), con = 'uk.sql')

Then import at the command line (can also copy / paste to terminal, but this boundary is long)

psql -U bety -d bety < uk.sql

References

• PostGIS ST_GeomFromText documentation: http://www.postgis.org/docs/ST_GeomFromText.html

• gis.stackexchange: http://gis.stackexchange.com/q/111212/1239

• Github issues: https://github.com/PecanProject/pecan/issues/570

Treatments provide a description of a study’s treatments. Any specific information such as rate of fertilizer application should be recorded in the managements table. In general, managements are recorded when Yield data is collected, but not when only Trait data is collected.

When not to use treatment: predictor variables that are not based on distinct managements, or that are distinguished by information already contained in the trait (e.g. site, cultivar, date fields) should not be given distinct treatments. For example, a study that compares two different species, cultivars or genotypes can be assigned the same control treatment; these categories will be distinguished by the species or cultivar field. Another example is when the observation is made at two sites: the site field will include this information.

• A treatment name is used as a categorical (rather than continuous)

  variable: it should be easy to find the treatment in the paper based on

  the name in the database. The treatment name does not have to indicate

  the level of treatment used in a particular treatment - this information

  will be included in the management table.

• It is essential that a control group is identified with each study. If there is no experimental manipulation, then there is only one treatment. In this case, the treatment should be named 'observational' and listed as control. To determine the control when it is not explicitly stated, first determine if one of the treatments is most like a background condition or how a system would be in its non-experimental state. In the case of crops, this could be how a farmer would be most likely to treat a crop.

  Name: indicates type of treatment; it should be easy for anyone with the original paper to be able to identify the treatment from its name. Control: make sure to indicate if the treatment is the study 'control' by selecting true or false Definition: indicates the specifics of the treatment. It is useful for identification purposes to use a quantified description of the treatment even though this information can only be used for analysis when entered as a management.

Plant functional types (PFTs) are used to group plants for statistical modeling and analysis. PFTs are associated with both a specific set of priors, and a subset species for which the traits and yields data will be queried. In many cases, it is appropriate to use default PFTs (e.g. tempdecid is temperate deciduous trees)

In other cases, it is necessary to define PFTs for a specific project. For example, to query a specific set of priors or a subset of a species, a new PFT may be defined. For example, Xiaohui Feng defined PFTs for the species found at the EBI Farm prairie. Such project-specific PFTs can be defined as `projectname`.`pft` (i.e. ebifarm.c4grass instead of c4grass).

Species that are found or cultivated in the United States should be in the Plants table. Look it up there first.

To add a new Cultivar, go to the page: Cultivar → new.

Citation provides information regarding the source of the data. A PDF copy of each paper should be available through Mendeley.

1. Select one of the starred papers from your project's Mendeley folder.

2. The data to be entered should be specified in the notes associated

   with the paper in Mendeley

3. Identify (highlight or underline) the data (means and statistics)

   that you will enter

4. Enter citation information

   • Data entry form for a new

     site: BETYdb → Citations → new

   • Author: Input the first author’s last name only

   • Year: Input the year the paper was published, not submitted, reviewed,

     or anything else

   • Fill out Title, Journal, Vol, & Pg. For unknown information, input 'NA'

   • DOI: The 'digital object identifier'. If DOI is available, PDF and

     URL are optional. This can be located in the article or in the

     article website. Use Ctrl+F 'DOI' to find it. Some older

     articles do not have a DOI.

     When entering the DOI, don't include a "doi:" prefix; the DOI should start with "10.".

   • URL: Web address of the article, preferably from publisher's website. Include the "http://" or "https://" prefix. If no on-line version is available, but some other information about how to obtain the citation is available, you may use a parenthesized note in lieu of a bona fide URL, e.g. "(e-mail Dr. No at no@example.com for a written copy)".

   • PDF: URL of the PDF of the article. Include the "http://" or "https://" prefix. (A parenthesized note is allowed here as well.)

There are two ways to add management information, through the web interface or from a spreadsheet. These are discussed in turn, below. Recall that managements can be associated with one or more treatments.

Managements refers to something that occurs at a specific time and has a quantity. Managements include actions that are done to a plant or ecosystem, such as the planting density or rate of fertilization, for example. Managements are distinct from treatments in that a treatment is used to categorically identify an experimental treatment, whereas a management is used to describe what has been done. Managements are the way a treatment becomes quantified. Each treatment is often associated with multiple managements. The combination of managements associated with a particular treatment will distinguish it from other treatments. The management types that can be entered into BETY are described in Table \ref{tab:managements}. Each management may be associated with one or more treatments. For example, in a fertilization experiment, planting, irrigation, and herbicide managements would be applied to all plots but the fertilization will be specific to a treatment. For a multi-year experiment, there may be multiple entries for the same type of management, reflecting, for example, repeated applications of herbicide or fertilizer.

note:Managements are not always required - and the level of detail depends on the scope of research. By default managements are recorded for Yields but not for Traits, unless specifically required by the data or project manager.

• Date: in format YYYY-MM-DD or YYYY-MM-DD HH:MM

• Dateloc: date level of confidence, explained in Section \ref{sec:traits} and defined in Table \ref{tab:traits}.

• Mgmttype: the name of the management being used. A list of standardized

  management types can be found in Table \ref{tab:managements}

• Level: a quantification of mgmttype

• Units: refers to the units of the level. Units should be converted to those

  in Table \ref{tab:managements}

Managements This is a list of managements to enter, with the most common management types in bold. It is more important to have management records for Yields than for traits. For greenhouse experiments, it is not necessary to include informaton on fertilizaton, lighting, or greenhouse temperature.

Via Web interface

Managements can be entered via the web interface. First enter the management, and then associate it with one or more treatments. To associate a management with multiple treatments, first create the management, then edit the management and add treatment relationships.

Preparing a managements spreadsheet for Upload

When there is a long list of managements, the insert_managements scripts enables users to insert data organized in a text based (csv) file.

Preparing the csv file can be done in any spreadsheet program such as Excel or Google Sheets. The insertion is straightforward, but requires familiarity with the bash shell as well as administrative access to the Postgres database.

File format

Required Fields the spreadsheet or CSV file must contain the following column headings:

These columns map to fields in the database (in the citations, treatments, and managements field). Each row must have non-empty values in each of these columns. Moreover, the citation columns must match exactly one row in the citations row of the database and the treatment name must match exactly one of the treatment rows associated with the matched citation.

Optional Fields The table may also contain the following column headings:

Each optional column heading corresponds to an optional field in the database managements table. The column can contain one or more empty rows.

If the table is prepared in a spreadsheet program, use the "save as --> .csv" option to export a single text based .csv file.

Inserting Management Insertion Script

insert_managements.rb

DATABASE SPECIFICATION

The database used by the script is determined by the environment specified by the '--environment' option (or 'development' if not specified) and the contents of the configuration file 'config/database.yml'. (Run 'rake dbconf' to view the contents of this file on the command line.)

USING THE SCRIPT TO UPDATE THE PRODUCTION DATABASE

There are three options for using this script to update the production database.

Option A: Run the script on the production server in the Rails root directory of the production deployment of the BETYdb Rails app.

1. Upload the input CSV file to the production machine.

2. Log in to the production machine and cd to the root directory of production deployment of the BETYdb Rails app.

3. Run the script using the '--environment=production' option and with '--login' set to your own BETYdb Rails login for the production deployment. The command-line argument specifying the input CSV file path should match the location you uploaded it to.

4. After examining the resulting output file, apply it to the database with the command

(If your machine login doesn't match a PostgreSQL user name that has insert permissions on the production database, you will have to use the '-U' option to specify a user who does have such permission.)

Option B: Run the script on your local machine using an up-to-date copy of the BETYdb database.

To do this:

1. Switch to the root of the copy of the BETYdb Rails app you want to use.

2. For the copy of the BETYdb database connected to this copy of the Rails app, ensure that at least the citations and the treatments tables are up-to-date with the production copy of the BETYdb database. (If you have different databases specified for your development and your production environments, be sure that the environment you specify with the '--environment' option points to the right database.)

3. Run this script.

4. Upload the output file to the production server and apply it to the production database using the psql command given above.

Option C: Run the script on your local machine using a Rails environment connected to the production database.

1. Go to the copy of the BETYdb Rail app on your local machine that you wish to use.

2. Edit the file config/database.yml, adding the following section:

   Most of these values can be copied from the production copy config/database.yml if you have access to it. The port and host entries are 'new'.

3. Set up an ssh tunnel to the production server using the command

   This will log you into the production server, but at the same time it will connect port 8000 on your local machine with port 5432 (the PostgreSQL server port) on the production machine. (The choice of 8000 for port number is somewhat arbitrary, but whatever value you use should match the value you specified for the port number in the database.yml file.)

4. Run this script with the environment option '--environment=ebi'. (Again, the name 'ebi' for the environment is somewhat arbitrary, but the option value should match the name in your database.yml file.)

5. Continue as in step 4 under option B.