--

-- PostgreSQL database dump

--

--

-- Name: establishmentmeans_dwc; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE establishmentmeans_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: growthform; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: occurrencestatus_dwc; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: placerank; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE placerank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: rankedplacename; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: taxonrank; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE taxonrank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: rankedtaxonname; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: role; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: taxonclass; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

-- Name: _taxonconcept_set_matched_concept_id(int(11), int(11), double precision); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: _taxonconcept_set_parent_id(int(11), int(11)); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: party_creator_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: place_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_0_matched_concept_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_1_matched_concept_min_fit(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_2_propagate_accepted_concept_id(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_3_parent_id_avoid_self_ref(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonconcept (

    taxonconcept_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    creationdate date,

    accepted_concept_id int(11),

    matched_concept_id int(11),

    matched_concept_fit_fraction double precision,

    parent_id int(11),

    taxonepithet text,

    rank text,

    verbatimrank text,

    identifyingtaxonomicname text,

    taxonomicname text,

    author text,

    taxonomicnamewithauthor text,

    family text,

    genus text,

    species text,

    description text,

    accessioncode text

);

--

-- Name: TABLE taxonconcept; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.creator_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.sourceaccessioncode; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.creationdate; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.accepted_concept_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.matched_concept_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.matched_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.parent_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.taxonepithet; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.rank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.identifyingtaxonomicname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.taxonomicnamewithauthor; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.family; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.genus; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.species; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonconcept_update_ancestors(taxonconcept, int(11)); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_update_ancestors_on_insert(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_update_ancestors_on_update(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: -

--

--

-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE address (

    address_id int(11) NOT NULL,

    party_id int(11) NOT NULL,

    organization_id int(11),

    orgposition text,

    email text,

    deliverypoint text,

    city text,

    administrativearea text,

    postalcode text,

    country text,

    currentflag int(1),

    addressstartdate date

);

--

-- Name: address_address_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: address_address_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE aggregateoccurrence (

    aggregateoccurrence_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    taxonoccurrence_id int(11) NOT NULL,

    collectiondate date,

    cover_fraction double precision,

    linecover_m double precision,

    basalarea_m2 double precision,

    biomass_kg_m2 double precision,

    inferencearea_m2 double precision,

    count int(11),

    stratum_id int(11),

    coverindex_id int(11),

    occurrencestatus_dwc text DEFAULT 'present' NOT NULL,

    method_id int(11),

    notes text,

    accessioncode text

);

--

-- Name: TABLE aggregateoccurrence; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN aggregateoccurrence.linecover_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN aggregateoccurrence.occurrencestatus_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: location; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE location (

    location_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    parent_id int(11),

    authorlocationcode text,

    confidentialitystatus int(11) DEFAULT 0 NOT NULL,

    confidentialityreason text,

    sublocationxposition_m double precision,

    sublocationyposition_m double precision,

    authorzone text,

    authordatum text,

    authorlocation text,

    locationnarrative text,

    azimuth double precision,

    shape text,

    area_m2 double precision,

    standsize text,

    placementmethod text,

    permanence int(1),

    layoutnarrative text,

    elevation_m double precision,

    elevationaccuracy_m double precision,

    elevationrange_m double precision,

    verbatimelevation text,

    slopeaspect_deg double precision,

    minslopeaspect_deg double precision,

    maxslopeaspect_deg double precision,

    slopegradient_fraction double precision,

    minslopegradient_fraction double precision,

    maxslopegradient_fraction double precision,

    topoposition text,

    landform text,

    surficialdeposits text,

    rocktype text,

    submitter_surname text,

    submitter_givenname text,

    submitter_email text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    dateentered date ,

    locationrationalenarrative text,

    accessioncode text

);

--

-- Name: TABLE location; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationcoords; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationcoords (

    locationcoords_id int(11) NOT NULL,

    location_id int(11) NOT NULL,

    latitude_deg double precision,

    longitude_deg double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision,

    identifier_id int(11),

    determinationdate date,

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    calculated int(1)

);

--

-- Name: COLUMN locationcoords.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN locationcoords.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationevent; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationevent (

    locationevent_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    parent_id int(11),

    location_id int(11),

    project_id int(11),

    authoreventcode text,

    previous_id int(11),

    obsstartdate date,

    obsenddate date,

    dateaccuracy text,

    method_id int(11),

    temperature_c double precision,

    precipitation_m double precision,

    autotaxoncover int(1),

    originaldata text,

    effortlevel text,

    floristicquality text,

    bryophytequality text,

    lichenquality text,

    locationeventnarrative text,

    landscapenarrative text,

    homogeneity text,

    phenologicaspect text,

    representativeness text,

    standmaturity text,

    successionalstatus text,

    basalarea double precision,

    hydrologicregime text,

    soilmoistureregime text,

    soildrainage text,

    watersalinity text,

    waterdepth_m double precision,

    shoredistance double precision,

    soildepth double precision,

    organicdepth double precision,

    soiltaxon_id int(11),

    soiltaxonsrc text,

    percentbedrock double precision,

    percentrockgravel double precision,

    percentwood double precision,

    percentlitter double precision,

    percentbaresoil double precision,

    percentwater double precision,

    percentother double precision,

    nameother text,

    treeht double precision,

    shrubht double precision,

    fieldht double precision,

    nonvascularht double precision,

    submergedht double precision,

    treecover double precision,

    shrubcover double precision,

    fieldcover double precision,

    nonvascularcover double precision,

    floatingcover double precision,

    submergedcover double precision,

    dominantstratum text,

    growthform1type text,

    growthform2type text,

    growthform3type text,

    growthform1cover double precision,

    growthform2cover double precision,

    growthform3cover double precision,

    totalcover double precision,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    dateentered date ,

    toptaxon1name text,

    toptaxon2name text,

    toptaxon3name text,

    toptaxon4name text,

    toptaxon5name text,

    numberoftaxa int(11),

    accessioncode text

);

--

-- Name: TABLE locationevent; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationplace; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationplace (

    locationplace_id int(11) NOT NULL,

    location_id int(11) NOT NULL,

    placepath_id int(11) NOT NULL,

    identifier_id int(11)

);

--

-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE method (

    method_id int(11) NOT NULL,

    reference_id int(11),

    name text NOT NULL,

    description text,

    diameterheight_m double precision,

    mindiameter_m double precision,

    maxdiameter_m double precision,

    minheight_m double precision,

    maxheight_m double precision,

    observationtype text,

    observationmeasure text,

    covermethod_id int(11),

    samplingfactor double precision DEFAULT 1 NOT NULL,

    coverbasis text,

    stemsamplemethod text,

    shape text,

    length_m double precision,

    width_m double precision,

    radius_m double precision,

    area_m2 double precision,

    samplearea_m2 double precision,

    subplotspacing_m double precision,

    subplotmethod_id int(11),

    pointsperline int(11),

    accessioncode text

);

--

-- Name: TABLE method; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.reference_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.description; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.diameterheight_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.mindiameter_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.maxdiameter_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.minheight_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.maxheight_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.observationtype; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.observationmeasure; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.samplingfactor; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.coverbasis; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.stemsamplemethod; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.samplearea_m2; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.subplotspacing_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.subplotmethod_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.pointsperline; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: party; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE party (

    party_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    fullname text,

    salutation text,

    givenname text,

    middlename text,

    surname text,

    organizationname text,

    currentname_id int(11),

    contactinstructions text,

    email text,

    partytype text,

    partypublic int(1) DEFAULT true,

    d_obscount int(11),

    accessioncode text

);

--

-- Name: COLUMN party.creator_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: placepath; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE placepath (

    placepath_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    placecode text,

    matched_placepath_id int(11),

    place_id int(11),

    continent text,

    country text,

    stateprovince text,

    county text,

    municipality text,

    site text,

    otherranks text

);

--

-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: plantobservation; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantobservation (

    plantobservation_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    aggregateoccurrence_id int(11) NOT NULL,

    overallheight_m double precision,

    overallheightaccuracy_m double precision,

    collectionnumber text,

    stemcount int(11),

    plant_id int(11),

    accessioncode text

);

--

-- Name: TABLE plantobservation; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN plantobservation.collectionnumber; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: specimenreplicate; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE specimenreplicate (

    specimenreplicate_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    plantobservation_id int(11),

    institution_id int(11),

    collectioncode_dwc text,

    catalognumber_dwc text,

    description text,

    specimen_id int(11),

    accessioncode text

);

--

-- Name: TABLE specimenreplicate; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxondetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxondetermination (

    taxondetermination_id int(11) NOT NULL,

    taxonoccurrence_id int(11) NOT NULL,

    taxonconcept_id int(11) NOT NULL,

    party_id int(11),

    role text DEFAULT 'unknown' NOT NULL,

    determinationtype text,

    reference_id int(11),

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    determinationdate date,

    accessioncode text

);

--

-- Name: TABLE taxondetermination; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonoccurrence (

    taxonoccurrence_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    locationevent_id int(11),

    authortaxoncode text,

    verbatimcollectorname text,

    growthform text,

    iscultivated int(1),

    cultivatedbasis text,

    isnative int(1),

    accessioncode text

);

--

-- Name: TABLE taxonoccurrence; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW analytical_db_view AS

    SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS `taxonAuthor`, accepted_taxonconcept.taxonepithet 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`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_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 taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

--

-- Name: classcontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE classcontributor (

    classcontributor_id int(11) NOT NULL,

    commclass_id int(11) NOT NULL,

    party_id int(11) NOT NULL,

    role text

);

--

-- Name: classcontributor_classcontributor_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: classcontributor_classcontributor_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commclass; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commclass (

    commclass_id int(11) NOT NULL,

    locationevent_id int(11) NOT NULL,

    inspection int(1),

    tableanalysis int(1),

    multivariateanalysis int(1),

    classpublication_id int(11),

    classnotes text,

    commname text,

    commcode text,

    commframework text,

    commlevel text,

    classstartdate date,

    classstopdate date,

    expertsystem text,

    accessioncode text

);

--

-- Name: commclass_commclass_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commclass_commclass_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commconcept (

    commconcept_id int(11) NOT NULL,

    commname_id int(11) NOT NULL,

    reference_id int(11),

    commdescription text,

    d_obscount int(11),

    commname text,

    d_currentaccepted int(1),

    accessioncode text

);

--

-- Name: commconcept_commconcept_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commconcept_commconcept_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commcorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--