Revision 12607
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/validation/aggregating/plots/bien3_validations_salvias_vegbien.sql | ||
|---|---|---|
| 20 | 20 |
|
| 21 | 21 |
SET search_path TO public_validations, public; |
| 22 | 22 |
|
| 23 |
-- ------------------ |
|
| 24 |
-- 1. Count of projects |
|
| 25 |
-- Check: identical count in source db |
|
| 26 |
-- ------------------ |
|
| 27 | 23 |
CREATE OR REPLACE VIEW _plots_01_count_of_projects AS |
| 28 |
SELECT COUNT(*) AS projects
|
|
| 29 |
FROM project p JOIN source s
|
|
| 30 |
ON p.source_id=s.source_id
|
|
| 31 |
WHERE s.shortname=current_schema;
|
|
| 24 |
SELECT count(*) AS projects
|
|
| 25 |
FROM (project p
|
|
| 26 |
JOIN source s ON ((p.source_id = s.source_id)))
|
|
| 27 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
| 32 | 28 |
|
| 33 |
-- ------------------ |
|
| 34 |
-- 2. List of project names |
|
| 35 |
-- Check: join to source db returns same number of rows |
|
| 36 |
-- ------------------ |
|
| 37 | 29 |
CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS |
| 38 |
SELECT p.projectname
|
|
| 39 |
FROM project p JOIN source s
|
|
| 40 |
ON p.source_id=s.source_id
|
|
| 41 |
WHERE s.shortname=current_schema;
|
|
| 30 |
SELECT p.projectname AS project_name
|
|
| 31 |
FROM (project p
|
|
| 32 |
JOIN source s ON ((p.source_id = s.source_id)))
|
|
| 33 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
| 42 | 34 |
|
| 43 |
-- ------------------ |
|
| 44 |
-- 3. Count of all plots in this source |
|
| 45 |
-- Check: identical count in source db |
|
| 46 |
-- ------------------ |
|
| 47 | 35 |
CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS |
| 48 |
SELECT COUNT(*) AS plots |
|
| 49 |
FROM location l JOIN locationevent le |
|
| 50 |
ON l.location_id=le.location_id |
|
| 51 |
JOIN project p |
|
| 52 |
ON p.project_id=le.project_id |
|
| 53 |
JOIN source s |
|
| 54 |
ON p.source_id=s.source_id |
|
| 55 |
WHERE s.shortname=current_schema; |
|
| 36 |
SELECT count(*) AS plots |
|
| 37 |
FROM (((plot l |
|
| 38 |
JOIN locationevent le ON ((l.location_id = le.location_id))) |
|
| 39 |
JOIN project p ON ((p.project_id = le.project_id))) |
|
| 40 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 41 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
| 56 | 42 |
|
| 57 |
-- ------------------ |
|
| 58 |
-- 4. Count of plots in each project in this source |
|
| 59 |
-- ------------------ |
|
| 60 | 43 |
CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS |
| 61 |
SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots |
|
| 62 |
FROM location l JOIN locationevent le |
|
| 63 |
ON l.location_id=le.location_id |
|
| 64 |
JOIN project p |
|
| 65 |
ON p.project_id=le.project_id |
|
| 66 |
JOIN source s |
|
| 67 |
ON p.source_id=s.source_id |
|
| 68 |
WHERE s.shortname=current_schema |
|
| 69 |
GROUP BY p.projectname; |
|
| 44 |
SELECT p.projectname AS project_name, |
|
| 45 |
count(DISTINCT l.location_id) AS plots |
|
| 46 |
FROM (((plot l |
|
| 47 |
JOIN locationevent le ON ((l.location_id = le.location_id))) |
|
| 48 |
JOIN project p ON ((p.project_id = le.project_id))) |
|
| 49 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 50 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 51 |
GROUP BY p.projectname; |
|
| 70 | 52 |
|
| 71 |
-- ------------------ |
|
| 72 |
-- 5.List of plot codes by project |
|
| 73 |
-- Check: join to source db by all columns returns same number of rows |
|
| 74 |
-- ------------------ |
|
| 75 | 53 |
CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS |
| 76 |
SELECT p.projectname, l.authorlocationcode AS "plotCode" |
|
| 77 |
FROM location l JOIN locationevent le |
|
| 78 |
ON l.location_id=le.location_id |
|
| 79 |
JOIN project p |
|
| 80 |
ON p.project_id=le.project_id |
|
| 81 |
JOIN source s |
|
| 82 |
ON p.source_id=s.source_id |
|
| 83 |
WHERE s.shortname=current_schema; |
|
| 54 |
SELECT p.projectname AS project_name, |
|
| 55 |
l.authorlocationcode AS plot_code |
|
| 56 |
FROM (((plot l |
|
| 57 |
JOIN locationevent le ON ((l.location_id = le.location_id))) |
|
| 58 |
JOIN project p ON ((p.project_id = le.project_id))) |
|
| 59 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 60 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
| 84 | 61 |
|
| 85 |
-- ------------------ |
|
| 86 |
-- 6. List of plots with stem measurements |
|
| 87 |
-- ------------------ |
|
| 88 | 62 |
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS |
| 63 |
SELECT project.projectname AS project_name, |
|
| 64 |
plot.authorlocationcode AS plot_code |
|
| 65 |
FROM ((plot |
|
| 66 |
JOIN locationevent USING (location_id)) |
|
| 67 |
JOIN project USING (project_id)) |
|
| 68 |
WHERE ((plot.source_id = ( SELECT source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 69 |
FROM (((((location |
|
| 70 |
JOIN locationevent locationevent_1 USING (location_id)) |
|
| 71 |
JOIN taxonoccurrence USING (locationevent_id)) |
|
| 72 |
JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
|
| 73 |
JOIN plantobservation USING (aggregateoccurrence_id)) |
|
| 74 |
JOIN stemobservation USING (plantobservation_id)) |
|
| 75 |
WHERE (location.plot_location_id = plot.location_id) |
|
| 76 |
LIMIT 1))) |
|
| 77 |
ORDER BY plot.authorlocationcode; |
|
| 89 | 78 |
|
| 90 |
SELECT authorlocationcode |
|
| 91 |
FROM top_plot |
|
| 92 |
WHERE source_id = (SELECT source_by_shortname(current_schema)) |
|
| 93 |
AND EXISTS( |
|
| 94 |
SELECT NULL |
|
| 95 |
FROM location |
|
| 96 |
JOIN locationevent USING (location_id) |
|
| 97 |
JOIN taxonoccurrence USING (locationevent_id) |
|
| 98 |
JOIN aggregateoccurrence USING (taxonoccurrence_id) |
|
| 99 |
JOIN plantobservation USING (aggregateoccurrence_id) |
|
| 100 |
JOIN stemobservation USING (plantobservation_id) |
|
| 101 |
WHERE location.top_plot = top_plot.location_id |
|
| 102 |
LIMIT 1 |
|
| 103 |
) |
|
| 104 |
ORDER BY authorlocationcode |
|
| 105 |
; |
|
| 106 |
|
|
| 107 | 79 |
-- ------------------ |
| 108 | 80 |
-- 7.List of plots with counts of individuals per species |
| 109 | 81 |
-- ------------------ |
| ... | ... | |
| 124 | 96 |
-- ------------------ |
| 125 | 97 |
CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS |
| 126 | 98 |
|
| 127 |
|
|
| 128 |
-- ------------------ |
|
| 129 |
-- 10. Count of individuals per plot in each project |
|
| 130 |
-- |
|
| 131 |
-- Method: count taxonObservations |
|
| 132 |
-- Note 1: `individuals` should be NULL for plots which use percent cover |
|
| 133 |
-- or line-intercept methods. |
|
| 134 |
-- Note 2: Does this method requires that plots have 2-level nesting of |
|
| 135 |
-- subplots within plots? |
|
| 136 |
-- ------------------ |
|
| 137 | 99 |
CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS |
| 138 |
select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals |
|
| 139 |
from project p join source s |
|
| 140 |
on p.source_id=s.source_id |
|
| 141 |
join locationevent le on p.project_id=le.project_id |
|
| 142 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 143 |
join location l on le.location_id=l.location_id |
|
| 144 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 145 |
join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id |
|
| 146 |
where s.shortname=current_schema |
|
| 147 |
group by p.projectname, l.authorlocationcode |
|
| 148 |
order by p.projectname, l.authorlocationcode; |
|
| 100 |
SELECT p.projectname AS project_name, |
|
| 101 |
l.authorlocationcode AS plot_code, |
|
| 102 |
sum(ao.count) AS individuals |
|
| 103 |
FROM ((((((project p |
|
| 104 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 105 |
JOIN place_visit ON ((p.project_id = place_visit.project_id))) |
|
| 106 |
JOIN locationevent le ON ((place_visit.locationevent_id = le.place_visit_id))) |
|
| 107 |
JOIN plot l ON ((place_visit.location_id = l.location_id))) |
|
| 108 |
JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id))) |
|
| 109 |
JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
|
| 110 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 111 |
GROUP BY p.projectname, l.authorlocationcode |
|
| 112 |
ORDER BY p.projectname, l.authorlocationcode; |
|
| 113 |
/* |
|
| 114 |
Method: count taxonObservations |
|
| 115 |
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods. |
|
| 116 |
Note 2: Does this method requires that plots have 2-level nesting of subplots within plots? |
|
| 117 |
*/ |
|
| 149 | 118 |
|
| 150 |
-- ------------------ |
|
| 151 |
-- 11. Count of stems per plot in each project |
|
| 152 |
-- |
|
| 153 |
-- Method: count records in stemobservation table |
|
| 154 |
-- ------------------ |
|
| 119 |
CREATE OR REPLACE VIEW _plots_10a_aggregate_observation_individual_counts AS |
|
| 120 |
SELECT p.projectname AS project_name, |
|
| 121 |
l.authorlocationcode AS plot_code, |
|
| 122 |
o.sourceaccessioncode AS individual_id, |
|
| 123 |
ao.count AS individuals |
|
| 124 |
FROM ((((((project p |
|
| 125 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 126 |
JOIN place_visit ON ((p.project_id = place_visit.project_id))) |
|
| 127 |
JOIN locationevent le ON ((place_visit.locationevent_id = le.place_visit_id))) |
|
| 128 |
JOIN plot l ON ((place_visit.location_id = l.location_id))) |
|
| 129 |
JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id))) |
|
| 130 |
JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
|
| 131 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 132 |
ORDER BY p.projectname, l.authorlocationcode, ao.sourceaccessioncode; |
|
| 133 |
/* |
|
| 134 |
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods. |
|
| 135 |
*/ |
|
| 136 |
|
|
| 155 | 137 |
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS |
| 156 |
select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems |
|
| 157 |
from project p join source s |
|
| 158 |
on p.source_id=s.source_id |
|
| 159 |
join locationevent le on p.project_id=le.project_id |
|
| 160 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 161 |
join location l on le.location_id=l.location_id |
|
| 162 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 163 |
join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id |
|
| 164 |
join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id |
|
| 165 |
join stemobservation so on so.plantobservation_id=po.plantobservation_id |
|
| 166 |
where s.shortname=current_schema |
|
| 167 |
group by p.projectname, l.authorlocationcode |
|
| 168 |
order by p.projectname, l.authorlocationcode; |
|
| 138 |
SELECT p.projectname AS project_name, |
|
| 139 |
l.authorlocationcode AS plot_code, |
|
| 140 |
count(DISTINCT so.stemobservation_id) AS stems |
|
| 141 |
FROM ((((((((project p |
|
| 142 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 143 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 144 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 145 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 146 |
JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
|
| 147 |
JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
|
| 148 |
JOIN plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id))) |
|
| 149 |
JOIN stemobservation so ON ((so.plantobservation_id = po.plantobservation_id))) |
|
| 150 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 151 |
GROUP BY p.projectname, l.authorlocationcode |
|
| 152 |
ORDER BY p.projectname, l.authorlocationcode; |
|
| 153 |
/* |
|
| 154 |
Method: count records in stemobservation table |
|
| 155 |
*/ |
|
| 169 | 156 |
|
| 170 |
-- ------------------ |
|
| 171 |
-- 12. Count of verbatim taxa per plot in each project |
|
| 172 |
-- Check: join to source db by all columns, returns same number of rows |
|
| 173 |
-- Note: Must perform equivalent concatenation of taxonomic field in source db. |
|
| 174 |
-- NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY |
|
| 175 |
-- ------------------ |
|
| 176 | 157 |
CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS |
| 177 |
select p.projectname, l.authorlocationcode as plotcode, |
|
| 178 |
count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' || coalesce(morphospecies::text,'')))
|
|
| 179 |
as taxa |
|
| 180 |
from project p join source s |
|
| 181 |
on p.source_id=s.source_id |
|
| 182 |
join locationevent le on p.project_id=le.project_id |
|
| 183 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 184 |
join location l on le.location_id=l.location_id |
|
| 185 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 186 |
join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id |
|
| 187 |
join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id |
|
| 188 |
where s.shortname=current_schema and td.isoriginal='t' |
|
| 189 |
group by p.projectname, l.authorlocationcode |
|
| 190 |
order by p.projectname, l.authorlocationcode; |
|
| 158 |
SELECT p.projectname AS project_name, |
|
| 159 |
l.authorlocationcode AS plot_code, |
|
| 160 |
count(DISTINCT btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))) AS taxa
|
|
| 161 |
FROM (((((((project p |
|
| 162 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 163 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 164 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 165 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 166 |
JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
|
| 167 |
JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
|
| 168 |
JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
|
| 169 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
| 170 |
GROUP BY p.projectname, l.authorlocationcode |
|
| 171 |
ORDER BY p.projectname, l.authorlocationcode; |
|
| 172 |
/* |
|
| 173 |
Check: join to source db by all columns, returns same number of rows |
|
| 174 |
Note: Must perform equivalent concatenation of taxonomic field in source db. |
|
| 175 |
NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY |
|
| 176 |
*/ |
|
| 191 | 177 |
|
| 192 |
-- ------------------ |
|
| 193 |
-- 13. List of distinct verbatim taxa in each plot in each project |
|
| 194 |
-- |
|
| 195 |
-- Note: Must perform equivalent concatenation of taxonomic field in source db. |
|
| 196 |
-- ------------------ |
|
| 197 |
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS |
|
| 198 |
select distinct p.projectname, l.authorlocationcode as plotcode, |
|
| 199 |
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' || coalesce(morphospecies::text,'')) as taxon
|
|
| 200 |
from project p join source s |
|
| 201 |
on p.source_id=s.source_id |
|
| 202 |
join locationevent le on p.project_id=le.project_id |
|
| 203 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 204 |
join location l on le.location_id=l.location_id |
|
| 205 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 206 |
join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id |
|
| 207 |
join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id |
|
| 208 |
where s.shortname=current_schema and td.isoriginal='t' |
|
| 209 |
order by p.projectname, l.authorlocationcode, taxon; |
|
| 178 |
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS |
|
| 179 |
SELECT DISTINCT p.projectname AS project_name, |
|
| 180 |
l.authorlocationcode AS plot_code, |
|
| 181 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon
|
|
| 182 |
FROM (((((((project p |
|
| 183 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 184 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 185 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 186 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 187 |
JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
|
| 188 |
JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
|
| 189 |
JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
|
| 190 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
| 191 |
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)));
|
|
| 192 |
/* |
|
| 193 |
Note: Must perform equivalent concatenation of taxonomic field in source db. |
|
| 194 |
*/ |
|
| 210 | 195 |
|
| 211 |
-- ------------------ |
|
| 212 |
-- 14. Count of individuals per (verbatim) taxon per plot in each project |
|
| 213 |
-- |
|
| 214 |
-- Note: Must do equivalent concatenation of taxonomic field in source db. |
|
| 215 |
-- ------------------ |
|
| 216 |
CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS |
|
| 217 |
select distinct p.projectname, l.authorlocationcode as plotcode, |
|
| 218 |
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' || coalesce(morphospecies::text,'')) as taxon,
|
|
| 219 |
sum(ao.count) as individuals |
|
| 220 |
from project p join source s |
|
| 221 |
on p.source_id=s.source_id |
|
| 222 |
join locationevent le on p.project_id=le.project_id |
|
| 223 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 224 |
join location l on le.location_id=l.location_id |
|
| 225 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 226 |
join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id |
|
| 227 |
join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id |
|
| 228 |
join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id |
|
| 229 |
where s.shortname=current_schema and td.isoriginal='t' |
|
| 230 |
group by p.projectname, l.authorlocationcode, taxon |
|
| 231 |
order by p.projectname, l.authorlocationcode, taxon; |
|
| 196 |
CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS |
|
| 197 |
SELECT DISTINCT p.projectname AS project_name, |
|
| 198 |
l.authorlocationcode AS plot_code, |
|
| 199 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
|
| 200 |
sum(ao.count) AS individuals |
|
| 201 |
FROM ((((((((project p |
|
| 202 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 203 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 204 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 205 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 206 |
JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
|
| 207 |
JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
|
| 208 |
JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
|
| 209 |
JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
|
| 210 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
| 211 |
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)))
|
|
| 212 |
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)));
|
|
| 213 |
/* |
|
| 214 |
Note: Must do equivalent concatenation of taxonomic field in source db. |
|
| 215 |
*/ |
|
| 232 | 216 |
|
| 233 |
-- ------------------ |
|
| 234 |
-- 15. Percent cover of each (verbatim) taxon in each plot in each project |
|
| 235 |
-- Applies to: aggregate-cover plots only |
|
| 236 |
-- Method: sums percent cover in aggregateoccurrence (as recorded in coverindex) |
|
| 237 |
-- Check: join to source db by all columns, returns same number of rows |
|
| 238 |
-- Note: Must do equivalent concatenation of taxonomic field in source db. |
|
| 239 |
-- ------------------ |
|
| 240 |
CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS |
|
| 241 |
select distinct p.projectname, l.authorlocationcode as plotcode, |
|
| 242 |
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' || coalesce(morphospecies::text,'')) as taxon,
|
|
| 243 |
sum(ci.coverpercent) as totalpercentcover |
|
| 244 |
from project p join source s |
|
| 245 |
on p.source_id=s.source_id |
|
| 246 |
join locationevent le on p.project_id=le.project_id |
|
| 247 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 248 |
join location l on le.location_id=l.location_id |
|
| 249 |
join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id |
|
| 250 |
join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id |
|
| 251 |
join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id |
|
| 252 |
join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id |
|
| 253 |
join coverindex ci on ao.coverindex_id=ci.coverindex_id |
|
| 254 |
where s.shortname=current_schema and td.isoriginal='t' |
|
| 255 |
and ci.coverpercent is not null |
|
| 256 |
group by p.projectname, l.authorlocationcode, taxon |
|
| 257 |
order by p.projectname, l.authorlocationcode, taxon; |
|
| 217 |
CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS |
|
| 218 |
SELECT DISTINCT p.projectname AS project_name, |
|
| 219 |
l.authorlocationcode AS plot_code, |
|
| 220 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
|
| 221 |
sum(ci.coverpercent) AS totalpercentcover |
|
| 222 |
FROM (((((((((project p |
|
| 223 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 224 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 225 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 226 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 227 |
JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
|
| 228 |
JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
|
| 229 |
JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
|
| 230 |
JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
|
| 231 |
JOIN coverindex ci ON ((ao.coverindex_id = ci.coverindex_id))) |
|
| 232 |
WHERE (((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true)) AND (ci.coverpercent IS NOT NULL))
|
|
| 233 |
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)))
|
|
| 234 |
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)));
|
|
| 235 |
/* |
|
| 236 |
Applies to: aggregate-cover plots only |
|
| 237 |
Method: sums percent cover in aggregateoccurrence (as recorded in coverindex) |
|
| 238 |
Check: join to source db by all columns, returns same number of rows |
|
| 239 |
Note: Must do equivalent concatenation of taxonomic field in source db. |
|
| 240 |
*/ |
|
| 258 | 241 |
|
| 259 | 242 |
-- ------------------ |
| 260 | 243 |
-- 16. Intercept values for each (verbatim) taxon in each plot in each project |
| ... | ... | |
| 270 | 253 |
-- ------------------ |
| 271 | 254 |
CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project AS |
| 272 | 255 |
|
| 273 |
|
|
| 274 |
|
|
| 275 |
|
|
| 276 |
|
|
| 277 |
|
|
| 278 |
-- ------------------ |
|
| 279 |
-- 17. Count of subplots per plot, for each project |
|
| 280 |
-- ------------------ |
|
| 281 | 256 |
CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS |
| 282 |
select p.projectname, l.authorlocationcode as plotcode, |
|
| 283 |
count(distinct sub_locationevent.locationevent_id) as subplots |
|
| 284 |
from project p join source s |
|
| 285 |
on p.source_id=s.source_id |
|
| 286 |
join locationevent le on p.project_id=le.project_id |
|
| 287 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 288 |
join location l on le.location_id=l.location_id |
|
| 289 |
where s.shortname=current_schema |
|
| 290 |
group by p.projectname, l.authorlocationcode |
|
| 291 |
order by p.projectname, l.authorlocationcode; |
|
| 257 |
SELECT p.projectname AS project_name, |
|
| 258 |
l.authorlocationcode AS plot_code, |
|
| 259 |
count(DISTINCT sub_locationevent.locationevent_id) AS subplots |
|
| 260 |
FROM ((((project p |
|
| 261 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 262 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 263 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 264 |
JOIN plot l ON ((le.location_id = l.location_id))) |
|
| 265 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 266 |
GROUP BY p.projectname, l.authorlocationcode |
|
| 267 |
ORDER BY p.projectname, l.authorlocationcode; |
|
| 292 | 268 |
|
| 293 |
-- ------------------ |
|
| 294 |
-- 18. List of subplots codes for each plot for each project |
|
| 295 |
-- DOESN'T YET WORK PROPERLY |
|
| 296 |
-- ------------------ |
|
| 297 | 269 |
CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS |
| 298 |
select p.projectname, |
|
| 299 |
l.authorlocationcode as plotcode, |
|
| 300 |
sub_locationevent.authoreventcode as "subplotCode" |
|
| 301 |
from project p join source s |
|
| 302 |
on p.source_id=s.source_id |
|
| 303 |
join locationevent le on p.project_id=le.project_id |
|
| 304 |
join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id |
|
| 305 |
join location l on sub_locationevent.location_id=l.location_id |
|
| 306 |
where s.shortname=current_schema |
|
| 307 |
order by p.projectname, l.authorlocationcode; |
|
| 270 |
SELECT p.projectname AS project_name, |
|
| 271 |
l.authorlocationcode AS plot_code, |
|
| 272 |
sub_locationevent.authoreventcode AS subplot_code |
|
| 273 |
FROM ((((project p |
|
| 274 |
JOIN source s ON ((p.source_id = s.source_id))) |
|
| 275 |
JOIN locationevent le ON ((p.project_id = le.project_id))) |
|
| 276 |
JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
|
| 277 |
JOIN plot l ON ((sub_locationevent.location_id = l.location_id))) |
|
| 278 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
| 279 |
ORDER BY p.projectname, l.authorlocationcode; |
|
| 280 |
/* |
|
| 281 |
DOESN''T YET WORK PROPERLY |
|
| 282 |
*/ |
|
Also available in: Unified diff
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB