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