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

    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

);

--

    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)))) LEFT JOIN locationcoords USING (location_id)) LEFT 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)) 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 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: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: -

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

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

CREATE TABLE placecorrelation (

    placecorrelation_id int(11) NOT NULL,

    parentplace_id int(11) NOT NULL,

    childplace_id int(11) NOT NULL,

    placeconvergence text NOT NULL

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

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

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

CREATE TABLE placepath (

    placepath_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    placecode text,

    place_id int(11),

    continent text,

    country text,

    stateprovince text,

    county text,

    municipality text,

    site text

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

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

CREATE TABLE place (

    place_id integer NOT NULL,

    parent_id integer,

    rank placerank NOT NULL,

    placename text NOT NULL,

    placedescription text,

    accessioncode text

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

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

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

--

CREATE TABLE place_ancestor (

    place_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

    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)))) LEFT JOIN locationcoords USING (location_id)) LEFT 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 = 'continent'::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 = 'country'::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 = 'stateProvince'::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 = 'county'::placerank)))) 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 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: -

--

COMMENT ON VIEW analytical_db_view IS 'To generate hierarchical-table joins:

place:

For each placerank value, replace regexp ".*" with:

-----

LEFT JOIN place_ancestor AS $0_ancestor

ON $0_ancestor.place_id = locationcoords.place_id

LEFT JOIN place AS $0

ON $0.place_id = $0_ancestor.ancestor_id

AND $0.rank = ''$0''

-----

';

--

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

--

CREATE TABLE placepath (

    placepath_id integer NOT NULL,

    datasource_id integer NOT NULL,

    placecode text,

    place_id integer,

    continent text,

    country text,

    stateprovince text,

    county text,

    municipality text,

    site text

);

--

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

--

COMMENT ON TABLE placepath IS 'To include a place at a rank with no explicit column, create a place for it and point to it using place_id. To include multiple such places, chain the places together using parent_id, as a form of ordered linked list. Note that lower-level places should point to higher-level places.';

--