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

--

CREATE TABLE reference (

    reference_id int(11) NOT NULL,

    shortname text,

    `fulltext` text,

    referencetype text,

    title text,

    titlesuperior text,

    referencejournal_id int(11),

    volume text,

    issue text,

    pagerange text,

    totalpages int(11),

    publisher text,

    publicationplace text,

    isbn text,

    edition text,

    numberofvolumes int(11),

    chapternumber int(11),

    reportnumber int(11),

    communicationtype text,

    degree text,

    url text,

    doi text,

    additionalinfo text,

    pubdate date,

    accessdate date,

    conferencedate date,

    accessioncode text

);

--

    SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.reference_id = datasource.party_id))) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_id)) LEFT JOIN coordinates USING (coordinates_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 datasource_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.reference_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));

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

--

CREATE TABLE reference (

    reference_id integer NOT NULL,

    shortname text,

    fulltext text,

    referencetype text,

    title text,

    titlesuperior text,

    referencejournal_id integer,

    volume text,

    issue text,

    pagerange text,

    totalpages integer,

    publisher text,

    publicationplace text,

    isbn text,

    edition text,

    numberofvolumes integer,

    chapternumber integer,

    reportnumber integer,

    communicationtype text,

    degree text,

    url text,

    doi text,

    additionalinfo text,

    pubdate date,

    accessdate date,

    conferencedate date,

    accessioncode text

);

--