Revision 14097
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 8410 | 8410 |
|
| 8411 | 8411 |
|
| 8412 | 8412 |
-- |
| 8413 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 8414 |
-- |
|
| 8415 |
|
|
| 8416 |
CREATE TABLE threatened_taxonlabel ( |
|
| 8417 |
taxonlabel_id integer NOT NULL |
|
| 8418 |
); |
|
| 8419 |
|
|
| 8420 |
|
|
| 8421 |
-- |
|
| 8422 | 8413 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public; Owner: - |
| 8423 | 8414 |
-- |
| 8424 | 8415 |
|
| ... | ... | |
| 8480 | 8471 |
taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies__@VegCore__@vegpath.org", |
| 8481 | 8472 |
taxonoccurrence.growthform AS "[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org", |
| 8482 | 8473 |
plantobservation.reproductivecondition AS "reproductiveCondition__@DwC__@vegpath.org", |
| 8483 |
((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, |
|
| 8484 | 8474 |
(((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, |
| 8485 | 8475 |
CASE |
| 8486 | 8476 |
WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis |
| ... | ... | |
| 8501 | 8491 |
plantobservation.authorplantcode AS "individualID__@DwC__@vegpath.org", |
| 8502 | 8492 |
aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org", |
| 8503 | 8493 |
stemobservation.authorstemcode AS "stemCode__@VegBank__.stemLocation@vegpath.org" |
| 8504 |
FROM ((((((((((((((("plot.**"
|
|
| 8494 |
FROM (((((((((((((("plot.**"
|
|
| 8505 | 8495 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**"."locationevent.locationevent_id__@VegBIEN__.public@vegpath.org"))) |
| 8506 | 8496 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| 8507 | 8497 |
LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 8515 | 8505 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 8516 | 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname))) |
| 8517 | 8507 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 8518 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))) |
|
| 8519 |
LEFT JOIN threatened_taxonlabel USING (taxonlabel_id)); |
|
| 8508 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))); |
|
| 8520 | 8509 |
|
| 8521 | 8510 |
|
| 8522 | 8511 |
-- |
| ... | ... | |
| 8937 | 8926 |
|
| 8938 | 8927 |
|
| 8939 | 8928 |
-- |
| 8940 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
|
| 8941 |
-- |
|
| 8942 |
|
|
| 8943 |
COMMENT ON COLUMN analytical_stem_view.threatened_bien IS 'whether the taxon is on the IUCN Red List of Threatened Species'; |
|
| 8944 |
|
|
| 8945 |
|
|
| 8946 |
-- |
|
| 8947 | 8929 |
-- Name: COLUMN analytical_stem_view.cultivated_bien; Type: COMMENT; Schema: public; Owner: - |
| 8948 | 8930 |
-- |
| 8949 | 8931 |
|
| ... | ... | |
| 11099 | 11081 |
|
| 11100 | 11082 |
|
| 11101 | 11083 |
-- |
| 11084 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 11085 |
-- |
|
| 11086 |
|
|
| 11087 |
CREATE TABLE threatened_taxonlabel ( |
|
| 11088 |
taxonlabel_id integer NOT NULL |
|
| 11089 |
); |
|
| 11090 |
|
|
| 11091 |
|
|
| 11092 |
-- |
|
| 11102 | 11093 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
| 11103 | 11094 |
-- |
| 11104 | 11095 |
|
| ... | ... | |
| 18513 | 18504 |
|
| 18514 | 18505 |
|
| 18515 | 18506 |
-- |
| 18516 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 18517 |
-- |
|
| 18518 |
|
|
| 18519 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 18520 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 18521 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 18522 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 18523 |
|
|
| 18524 |
|
|
| 18525 |
-- |
|
| 18526 | 18507 |
-- Name: analytical_stem_view; Type: ACL; Schema: public; Owner: - |
| 18527 | 18508 |
-- |
| 18528 | 18509 |
|
| ... | ... | |
| 19068 | 19049 |
|
| 19069 | 19050 |
|
| 19070 | 19051 |
-- |
| 19052 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 19053 |
-- |
|
| 19054 |
|
|
| 19055 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 19056 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 19057 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 19058 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 19059 |
|
|
| 19060 |
|
|
| 19061 |
-- |
|
| 19071 | 19062 |
-- Name: threatened_taxonlabel_view; Type: ACL; Schema: public; Owner: - |
| 19072 | 19063 |
-- |
| 19073 | 19064 |
|
Also available in: Unified diff
fix: schemas/public_.sql: analytical_stem_view: don't use threatened_taxonlabel, which was never populated correctly. instead, this can eventually be rewritten to use new iucn_red_list.