Revision 12632
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 6314 | 6314 |
-- |
| 6315 | 6315 |
|
| 6316 | 6316 |
CREATE VIEW plot AS |
| 6317 |
SELECT location.location_id, |
|
| 6317 |
SELECT location.location_id AS plot_id,
|
|
| 6318 | 6318 |
location.source_id, |
| 6319 | 6319 |
location.sourceaccessioncode, |
| 6320 | 6320 |
location.plot_location_id AS plot, |
| ... | ... | |
| 7298 | 7298 |
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS |
| 7299 | 7299 |
SELECT count(*) AS plots |
| 7300 | 7300 |
FROM (((public.plot l |
| 7301 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
| 7301 |
JOIN public.locationevent le ON ((l.plot_id = le.location_id)))
|
|
| 7302 | 7302 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
| 7303 | 7303 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7304 | 7304 |
WHERE (s.shortname = ("current_schema"())::text);
|
| ... | ... | |
| 7310 | 7310 |
|
| 7311 | 7311 |
CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS |
| 7312 | 7312 |
SELECT p.projectname AS project_name, |
| 7313 |
count(DISTINCT l.location_id) AS plots
|
|
| 7313 |
count(DISTINCT l.plot_id) AS plots
|
|
| 7314 | 7314 |
FROM (((public.plot l |
| 7315 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
| 7315 |
JOIN public.locationevent le ON ((l.plot_id = le.location_id)))
|
|
| 7316 | 7316 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
| 7317 | 7317 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7318 | 7318 |
WHERE (s.shortname = ("current_schema"())::text)
|
| ... | ... | |
| 7327 | 7327 |
SELECT p.projectname AS project_name, |
| 7328 | 7328 |
l.authorlocationcode AS plot_code |
| 7329 | 7329 |
FROM (((public.plot l |
| 7330 |
JOIN public.locationevent le ON ((l.location_id = le.location_id)))
|
|
| 7330 |
JOIN public.locationevent le ON ((l.plot_id = le.location_id)))
|
|
| 7331 | 7331 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
| 7332 | 7332 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7333 | 7333 |
WHERE (s.shortname = ("current_schema"())::text);
|
| ... | ... | |
| 7340 | 7340 |
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS |
| 7341 | 7341 |
SELECT project.projectname AS project_name, |
| 7342 | 7342 |
plot.authorlocationcode AS plot_code |
| 7343 |
FROM ((public.plot |
|
| 7343 |
FROM ((public.plot plot(location_id, source_id, sourceaccessioncode, plot, parent_id, authorlocationcode, place_id, accesslevel, accessconditions, sublocationxposition_m, sublocationyposition_m, iscultivated, authorzone, authordatum, authorlocation, locationnarrative, azimuth, shape, area_m2, standsize, placementmethod, permanence, layoutnarrative, elevation_m, elevationaccuracy_m, elevationrange_m, verbatimelevation, slopeaspect_deg, minslopeaspect_deg, maxslopeaspect_deg, slopegradient_fraction, minslopegradient_fraction, maxslopegradient_fraction, topoposition, landform, surficialdeposits, rocktype, submitter_surname, submitter_givenname, submitter_email, notespublic, notesmgt, revisions, dateentered, locationrationalenarrative)
|
|
| 7344 | 7344 |
JOIN public.locationevent USING (location_id)) |
| 7345 | 7345 |
LEFT JOIN public.project USING (project_id)) |
| 7346 | 7346 |
WHERE ((plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
| ... | ... | |
| 7367 | 7367 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7368 | 7368 |
JOIN public.place_visit ON ((p.project_id = place_visit.project_id))) |
| 7369 | 7369 |
JOIN public.locationevent le ON ((place_visit.locationevent_id = le.place_visit_id))) |
| 7370 |
JOIN public.plot l ON ((place_visit.location_id = l.location_id)))
|
|
| 7370 |
JOIN public.plot l ON ((place_visit.location_id = l.plot_id)))
|
|
| 7371 | 7371 |
JOIN public.taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id))) |
| 7372 | 7372 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
| 7373 | 7373 |
WHERE (s.shortname = ("current_schema"())::text)
|
| ... | ... | |
| 7399 | 7399 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7400 | 7400 |
JOIN public.place_visit ON ((p.project_id = place_visit.project_id))) |
| 7401 | 7401 |
JOIN public.locationevent le ON ((place_visit.locationevent_id = le.place_visit_id))) |
| 7402 |
JOIN public.plot l ON ((place_visit.location_id = l.location_id)))
|
|
| 7402 |
JOIN public.plot l ON ((place_visit.location_id = l.plot_id)))
|
|
| 7403 | 7403 |
JOIN public.taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id))) |
| 7404 | 7404 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
| 7405 | 7405 |
WHERE (s.shortname = ("current_schema"())::text)
|
| ... | ... | |
| 7427 | 7427 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7428 | 7428 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7429 | 7429 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7430 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7430 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7431 | 7431 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
| 7432 | 7432 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
| 7433 | 7433 |
JOIN public.plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id))) |
| ... | ... | |
| 7458 | 7458 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7459 | 7459 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7460 | 7460 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7461 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7461 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7462 | 7462 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
| 7463 | 7463 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
| 7464 | 7464 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
| ... | ... | |
| 7490 | 7490 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7491 | 7491 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7492 | 7492 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7493 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7493 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7494 | 7494 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
| 7495 | 7495 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
| 7496 | 7496 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
| ... | ... | |
| 7520 | 7520 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7521 | 7521 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7522 | 7522 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7523 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7523 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7524 | 7524 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
| 7525 | 7525 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
| 7526 | 7526 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
| ... | ... | |
| 7552 | 7552 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7553 | 7553 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7554 | 7554 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7555 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7555 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7556 | 7556 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
| 7557 | 7557 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
| 7558 | 7558 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
| ... | ... | |
| 7587 | 7587 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7588 | 7588 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7589 | 7589 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7590 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
| 7590 |
JOIN public.plot l ON ((le.location_id = l.plot_id)))
|
|
| 7591 | 7591 |
WHERE (s.shortname = ("current_schema"())::text)
|
| 7592 | 7592 |
GROUP BY p.projectname, l.authorlocationcode |
| 7593 | 7593 |
ORDER BY p.projectname, l.authorlocationcode; |
| ... | ... | |
| 7605 | 7605 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| 7606 | 7606 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
| 7607 | 7607 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
| 7608 |
JOIN public.plot l ON ((sub_locationevent.location_id = l.location_id)))
|
|
| 7608 |
JOIN public.plot l ON ((sub_locationevent.location_id = l.plot_id)))
|
|
| 7609 | 7609 |
WHERE (s.shortname = ("current_schema"())::text)
|
| 7610 | 7610 |
ORDER BY p.projectname, l.authorlocationcode; |
| 7611 | 7611 |
|
Also available in: Unified diff
schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.