Revision 5864
Added by Aaron Marcuse-Kubitza over 12 years ago
schemas/vegbien.sql | ||
---|---|---|
438 | 438 |
|
439 | 439 |
|
440 | 440 |
-- |
441 |
-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: -
|
|
441 |
-- Name: make_analytical_stem(); Type: FUNCTION; Schema: public; Owner: -
|
|
442 | 442 |
-- |
443 | 443 |
|
444 |
CREATE FUNCTION make_analytical_db() RETURNS void
|
|
444 |
CREATE FUNCTION make_analytical_stem() RETURNS void
|
|
445 | 445 |
LANGUAGE sql |
446 | 446 |
AS $$ |
447 |
INSERT INTO analytical_db SELECT * FROM analytical_db_view;
|
|
447 |
INSERT INTO analytical_stem SELECT * FROM analytical_stem_view;
|
|
448 | 448 |
INSERT INTO aggregated_analytical_db SELECT * FROM aggregated_analytical_db_view; |
449 | 449 |
$$; |
450 | 450 |
|
... | ... | |
569 | 569 |
|
570 | 570 |
|
571 | 571 |
-- |
572 |
-- Name: sync_analytical_db_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
572 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
573 | 573 |
-- |
574 | 574 |
|
575 |
CREATE FUNCTION sync_analytical_db_to_view() RETURNS void
|
|
575 |
CREATE FUNCTION sync_analytical_stem_to_view() RETURNS void
|
|
576 | 576 |
LANGUAGE sql |
577 | 577 |
AS $$ |
578 |
DROP TABLE IF EXISTS analytical_db;
|
|
579 |
CREATE TABLE analytical_db AS SELECT * FROM analytical_db_view;
|
|
578 |
DROP TABLE IF EXISTS analytical_stem;
|
|
579 |
CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view;
|
|
580 | 580 |
$$; |
581 | 581 |
|
582 | 582 |
|
... | ... | |
997 | 997 |
|
998 | 998 |
|
999 | 999 |
-- |
1000 |
-- Name: analytical_db; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
1000 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
1001 | 1001 |
-- |
1002 | 1002 |
|
1003 |
CREATE TABLE analytical_db (
|
|
1003 |
CREATE TABLE analytical_stem (
|
|
1004 | 1004 |
"institutionCode" text, |
1005 | 1005 |
country text, |
1006 | 1006 |
"stateProvince" text, |
... | ... | |
1038 | 1038 |
-- |
1039 | 1039 |
|
1040 | 1040 |
CREATE VIEW aggregated_analytical_db_view AS |
1041 |
SELECT analytical_db."institutionCode", analytical_db.country, analytical_db."stateProvince", analytical_db.county, analytical_db."decimalLatitude", analytical_db."decimalLongitude", analytical_db."plotName", analytical_db."elevationInMeters", analytical_db."plotArea_ha", analytical_db."samplingProtocol", analytical_db."dateCollected", analytical_db.family, analytical_db.genus, analytical_db."speciesBinomial", analytical_db."scientificName", analytical_db."scientificNameAuthorship", analytical_db."scientificNameWithMorphospecies", count(analytical_db."diameterBreastHeight_cm") AS "individualCount", count(((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_db."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_db."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", count((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_db WHERE (analytical_db."diameterBreastHeight_cm" IS NOT NULL) GROUP BY analytical_db."institutionCode", analytical_db.country, analytical_db."stateProvince", analytical_db.county, analytical_db."decimalLatitude", analytical_db."decimalLongitude", analytical_db."plotName", analytical_db."elevationInMeters", analytical_db."plotArea_ha", analytical_db."samplingProtocol", analytical_db."dateCollected", analytical_db.family, analytical_db.genus, analytical_db."speciesBinomial", analytical_db."scientificName", analytical_db."scientificNameAuthorship", analytical_db."scientificNameWithMorphospecies";
|
|
1041 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem WHERE (analytical_stem."diameterBreastHeight_cm" IS NOT NULL) GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies";
|
|
1042 | 1042 |
|
1043 | 1043 |
|
1044 | 1044 |
-- |
... | ... | |
1846 | 1846 |
|
1847 | 1847 |
|
1848 | 1848 |
-- |
1849 |
-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: -
|
|
1849 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public; Owner: -
|
|
1850 | 1850 |
-- |
1851 | 1851 |
|
1852 |
CREATE VIEW analytical_db_view AS
|
|
1852 |
CREATE VIEW analytical_stem_view AS
|
|
1853 | 1853 |
SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_id)) LEFT JOIN coordinates USING (coordinates_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal)); |
1854 | 1854 |
|
1855 | 1855 |
|
Also available in: Unified diff
schemas/vegbien.sql: Renamed analytical_db to analytical_stem since this contains just the individual stems, not the aggregated data in the main analytical DB