| 5179 |
5179 |
ALTER SEQUENCE voucher_voucher_id_seq OWNED BY voucher.voucher_id;
|
| 5180 |
5180 |
|
| 5181 |
5181 |
|
|
5182 |
SET search_path = public_validation_plots, pg_catalog;
|
|
5183 |
|
|
5184 |
--
|
|
5185 |
-- Name: _01_count_of_projects; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5186 |
--
|
|
5187 |
|
|
5188 |
CREATE VIEW _01_count_of_projects AS
|
|
5189 |
SELECT count(*) AS projects
|
|
5190 |
FROM (public.project p
|
|
5191 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5192 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
5193 |
|
|
5194 |
|
|
5195 |
--
|
|
5196 |
-- Name: _02_list_of_project_names; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5197 |
--
|
|
5198 |
|
|
5199 |
CREATE VIEW _02_list_of_project_names AS
|
|
5200 |
SELECT p.projectname
|
|
5201 |
FROM (public.project p
|
|
5202 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5203 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
5204 |
|
|
5205 |
|
|
5206 |
--
|
|
5207 |
-- Name: _03_count_of_all_plots_in_this_source; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5208 |
--
|
|
5209 |
|
|
5210 |
CREATE VIEW _03_count_of_all_plots_in_this_source AS
|
|
5211 |
SELECT count(*) AS plots
|
|
5212 |
FROM (((public.location l
|
|
5213 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
5214 |
JOIN public.project p ON ((p.project_id = le.project_id)))
|
|
5215 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5216 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
5217 |
|
|
5218 |
|
|
5219 |
--
|
|
5220 |
-- Name: _04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5221 |
--
|
|
5222 |
|
|
5223 |
CREATE VIEW _04_count_of_plots_in_each_project_in_this_source AS
|
|
5224 |
SELECT p.projectname,
|
|
5225 |
count(DISTINCT l.location_id) AS plots
|
|
5226 |
FROM (((public.location l
|
|
5227 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
5228 |
JOIN public.project p ON ((p.project_id = le.project_id)))
|
|
5229 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5230 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
5231 |
GROUP BY p.projectname;
|
|
5232 |
|
|
5233 |
|
|
5234 |
--
|
|
5235 |
-- Name: _05_list_of_plot_codes_by_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5236 |
--
|
|
5237 |
|
|
5238 |
CREATE VIEW _05_list_of_plot_codes_by_project AS
|
|
5239 |
SELECT p.projectname,
|
|
5240 |
l.authorlocationcode AS "plotCode"
|
|
5241 |
FROM (((public.location l
|
|
5242 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
5243 |
JOIN public.project p ON ((p.project_id = le.project_id)))
|
|
5244 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5245 |
WHERE (s.shortname = ("current_schema"())::text);
|
|
5246 |
|
|
5247 |
|
|
5248 |
--
|
|
5249 |
-- Name: _06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5250 |
--
|
|
5251 |
|
|
5252 |
CREATE VIEW _06_list_of_plots_with_stem_measurements AS
|
|
5253 |
SELECT top_plot.authorlocationcode
|
|
5254 |
FROM public.top_plot
|
|
5255 |
WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown
|
|
5256 |
FROM (((((public.location
|
|
5257 |
JOIN public.locationevent USING (location_id))
|
|
5258 |
JOIN public.taxonoccurrence USING (locationevent_id))
|
|
5259 |
JOIN public.aggregateoccurrence USING (taxonoccurrence_id))
|
|
5260 |
JOIN public.plantobservation USING (aggregateoccurrence_id))
|
|
5261 |
JOIN public.stemobservation USING (plantobservation_id))
|
|
5262 |
WHERE (location.top_plot = top_plot.location_id)
|
|
5263 |
LIMIT 1)))
|
|
5264 |
ORDER BY top_plot.authorlocationcode;
|
|
5265 |
|
|
5266 |
|
|
5267 |
--
|
|
5268 |
-- Name: _10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5269 |
--
|
|
5270 |
|
|
5271 |
CREATE VIEW _10_count_of_individuals_per_plot_in_each_project AS
|
|
5272 |
SELECT p.projectname,
|
|
5273 |
l.authorlocationcode AS plotcode,
|
|
5274 |
sum(ao.count) AS individuals
|
|
5275 |
FROM ((((((public.project p
|
|
5276 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5277 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5278 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5279 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5280 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5281 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
|
|
5282 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
5283 |
GROUP BY p.projectname, l.authorlocationcode
|
|
5284 |
ORDER BY p.projectname, l.authorlocationcode;
|
|
5285 |
|
|
5286 |
|
|
5287 |
--
|
|
5288 |
-- Name: _11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5289 |
--
|
|
5290 |
|
|
5291 |
CREATE VIEW _11_count_of_stems_per_plot_in_each_project AS
|
|
5292 |
SELECT p.projectname,
|
|
5293 |
l.authorlocationcode AS plotcode,
|
|
5294 |
count(DISTINCT so.stemobservation_id) AS stems
|
|
5295 |
FROM ((((((((public.project p
|
|
5296 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5297 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5298 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5299 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5300 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5301 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
|
|
5302 |
JOIN public.plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id)))
|
|
5303 |
JOIN public.stemobservation so ON ((so.plantobservation_id = po.plantobservation_id)))
|
|
5304 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
5305 |
GROUP BY p.projectname, l.authorlocationcode
|
|
5306 |
ORDER BY p.projectname, l.authorlocationcode;
|
|
5307 |
|
|
5308 |
|
|
5309 |
--
|
|
5310 |
-- Name: _12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5311 |
--
|
|
5312 |
|
|
5313 |
CREATE VIEW _12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
|
5314 |
SELECT p.projectname,
|
|
5315 |
l.authorlocationcode AS plotcode,
|
|
5316 |
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
|
|
5317 |
FROM (((((((public.project p
|
|
5318 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5319 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5320 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5321 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5322 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5323 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
|
|
5324 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
|
|
5325 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
5326 |
GROUP BY p.projectname, l.authorlocationcode
|
|
5327 |
ORDER BY p.projectname, l.authorlocationcode;
|
|
5328 |
|
|
5329 |
|
|
5330 |
--
|
|
5331 |
-- Name: _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5332 |
--
|
|
5333 |
|
|
5334 |
CREATE VIEW _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS
|
|
5335 |
SELECT DISTINCT p.projectname,
|
|
5336 |
l.authorlocationcode AS plotcode,
|
|
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
|
|
5338 |
FROM (((((((public.project p
|
|
5339 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5340 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5341 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5342 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5343 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5344 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
|
|
5345 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
|
|
5346 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
5347 |
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)));
|
|
5348 |
|
|
5349 |
|
|
5350 |
--
|
|
5351 |
-- Name: _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5352 |
--
|
|
5353 |
|
|
5354 |
CREATE VIEW _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS
|
|
5355 |
SELECT DISTINCT p.projectname,
|
|
5356 |
l.authorlocationcode AS plotcode,
|
|
5357 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
|
5358 |
sum(ao.count) AS individuals
|
|
5359 |
FROM ((((((((public.project p
|
|
5360 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5361 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5362 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5363 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5364 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5365 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
|
|
5366 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
|
|
5367 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
|
|
5368 |
WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))
|
|
5369 |
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)))
|
|
5370 |
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)));
|
|
5371 |
|
|
5372 |
|
|
5373 |
--
|
|
5374 |
-- Name: _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5375 |
--
|
|
5376 |
|
|
5377 |
CREATE VIEW _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS
|
|
5378 |
SELECT DISTINCT p.projectname,
|
|
5379 |
l.authorlocationcode AS plotcode,
|
|
5380 |
btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
|
|
5381 |
sum(ci.coverpercent) AS totalpercentcover
|
|
5382 |
FROM (((((((((public.project p
|
|
5383 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5384 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5385 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5386 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5387 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
|
|
5388 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
|
|
5389 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
|
|
5390 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
|
|
5391 |
JOIN public.coverindex ci ON ((ao.coverindex_id = ci.coverindex_id)))
|
|
5392 |
WHERE (((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true)) AND (ci.coverpercent IS NOT NULL))
|
|
5393 |
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)))
|
|
5394 |
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)));
|
|
5395 |
|
|
5396 |
|
|
5397 |
--
|
|
5398 |
-- Name: _17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5399 |
--
|
|
5400 |
|
|
5401 |
CREATE VIEW _17_count_of_subplots_per_plot_for_each_project AS
|
|
5402 |
SELECT p.projectname,
|
|
5403 |
l.authorlocationcode AS plotcode,
|
|
5404 |
count(DISTINCT sub_locationevent.locationevent_id) AS subplots
|
|
5405 |
FROM ((((public.project p
|
|
5406 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5407 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5408 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5409 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
5410 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
5411 |
GROUP BY p.projectname, l.authorlocationcode
|
|
5412 |
ORDER BY p.projectname, l.authorlocationcode;
|
|
5413 |
|
|
5414 |
|
|
5415 |
--
|
|
5416 |
-- Name: _18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: public_validation_plots; Owner: -
|
|
5417 |
--
|
|
5418 |
|
|
5419 |
CREATE VIEW _18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
|
5420 |
SELECT p.projectname,
|
|
5421 |
l.authorlocationcode AS plotcode,
|
|
5422 |
sub_locationevent.authoreventcode AS "subplotCode"
|
|
5423 |
FROM ((((public.project p
|
|
5424 |
JOIN public.source s ON ((p.source_id = s.source_id)))
|
|
5425 |
JOIN public.locationevent le ON ((p.project_id = le.project_id)))
|
|
5426 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
|
|
5427 |
JOIN public.location l ON ((sub_locationevent.location_id = l.location_id)))
|
|
5428 |
WHERE (s.shortname = ("current_schema"())::text)
|
|
5429 |
ORDER BY p.projectname, l.authorlocationcode;
|
|
5430 |
|
|
5431 |
|
| 5182 |
5432 |
SET search_path = public_validation_traits, pg_catalog;
|
| 5183 |
5433 |
|
| 5184 |
5434 |
--
|
schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql