/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 14423

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: taxon_scrub: use taxon_match derived columns instead of the incorrect values in taxon_scrub.scrubbed_unique_taxon_name.* (which does not work with the multi-match strategy)

     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     --
     CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
      SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
         taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
         taxon_match."*Genus_matched" AS scrubbed_genus,
         taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
         taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
         taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         taxon_match."*Name_matched_author" AS scrubbed_author,
         taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
         (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
        FROM taxon_match;
     --
     -- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
     --
     COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
     to modify:
     SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
     SELECT __
     $$);
     scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
     ';
     --
     -- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
     --
-...
         "ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org",
         "ValidMatchedTaxon"."taxonomicStatus",
         "ValidMatchedTaxon".accepted_morphospecies_binomial,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_rank,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_family,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_genus,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_specific_epithet,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_infraspecific_rank,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_infraspecific_epithet,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_name_no_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_name_with_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_morphospecies_binomial
        FROM ("ValidMatchedTaxon"
        LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
        FROM "ValidMatchedTaxon";
     --
-...
     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     --
     CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
      SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
         taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
         taxon_match."*Genus_matched" AS scrubbed_genus,
         taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
         taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
         taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         taxon_match."*Name_matched_author" AS scrubbed_author,
         taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
         (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
        FROM taxon_match;
     --
     -- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
     --
     COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
     to modify:
     SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
     SELECT __
     $$);
     scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
     ';
     --
     -- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
     --
-...
     --
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     --
     REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
     REVOKE ALL ON TABLE taxon_scrub FROM bien;
     GRANT ALL ON TABLE taxon_scrub TO bien;
     GRANT SELECT ON TABLE taxon_scrub TO bien_read;
     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
     --
-...
     --
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     --
     REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
     REVOKE ALL ON TABLE taxon_scrub FROM bien;
     GRANT ALL ON TABLE taxon_scrub TO bien;
     GRANT SELECT ON TABLE taxon_scrub TO bien_read;
     --
     -- PostgreSQL database dump complete
     --

     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     -- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
     --
     --
     -- Name: VIEW `taxon_scrub.scrubbed_unique_taxon_name.*`; Type: COMMENT; Schema: TNRS; Owner: -
     -- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -
     --
     --
     -- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     --
     --
     -- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -
     -- Name: VIEW `taxon_scrub.scrubbed_unique_taxon_name.*`; Type: COMMENT; Schema: TNRS; Owner: -
     --
-...
     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     --
-...
     --
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
     --

     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     --
     CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
      SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
         taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
         taxon_match."*Genus_matched" AS scrubbed_genus,
         taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
         taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
         taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         taxon_match."*Name_matched_author" AS scrubbed_author,
         taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
         (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
        FROM taxon_match;
     --
     -- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
     --
     COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
     to modify:
     SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
     SELECT __
     $$);
     scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
     ';
     --
     -- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
     --
-...
         "ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org",
         "ValidMatchedTaxon"."taxonomicStatus",
         "ValidMatchedTaxon".accepted_morphospecies_binomial,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
         "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_rank,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_family,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_genus,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_specific_epithet,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_infraspecific_rank,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_infraspecific_epithet,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_name_no_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_taxon_name_with_author,
             CASE
                 WHEN true THEN "ValidMatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"
                 ELSE NULL::text
             END AS scrubbed_morphospecies_binomial
        FROM ("ValidMatchedTaxon"
        LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
        FROM "ValidMatchedTaxon";
     --
-...
     ';
     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
     --
     CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
      SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
         taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
         taxon_match."*Genus_matched" AS scrubbed_genus,
         taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
         taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
         taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         taxon_match."*Name_matched_author" AS scrubbed_author,
         taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
         (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
        FROM taxon_match;
     --
     -- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
     --
     COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
     to modify:
     SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
     SELECT __
     $$);
     scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
     ';
     SET search_path = geoscrub, pg_catalog;
     --
-...
     --
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     --
     REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
     REVOKE ALL ON TABLE taxon_scrub FROM bien;
     GRANT ALL ON TABLE taxon_scrub TO bien;
     GRANT SELECT ON TABLE taxon_scrub TO bien_read;
     --
     -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
     --
-...
     --
     -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
     --
     REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
     REVOKE ALL ON TABLE taxon_scrub FROM bien;
     GRANT ALL ON TABLE taxon_scrub TO bien;
     GRANT SELECT ON TABLE taxon_scrub TO bien_read;
     --
     -- PostgreSQL database dump complete
     --

Also available in: Unified diff

Project

General

Profile

Revision 14423

Added by Aaron Marcuse-Kubitza over 10 years ago