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

--

CREATE FUNCTION placepath_matched_placepath_id_self_ref() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.placepath_id IS NULL THEN -- prepopulate placepath_id

        new.placepath_id = nextval('placepath_placepath_id_seq'::regclass);

    END IF;

    IF new.matched_placepath_id = 0 THEN -- make self-reference

        new.matched_placepath_id = new.placepath_id;

    END IF;

    RETURN new;

END;

$$;

--

    placepath_id integer NOT NULL,

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

CREATE TABLE placepath (

    placepath_id integer NOT NULL,

    matched_placepath_id integer,

    CONSTRAINT placepath_required_key CHECK (((((((placecode IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)) OR (otherranks IS NOT NULL)))

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

COMMENT ON TABLE placepath IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

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

COMMENT ON COLUMN placepath.matched_placepath_id IS 'The placepath containing the accepted name of this verbatim place path.  placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.

A accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.';

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

COMMENT ON COLUMN placepath.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level places come after higher-level places.';

    SELECT datasource.organizationname AS "institutionCode", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county, locationcoords.latitude_deg AS "decimalLatitude", locationcoords.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.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.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.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 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.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));

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

--

CREATE SEQUENCE placepath_placepath_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

--

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

--

ALTER SEQUENCE placepath_placepath_id_seq OWNED BY placepath.placepath_id;

--

-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE placecorrelation ALTER COLUMN placecorrelation_id SET DEFAULT nextval('placecorrelation_placecorrelation_id_seq'::regclass);

-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE placename ALTER COLUMN placename_id SET DEFAULT nextval('placename_placename_id_seq'::regclass);

-- Name: placepath_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE placepath ALTER COLUMN placepath_id SET DEFAULT nextval('placepath_placepath_id_seq'::regclass);

-- Name: placepath_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY placepath

    ADD CONSTRAINT placepath_pkey PRIMARY KEY (placepath_id);

--

CREATE UNIQUE INDEX locationplace_unique ON locationplace USING btree (location_id, placepath_id, (COALESCE(identifier_id, 2147483647)));

-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX placename_accessioncode_index ON placename USING btree (accessioncode);

-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX placename_unique ON placename USING btree ((COALESCE(parent_id, 2147483647)), placename, rank);

-- Name: placepath_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX placepath_unique_within_creator_by_code ON placepath USING btree (creator_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);

-- Name: placepath_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX placepath_unique_within_creator_by_name ON placepath USING btree (creator_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedplacename[]))) WHERE (placecode IS NULL);

-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

CREATE CONSTRAINT TRIGGER placename_update_ancestors AFTER INSERT OR UPDATE ON placename DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE placename_update_ancestors();

-- Name: placepath_matched_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -

CREATE TRIGGER placepath_matched_placepath_id_self_ref BEFORE INSERT OR UPDATE ON placepath FOR EACH ROW EXECUTE PROCEDURE placepath_matched_placepath_id_self_ref();

-- Name: locationplace_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT locationplace_placepath_id_fkey FOREIGN KEY (placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY placepath

    ADD CONSTRAINT placepath_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: placepath_matched_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY placepath

    ADD CONSTRAINT placepath_matched_placepath_id_fkey FOREIGN KEY (matched_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: placepath_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY placepath

    ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;

--