HANDBOOK FOR MANAGEMENT OF CTFS DATABASES

Written by CTFS Systems Group April 2014

Suzanne Lao, Shameema Esufali, Anudeep Singh, Richard Condit

Contact for further information

shameemaesufali@gmail.com

laoz@si.com

CHAPTER 1. Overview of Data Management Protocols

1. Introduction

This Chapter is an overview of the processes and protocols used to manage CTFS data. You need to think about who does these jobs at your site and how to make sure protocols are followed to keep your dataset to standard.

2. Role of the Data Manager

The Data Manager will be managing the data collected at the site.

In general, this involves:

1. Assuring the quality of the data collected by screening it as often as needed

2. Creating forms and data entry screens that try to minimize collection and data entry errors

3. Trying to screen data as soon as it is collected, so errors can be fixed in the field if necessary

4. Keeping records of all errors found and how they were fixed

5. Scanning field forms (if they are in hard copy format), and backing up all temporary electronic work files, in a timely fashion

6. Uploading all data into the production database

7. Backing up the final database after a census

8. Making changes to database between censuses as necessary, keeping records, backing up all versions

9. Archiving final database in a repository

3. Common platform for software

CTFS sites use free open source software as a standard. The database environment comprises of Apache, MySQL and PHP. Basic database management and simple queries can be completely managed in this environment with the tools provided. Once these are installed, site managers can install and use CTFS software to enter, screen and verify, correct, manage and analyse data. Detailed instructions for installing all software are given in the Installation Guide below.

1. Install Apache, MySQL, PHP. In the case of Windows and Mac, install XAMPP, which is very user-friendly

Windows: https://www.apachefriends.org/xampp-files/5.6.31/xampp-win32-5.6.31-0-VC11-installer.exe

Linux: https://www.apachefriends.org/xampp-files/5.6.31/xampp-linux-x64-5.6.31-0-installer.run

Mac: https://www.apachefriends.org/xampp-files/5.6.31/xampp-osx-5.6.31-0-installer.dmg

2. Install ctfsweb

http://ctfs.si.edu.edu/Public/software/ctfsweb/latest/Installation/Installation Guide.pdf

4. Common database structure for all sites

CTFS sites use a common database structure. In order to include your site in the network the data manager needs to:

- Install the tables and import shared taxonomic data

- Import census data or enter data using ctfsweb a complete system for data entry and verification of field data.

5. Shared higher level taxonomy

- APG Family and Genus information is shared by all sites in the CTFS network.

- Species data is specific to each site.

Detailed instructions for setting up your site database , configuring taxonomic data and importing or entering your site data are available in:

http://ctfs.si.edu.edu/DatabaseGroup/final/INSTRUCTIONS FOR SETTING UP A CTFS MySQL DATABASE_31Oct2013.pdf

6. Data Entry and Verification Tools and Protocols

CTFS provides tools for data entry and verification of census data. With ctfsweb you can.

- Enter Census data into temporary tables using single or double entry

- Import previously entered data from text files

- Screen your data and correct data entry errors by consulting your field sheets.

- Upload your data into the permanent database tables.

- If there are outstanding errors that cannot be corrected at this point due to lack of information you must create scripts to correct these errors according to a scripting protocol.

Check this link for answers to frequently asked questions

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/CTFSWEBDATAENTRYFAQsandTIPS.pdf

7. Exporting data for analysis

The CTFS data structure includes a consolidated (denormalized) view of all the data. This view table is called ViewFullData.

The REPORTS module of ctfsweb runs off this table. This table may be exported into a flat file for use with any statistical software.

In particular the CTFS systems group uses it to create R Analytical Tables for all sites. These tables are formatted for use with the CTFS R Package, a powerful package designed specifically to analyze plot data.

The CTFS R Package is available at

http://ctfs.si.edu.edu/Public/CTFSRPackage/

8. Backups and data storage

1. Back up the database using mysqldump

eg. mysqldump -u your_mysql_username -p current_blue > blue.sql

2. Error corrections should be accumulated and updates to the database done according to a schedule, not every time an error is found. Any updates to the database should be recorded in a script. Updates include saving the original data from the record in the Log table before making the change to the record.

3. The database should be backed up every time a set of corrections are done. The update scripts should be saved along with the database backup.

4. The official database, or the database to be used for analyses and publications, should be updated only once a year or less (once every two years).

5. Official databases should be archived in a digital repository. Repositories maintain your files and ensure that they remain usable in the future. Repositories also provide online access to papers and data for the research community, and can serve as a method of publishing files and data, making them more easily citable.

6. Most repositories are public access, but some may restrict access for a few years, or store some parts publically and maintain other more proprietary parts inaccessible.

7. The BCI data is archived in the Smithsonian Digital Repository in several formats: MySQL, R tables, and ASCII. It is especially important to save files in ASCII format as MySQL and R may not be maintained in the future. A detailed metadata is necessary to explain what data is collected, the quality of the data, etc.

9. Configuration Management Policy for CTFS Datasets

• We require that there be 2 versions of each database. The names of the databases should have the following prefixes:

  • stable_  - this is the official version. Analyses and publications should be based on this version. Updates to this database should not happen more than once a year.

  • current_ – updates and changes can be done to this database throughout the year. Every time an update is done, this database should be backed up. This allows the manager to go back to a previous copy if he makes a mistake updating the current database. Once a year (or once every two years) this database will be copied over to the stable database.

• By convention we name test databases working_ – this database is used for testing scripts or programs. These databases are created explicitly for testing and dropped once the database is finalised to either a current or stable database.

We require that the backups should include the date of the backup as part of the file name. . DO NOT overwrite previous backups, all backups should be saved. This allows analyses from publications to be repeated on a previous version of a database if required, as well as allows us to go to a previous database version in case errors were committed during an update.

10. Managing  Databases on a Server.

Managing databases on a server requires in most cases having a working knowledge of the unix operating system. The manager will have to set up usernames and passwords and restrict access to different databases to different users. The database manager will also have to control the type of access to different users on the same database, i.e. complete privileges versus restricted privileges (such as querying the database but not allowing changes to be made).

Cron jobs can be set up to mirror databases on other servers as needed, i.e. once a year or once every two years.

Cron jobs can be set up to backup certain databases or files automatically when necessary.

The server should also have a backup system to backup up all files and databases on the server. Most institutions use tape backups and these can be programmed to back up as frequently as needed.

CHAPTER 2. Formatting input files to CTFS requirements

1. Introduction

Data intended for input into the CTFS Data Management systems need to be presented in a specific way. This chapter details the files you need to submit for forest plot data and also specifies their ideal format. If your data is in a different format or you have additional information that you would like to preserve in the system, please contact the CTFS Systems Group with details.

You will need to submit these data file for review prior to upload, either at a workshop or by email or dropbox.

All data files should be tab-delimited text files with a header using the column names outlined below. Avoid special characters and quotes (single or double).

A complete data set requires

1. A species file

2. A list of all tree measurement codes used in the censuses

3. A quadrat file

4. A file with the names of the personnel involved with the census.

5. A file with staff roles defined

6. If you have already completed a census, one file with the tree and stem measurements for each census; that is, with 3 different censuses, 3 different files

Following is a detailed explanation of the format for each of the above files. You may include additional data that needs to be stored but each file must have the columns specified in the formats below.

2. Format Data Files

For the initial census you will need permanent static data such as your country and site description including the global coordinates of your plot and ts size and shape and files containing quadrat data, species and codes lists etc. Finally, for any census you will need a census file with dbh measurements for each stem. At recensus you must also submit any changes to the fixed data such as new species, additional codes etc. This section specifies the files and formats for these data.

1. Species file

The columns spcode, genus, species, and idlevel are required, the other two are optional.

spcode   - a code used in the field to identify the species of the tree. At CTFS most sites use 6 letter codes where the first 4 letters are from the Genus name and the last two from the species. If two species yield the same code then the first unique part of letters is used to break the tie.

For example I have used the first character of the species names together with the first unique character to form the 6 character code. Eg's

You should use a similar naming convention for each morphospecies incorporating details you know. For example, using LITSBL (Litsea “Big Leaf”) or APORS1 (Aporosa sp. 1) are fine – as long as each code applies to only one morphospecies. These can be changed once identification is more complete.

Other combinations are also acceptable. Other sites use 3 letters from the genus and 3 from the species, while others use 4 letters instead of 6 (2 letters from the genus and 2 from the species).

genus  – the taxonomic genus name (in case of unknown genus, use “Unidentified”)

species  – the taxonomic species name

family  – the taxonomic family name

authority  – (optional) author of the species name

idlevel  – to indicate to which level the species or  morphospecies has been identified

• species – species known.

• subspecies – subspecies known.

• genus - Genus known, species unknown but single morphospecies

• family - Family known, genus and species unknow but single morphospecies

• none – no identification has been done. But single morphospecis

• multiple – not a single morphospecies but one of a group possibly of a genus or family.

2. Tree measurement codes

These are the codes used by field personnel to describe the condition of a tree, stem or measurement. A few are required for data processing and others are optional and may be site specific. Examples of optional codes are L – Leaning tree, BA – stem Broken Above point of measurement.

For each code you need to supply a short code and a longer description.

code – one or more letter codes used in the codes field in the census tables

description – a brief description of what the code means

Please check that this is a complete list of codes used in all the census datasets.

The following descriptions are required in your list (the code you decide to use is up to you):

1. main – to indicate the main stem in multiple-stemmed trees; that is, one of the codes should have description main

     For datasets with more than one census:

2. dead  - this code should indicate that the entire tree is dead. Do not use this word in the description of any other code. If only one stem is “dead” and there are other live stems, use the following code (stem lost) or some other description.

3. stem lost  - to indicate that a stem that was previously 1 cm dbh or above broke off and is still alive but is < 1 cm dbh

3. Quadrat file

This contains a complete list of the quadrat names used in the plot. The following fields are required.

quadrat – the name of the quadrat eg 0101

startx – the x coordinate of the lower left corner of the quadrat eg. 0

starty – the y coordinate of the lower left corner of the quadrat eg. 0

dimx – the x dimension of the quadrat eg. 20

dimy – the y dimension of  the quadrat eg. 20

The x and y coordinates (startx and starty) refer to the distance in meters of the quadrat from the lower left corner of the entire plot.

Most of the sites have 20m x 20m quadrats and use the same naming system (0000, 0001, ….0024, 0100, 0101, ….4900, 4901, ….4924) for a 1000m x 500m plot.

However, some sites may use 10x10m quadrats or name your quadrats starting from 0101 (no 00 used). There is at least one site where quadrat 0000 refers to a quadrat in the center of the plot.

This table should reflect the names used in your plot, as long as the startx and starty of the quadrat indicate where this quadrat is in relation to the lower left corner or the entire plot, assuming that this lower left corner has x,y coordinates 0,0.

4. Role Reference file

This file should have a list of the job titles involved in the collection and management of CTFS plot data. It needs to contain only a single column. Roles typically include field technicians, data entry technicians, supervisors, students, volunteers, as well as the data managers and principal investigator, among others.

role – the role the person played in the census.

5. Personnel file

This file contains the names of the people who are or were involved with the plot, and the role that they played. The first and last names should be separate.

firstname – the first name of the person

lastname -  the last name of the person

role – the role the person played in the census. This should match exactly one of the descriptions in the RoleReference table.

If a person has more than one role (for example, he was a field technician in one census, then promoted to field supervisor in a later census), then that name should be entered twice. Note, this file is optional for self-managed plots.

3. Census data for uploading via ctfsweb

Bring the tree data from each census in a separate file. Each file must have the fields listed below. The columns can be in any order. You may have extra fields in the dataset, but they will not be uploaded into the tables of the database.

tag – the tag of the tree (should be unique)

stemtag – the tag of the stem. If your site does not use stem tags, you may leave this column blank. The header however, should include this variable name.

spcode – the species code of the tree. All species codes should appear in the species file.

quadrat – the name of the quadrat the tree is located in

lx – the x coordinate in meters of the tree within its quadrat.

ly – the y coordinate in meters of the tree within its quadrat.

These coordinates are the coordinates that result from digitizing.

dbh – the diameter of the tree. If there is no diameter measurement (missing, dead, or resprout), please put NA or NULL.

codes – tree or measurement codes. If there is more than one code, they have to be delimited with semicolons. This allows for codes with more than one letter. Each and every code should be accounted for in the Codes table in (2) above. The codes field may be left blank if there are no codes.

hom – height (in meters) where the diameter was measured, if different from 1.3 m. You may leave this field blank if the stem was measured at 1.3 m, and just fill it in when the hom is different from 1.3

date – date the stem was measured. This date should be in yyyy-mm-dd format. Example, 2011-02-24.

Note that all the multiple stems should be included in these files – you may indicate in the codes field which one is the main stem. If the tree only has one stem, you do not have to include the main stem code. The rest of the information should be repeated for each multiple stem - make sure that the information (species code, date, etc.) is the same for all multiple stems of the same tree.

The dataset for the first census should only contain trees and stems from that census. The dataset for subsequent censuses should contain stems from the previous  census. Dead or lost stems should have the appropriate codes.

CHAPTER 3. INSTRUCTIONS FOR SETTING UP A CTFS MySQL DATABASE

1. Introduction

This chapter outlines the process of settting up a new system. It begins with the installation of the programs needed on your computer to use the CTFS Web Data Management Systems and goes on to a step by step guide to creating a plot database.

2. Installing Apache, MySQL, and PHP

You need to install Apache (a web server software program), MySQL (an open-source relational database management system), and PHP (a programming language designed for web development) in your computer. There are several packages available, but we suggest you use XAMPP, which is relatively user-friendly. To install XAMPP, please follow the instructions in the document Installation Guide ctfsweb.pdf up to step 3.

http://ctfs.si.edu.edu/Public/software/ctfsweb/latest/Installation/Installation%20Guide.pdf

3. Creating the Site Database

To create the site database (step 4 of the Installation Guide), do the following:

Open a MySQL console in your computer with the following steps:

On your Desktop, click on Start, then Run

Type: cmd, then click on OK

In the DOS Window, change to the mysql folder by typing: cd\xampp\mysql\bin

If it asks for a password, just press ENTER (no password needed).

______________________________________________________________________________

NOTE:

If the xampp directory is not in your DOS path and you want to be able call up mysql anywhere, do the following carefully:

Go to the Control Panel in your System Settings

Click on System

Click on Advanced

Click on “Environment Variables” in the bottom

In System Variables in the second window, scroll to Path

Add the following to the end of the existing path:

;C:\xampp\mysql\bin

IMPORTANT: You can do the above only if you are an administrator of your computer.

______________________________________________________________________________

Once you have opened a mysql terminal, create a database by typing the following. Replace the name of the database current_yoursitename with the name of your database eg current_bci. The names of the mysql commands are in caps, but they can be in small letters. The names of the databases and tables, however, are case-sensitive.

mysql> CREATE DATABASE current_yoursitename;

mysql> USE current_yoursitename;

If you already have a database that you dumped (i.e.backed up), just source the resulting file:

mysql> SOURCE /path-to-file/yoursite.sql;

eg SOURCE C;/ctfs/databases/bci.sql;

NOTE: The path cannot contain spaces.

_____________________________________________________________________________

NOTE: For the following steps, the scripts can be downloaded from:

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/table_scripts/

______________________________________________________________________________

In the case that you have not entered any data yet and are not migrating from the old database structure, insert the empty tables into the database:

mysql> SOURCE New_Database_Structure.sql;

--Enter the taxonomic family and genus names from the APG system into the Family and Genus tables --

mysql> SOURCE Family.sql;

mysql> SOURCE Genus.sql;

4. Installing the ctfsweb System

Once the database is created, install the ctfsweb system by continuing on to Steps 5 through 7 in the Installation Guide ctfsweb.pdf.

5. Uploading your Site Data into the MySQL Database

1. Loading your site's fixed data

Ths step is required for all users creating a initial census dataset, whether you are using the ctfsweb data entry module or uploading previously entered census data. Prepare your data for uploading into your database. Your data files should be in the format as described in the chapter Format Data Files.

-- Create the temporary tables needed for ctfsweb --

mysql> SOURCE createTempTables.sql;

NOTE: For a brief explanation of the variables in these tables, refer to the data dictionary

http://ctfs.si.edu.edu/Public/DataDict/data_dict.php

– COUNTRY DATA –

mysql> INSERT INTO Country (CountryID, CountryName)

VALUES (1, 'Your Country Name');

– SITE DATA -

Here you must substitute your own site information. This information can be displayed on reports etc. Site description usually specifies forest type eg. “Upper montane tropical forest' , Shape can contain the length of each side for a rectangular plot or give particulars of the shape for an irregular plot. Area specifies the number of hectares or square meters of your plot and QdimX and QdimY the dimensions of each quadrat. If you use UOM other than meters you can specify them in the next four fields.

mysql> INSERT INTO Site (PlotID, PlotName, LocationName, CountryID, ShapeOfSite, DescriptionOfSite, Area, QDimX, QDimY, GUOM, GZUOM, PUOM, QUOM, GCoorCollected, PCoorCollected, QCoorCollected, IsStandardSize)

VALUES (1, 'yoursitename', 'Your Site Location eg. South Western Province', 1, '1000x500', 'Any site description you want to insert here', 500000, 20, 20,'','', 'm', 'm', 'N', 'Y', 'Y', 'Y');

– CENSUS DATA –

You need to insert one census record for each census you plan to upload.

mysql> INSERT INTO Census (CensusID, PlotID, PlotCensusNumber, StartDate, EndDate,

Description) VALUES (1, 1, 1, '2010-01-02', '2010-10-31', 'Any census description you want to insert here');

-- MEASUREMENT CODES –

The statement below directly loads your codes file into the codes table (TSMAttributes).

mysql> LOAD DATA LOCAL INFILE '/path-to-file/codes.txt' INTO TABLE TSMAttributes fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES (TSMCode, description);

-- PERSONNEL –

There are three tables that hold Personnel data, The Personneltable stores names, The RoleReference table holds job descriptions or roles and the PersonnelRole table links people with their roles.

-- First insert roles into the RoleReference table if empty.

[f you have a role text file with your list of roles then you may use the LOAD DATA LOCAL INFILE command to insert your data.

LOAD DATA LOCAL INFILE '/path-to-file/rolel.txt' INTO TABLE RoleReference fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES (Description);]

If you do not have a role.txt you can create some records with the insert statement below.

-- The three roles: field technician, field supervisor, and data entry technician are necessary, the

-- others are optional.

mysql> INSERT INTO RoleReference (RoleID, Description) VALUES

(1, 'scholarship recipient'),

(2, 'data administrator'),

(3, 'data entry technician'),

(4, 'data manager'),

(5, 'field supervisor'),

(6, 'field technician'),

(7, 'plot supervisor'),

(8, 'predoc student'),

(9, 'principal investigator'),

(10, 'volunteer');

Then add a list of people involved in the census and link to their roles. Your personnel.txt file should have the three columns firstname, lastname, and role as specified in the Format Data Files section.

mysql> DROP TABLE IF EXISTS stagePersonnel;

mysql> CREATE TABLE stagePersonnel(

PersonnelRoleID INT UNSIGNED AUTO_INCREMENT,

FirstName CHAR(30),

LastName CHAR(32),

Role CHAR(40),

RoleID INT,

PersonnelID INT,

PRIMARY KEY (PersonnelRoleID)

)ENGINE=MyISAM;

mysql> LOAD DATA LOCAL INFILE '/path-to-file/personnel.txt' INTO TABLE stagePersonnel fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES (FirstName, LastName, Role);

mysql> UPDATE stagePersonnel A, RoleReference B SET A.RoleID=B.RoleID WHERE A.Role=B.Description;

mysql> INSERT into Personnel (firstname, lastname) SELECT DISTINCT firstname,lastname FROM stagePersonnel;

mysql> UPDATE stagePersonnel A, Personnel B SET A.PersonnelID=B.PersonnelID WHERE A.FirstName=B.FirstName and A.LastName=B.LastName;

mysql> INSERT INTO PersonnelRole (PersonnelRoleID,PersonnelID,RoleID) SELECT PersonnelRoleID,PersonnelID,RoleID FROM stagePersonnel;

-- SPECIES DATA --

This is a key table in the system so take the time to review the file for spelling errors etc. Make sure the IDLevels are filled in. There should be at least one species code for unidentified species. Check the instructions in the Format Data Files section.

mysql> LOAD DATA LOCAL INFILE '/path-to-file/species.txt' INTO TABLE TempSpecies fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES (Mnemonic, Genus, SpeciesName, FieldFamily, Authority, IDLevel);

-- QUADRAT DATA –

There are three tables that hold location information. Quadrat, CensusQuadrat and Coordinates. The code below is written for census=1. If you are loading a recensus change the censusid accordingly.

-- LOAD Quadrat AND CensusQuadrat INFORMATION --

mysql> LOAD DATA LOCAL INFILE '/path-to-file/quadrat.txt' INTO TABLE TempQuadrat FIELDS TERMINATED BY '\t' IGNORE 1 LINES (QuadratName, StartX, StartY, DimX, DimY);

mysql> UPDATE TempQuadrat SET PlotID=1,CensusID=1;

mysql> INSERT INTO Quadrat (PlotID,QuadratName,Area,IsStandardShape,QuadratID)

SELECT PlotID,QuadratName,DimX*DimY,'Y',QuadratID FROM TempQuadrat;

mysql> INSERT INTO CensusQuadrat (CensusID,QuadratID) SELECT CensusID,QuadratID FROM TempQuadrat;

-- QX and QY are 0,0 if StartX and StartY refer to the lower lefthand corner of the quadrat --

mysql> INSERT INTO Coordinates (PlotID, QuadratID, PX, PY, QX, QY) SELECT PlotID, QuadratID, StartX, StartY, 0, 0) FROM TempQuadrat;

2. Loading your site's census data

If you are using ctfsweb data entry to enter data skip this section. Your data has to be uploaded into the corresponding Temp (temporary) tables in the database so that ctfsweb can then upload into the production (permanent) tables.

Because your data is probably not in the correct format for uploading into the Temp tables, upload them into staging tables first. Create staging tables that exactly reflect the structure of your text files, their columns and the type of data in them.

Below is only a guide: change the structure of the staging tables to conform to the structure of your text files.

-- LOAD CENSUS DATA --

-- The last 4 variables are necessary, include them in your structure.

mysql> DROP TABLE IF EXISTS stageCensus;

mysql> CREATE TABLE stageCensus(

Tag CHAR(10),

StemTag CHAR(32),

Mnemonic CHAR(10),

QuadratName CHAR(12),

x FLOAT(8) DEFAULT NULL,

y FLOAT(8) DEFAULT NULL,

DBH FLOAT(8) DEFAULT NULL,

Codes VARCHAR(50) DEFAULT NULL,

HOM FLOAT(8) DEFAULT NULL,

ExactDate DATE,

PlotID INT UNSIGNED,

PlotCensusNumber INT UNSIGNED,

CensusID INT UNSIGNED,

tempID INT UNSIGNED AUTO_INCREMENT,

PRIMARY KEY (tempID)

)ENGINE=MyISAM;

-- Change “path-to-file” to the path where your tab-delimited data files are --

mysql> LOAD DATA LOCAL INFILE '/path-to-file/census.txt' INTO TABLE stageCensus fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES (Tag, StemTag, Mnemonic, QuadratName, x, y, DBH, codes, HOM, Exactdate);

-- Replace all PlotID, PlotCensusNumber, and CensusID with the correct numbers, i.e. if first census, then use 1 --

mysql> UPDATE stageCensus SET PlotID=1,PlotCensusNumber=1,CensusID=1;

-- Add an index to the table --

mysql> ALTER TABLE stageCensus ADD INDEX(Tag,PlotID);

-- If site uses stemtags, create the following index instead:

mysql> ALTER TABLE stageCensus ADD INDEX(Tag,StemTag,PlotID);

-- Now insert the relevant columns into TempNewPlants and TempLocations--

mysql> INSERT INTO TempNewPlants (QuadratName, Tag, StemTag, Mnemonic, DBH, codes, HOM, ExactDate, x, y, PlotCensusNumber, PlotID, CensusID) SELECT QuadratName, Tag, StemTag, Mnemonic, DBH, codes, HOM, ExactDate, x, y, PlotCensusNumber, PlotID, CensusID FROM stageCensus;

mysql> INSERT INTO TempLocations (QuadratName, Tag, StemTag, X, Y, PlotCensusNumber, PlotID, CensusID) SELECT DISTINCT QuadratName, Tag, StemTag, x, y, PlotCensusNumber, PlotID, CensusID FROM stageCensus;

CHAPTER 4. GUIDE TO CTFSWEB

1. Creating a Configuration File with ctfsweb

Go to the ctfsweb system and call up the database using the username and password that you set up in Step 7d of the Installation Guide ctfsweb.pdf.

Click on Data Entry Configuration in the top menu and select your plot. Select New Plants Form as the form type. Then set up a configuration file with columns that are in the same order as your field sheet. You can start from scratch and add new columns or alternatively, you can use the default configuration and change it to reflect your field sheet.

The names that you enter in the Column Name parameter have to be written exactly the same as the names in Column Name in the List of Column Names (click on this list in the left panel), including whether they are in capital letters or not.

The options for column types are:

• Simple textbox with text, integer or decimal values without limits

This allows you to set up a column where you can enter any type of data but where no screening is done

• Simple textbox with numeric values with limits

This allows you to set up a column to enter numeric values, specifying their minimum and maximum values, preventing the user from entering values outside this range.

• Species dropdown

This creates a column where the user can only choose valid codes from the Species table

• Subquadrat dropdown

This allows the user to include as one of the fields the subquadrat where the current tree is located. In most sites, this refers to the 5x5 m subquadrat within the 20x20 m quadrat.

The “Starts with” option allows you to enter a “0” or “1”, referring to which digit the site uses to start naming the subquadrats, i.e. 00, 01, 02, 03, …..33 or 11, 12, 13, 14, …44.

• User dropdown

Allows you to specify specific options for the user to select. Separate the options with semicolons (;). Examples:

yes; no

L; B; M; D; B

• Simple textbox with a prefix value

This allows you to specify a field where the first few characters are set, while the rest changes from one record to the next. For example, some sites use tag numbers where the first 4 characters refer to the quadrat number, i.e. 1212-0001, 1212-0002, 1212-0003, and so on.

• Tree tag increment with stem tag

Several sites use stem tags that increase throughout the plot, instead of starting at 1 again for each new tree. This option allows the user to enter incremental numbers for the stem, whereas the tree gets the tag that is the smallest stem tag of that tree.

In all the options, except for the dropdowns, you have to configure the following 3 parameters for each column. These parameters permit the data entry person to enter data faster by not having to key in the tag numbers for each tree and stem, allowing the tag numbers to increment automatically from one record to the next.

1. Is Value Changed with Stem:

This lets you configure data entry such that the number entered into the current record is incremented by 1 in the next stem record (incremented) or optionally, the number in the current record is repeated in the next stem record (carried over).

2. Is Value Changed with Tree:

This lets you configure the data entry such that the number that is typed into the current record is incremented by 1 in the next tree record (incremented) or optionally, the number in the current record is repeated in the next tree record (carried over).

3. Add Next Row Stoke:

If you choose “Yes”, this allows the cursor to go to the next record when you press the [ENTER] or [PLUS] in this field.

You should not choose YES in the first fields of the form, not until all the necessary fields have data entered. This prevents blank variables from being erroneously recorded. We suggest that you choose the YES option only after the DBH variable.

CHAPTER 5. Guide to Correcting Data Errors

1. Field Staff

1. What is a data error?

Field Errors – A mistake made in the field. Some example of these errors may be duplication of existing tag numbers, incorrect measurements of dbh, incorrect identification of species, incorrect assumption of death in an individual. Some errors may be systematic. Eg a quadrat number may be off by one for an entire line.

Data Transcription errors- These errors are made after field data is brought in. They may be typing errors, format errors such as the incorrect use of delimiters for codes, omission errors where a data sheet or an entire column has not been entered. Errors may also occur in the digitizing system when entering new plants. Incorrect reading of tag numbers when digitising locations, incorrect subquadrat order when using ImageJ to convert pixels to plot coordinates etc.

2. When can you correct it?

While errors are best corrected as quickly as possible, it is more efficient to gather errors into coherent batches for submission. Typically a lot of error correction will occur immediately after a census. When all the known errors have been fixed the database is uploaded and a version of the database is frozen. Subsequent errors should be collected into batches and submitted every three months or so. Each batch of corrections will result in a minor freeze which is a new version of the database. At the beginning of each calendar year a special effort must be made to clean up all known database errors so that they can be corrected and uploaded by March each year for inclusion into a major freeze which is a version of the database that can be used by researchers.

3. Who is authorized to correct a data error for your site?

Each site has a data manager who is the person authorised to apply corrections to the official database for your site. The data manager may be local to your site or you may request assistance from a CTFS systems group data manager either Suzanne Lao and Shameema Esufali

4. How do you submit your corrections?

Corrections should be submitted in the form of spreadsheets (excel) or comma delimited textfiles. Each column in the spreadsheet should contain only one data item. At the minimum the spreadsheet should have the following columns:

Key - necessary column or columns needed to completely identify the item to be changed (eg tag and stem number to identify a stem to be changed, species code or complete name for a species change etc.

Old value – value to be changed eg. old dbh, old tag

New value – Corrected dbh, corrected tag

If there are different kinds of changes, for instance changes to individual trees and changes to species records then report them in separate spreadsheets or csv files.

2. Data Managers

1. How to correct data errors

Data errors are corrected by using a script to apply corrections to a known version of the database, typically the export of the current database.

This is best done in the following way.

1. Login to MySQL console window and create a blank test database in MySQL on your local computer.

mysql> DROP DATABASE IF EXISTS test_sitename;

mysql> CREATE DATABASE test_sitename;

mysql> USE test sitename;

2. Install a known version (usually the last export) of your dataset. This is your starting point or base dataset.

mysql> source ../sitename.sql

Every site should have an export file of their database in its current state. These can be downloaded from the CTFS website for the site using the login given to the PI for each site.

http://ctfs.si.edu.edu/sitename/

The current database is in the current subdirectory and previous versions in the archive directory

3. Source your correction script against the imported database

mysql> source ../correction_script.sql

Check your results, debug the script if needed and repeat from step 1.

When you are satisfied that the script is working correctly proceed to the next step.

4. Rebuild ViewFullTable

Use the Create Reports Tables in the Finishing Up option in the ctfsweb application to recreate ViewFullTable.

5. Export the corrected database. This is your new current version.

6. Send the following files to your CTFS Systems Data Manager (Suzanne or Shameema)

Correction_script.sql

csv/excel files used by the correction script

A README.txt file listing the base version of the database that you used at the beginning of this process.

A check sum file. A standard checksum script is available at ….. Run this and include the results file in your package.

3. Script writing

1. What is a script

A script is a program that can be run against the database to install corrections. Typically it reads one or more data correction files, the comma delimited files described above, and updates the erroneous value to a new value. Sometimes it needs to delete records, for example if there are duplicates, and sometimes missed records need to be inserted. Scripts are written in MySQL or php. A data manager should have a reasonable knowledge of MySQL. Scripts may be submitted to Suzanne or Shameema for review and either of us is always available for advice and examples of code.

2. Why do we write scripts to correct errors

This is an important standard for making corrections for several reasons.

If you start with a known database which has an exported dump file and run a script against it to make corrections and if you find that you have made a mistake, then you simply correct the script, reimport your clean database and rerun the corrected script.

The same process can be duplicated on any server that holds your database to produce an exact duplicate of your current database.

A permanent record of your changes is available to inspect in case of later problems.

3. Text editors and language standards for CTFS data manipulation

CTFS recommends the use of Geany, Notepad++ or gedit for script writing. These text editors are much more powerful than the standard Wordpad and Notepad: they can open large files significantly faster, they support smart syntax and code highlighting for many programming languages, they have line numbering, etc. Scripts must be written in MySQL. Advanced users may use php.

4. Base data

Corrections are usually applied to the last exported dataset. These datasets are available to authorised users at http://ctfs.si.edu.edu/sitename/current/ The PI for each site has the user name and password for the site and you must be authorised by the PI to retrieve and use site data.

5. Multiple scripts

It may be necessary to use more than one script. If scripts are to be run sequentially then the README file should list the commands needed to run the scripts in the correct order and provide any other information required to complete the upgrade.

4. Creating a package

6. What should be in a package

The following files should be in an upgrade package. The package should be compressed using winzip or tar before emailing.

Script or scripts (.sql or .php files)

Data (excel or csv files)

README.txt containing:

1. Name of programmer, site, date and a brief description of the corrections being made.

2. The version number of the base dataset prior to the application of the script

3. Source command or commands to execute script or scripts.

4. Checksum.sql (Checksum.sql is available at

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/sample_scripts/Checksum.sql

5. Run the Checksum.sql on your database before you make any changes.and save the results to a text file called ResultofChecksumbeforechanges.txt,

Repeat the checksum after you run the changes and copy the results to a text file called ResultofChecksumafterchanges.txt

Send these as the final component of your package.

5. How to test a package before submission

After you test the upgrade for accuracy on your own computer, one more step is required before dispatching the package. Programs that refer to files typically have a directory path that is only valid on your own computer. These paths can be standardized so that they work on any computer. The package root directory should have the following structure.

yoursiteupgrade2013OCT (root folder for the upgrade -change to reflect your site and upgrade)

scripts - directory

data - directory

README.txt -file

Checksum.sql -file

ResultofChecksumbeforechanges.txt -file

ResultofChecksumafterchanges.txt -file

Now go into the scripts directory and change the path of any referenced file so that it is relative to the root directory for the package .

Eg SOURCE scripts/correct_errors.sql

LOAD DATA LOCAL INFILE data/tag_corrections.csv …

Now start your mysql console from the root and run your upgrade according to the instructions in your readme file. If this runs without error your package is ready to compress and send.

6. Sample scripts – This chapter is in development

1. Insert new records

A sample script is available iat

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/sample_scripts/sampledeletetree.sql

2. Update existing records

3. Delete existing records

A sample script is available at

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/sample_scripts/sampleinserttree.sql

You will also need to download the utilities required to run this script

http://ctfs.si.edu.edu/Public/DatabaseSetup/ctfsweb/database_instructions/sample_scripts/utilities.sql

Change tag

Change Stemtag

Change DBH

4. Delete existing records

CHAPTER 6. Digitizing and Uploading New Plant Coordinates

1. Installing ImageJ and Point Picker

If your site uses paper maps, we suggest using the image processing and analysis software called ImageJ to digitize the points. This software is open source and available for Windows, Linux, and Mac OS platforms. You can download the program, as well as the instructions, from:

http://rsb.info.nih.gov/ij

You also need to install the plugin Point Picker. We have modified the original Point Picker program to allow labeling of the points. Install the modified Point Picker by extracting the files from the zipped file “pointpicker_modified3.zip” into /Program Files/ImageJ/plugins (or wherever ImageJ was installed). The description and user manual for Point Picker can be downloaded from: http://bigwww.epfl.ch/thevenaz/pointpicker/.

2. Using ImageJ to Digitize Plot Maps

1. Scan all your paper maps and save them as JPEGs on your computer. Use the quadrat name (and subquadrat, if applicable) of the map as part of the file name. Be consistent when naming the map images. Examples of possible naming conventions for JPEG map files are:
   e.g. “Q0312.jpg” or “Map_0312_2.jpg”
   “0312” denotes the quadrat number and in the second case, “2” specifies the subquadrat.

2. Open the ImageJ application and then open the specific JPEG map file that will be digitized and ultimately recorded as a text file.

Note: It is strongly recommended that the original paper copy of the hand-drawn map be examined alongside the corresponding datasheet to make sure all stems are actually mapped for the specific quadrat/subquadrat, and that all tag numbers correspond.

3. Once the JPEG image is open in ImageJ, select the “pointpicker” menu option as follows:
   Plugins → pointpicker → PointPicker

4. Select the “Add crosses” button (the very first option with the plus sign) to add points to the four corners of the map. The four corners of the subquadrat map should be digitized first for calibration, in the following order, and labeled as follows:
   p1 = lower left
   p2 = upper left

p3 = upper right

p4 = lower right

5. Now select the “Add crosses” button to digitize the trees by putting the cursor over the center of all the points. Label the points with the complete tag number.

One of the most common errors when digitizing maps is to leave out points, another is to label the points incorrectly. There are severaI ways of preventing this.

One suggestion is to digitize the points in sequential tag order, with out-of-sequence tags done at the very end.

Another approach would be to finish one subquadrat at a time, making sure to digitize all the stems from that subquadrat.

6. Check the resulting list after all crosses have been added to the map image. The Results list is accessed by clicking the “Export/Import list of points” button and then clicking on the “Show” option.

7. The “Magnifying glass” feature may be used to zoom in to areas of the map where stem dots or tag numbers appear small at the original magnification, or where many dots are clustered close together. Use the left-click button on the mouse to zoom in and the right-click button to zoom out.

Tag numbers may be edited using the “Edit labels” button (the second icon with a plus sign).

Crosses may be deleted using the “Remove crosses” button .

If any crosses added appear off center, the crosses may be adjusted using the “Move crosses” button .

8. In sites where the multiple stems from one tree may have different locations, each multiple stem should be digitized. If there is a case where only one dot is drawn in for a group of multiple stems, it is suggested that crosses be added to the main stem tag (the drawn dot) and the non-represented stems around the dot to account for the multiple stem tags.

9. Once all crosses have been added, check over the map image to ensure that every drawn-in tagged stem has an added cross to it. Next, the Results list should be reviewed to ensure that all four corners and all the tagged stems are displayed in the list and labeled correctly.

10. When everything is verified as being correct for the now digitized quadrat or subquadrat map, save the list of points and corresponding x/y coordinates to a text file. The text file may be saved by clicking the “Export/Import list of points” button and then clicking the “Save as” option.

11. Be consistent when naming the text files. The text files should be saved following the naming convention for the scanned maps.
    e.g. “Q0312.txt” or “Map_0312_2.txt”
    We suggest that the resulting text files be saved in directories denoting the column:
    e.g. “C:\Maps\Column03”

12. Following completion of the digitized quadrat/subquadrat map, the image must be closed before opening the next quadrat or subquadrat map image. This can be done in one of two ways:
    a) Click on the “Exit PointPicker” button , then click the “Done” option to exit the “PointPicker” feature which will remove all added crosses from the map image and return to the original ImageJ configuration. Then close the map image. Now the next map image may be opened.
    Or
    b) Simply close the ImageJ application altogether and then reopen the application to completely reset it for the next map image.

13. If a completed digitized map needs to be reopened to review the placement of crosses on the map image or the Results list, simply open the specific quadrat/subquadrat map image in ImageJ, select the “PointPicker” menu option, click on the “Export/Import list of points” button , and then click the “Open” option to open the text file that corresponds with the matching quadrat/subquadrat map image. All added crosses should appear in correct placement over the map image and the Results list can then be accessed following the same procedure outlined in step 7 above.

3. Converting ImageJ Text Files from Pixels to Meters

To convert imageJ text files from pixels to x,y coordinates in meters do the following in R:

1) Open R

2) Install or load the the CTFSRPackage:

attach('/path/CTFSRPackage.rdata')

3) Use the fullplot.imageJ function. A tutorial is available in:

http://ctfs.si.edu.edu/Public/CTFSRPackage/index.php/web/tutorials

Select ImageJ.

Choose the correct parameters for fullplot.imageJ according to the path where the text files are located, the size of your quadrats or subquadrats, and how you named your map files

For an explanation of the parameters, go to: http://ctfs.si.edu.edu/Public/CTFSRPackage/index.php/web/topics/topography~slash~imageJ.r/fullplot.imageJ

e.g. fullplot.imageJ(path='/maps/Column02', gridsize=c(10,10),

outfile='ConvertedCoord1.txt', prefix='Map_', colrange=c(0,49), rowrange=c(0,24),

corners=c('p1','p2','p3','p4'), subquadsuffix=c(‘_1’,’_2’,’_3’,’_4’))

a) This will put all the converted coordinates with their tag numbers and corresponding quadrat names (from the name of the text files) into a file called “ConvertedCoord1.txt”

b) This file will be saved in the path you specify: /maps/Column02/

c) All files in this path with “.txt” extension, “Map_” prefix and subquadrat suffix specified above will be converted (example: Map_0201_1.txt, Map_0222_2.txt, etc.)

d) The subquadrat suffix should be named in a clockwise direction starting from the lower left corner

e) The calibrated corners should also be named in a clockwise directions from the lower left corner

In the case that there are no subquadrats, only 20x20m quadrats:

e.g. fullplot.imageJ(path='/maps/Column02', gridsize=c(20,20), outfile='Converted.txt',
prefix='Q', colrange=c(0,49), rowrange=c(0,24), corners=c('p1','p2','p3','p4'))

All files in this path with “.txt” extension and “Q” prefix will be converted (example: Q0301.txt, Q0324.txt, etc.) and saved into a text file called “Converted.txt”.

Appendix to Chapter 2. Table Descriptions

The tables below need to be submitted to CTFS for inclusion into the program. The key transaction table is the last one listed 'ViewFullTable'. The balance tables are key reference tables that contain static data about the plot. Leave blank any foreign key id's or any other inapplicable data.

Appendix to Chapter 4. Troubleshooting long fieldsheets not saved.

Some computers have run into memory problems when entering data in ctfsweb, where long field sheets from a quadrat are not saved. When this happens, you may want to change the configuration php file that comes with XAMPP.

Do the following carefully.

1. Go to the /xampp/php/ folder and make a copy of the original php.ini file (i.e. copy it to another file called php_orig.ini).

2. Open php.ini in a text editor, look for the following parameters, and make the corresponding changes. Leave a space in front of and after the equal sign.

max_execution_time = 2400

max_input_time = 600

memory_limit = 768M

post_max_size = 48M

3. Add the following line, maybe after line 460:

max_input_vars = 20000

4. Save php.ini.

5. Stop Xampp and restart Apache and MySQL.

If you are still having problems, change the parameters again by increasing the numbers.