Revision 12186
Added by Aaron Marcuse-Kubitza about 11 years ago
| vegbien.sql | ||
|---|---|---|
| 10 | 10 |
SET client_min_messages = warning; |
| 11 | 11 |
|
| 12 | 12 |
-- |
| 13 |
-- Name: public_validations_plots; Type: SCHEMA; Schema: -; Owner: -
|
|
| 13 |
-- Name: public_validations; Type: SCHEMA; Schema: -; Owner: - |
|
| 14 | 14 |
-- |
| 15 | 15 |
|
| 16 |
CREATE SCHEMA public_validations_plots;
|
|
| 16 |
CREATE SCHEMA public_validations; |
|
| 17 | 17 |
|
| 18 | 18 |
|
| 19 | 19 |
-- |
| 20 |
-- Name: SCHEMA public_validations_plots; Type: COMMENT; Schema: -; Owner: -
|
|
| 20 |
-- Name: SCHEMA public_validations; Type: COMMENT; Schema: -; Owner: - |
|
| 21 | 21 |
-- |
| 22 | 22 |
|
| 23 |
COMMENT ON SCHEMA public_validations_plots IS 'aggregating validations queries for plots';
|
|
| 23 |
COMMENT ON SCHEMA public_validations IS 'aggregating validations queries';
|
|
| 24 | 24 |
|
| 25 | 25 |
|
| 26 |
-- |
|
| 27 |
-- Name: public_validations_specimens; Type: SCHEMA; Schema: -; Owner: - |
|
| 28 |
-- |
|
| 29 |
|
|
| 30 |
CREATE SCHEMA public_validations_specimens; |
|
| 31 |
|
|
| 32 |
|
|
| 33 |
-- |
|
| 34 |
-- Name: SCHEMA public_validations_specimens; Type: COMMENT; Schema: -; Owner: - |
|
| 35 |
-- |
|
| 36 |
|
|
| 37 |
COMMENT ON SCHEMA public_validations_specimens IS 'aggregating validations queries for specimens'; |
|
| 38 |
|
|
| 39 |
|
|
| 40 |
-- |
|
| 41 |
-- Name: public_validations_traits; Type: SCHEMA; Schema: -; Owner: - |
|
| 42 |
-- |
|
| 43 |
|
|
| 44 |
CREATE SCHEMA public_validations_traits; |
|
| 45 |
|
|
| 46 |
|
|
| 47 |
-- |
|
| 48 |
-- Name: SCHEMA public_validations_traits; Type: COMMENT; Schema: -; Owner: - |
|
| 49 |
-- |
|
| 50 |
|
|
| 51 |
COMMENT ON SCHEMA public_validations_traits IS 'aggregating validations queries for traits'; |
|
| 52 |
|
|
| 53 |
|
|
| 54 | 26 |
SET search_path = public, pg_catalog; |
| 55 | 27 |
|
| 56 | 28 |
-- |
| ... | ... | |
| 5179 | 5151 |
ALTER SEQUENCE voucher_voucher_id_seq OWNED BY voucher.voucher_id; |
| 5180 | 5152 |
|
| 5181 | 5153 |
|
| 5182 |
SET search_path = public_validations_plots, pg_catalog;
|
|
| 5154 |
SET search_path = public_validations, pg_catalog; |
|
| 5183 | 5155 |
|
| 5184 | 5156 |
-- |
| 5185 |
-- Name: _01_count_of_projects; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5157 |
-- Name: plots_01_count_of_projects; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5186 | 5158 |
-- |
| 5187 | 5159 |
|
| 5188 |
CREATE VIEW _01_count_of_projects AS |
|
| 5160 |
CREATE VIEW plots_01_count_of_projects AS
|
|
| 5189 | 5161 |
SELECT count(*) AS projects |
| 5190 | 5162 |
FROM (public.project p |
| 5191 | 5163 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 5193 | 5165 |
|
| 5194 | 5166 |
|
| 5195 | 5167 |
-- |
| 5196 |
-- Name: _02_list_of_project_names; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5168 |
-- Name: plots_02_list_of_project_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5197 | 5169 |
-- |
| 5198 | 5170 |
|
| 5199 |
CREATE VIEW _02_list_of_project_names AS |
|
| 5171 |
CREATE VIEW plots_02_list_of_project_names AS
|
|
| 5200 | 5172 |
SELECT p.projectname |
| 5201 | 5173 |
FROM (public.project p |
| 5202 | 5174 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 5204 | 5176 |
|
| 5205 | 5177 |
|
| 5206 | 5178 |
-- |
| 5207 |
-- Name: _03_count_of_all_plots_in_this_source; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5179 |
-- Name: plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5208 | 5180 |
-- |
| 5209 | 5181 |
|
| 5210 |
CREATE VIEW _03_count_of_all_plots_in_this_source AS |
|
| 5182 |
CREATE VIEW plots_03_count_of_all_plots_in_this_source AS
|
|
| 5211 | 5183 |
SELECT count(*) AS plots |
| 5212 | 5184 |
FROM (((public.location l |
| 5213 | 5185 |
JOIN public.locationevent le ON ((l.location_id = le.location_id))) |
| ... | ... | |
| 5217 | 5189 |
|
| 5218 | 5190 |
|
| 5219 | 5191 |
-- |
| 5220 |
-- Name: _04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5192 |
-- Name: plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5221 | 5193 |
-- |
| 5222 | 5194 |
|
| 5223 |
CREATE VIEW _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
|
|
| 5224 | 5196 |
SELECT p.projectname, |
| 5225 | 5197 |
count(DISTINCT l.location_id) AS plots |
| 5226 | 5198 |
FROM (((public.location l |
| ... | ... | |
| 5232 | 5204 |
|
| 5233 | 5205 |
|
| 5234 | 5206 |
-- |
| 5235 |
-- Name: _05_list_of_plot_codes_by_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5207 |
-- Name: plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5236 | 5208 |
-- |
| 5237 | 5209 |
|
| 5238 |
CREATE VIEW _05_list_of_plot_codes_by_project AS |
|
| 5210 |
CREATE VIEW plots_05_list_of_plot_codes_by_project AS
|
|
| 5239 | 5211 |
SELECT p.projectname, |
| 5240 | 5212 |
l.authorlocationcode AS "plotCode" |
| 5241 | 5213 |
FROM (((public.location l |
| ... | ... | |
| 5246 | 5218 |
|
| 5247 | 5219 |
|
| 5248 | 5220 |
-- |
| 5249 |
-- Name: _06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5221 |
-- Name: plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5250 | 5222 |
-- |
| 5251 | 5223 |
|
| 5252 |
CREATE VIEW _06_list_of_plots_with_stem_measurements AS |
|
| 5224 |
CREATE VIEW plots_06_list_of_plots_with_stem_measurements AS
|
|
| 5253 | 5225 |
SELECT top_plot.authorlocationcode |
| 5254 | 5226 |
FROM public.top_plot |
| 5255 | 5227 |
WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
| ... | ... | |
| 5265 | 5237 |
|
| 5266 | 5238 |
|
| 5267 | 5239 |
-- |
| 5268 |
-- Name: _10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5240 |
-- Name: plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5269 | 5241 |
-- |
| 5270 | 5242 |
|
| 5271 |
CREATE VIEW _10_count_of_individuals_per_plot_in_each_project AS |
|
| 5243 |
CREATE VIEW plots_10_count_of_individuals_per_plot_in_each_project AS
|
|
| 5272 | 5244 |
SELECT p.projectname, |
| 5273 | 5245 |
l.authorlocationcode AS plotcode, |
| 5274 | 5246 |
sum(ao.count) AS individuals |
| ... | ... | |
| 5285 | 5257 |
|
| 5286 | 5258 |
|
| 5287 | 5259 |
-- |
| 5288 |
-- Name: VIEW _10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5260 |
-- Name: VIEW plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5289 | 5261 |
-- |
| 5290 | 5262 |
|
| 5291 |
COMMENT ON VIEW _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
|
|
| 5292 | 5264 |
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods. |
| 5293 | 5265 |
Note 2: Does this method requires that plots have 2-level nesting of subplots within plots? |
| 5294 | 5266 |
'; |
| 5295 | 5267 |
|
| 5296 | 5268 |
|
| 5297 | 5269 |
-- |
| 5298 |
-- Name: _11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5270 |
-- Name: plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5299 | 5271 |
-- |
| 5300 | 5272 |
|
| 5301 |
CREATE VIEW _11_count_of_stems_per_plot_in_each_project AS |
|
| 5273 |
CREATE VIEW plots_11_count_of_stems_per_plot_in_each_project AS
|
|
| 5302 | 5274 |
SELECT p.projectname, |
| 5303 | 5275 |
l.authorlocationcode AS plotcode, |
| 5304 | 5276 |
count(DISTINCT so.stemobservation_id) AS stems |
| ... | ... | |
| 5317 | 5289 |
|
| 5318 | 5290 |
|
| 5319 | 5291 |
-- |
| 5320 |
-- Name: VIEW _11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5292 |
-- Name: VIEW plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5321 | 5293 |
-- |
| 5322 | 5294 |
|
| 5323 |
COMMENT ON VIEW _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
|
|
| 5324 | 5296 |
'; |
| 5325 | 5297 |
|
| 5326 | 5298 |
|
| 5327 | 5299 |
-- |
| 5328 |
-- Name: _12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5300 |
-- Name: plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5329 | 5301 |
-- |
| 5330 | 5302 |
|
| 5331 |
CREATE VIEW _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
|
|
| 5332 | 5304 |
SELECT p.projectname, |
| 5333 | 5305 |
l.authorlocationcode AS plotcode, |
| 5334 | 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
|
| ... | ... | |
| 5346 | 5318 |
|
| 5347 | 5319 |
|
| 5348 | 5320 |
-- |
| 5349 |
-- Name: VIEW _12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5321 |
-- Name: VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5350 | 5322 |
-- |
| 5351 | 5323 |
|
| 5352 |
COMMENT ON VIEW _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
|
|
| 5353 | 5325 |
Note: Must perform equivalent concatenation of taxonomic field in source db. |
| 5354 | 5326 |
NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY |
| 5355 | 5327 |
'; |
| 5356 | 5328 |
|
| 5357 | 5329 |
|
| 5358 | 5330 |
-- |
| 5359 |
-- Name: _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5331 |
-- Name: plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5360 | 5332 |
-- |
| 5361 | 5333 |
|
| 5362 |
CREATE VIEW _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS
|
|
| 5334 |
CREATE VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr AS
|
|
| 5363 | 5335 |
SELECT DISTINCT p.projectname, |
| 5364 | 5336 |
l.authorlocationcode AS plotcode, |
| 5365 | 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
|
| ... | ... | |
| 5376 | 5348 |
|
| 5377 | 5349 |
|
| 5378 | 5350 |
-- |
| 5379 |
-- Name: VIEW _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5351 |
-- Name: VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_pr; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5380 | 5352 |
-- |
| 5381 | 5353 |
|
| 5382 |
COMMENT ON VIEW _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project 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_pr IS 'Note: Must perform equivalent concatenation of taxonomic field in source db.
|
|
| 5383 | 5355 |
'; |
| 5384 | 5356 |
|
| 5385 | 5357 |
|
| 5386 | 5358 |
-- |
| 5387 |
-- Name: _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5359 |
-- Name: plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5388 | 5360 |
-- |
| 5389 | 5361 |
|
| 5390 |
CREATE VIEW _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS
|
|
| 5362 |
CREATE VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro AS
|
|
| 5391 | 5363 |
SELECT DISTINCT p.projectname, |
| 5392 | 5364 |
l.authorlocationcode AS plotcode, |
| 5393 | 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,
|
| ... | ... | |
| 5407 | 5379 |
|
| 5408 | 5380 |
|
| 5409 | 5381 |
-- |
| 5410 |
-- Name: VIEW _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5382 |
-- Name: VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pro; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5411 | 5383 |
-- |
| 5412 | 5384 |
|
| 5413 |
COMMENT ON VIEW _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project 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_pro IS 'Note: Must do equivalent concatenation of taxonomic field in source db.
|
|
| 5414 | 5386 |
'; |
| 5415 | 5387 |
|
| 5416 | 5388 |
|
| 5417 | 5389 |
-- |
| 5418 |
-- Name: _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5390 |
-- Name: plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5419 | 5391 |
-- |
| 5420 | 5392 |
|
| 5421 |
CREATE VIEW _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS
|
|
| 5393 |
CREATE VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj AS
|
|
| 5422 | 5394 |
SELECT DISTINCT p.projectname, |
| 5423 | 5395 |
l.authorlocationcode AS plotcode, |
| 5424 | 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,
|
| ... | ... | |
| 5439 | 5411 |
|
| 5440 | 5412 |
|
| 5441 | 5413 |
-- |
| 5442 |
-- Name: VIEW _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5414 |
-- Name: VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_proj; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5443 | 5415 |
-- |
| 5444 | 5416 |
|
| 5445 |
COMMENT ON VIEW _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project IS 'Applies to: aggregate-cover plots only
|
|
| 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
|
|
| 5446 | 5418 |
Method: sums percent cover in aggregateoccurrence (as recorded in coverindex) |
| 5447 | 5419 |
Check: join to source db by all columns, returns same number of rows |
| 5448 | 5420 |
Note: Must do equivalent concatenation of taxonomic field in source db. |
| ... | ... | |
| 5450 | 5422 |
|
| 5451 | 5423 |
|
| 5452 | 5424 |
-- |
| 5453 |
-- Name: _17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5425 |
-- Name: plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5454 | 5426 |
-- |
| 5455 | 5427 |
|
| 5456 |
CREATE VIEW _17_count_of_subplots_per_plot_for_each_project AS |
|
| 5428 |
CREATE VIEW plots_17_count_of_subplots_per_plot_for_each_project AS
|
|
| 5457 | 5429 |
SELECT p.projectname, |
| 5458 | 5430 |
l.authorlocationcode AS plotcode, |
| 5459 | 5431 |
count(DISTINCT sub_locationevent.locationevent_id) AS subplots |
| ... | ... | |
| 5468 | 5440 |
|
| 5469 | 5441 |
|
| 5470 | 5442 |
-- |
| 5471 |
-- Name: _18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: public_validations_plots; Owner: -
|
|
| 5443 |
-- Name: plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5472 | 5444 |
-- |
| 5473 | 5445 |
|
| 5474 |
CREATE VIEW _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
|
|
| 5475 | 5447 |
SELECT p.projectname, |
| 5476 | 5448 |
l.authorlocationcode AS plotcode, |
| 5477 | 5449 |
sub_locationevent.authoreventcode AS "subplotCode" |
| ... | ... | |
| 5485 | 5457 |
|
| 5486 | 5458 |
|
| 5487 | 5459 |
-- |
| 5488 |
-- Name: VIEW _18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: public_validations_plots; Owner: -
|
|
| 5460 |
-- Name: VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5489 | 5461 |
-- |
| 5490 | 5462 |
|
| 5491 |
COMMENT ON VIEW _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
|
|
| 5492 | 5464 |
'; |
| 5493 | 5465 |
|
| 5494 | 5466 |
|
| 5495 |
SET search_path = public_validations_traits, pg_catalog; |
|
| 5496 |
|
|
| 5497 | 5467 |
-- |
| 5498 |
-- Name: _01_count_records; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5468 |
-- Name: traits_01_count_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5499 | 5469 |
-- |
| 5500 | 5470 |
|
| 5501 |
CREATE VIEW _01_count_records AS |
|
| 5471 |
CREATE VIEW traits_01_count_records AS
|
|
| 5502 | 5472 |
SELECT count(*) AS "totalRecords" |
| 5503 | 5473 |
FROM (public.trait |
| 5504 | 5474 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5506 | 5476 |
|
| 5507 | 5477 |
|
| 5508 | 5478 |
-- |
| 5509 |
-- Name: _02_count_trait_names; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5479 |
-- Name: traits_02_count_trait_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5510 | 5480 |
-- |
| 5511 | 5481 |
|
| 5512 |
CREATE VIEW _02_count_trait_names AS |
|
| 5482 |
CREATE VIEW traits_02_count_trait_names AS
|
|
| 5513 | 5483 |
SELECT count(DISTINCT trait.name) AS traits |
| 5514 | 5484 |
FROM (public.trait |
| 5515 | 5485 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5517 | 5487 |
|
| 5518 | 5488 |
|
| 5519 | 5489 |
-- |
| 5520 |
-- Name: _03_list_trait_names; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5490 |
-- Name: traits_03_list_trait_names; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5521 | 5491 |
-- |
| 5522 | 5492 |
|
| 5523 |
CREATE VIEW _03_list_trait_names AS |
|
| 5493 |
CREATE VIEW traits_03_list_trait_names AS
|
|
| 5524 | 5494 |
SELECT DISTINCT trait.name AS trait |
| 5525 | 5495 |
FROM (public.trait |
| 5526 | 5496 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 5529 | 5499 |
|
| 5530 | 5500 |
|
| 5531 | 5501 |
-- |
| 5532 |
-- Name: _04_count_records_per_trait; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5502 |
-- Name: traits_04_count_records_per_trait; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5533 | 5503 |
-- |
| 5534 | 5504 |
|
| 5535 |
CREATE VIEW _04_count_records_per_trait AS |
|
| 5505 |
CREATE VIEW traits_04_count_records_per_trait AS
|
|
| 5536 | 5506 |
SELECT trait.name AS trait, |
| 5537 | 5507 |
count(*) AS measurements |
| 5538 | 5508 |
FROM (public.trait |
| ... | ... | |
| 5543 | 5513 |
|
| 5544 | 5514 |
|
| 5545 | 5515 |
-- |
| 5546 |
-- Name: _05_count_taxa; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5516 |
-- Name: traits_05_count_taxa; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5547 | 5517 |
-- |
| 5548 | 5518 |
|
| 5549 |
CREATE VIEW _05_count_taxa AS |
|
| 5519 |
CREATE VIEW traits_05_count_taxa AS
|
|
| 5550 | 5520 |
SELECT count(DISTINCT ROW(taxonverbatim.taxonname, taxonverbatim.author)) AS taxa |
| 5551 | 5521 |
FROM public.taxonverbatim |
| 5552 | 5522 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text));
|
| 5553 | 5523 |
|
| 5554 | 5524 |
|
| 5555 | 5525 |
-- |
| 5556 |
-- Name: VIEW _05_count_taxa; Type: COMMENT; Schema: public_validations_traits; Owner: -
|
|
| 5526 |
-- Name: VIEW traits_05_count_taxa; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5557 | 5527 |
-- |
| 5558 | 5528 |
|
| 5559 |
COMMENT ON VIEW _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
|
|
| 5560 | 5530 |
'; |
| 5561 | 5531 |
|
| 5562 | 5532 |
|
| 5563 | 5533 |
-- |
| 5564 |
-- Name: _06_list_taxa; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5534 |
-- Name: traits_06_list_taxa; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5565 | 5535 |
-- |
| 5566 | 5536 |
|
| 5567 |
CREATE VIEW _06_list_taxa AS |
|
| 5537 |
CREATE VIEW traits_06_list_taxa AS
|
|
| 5568 | 5538 |
SELECT DISTINCT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor
|
| 5569 | 5539 |
FROM public.taxonverbatim |
| 5570 | 5540 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
| ... | ... | |
| 5572 | 5542 |
|
| 5573 | 5543 |
|
| 5574 | 5544 |
-- |
| 5575 |
-- Name: VIEW _06_list_taxa; Type: COMMENT; Schema: public_validations_traits; Owner: -
|
|
| 5545 |
-- Name: VIEW traits_06_list_taxa; Type: COMMENT; Schema: public_validations; Owner: -
|
|
| 5576 | 5546 |
-- |
| 5577 | 5547 |
|
| 5578 |
COMMENT ON VIEW _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
|
|
| 5579 | 5549 |
Note 2: Note formation of taxonCorrected: includes family ONLY if taxon is not determined at least to genus |
| 5580 | 5550 |
'; |
| 5581 | 5551 |
|
| 5582 | 5552 |
|
| 5583 | 5553 |
-- |
| 5584 |
-- Name: _07_trait_value_and_units_for_first_5000_records; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5554 |
-- Name: traits_07_trait_value_and_units_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5585 | 5555 |
-- |
| 5586 | 5556 |
|
| 5587 |
CREATE VIEW _07_trait_value_and_units_for_first_5000_records AS |
|
| 5557 |
CREATE VIEW traits_07_trait_value_and_units_for_first_5000_records AS
|
|
| 5588 | 5558 |
SELECT trait.name AS trait, |
| 5589 | 5559 |
trait.value, |
| 5590 | 5560 |
trait.units |
| ... | ... | |
| 5596 | 5566 |
|
| 5597 | 5567 |
|
| 5598 | 5568 |
-- |
| 5599 |
-- Name: _08_taxon_trait_and_value_for_first_5000_records; Type: VIEW; Schema: public_validations_traits; Owner: -
|
|
| 5569 |
-- Name: traits_08_taxon_trait_and_value_for_first_5000_records; Type: VIEW; Schema: public_validations; Owner: -
|
|
| 5600 | 5570 |
-- |
| 5601 | 5571 |
|
| 5602 |
CREATE VIEW _08_taxon_trait_and_value_for_first_5000_records AS |
|
| 5572 |
CREATE VIEW traits_08_taxon_trait_and_value_for_first_5000_records AS
|
|
| 5603 | 5573 |
SELECT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor,
|
| 5604 | 5574 |
trait.name AS trait, |
| 5605 | 5575 |
trait.value |
| ... | ... | |
| 11362 | 11332 |
|
| 11363 | 11333 |
|
| 11364 | 11334 |
-- |
| 11365 |
-- Name: public_validations_plots; Type: ACL; Schema: -; Owner: -
|
|
| 11335 |
-- Name: public_validations; Type: ACL; Schema: -; Owner: - |
|
| 11366 | 11336 |
-- |
| 11367 | 11337 |
|
| 11368 |
REVOKE ALL ON SCHEMA public_validations_plots FROM PUBLIC;
|
|
| 11369 |
REVOKE ALL ON SCHEMA public_validations_plots FROM bien;
|
|
| 11370 |
GRANT ALL ON SCHEMA public_validations_plots TO bien;
|
|
| 11371 |
GRANT USAGE ON SCHEMA public_validations_plots TO bien_read;
|
|
| 11372 |
GRANT USAGE ON SCHEMA public_validations_plots TO public_;
|
|
| 11338 |
REVOKE ALL ON SCHEMA public_validations FROM PUBLIC; |
|
| 11339 |
REVOKE ALL ON SCHEMA public_validations FROM bien; |
|
| 11340 |
GRANT ALL ON SCHEMA public_validations TO bien; |
|
| 11341 |
GRANT USAGE ON SCHEMA public_validations TO bien_read; |
|
| 11342 |
GRANT USAGE ON SCHEMA public_validations TO public_; |
|
| 11373 | 11343 |
|
| 11374 | 11344 |
|
| 11375 | 11345 |
-- |
| 11376 |
-- Name: public_validations_specimens; Type: ACL; Schema: -; Owner: - |
|
| 11377 |
-- |
|
| 11378 |
|
|
| 11379 |
REVOKE ALL ON SCHEMA public_validations_specimens FROM PUBLIC; |
|
| 11380 |
REVOKE ALL ON SCHEMA public_validations_specimens FROM bien; |
|
| 11381 |
GRANT ALL ON SCHEMA public_validations_specimens TO bien; |
|
| 11382 |
GRANT USAGE ON SCHEMA public_validations_specimens TO bien_read; |
|
| 11383 |
GRANT USAGE ON SCHEMA public_validations_specimens TO public_; |
|
| 11384 |
|
|
| 11385 |
|
|
| 11386 |
-- |
|
| 11387 |
-- Name: public_validations_traits; Type: ACL; Schema: -; Owner: - |
|
| 11388 |
-- |
|
| 11389 |
|
|
| 11390 |
REVOKE ALL ON SCHEMA public_validations_traits FROM PUBLIC; |
|
| 11391 |
REVOKE ALL ON SCHEMA public_validations_traits FROM bien; |
|
| 11392 |
GRANT ALL ON SCHEMA public_validations_traits TO bien; |
|
| 11393 |
GRANT USAGE ON SCHEMA public_validations_traits TO bien_read; |
|
| 11394 |
GRANT USAGE ON SCHEMA public_validations_traits TO public_; |
|
| 11395 |
|
|
| 11396 |
|
|
| 11397 |
-- |
|
| 11398 | 11346 |
-- Name: source; Type: ACL; Schema: public; Owner: - |
| 11399 | 11347 |
-- |
| 11400 | 11348 |
|
Also available in: Unified diff
schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using