--

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

--

--

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

--

--

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

--

--

-- Name: _has_taxonomic_name(taxonrank); Type: FUNCTION; Schema: public; Owner: -

--

--

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

--

--

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

--

--

-- Name: _taxonomic_name_is_epithet(taxonrank); 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: taxonlabel_0_matched_label_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

CREATE TABLE taxonlabel (

    taxonlabel_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    creationdate date,

    taxonstatus text,

    canon_label_id int(11),

    matched_label_id int(11),

    matched_label_fit_fraction double precision,

    parent_id int(11),

    taxonepithet text,

    rank text,

    taxonomicname text,

    accessioncode text

);

--

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

--

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN taxonlabel.canon_label_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonlabel.matched_label_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonlabel.matched_label_fit_fraction; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

CREATE TABLE stemobservation (

    stemobservation_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    plantobservation_id int(11) NOT NULL,

    authorstemcode text,

    tag text,

    tags text,

    xposition_m double precision,

    yposition_m double precision,

    diameterbreastheight_m double precision,

    basaldiameter_m double precision,

    diameteraccuracy_m double precision,

    height_m double precision,

    heightfirstbranch_m double precision,

    heightaccuracy_m double precision,

    health text,

    age double precision,

    accessioncode text

);

--

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

--

--

-- Name: COLUMN stemobservation.tags; 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,

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

--

CREATE TABLE taxonverbatim (

    taxonverbatim_id int(11) NOT NULL,

    taxonlabel_id int(11) NOT NULL,

    verbatimrank text,

    taxonomicname text,

    binomial text,

    author text,

    family text,

    genus text,

    species text,

    morphospecies text,

    description text

);

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN taxonverbatim.binomial; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN taxonverbatim.morphospecies; 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_taxonverbatim.family, accepted_taxonverbatim.genus, accepted_taxonverbatim.species, COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS taxon, accepted_taxonverbatim.author AS `taxonAuthor`, parsed_taxonverbatim.morphospecies AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)), 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`, stemobservation.xposition_m, stemobservation.yposition_m FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) 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 taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (datasource.organizationname IS NOT NULL);