-- -----------------------------------------------------------------------
-- Create the BIEN3 analytical table
--
-- Created by: Brad Boyle
-- Date created: 15 April 2014
-- -----------------------------------------------------------------------

-- -----------------------------------------------------------------------
-- Table: viewFullOccurrence
-- 
-- Uses: (1) Analyses requiring georeferenced occurrences (presence only) 
-- 		of species, (2) Analyses requiring standardized local abundance of 
-- 		species, (3) Analyses requiring additional information such as 
-- 		locality description, specimen description or observation date.
-- Scope: All georeferenced observations of taxa which are identified to
-- 		species level or lower, excluding morphospecies. Includes
--		specimen and plot observations, and trait observations, if 
-- 		georeferenced. A given species in a given
--		plot is recorded only once, with abundance summarized at the plot
-- 		level. Abundance applies only to plot observations, and it 
-- 		summarized in one of several columns as percent cover or counts 
-- 		of individuals at various minimum dbh cutoffs, whichever applies
-- 		to the plot in question. All observations in the database are 
--		included, and some may not be relevant or applicable to particular
-- 		analyses. Users are advised to filter records carefully using
--		columns such as isGeovalid, isCultivated, etc. (see Data Dictionary
-- 		for details). Trait measurements are not included; these should
-- 		be obtained from the separate analytical table "trait". 
-- Note: This table is modeled after table "observation" in the bien2 
-- 		bien2 analytical database "bien_web". However, I have 
-- 		preserved the name "viewFullOccurrence" because that is the 
-- 		name with which BIEN users are most familiar. Table 
-- 		bien_web.observation was a more metadata-rich version of 
-- 		viewFullOccurrence; it also corrected several issues present in 
-- 		the original bien2.viewFullOccurrence.
-- -----------------------------------------------------------------------

DROP TABLE IF EXISTS viewFullOccurrence;
CREATE TABLE viewFullOccurrence (
viewFullOccurrenceID INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
occurrenceType VARCHAR(10) DEFAULT NULL,
datasetID INTEGER(11) UNSIGNED DEFAULT NULL,
datasetName VARCHAR(100) DEFAULT NULL,
proximateProviderID INTEGER(11) UNSIGNED DEFAULT NULL,
proximateProviderName VARCHAR(100) DEFAULT NULL,
primaryProviderID INTEGER(11) UNSIGNED DEFAULT NULL,
primaryProviderName VARCHAR(100) DEFAULT NULL,
specimenReplicateID INTEGER(11) UNSIGNED DEFAULT NULL,
specimenHerbariumAcronym VARCHAR(10) DEFAULT NULL, 
plotID INTEGER(11) UNSIGNED DEFAULT NULL,
plotCode VARCHAR(100) DEFAULT NULL,
taxonID INTEGER(11) UNSIGNED DEFAULT NULL,
taxonRank VARCHAR(50) DEFAULT NULL,
familyVerbatim VARCHAR(100) DEFAULT NULL,
taxonAuthorityVerbatim VARCHAR(255) DEFAULT NULL,
higherPlantGroup VARCHAR(25) DEFAULT NULL,
family VARCHAR(100) DEFAULT NULL,
genus VARCHAR(100) DEFAULT NULL,
species VARCHAR(150) DEFAULT NULL,
taxon VARCHAR(200) DEFAULT NULL,
taxonAuthor VARCHAR(255) DEFAULT NULL,
taxonMorphospecies VARCHAR(255) DEFAULT NULL,
tnrsTaxonomicStatus VARCHAR(2) DEFAULT NULL,
country VARCHAR(50) DEFAULT NULL,
stateProvince VARCHAR(100) DEFAULT NULL,
countyParish VARCHAR(100) DEFAULT NULL,
countryError double DEFAULT NULL,
stateProvinceError double DEFAULT NULL,
countyParishError double DEFAULT NULL,
latitudeVerbatim VARCHAR(150) DEFAULT NULL,
longitudeVerbatim VARCHAR(150) DEFAULT NULL,
latitude DECIMAL(10,5) DEFAULT NULL,
longitude DECIMAL(10,5) DEFAULT NULL,
elevation_m decimal(6,1) DEFAULT NULL,
isGeovalid INTEGER(1) DEFAULT NULL,
isNewWorld INTEGER(1) DEFAULT NULL,
isIntroduced INTEGER(1) default 0,
isCultivated INTEGER(1) default 0,
specimenCollector VARCHAR(150) DEFAULT NULL,
observationDate DATE DEFAULT NULL,
identifiedBy VARCHAR(150) DEFAULT NULL,
identifiedDate DATE DEFAULT NULL, 
localityDescription VARCHAR(500) DEFAULT NULL,
specimenDescription VARCHAR(500) DEFAULT NULL,
plotCode VARCHAR(100) DEFAULT NULL,
plotMethod VARCHAR(100) DEFAULT NULL,
plotAreaHa double DEFAULT NULL,
plotMinDbh decimal(5,1) DEFAULT NULL,
abund INTEGER(11) DEFAULT NULL,
abund1 INTEGER(11) DEFAULT NULL,
`abund2.5` INTEGER(11) DEFAULT NULL,
abund10 INTEGER(11) DEFAULT NULL,
percentCover DECIMAL(10,5) DEFAULT NULL
) engine=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Indexes on table viewFullOccurrence
ALTER TABLE viewFullOccurrence
ADD PRIMARY KEY (viewFullOccurrenceID),
ADD INDEX (occurrenceType),
ADD INDEX (datasetID),
ADD INDEX (datasetName),
ADD INDEX (proximateProviderID),
ADD INDEX (proximateProviderName),
ADD INDEX (primaryProviderID),
ADD INDEX (primaryProviderName),
ADD INDEX (specimenReplicateID),
ADD INDEX (specimenHerbariumAcronym),
ADD INDEX (plotID),
ADD INDEX (plotCode),
ADD INDEX (taxonID),
ADD INDEX (taxonRank),
ADD INDEX (familyVerbatim),
ADD INDEX (taxonAuthorityVerbatim),
ADD INDEX (higherPlantGroup),
ADD INDEX (family),
ADD INDEX (genus),
ADD INDEX (species),
ADD INDEX (taxon),
ADD INDEX (taxonAuthor),
ADD INDEX (taxonMorphospecies),
ADD INDEX (tnrsTaxonomicStatus),
ADD INDEX (country),
ADD INDEX (stateProvince),
ADD INDEX (countyParish),
ADD INDEX (isGeovalid),
ADD INDEX (isNewWorld),
ADD INDEX (isIntroduced),
ADD INDEX (isCultivated),
ADD INDEX (specimenCollector),
ADD INDEX (identifiedBy),
ADD INDEX (plotCode),
ADD INDEX (plotMethod),
ADD INDEX (plotAreaHa),
ADD INDEX (plotMinDbh)
;