-- Name: keys_~type._plots_16_intercepts_for_each_verb_taxon_in_each_plo; Type: TYPE; Schema: public_validations; Owner: -

--

CREATE TYPE "keys_~type._plots_16_intercepts_for_each_verb_taxon_in_each_plo" AS (

	project_name text,

	plot_code text,

	taxon text

);

--

-- Name: values__~type._plots_16_intercepts_for_each_verb_taxon_in_each_; Type: TYPE; Schema: public_validations; Owner: -

--

CREATE TYPE "values__~type._plots_16_intercepts_for_each_verb_taxon_in_each_" AS (

	intercept_cm double precision

);

--

-- Name: ~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: 

--

CREATE TABLE "~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac" (

    project_name text,

    plot_code text,

    taxon text,

    intercept_cm double precision

);

--

-- Name: keys("~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac"); Type: FUNCTION; Schema: public_validations; Owner: -

--

CREATE FUNCTION keys(value "~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac") RETURNS "keys_~type._plots_16_intercepts_for_each_verb_taxon_in_each_plo"

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT ROW($1.project_name, $1.plot_code, $1.taxon)::public_validations."keys_~type._plots_16_intercepts_for_each_verb_taxon_in_each_plo"

$_$;

--

-- Name: values_("~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac"); Type: FUNCTION; Schema: public_validations; Owner: -

--

CREATE FUNCTION values_(value "~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac") RETURNS "values__~type._plots_16_intercepts_for_each_verb_taxon_in_each_"

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT ROW($1.intercept_cm)::public_validations."values__~type._plots_16_intercepts_for_each_verb_taxon_in_each_"

$_$;

--

-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: public_validations; Owner: -

--

CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS

 SELECT DISTINCT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon, 

    sum(ao.linecover_m) AS intercept_cm

   FROM ((((((((public.project p

   JOIN public.source s ON ((p.source_id = s.source_id)))

   JOIN public.locationevent le ON ((p.project_id = le.project_id)))

   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))

   JOIN public.plot l ON ((le.location_id = l.plot_id)))

   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))

   JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))

   JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))

   JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))

  WHERE (((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true)) AND (ao.linecover_m IS NOT NULL))

  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))

  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));

--

-- Name: VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: COMMENT; Schema: public_validations; Owner: -

--

COMMENT ON VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj IS '

Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in origina db

Note 2: Grouping mean cover for entire plot combines plots with subplots (separate cover measure for each species in each subplot) with  plots with strata (separate cover measures for each species in each  stratum) with plots without subplots or strata (one cover measure  per species per plot.

';

--

-- Data for Name: ~type._plots_16_intercepts_for_each_verb_taxon_in_each_plot_eac; Type: TABLE DATA; Schema: public_validations; Owner: -

--

--