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

CREATE TABLE locationcoords (

    locationcoords_id integer NOT NULL,

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

COMMENT ON COLUMN locationcoords.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).';

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

COMMENT ON COLUMN locationcoords.coordsaccuracy IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.';

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

--

CREATE TABLE locationplace (

    locationplace_id integer NOT NULL,

    location_id integer NOT NULL,

    namedplace_id integer NOT NULL

);

--

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

--

COMMENT ON TABLE locationplace IS 'VegBank''s place table.';

--

    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], ' '::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 namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationplace.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = 'continent'::placerank)))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationplace.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = 'country'::placerank)))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationplace.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = 'stateProvince'::placerank)))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationplace.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = 'county'::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));

ON $0_ancestor.namedplace_id = locationcoords.namedplace_id

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

CREATE SEQUENCE locationcoords_locationcoords_id_seq

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

ALTER SEQUENCE locationcoords_locationcoords_id_seq OWNED BY locationcoords.locationcoords_id;

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

--

CREATE SEQUENCE locationplace_locationplace_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

--

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

--

ALTER SEQUENCE locationplace_locationplace_id_seq OWNED BY locationplace.locationplace_id;

--

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

ALTER TABLE locationcoords ALTER COLUMN locationcoords_id SET DEFAULT nextval('locationcoords_locationcoords_id_seq'::regclass);

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

--

ALTER TABLE locationplace ALTER COLUMN locationplace_id SET DEFAULT nextval('locationplace_locationplace_id_seq'::regclass);

--

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

ALTER TABLE ONLY locationcoords

    ADD CONSTRAINT locationcoords_pkey PRIMARY KEY (locationcoords_id);

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

--

ALTER TABLE ONLY locationplace

    ADD CONSTRAINT locationplace_pkey PRIMARY KEY (locationplace_id);

--

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

--

ALTER TABLE ONLY locationplace

    ADD CONSTRAINT locationplace_unique UNIQUE (location_id, namedplace_id);

--

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

--

CREATE UNIQUE INDEX locationcoords_unique ON locationcoords USING btree (location_id, (COALESCE(identifier_id, 2147483647)));

--

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

ALTER TABLE ONLY locationcoords

    ADD CONSTRAINT locationcoords_identifier_id_fkey FOREIGN KEY (identifier_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY locationcoords

    ADD CONSTRAINT locationcoords_location_id_fkey FOREIGN KEY (location_id) REFERENCES location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

--

ALTER TABLE ONLY locationplace

    ADD CONSTRAINT locationplace_location_id_fkey FOREIGN KEY (location_id) REFERENCES location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

ALTER TABLE ONLY locationplace

    ADD CONSTRAINT locationplace_namedplace_id_fkey FOREIGN KEY (namedplace_id) REFERENCES namedplace(namedplace_id) ON UPDATE CASCADE ON DELETE CASCADE;

--