--

-- 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: role; Type: TYPE; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

-- Name: taxon_update_ancestors(); 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 timestamp NULL

);

--

-- 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,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    taxonoccurrence_id int(11) NOT NULL,

    collectiondate timestamp NULL,

    cover double precision,

    linecover double precision,

    basalarea double precision,

    biomass double precision,

    inferencearea 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; 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,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    parent_id int(11),

    authorlocationcode text,

    confidentialitystatus int(11) DEFAULT 0 NOT NULL,

    confidentialityreason text,

    sublocationxposition double precision,

    sublocationyposition double precision,

    authorzone text,

    authordatum text,

    authorlocation text,

    locationnarrative text,

    azimuth double precision,

    shape text,

    area double precision,

    standsize text,

    placementmethod text,

    permanence int(1),

    layoutnarrative text,

    elevation double precision,

    elevationaccuracy double precision,

    elevationrange double precision,

    slopeaspect double precision,

    minslopeaspect double precision,

    maxslopeaspect double precision,

    slopegradient double precision,

    minslopegradient double precision,

    maxslopegradient double precision,

    topoposition text,

    landform text,

    surficialdeposits text,

    rocktype text,

    stateprovince text,

    country text,

    submitter_surname text,

    submitter_givenname text,

    submitter_email text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    dateentered timestamp NULL ,

    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 double precision,

    longitude double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy double precision,

    identifier_id int(11),

    determinationdate timestamp NULL,

    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; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

CREATE TABLE locationevent (

    locationevent_id int(11) NOT NULL,

    datasource_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),

    dateaccuracy text,

    method_id int(11),

    temperature double precision,

    precipitation 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 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),

    obsstartdate timestamp NULL,

    obsenddate timestamp NULL,

    dateentered timestamp NULL ,

    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

);

--

-- 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 double precision,

    mindiameter double precision,

    maxdiameter double precision,

    minheight double precision,

    maxheight double precision,

    observationtype text,

    observationmeasure text,

    covermethod_id int(11),

    samplingfactor double precision DEFAULT 1 NOT NULL,

    coverbasis text,

    stemsamplemethod text,

    shape text,

    length double precision,

    width double precision,

    radius double precision,

    area double precision,

    samplearea double precision,

    subplotspacing 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; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN method.maxheight; 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.length; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN method.subplotspacing; 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,

    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: place; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE place (

    place_id int(11) NOT NULL,

    parent_id int(11),

    rank text NOT NULL,

    placename text NOT NULL,

    placecode text,

    placedescription text,

    accessioncode text

);

--

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

--

--

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

--

CREATE TABLE place_ancestor (

    place_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

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

--

CREATE TABLE plantobservation (

    plantobservation_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    aggregateoccurrence_id int(11) NOT NULL,

    overallheight double precision,

    overallheightaccuracy 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,

    datasource_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,

    taxonpath_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 timestamp NULL,

    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,

    datasource_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: taxonpath; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonpath (

    taxonpath_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    plantcode text,

    taxon_id int(11),

    scientificname text,

    scientificnameauthor text,

    scientificnamewithauthor text,

    domain text,

    kingdom text,

    phylum text,

    class text,

    `order` text,

    family text,

    genus text,

    species text,

    subspecies text,

    variety text,

    forma text,

    cultivar text,

    accessioncode text

);

--

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

--

--

-- Name: COLUMN taxonpath.scientificnamewithauthor; 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`, taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS `taxonAuthor`, taxonpath.variety AS `taxonMorphospecies`, country.placename AS country, stateprovince.placename AS `stateProvince`, county.placename 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`, location.area AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude, locationcoords.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, aggregateoccurrence.cover AS `pctCover` FROM ((((((((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) JOIN locationcoords USING (location_id)) JOIN locationplace USING (location_id)) LEFT JOIN place_ancestor continent_ancestor ON ((continent_ancestor.place_id = locationplace.placepath_id))) LEFT JOIN place continent ON (((continent.place_id = continent_ancestor.ancestor_id) AND (continent.rank = CAST('continent' AS placerank))))) LEFT JOIN place_ancestor country_ancestor ON ((country_ancestor.place_id = locationplace.placepath_id))) LEFT JOIN place country ON (((country.place_id = country_ancestor.ancestor_id) AND (country.rank = CAST('country' AS placerank))))) LEFT JOIN place_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.place_id = locationplace.placepath_id))) LEFT JOIN place stateprovince ON (((stateprovince.place_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = CAST('stateProvince' AS placerank))))) LEFT JOIN place_ancestor county_ancestor ON ((county_ancestor.place_id = locationplace.placepath_id))) LEFT JOIN place county ON (((county.place_id = county_ancestor.ancestor_id) AND (county.rank = CAST('county' AS placerank))))) JOIN locationevent USING (location_id)) JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

--

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

--

--

-- 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 timestamp NULL,

    classstopdate timestamp NULL,

    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: 

--

CREATE TABLE commcorrelation (

    commcorrelation_id int(11) NOT NULL,

    commstatus_id int(11) NOT NULL,

    commconcept_id int(11) NOT NULL,

    commconvergence text NOT NULL,

    correlationstart timestamp NOT NULL,

    correlationstop timestamp NULL

);

--

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

--

--

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

--

--

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

--

CREATE TABLE commdetermination (

    commdetermination_id int(11) NOT NULL,

    commclass_id int(11) NOT NULL,

    commconcept_id int(11) NOT NULL,

    commcode text,

    commname text,

    classfit text,

    classconfidence text,

    commauthority_id int(11),

    notes text,

    type int(1),

    nomenclaturaltype int(1)

);

--

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

--

--

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

--

--

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

--

CREATE TABLE commlineage (

    commlineage_id int(11) NOT NULL,

    parentcommstatus_id int(11) NOT NULL,

    childcommstatus_id int(11) NOT NULL

);

--

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

--

--

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

--

--

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

--

CREATE TABLE commname (

    commname_id int(11) NOT NULL,

    commname text NOT NULL,

    reference_id int(11),

    dateentered timestamp NULL 

);

--

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

--

--

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

--

--

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

--

CREATE TABLE commstatus (

    commstatus_id int(11) NOT NULL,

    commconcept_id int(11) NOT NULL,

    reference_id int(11),

    commconceptstatus text NOT NULL,

    commparent_id int(11),

    commlevel text,

    commpartycomments text,

    party_id int(11) NOT NULL,

    startdate timestamp NOT NULL,

    stopdate timestamp NULL,

    accessioncode text

);