Revision 12224
Added by Aaron Marcuse-Kubitza about 11 years ago
| vegbien.sql | ||
|---|---|---|
| 5154 | 5154 |
SET search_path = public_validations, pg_catalog; |
| 5155 | 5155 |
|
| 5156 | 5156 |
-- |
| 5157 |
-- Name: plots_01_count_of_projects; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5157 |
-- Name: _plots_01_count_of_projects; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5158 | 5158 |
-- |
| 5159 | 5159 |
|
| 5160 |
CREATE VIEW plots_01_count_of_projects AS |
|
| 5160 |
CREATE VIEW _plots_01_count_of_projects AS
|
|
| 5161 | 5161 |
SELECT count(*) AS projects |
| 5162 | 5162 |
FROM (public.project p |
| 5163 | 5163 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 5165 | 5165 |
|
| 5166 | 5166 |
|
| 5167 | 5167 |
-- |
| 5168 |
-- Name: plots_02_list_of_project_names; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5168 |
-- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5169 | 5169 |
-- |
| 5170 | 5170 |
|
| 5171 |
CREATE VIEW plots_02_list_of_project_names AS |
|
| 5171 |
CREATE VIEW _plots_02_list_of_project_names AS
|
|
| 5172 | 5172 |
SELECT p.projectname |
| 5173 | 5173 |
FROM (public.project p |
| 5174 | 5174 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 5176 | 5176 |
|
| 5177 | 5177 |
|
| 5178 | 5178 |
-- |
| 5179 |
-- Name: plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5179 |
-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5180 | 5180 |
-- |
| 5181 | 5181 |
|
| 5182 |
CREATE VIEW plots_03_count_of_all_plots_in_this_source AS |
|
| 5182 |
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS
|
|
| 5183 | 5183 |
SELECT count(*) AS plots |
| 5184 | 5184 |
FROM (((public.location l |
| 5185 | 5185 |
JOIN public.locationevent le ON ((l.location_id = le.location_id))) |
| ... | ... | |
| 5189 | 5189 |
|
| 5190 | 5190 |
|
| 5191 | 5191 |
-- |
| 5192 |
-- Name: plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5192 |
-- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5193 | 5193 |
-- |
| 5194 | 5194 |
|
| 5195 |
CREATE VIEW plots_04_count_of_plots_in_each_project_in_this_source AS |
|
| 5195 |
CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
|
|
| 5196 | 5196 |
SELECT p.projectname, |
| 5197 | 5197 |
count(DISTINCT l.location_id) AS plots |
| 5198 | 5198 |
FROM (((public.location l |
| ... | ... | |
| 5204 | 5204 |
|
| 5205 | 5205 |
|
| 5206 | 5206 |
-- |
| 5207 |
-- Name: plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5207 |
-- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5208 | 5208 |
-- |
| 5209 | 5209 |
|
| 5210 |
CREATE VIEW plots_05_list_of_plot_codes_by_project AS |
|
| 5210 |
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS
|
|
| 5211 | 5211 |
SELECT p.projectname, |
| 5212 | 5212 |
l.authorlocationcode AS "plotCode" |
| 5213 | 5213 |
FROM (((public.location l |
| ... | ... | |
| 5218 | 5218 |
|
| 5219 | 5219 |
|
| 5220 | 5220 |
-- |
| 5221 |
-- Name: plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5221 |
-- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5222 | 5222 |
-- |
| 5223 | 5223 |
|
| 5224 |
CREATE VIEW plots_06_list_of_plots_with_stem_measurements AS |
|
| 5224 |
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS
|
|
| 5225 | 5225 |
SELECT top_plot.authorlocationcode |
| 5226 | 5226 |
FROM public.top_plot |
| 5227 | 5227 |
WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
| ... | ... | |
| 5237 | 5237 |
|
| 5238 | 5238 |
|
| 5239 | 5239 |
-- |
| 5240 |
-- Name: plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5240 |
-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5241 | 5241 |
-- |
| 5242 | 5242 |
|
| 5243 |
CREATE VIEW plots_10_count_of_individuals_per_plot_in_each_project AS |
|
| 5243 |
CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
|
|
| 5244 | 5244 |
SELECT p.projectname, |
| 5245 | 5245 |
l.authorlocationcode AS plotcode, |
| 5246 | 5246 |
sum(ao.count) AS individuals |
| ... | ... | |
| 5257 | 5257 |
|
| 5258 | 5258 |
|
| 5259 | 5259 |
-- |
| 5260 |
-- Name: VIEW plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5260 |
-- Name: VIEW _plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5261 | 5261 |
-- |
| 5262 | 5262 |
|
| 5263 |
COMMENT ON VIEW plots_10_count_of_individuals_per_plot_in_each_project IS 'Method: count taxonObservations |
|
| 5263 |
COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS 'Method: count taxonObservations
|
|
| 5264 | 5264 |
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods. |
| 5265 | 5265 |
Note 2: Does this method requires that plots have 2-level nesting of subplots within plots? |
| 5266 | 5266 |
'; |
| 5267 | 5267 |
|
| 5268 | 5268 |
|
| 5269 | 5269 |
-- |
| 5270 |
-- Name: plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5270 |
-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5271 | 5271 |
-- |
| 5272 | 5272 |
|
| 5273 |
CREATE VIEW plots_11_count_of_stems_per_plot_in_each_project AS |
|
| 5273 |
CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
|
|
| 5274 | 5274 |
SELECT p.projectname, |
| 5275 | 5275 |
l.authorlocationcode AS plotcode, |
| 5276 | 5276 |
count(DISTINCT so.stemobservation_id) AS stems |
| ... | ... | |
| 5289 | 5289 |
|
| 5290 | 5290 |
|
| 5291 | 5291 |
-- |
| 5292 |
-- Name: VIEW plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5292 |
-- Name: VIEW _plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5293 | 5293 |
-- |
| 5294 | 5294 |
|
| 5295 |
COMMENT ON VIEW plots_11_count_of_stems_per_plot_in_each_project IS 'Method: count records in stemobservation table |
|
| 5295 |
COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS 'Method: count records in stemobservation table
|
|
| 5296 | 5296 |
'; |
| 5297 | 5297 |
|
| 5298 | 5298 |
|
| 5299 | 5299 |
-- |
| 5300 |
-- Name: plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5300 |
-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5301 | 5301 |
-- |
| 5302 | 5302 |
|
| 5303 |
CREATE VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS |
|
| 5303 |
CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
|
| 5304 | 5304 |
SELECT p.projectname, |
| 5305 | 5305 |
l.authorlocationcode AS plotcode, |
| 5306 | 5306 |
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
|
| ... | ... | |
| 5318 | 5318 |
|
| 5319 | 5319 |
|
| 5320 | 5320 |
-- |
| 5321 |
-- Name: VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5321 |
-- Name: VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5322 | 5322 |
-- |
| 5323 | 5323 |
|
| 5324 |
COMMENT ON VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS 'Check: join to source db by all columns, returns same number of rows |
|
| 5324 |
COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS 'Check: join to source db by all columns, returns same number of rows
|
|
| 5325 | 5325 |
Note: Must perform equivalent concatenation of taxonomic field in source db. |
| 5326 | 5326 |
NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY |
| 5327 | 5327 |
'; |
| 5328 | 5328 |
|
| 5329 | 5329 |
|
| 5330 | 5330 |
-- |
| 5331 |
-- Name: plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5331 |
-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5332 | 5332 |
-- |
| 5333 | 5333 |
|
| 5334 |
CREATE VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr AS
|
|
| 5334 |
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
|
|
| 5335 | 5335 |
SELECT DISTINCT p.projectname, |
| 5336 | 5336 |
l.authorlocationcode AS plotcode, |
| 5337 | 5337 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon
|
| ... | ... | |
| 5348 | 5348 |
|
| 5349 | 5349 |
|
| 5350 | 5350 |
-- |
| 5351 |
-- Name: VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5351 |
-- Name: VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5352 | 5352 |
-- |
| 5353 | 5353 |
|
| 5354 |
COMMENT ON VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr IS 'Note: Must perform equivalent concatenation of taxonomic field in source db.
|
|
| 5354 |
COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS 'Note: Must perform equivalent concatenation of taxonomic field in source db.
|
|
| 5355 | 5355 |
'; |
| 5356 | 5356 |
|
| 5357 | 5357 |
|
| 5358 | 5358 |
-- |
| 5359 |
-- Name: plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5359 |
-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5360 | 5360 |
-- |
| 5361 | 5361 |
|
| 5362 |
CREATE VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro AS
|
|
| 5362 |
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
|
|
| 5363 | 5363 |
SELECT DISTINCT p.projectname, |
| 5364 | 5364 |
l.authorlocationcode AS plotcode, |
| 5365 | 5365 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
| ... | ... | |
| 5379 | 5379 |
|
| 5380 | 5380 |
|
| 5381 | 5381 |
-- |
| 5382 |
-- Name: VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5382 |
-- Name: VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5383 | 5383 |
-- |
| 5384 | 5384 |
|
| 5385 |
COMMENT ON VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro IS 'Note: Must do equivalent concatenation of taxonomic field in source db.
|
|
| 5385 |
COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS 'Note: Must do equivalent concatenation of taxonomic field in source db.
|
|
| 5386 | 5386 |
'; |
| 5387 | 5387 |
|
| 5388 | 5388 |
|
| 5389 | 5389 |
-- |
| 5390 |
-- Name: plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5390 |
-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5391 | 5391 |
-- |
| 5392 | 5392 |
|
| 5393 |
CREATE VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj AS
|
|
| 5393 |
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
|
|
| 5394 | 5394 |
SELECT DISTINCT p.projectname, |
| 5395 | 5395 |
l.authorlocationcode AS plotcode, |
| 5396 | 5396 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
| ... | ... | |
| 5411 | 5411 |
|
| 5412 | 5412 |
|
| 5413 | 5413 |
-- |
| 5414 |
-- Name: VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5414 |
-- Name: VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5415 | 5415 |
-- |
| 5416 | 5416 |
|
| 5417 |
COMMENT ON VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj IS 'Applies to: aggregate-cover plots only
|
|
| 5417 |
COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS 'Applies to: aggregate-cover plots only
|
|
| 5418 | 5418 |
Method: sums percent cover in aggregateoccurrence (as recorded in coverindex) |
| 5419 | 5419 |
Check: join to source db by all columns, returns same number of rows |
| 5420 | 5420 |
Note: Must do equivalent concatenation of taxonomic field in source db. |
| ... | ... | |
| 5422 | 5422 |
|
| 5423 | 5423 |
|
| 5424 | 5424 |
-- |
| 5425 |
-- Name: plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5425 |
-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5426 | 5426 |
-- |
| 5427 | 5427 |
|
| 5428 |
CREATE VIEW plots_17_count_of_subplots_per_plot_for_each_project AS |
|
| 5428 |
CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
|
|
| 5429 | 5429 |
SELECT p.projectname, |
| 5430 | 5430 |
l.authorlocationcode AS plotcode, |
| 5431 | 5431 |
count(DISTINCT sub_locationevent.locationevent_id) AS subplots |
| ... | ... | |
| 5440 | 5440 |
|
| 5441 | 5441 |
|
| 5442 | 5442 |
-- |
| 5443 |
-- Name: plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5443 |
-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5444 | 5444 |
-- |
| 5445 | 5445 |
|
| 5446 |
CREATE VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS |
|
| 5446 |
CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
|
| 5447 | 5447 |
SELECT p.projectname, |
| 5448 | 5448 |
l.authorlocationcode AS plotcode, |
| 5449 | 5449 |
sub_locationevent.authoreventcode AS "subplotCode" |
| ... | ... | |
| 5457 | 5457 |
|
| 5458 | 5458 |
|
| 5459 | 5459 |
-- |
| 5460 |
-- Name: VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5460 |
-- Name: VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5461 | 5461 |
-- |
| 5462 | 5462 |
|
| 5463 |
COMMENT ON VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS 'DOESN''T YET WORK PROPERLY |
|
| 5463 |
COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS 'DOESN''T YET WORK PROPERLY
|
|
| 5464 | 5464 |
'; |
| 5465 | 5465 |
|
| 5466 | 5466 |
|
| 5467 | 5467 |
-- |
| 5468 |
-- Name: traits_01_count_records; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5468 |
-- Name: _traits_01_count_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5469 | 5469 |
-- |
| 5470 | 5470 |
|
| 5471 |
CREATE VIEW traits_01_count_records AS |
|
| 5471 |
CREATE VIEW _traits_01_count_records AS
|
|
| 5472 | 5472 |
SELECT count(*) AS "totalRecords" |
| 5473 | 5473 |
FROM (public.trait |
| 5474 | 5474 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5476 | 5476 |
|
| 5477 | 5477 |
|
| 5478 | 5478 |
-- |
| 5479 |
-- Name: traits_02_count_trait_names; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5479 |
-- Name: _traits_02_count_trait_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5480 | 5480 |
-- |
| 5481 | 5481 |
|
| 5482 |
CREATE VIEW traits_02_count_trait_names AS |
|
| 5482 |
CREATE VIEW _traits_02_count_trait_names AS
|
|
| 5483 | 5483 |
SELECT count(DISTINCT trait.name) AS traits |
| 5484 | 5484 |
FROM (public.trait |
| 5485 | 5485 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5487 | 5487 |
|
| 5488 | 5488 |
|
| 5489 | 5489 |
-- |
| 5490 |
-- Name: traits_03_list_trait_names; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5490 |
-- Name: _traits_03_list_trait_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5491 | 5491 |
-- |
| 5492 | 5492 |
|
| 5493 |
CREATE VIEW traits_03_list_trait_names AS |
|
| 5493 |
CREATE VIEW _traits_03_list_trait_names AS
|
|
| 5494 | 5494 |
SELECT DISTINCT trait.name AS trait |
| 5495 | 5495 |
FROM (public.trait |
| 5496 | 5496 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5499 | 5499 |
|
| 5500 | 5500 |
|
| 5501 | 5501 |
-- |
| 5502 |
-- Name: traits_04_count_records_per_trait; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5502 |
-- Name: _traits_04_count_records_per_trait; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5503 | 5503 |
-- |
| 5504 | 5504 |
|
| 5505 |
CREATE VIEW traits_04_count_records_per_trait AS |
|
| 5505 |
CREATE VIEW _traits_04_count_records_per_trait AS
|
|
| 5506 | 5506 |
SELECT trait.name AS trait, |
| 5507 | 5507 |
count(*) AS measurements |
| 5508 | 5508 |
FROM (public.trait |
| ... | ... | |
| 5513 | 5513 |
|
| 5514 | 5514 |
|
| 5515 | 5515 |
-- |
| 5516 |
-- Name: traits_05_count_taxa; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5516 |
-- Name: _traits_05_count_taxa; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5517 | 5517 |
-- |
| 5518 | 5518 |
|
| 5519 |
CREATE VIEW traits_05_count_taxa AS |
|
| 5519 |
CREATE VIEW _traits_05_count_taxa AS
|
|
| 5520 | 5520 |
SELECT count(DISTINCT ROW(taxonverbatim.taxonname, taxonverbatim.author)) AS taxa |
| 5521 | 5521 |
FROM public.taxonverbatim |
| 5522 | 5522 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text));
|
| 5523 | 5523 |
|
| 5524 | 5524 |
|
| 5525 | 5525 |
-- |
| 5526 |
-- Name: VIEW traits_05_count_taxa; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5526 |
-- Name: VIEW _traits_05_count_taxa; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5527 | 5527 |
-- |
| 5528 | 5528 |
|
| 5529 |
COMMENT ON VIEW traits_05_count_taxa IS 'Note: No morphospecies in trait table, therefore count taxon + authority only |
|
| 5529 |
COMMENT ON VIEW _traits_05_count_taxa IS 'Note: No morphospecies in trait table, therefore count taxon + authority only
|
|
| 5530 | 5530 |
'; |
| 5531 | 5531 |
|
| 5532 | 5532 |
|
| 5533 | 5533 |
-- |
| 5534 |
-- Name: traits_06_list_taxa; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5534 |
-- Name: _traits_06_list_taxa; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5535 | 5535 |
-- |
| 5536 | 5536 |
|
| 5537 |
CREATE VIEW traits_06_list_taxa AS |
|
| 5537 |
CREATE VIEW _traits_06_list_taxa AS
|
|
| 5538 | 5538 |
SELECT DISTINCT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor
|
| 5539 | 5539 |
FROM public.taxonverbatim |
| 5540 | 5540 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
| ... | ... | |
| 5542 | 5542 |
|
| 5543 | 5543 |
|
| 5544 | 5544 |
-- |
| 5545 |
-- Name: VIEW traits_06_list_taxa; Type: COMMENT; Schema: public_validations; Owner: - |
|
| 5545 |
-- Name: VIEW _traits_06_list_taxa; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5546 | 5546 |
-- |
| 5547 | 5547 |
|
| 5548 |
COMMENT ON VIEW traits_06_list_taxa IS 'Note 1: No morphospecies in trait table, use taxon + authority |
|
| 5548 |
COMMENT ON VIEW _traits_06_list_taxa IS 'Note 1: No morphospecies in trait table, use taxon + authority
|
|
| 5549 | 5549 |
Note 2: Note formation of taxonCorrected: includes family ONLY if taxon is not determined at least to genus |
| 5550 | 5550 |
'; |
| 5551 | 5551 |
|
| 5552 | 5552 |
|
| 5553 | 5553 |
-- |
| 5554 |
-- Name: traits_07_trait_value_and_units_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5554 |
-- Name: _traits_07_trait_value_and_units_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5555 | 5555 |
-- |
| 5556 | 5556 |
|
| 5557 |
CREATE VIEW traits_07_trait_value_and_units_for_first_5000_records AS |
|
| 5557 |
CREATE VIEW _traits_07_trait_value_and_units_for_first_5000_records AS
|
|
| 5558 | 5558 |
SELECT trait.name AS trait, |
| 5559 | 5559 |
trait.value, |
| 5560 | 5560 |
trait.units |
| ... | ... | |
| 5566 | 5566 |
|
| 5567 | 5567 |
|
| 5568 | 5568 |
-- |
| 5569 |
-- Name: traits_08_taxon_trait_and_value_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: - |
|
| 5569 |
-- Name: _traits_08_taxon_trait_and_value_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5570 | 5570 |
-- |
| 5571 | 5571 |
|
| 5572 |
CREATE VIEW traits_08_taxon_trait_and_value_for_first_5000_records AS |
|
| 5572 |
CREATE VIEW _traits_08_taxon_trait_and_value_for_first_5000_records AS
|
|
| 5573 | 5573 |
SELECT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor,
|
| 5574 | 5574 |
trait.name AS trait, |
| 5575 | 5575 |
trait.value |
Also available in: Unified diff
validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.