/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 13800

Added by Aaron Marcuse-Kubitza over 10 years ago

schemas/public_.sql, inputs/.TNRS/schema.sql: upgraded to Postgres 9.3.4 format, which removes trailing " "

     --
     CREATE VIEW "MatchedTaxon" AS
      SELECT s."*Name_matched.batch",
         s."concatenatedScientificName",
         s."matchedTaxonName",
         s."matchedTaxonRank",
         s."*Name_matched.Name_score",
         s."matchedScientificNameAuthorship",
         s."matchedScientificNameID",
         s."*Name_matched.Author_score",
         s."matchedFamilyConfidence_fraction",
         s."matchedFamily",
         s."matchedGenus",
         s."matchedGenusConfidence_fraction",
         s."matchedSpecificEpithet",
         s."matchedSpeciesConfidence_fraction",
         s."matchedInfraspecificEpithet",
         s."*Name_matched.Infraspecific_epithet_score",
         s."identificationQualifier",
         s."morphospeciesSuffix",
         s."taxonomicStatus",
         s.accepted_taxon_name_no_author,
         s.accepted_author,
         s.accepted_taxon_rank,
         s."acceptedScientificNameID",
         s.accepted_species_binomial,
         s.accepted_family,
         s."*Name_matched.Selected",
         s."*Name_matched.Source",
         s."*Name_matched.Warnings",
         s."*Name_matched.Accepted_name_lsid",
         s.taxon_scrub__is_valid_match,
         s.scrubbed_unique_taxon_name,
      SELECT s."*Name_matched.batch",
         s."concatenatedScientificName",
         s."matchedTaxonName",
         s."matchedTaxonRank",
         s."*Name_matched.Name_score",
         s."matchedScientificNameAuthorship",
         s."matchedScientificNameID",
         s."*Name_matched.Author_score",
         s."matchedFamilyConfidence_fraction",
         s."matchedFamily",
         s."matchedGenus",
         s."matchedGenusConfidence_fraction",
         s."matchedSpecificEpithet",
         s."matchedSpeciesConfidence_fraction",
         s."matchedInfraspecificEpithet",
         s."*Name_matched.Infraspecific_epithet_score",
         s."identificationQualifier",
         s."morphospeciesSuffix",
         s."taxonomicStatus",
         s.accepted_taxon_name_no_author,
         s.accepted_author,
         s.accepted_taxon_rank,
         s."acceptedScientificNameID",
         s.accepted_species_binomial,
         s.accepted_family,
         s."*Name_matched.Selected",
         s."*Name_matched.Source",
         s."*Name_matched.Warnings",
         s."*Name_matched.Accepted_name_lsid",
         s.taxon_scrub__is_valid_match,
         s.scrubbed_unique_taxon_name,
             CASE
                 WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
                 WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
                 ELSE s.accepted_species_binomial
             END AS accepted_morphospecies_binomial
        FROM ( SELECT tnrs.batch AS "*Name_matched.batch",
                 tnrs."Name_submitted" AS "concatenatedScientificName",
                 tnrs."Name_matched" AS "matchedTaxonName",
                 tnrs."Name_matched_rank" AS "matchedTaxonRank",
                 tnrs."Name_score" AS "*Name_matched.Name_score",
                 tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",
                 tnrs."Name_matched_url" AS "matchedScientificNameID",
                 tnrs."Author_score" AS "*Name_matched.Author_score",
                 tnrs."Family_score" AS "matchedFamilyConfidence_fraction",
                 COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",
                 tnrs."Genus_matched" AS "matchedGenus",
                 tnrs."Genus_score" AS "matchedGenusConfidence_fraction",
                 tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",
                 tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
                 tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
                 tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
                 tnrs."Annotations" AS "identificationQualifier",
                 tnrs."Unmatched_terms" AS "morphospeciesSuffix",
                 map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name") AS "taxonomicStatus",
                 tnrs."Accepted_name" AS accepted_taxon_name_no_author,
                 tnrs."Accepted_name_author" AS accepted_author,
                 tnrs."Accepted_name_rank" AS accepted_taxon_rank,
                 tnrs."Accepted_name_url" AS "acceptedScientificNameID",
                 tnrs."Accepted_name_species" AS accepted_species_binomial,
                 tnrs."Accepted_name_family" AS accepted_family,
                 tnrs."Selected" AS "*Name_matched.Selected",
                 tnrs."Source" AS "*Name_matched.Source",
                 tnrs."Warnings" AS "*Name_matched.Warnings",
                 tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
                 tnrs.is_valid_match AS taxon_scrub__is_valid_match,
        FROM ( SELECT tnrs.batch AS "*Name_matched.batch",
                 tnrs."Name_submitted" AS "concatenatedScientificName",
                 tnrs."Name_matched" AS "matchedTaxonName",
                 tnrs."Name_matched_rank" AS "matchedTaxonRank",
                 tnrs."Name_score" AS "*Name_matched.Name_score",
                 tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",
                 tnrs."Name_matched_url" AS "matchedScientificNameID",
                 tnrs."Author_score" AS "*Name_matched.Author_score",
                 tnrs."Family_score" AS "matchedFamilyConfidence_fraction",
                 COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",
                 tnrs."Genus_matched" AS "matchedGenus",
                 tnrs."Genus_score" AS "matchedGenusConfidence_fraction",
                 tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",
                 tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
                 tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
                 tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
                 tnrs."Annotations" AS "identificationQualifier",
                 tnrs."Unmatched_terms" AS "morphospeciesSuffix",
                 map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name") AS "taxonomicStatus",
                 tnrs."Accepted_name" AS accepted_taxon_name_no_author,
                 tnrs."Accepted_name_author" AS accepted_author,
                 tnrs."Accepted_name_rank" AS accepted_taxon_rank,
                 tnrs."Accepted_name_url" AS "acceptedScientificNameID",
                 tnrs."Accepted_name_species" AS accepted_species_binomial,
                 tnrs."Accepted_name_family" AS accepted_family,
                 tnrs."Selected" AS "*Name_matched.Selected",
                 tnrs."Source" AS "*Name_matched.Source",
                 tnrs."Warnings" AS "*Name_matched.Warnings",
                 tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
                 tnrs.is_valid_match AS taxon_scrub__is_valid_match,
                 tnrs.scrubbed_unique_taxon_name
                FROM tnrs) s;
-...
     --
     CREATE VIEW "ValidMatchedTaxon" AS
      SELECT "MatchedTaxon"."*Name_matched.batch",
         "MatchedTaxon"."concatenatedScientificName",
         "MatchedTaxon"."matchedTaxonName",
         "MatchedTaxon"."matchedTaxonRank",
         "MatchedTaxon"."*Name_matched.Name_score",
         "MatchedTaxon"."matchedScientificNameAuthorship",
         "MatchedTaxon"."matchedScientificNameID",
         "MatchedTaxon"."*Name_matched.Author_score",
         "MatchedTaxon"."matchedFamilyConfidence_fraction",
         "MatchedTaxon"."matchedFamily",
         "MatchedTaxon"."matchedGenus",
         "MatchedTaxon"."matchedGenusConfidence_fraction",
         "MatchedTaxon"."matchedSpecificEpithet",
         "MatchedTaxon"."matchedSpeciesConfidence_fraction",
         "MatchedTaxon"."matchedInfraspecificEpithet",
         "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
         "MatchedTaxon"."identificationQualifier",
         "MatchedTaxon"."morphospeciesSuffix",
         "MatchedTaxon"."taxonomicStatus",
         "MatchedTaxon".accepted_taxon_name_no_author,
         "MatchedTaxon".accepted_author,
         "MatchedTaxon".accepted_taxon_rank,
         "MatchedTaxon"."acceptedScientificNameID",
         "MatchedTaxon".accepted_species_binomial,
         "MatchedTaxon".accepted_family,
         "MatchedTaxon"."*Name_matched.Selected",
         "MatchedTaxon"."*Name_matched.Source",
         "MatchedTaxon"."*Name_matched.Warnings",
         "MatchedTaxon"."*Name_matched.Accepted_name_lsid",
         "MatchedTaxon".taxon_scrub__is_valid_match,
         "MatchedTaxon".scrubbed_unique_taxon_name,
      SELECT "MatchedTaxon"."*Name_matched.batch",
         "MatchedTaxon"."concatenatedScientificName",
         "MatchedTaxon"."matchedTaxonName",
         "MatchedTaxon"."matchedTaxonRank",
         "MatchedTaxon"."*Name_matched.Name_score",
         "MatchedTaxon"."matchedScientificNameAuthorship",
         "MatchedTaxon"."matchedScientificNameID",
         "MatchedTaxon"."*Name_matched.Author_score",
         "MatchedTaxon"."matchedFamilyConfidence_fraction",
         "MatchedTaxon"."matchedFamily",
         "MatchedTaxon"."matchedGenus",
         "MatchedTaxon"."matchedGenusConfidence_fraction",
         "MatchedTaxon"."matchedSpecificEpithet",
         "MatchedTaxon"."matchedSpeciesConfidence_fraction",
         "MatchedTaxon"."matchedInfraspecificEpithet",
         "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
         "MatchedTaxon"."identificationQualifier",
         "MatchedTaxon"."morphospeciesSuffix",
         "MatchedTaxon"."taxonomicStatus",
         "MatchedTaxon".accepted_taxon_name_no_author,
         "MatchedTaxon".accepted_author,
         "MatchedTaxon".accepted_taxon_rank,
         "MatchedTaxon"."acceptedScientificNameID",
         "MatchedTaxon".accepted_species_binomial,
         "MatchedTaxon".accepted_family,
         "MatchedTaxon"."*Name_matched.Selected",
         "MatchedTaxon"."*Name_matched.Source",
         "MatchedTaxon"."*Name_matched.Warnings",
         "MatchedTaxon"."*Name_matched.Accepted_name_lsid",
         "MatchedTaxon".taxon_scrub__is_valid_match,
         "MatchedTaxon".scrubbed_unique_taxon_name,
         "MatchedTaxon".accepted_morphospecies_binomial
        FROM "MatchedTaxon"
       WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
-...
     --
     CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
      SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,
         tnrs."Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family,
         tnrs."Genus_matched" AS scrubbed_genus,
         tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,
         tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,
         tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         tnrs."Name_matched_author" AS scrubbed_author,
         tnrs."Name_matched" AS scrubbed_taxon_name_no_author,
      SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,
         tnrs."Name_matched_rank" AS scrubbed_taxon_rank,
         COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family,
         tnrs."Genus_matched" AS scrubbed_genus,
         tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,
         tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,
         tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
         tnrs."Name_matched_author" AS scrubbed_author,
         tnrs."Name_matched" AS scrubbed_taxon_name_no_author,
         (tnrs."Name_matched" || COALESCE((' '::text || tnrs."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
        FROM tnrs;
-...
     --
     CREATE VIEW taxon_scrub AS
      SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
         "ValidMatchedTaxon"."*Name_matched.batch",
         "ValidMatchedTaxon"."concatenatedScientificName",
         "ValidMatchedTaxon"."matchedTaxonName",
         "ValidMatchedTaxon"."matchedTaxonRank",
         "ValidMatchedTaxon"."*Name_matched.Name_score",
         "ValidMatchedTaxon"."matchedScientificNameAuthorship",
         "ValidMatchedTaxon"."matchedScientificNameID",
         "ValidMatchedTaxon"."*Name_matched.Author_score",
         "ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
         "ValidMatchedTaxon"."matchedFamily",
         "ValidMatchedTaxon"."matchedGenus",
         "ValidMatchedTaxon"."matchedGenusConfidence_fraction",
         "ValidMatchedTaxon"."matchedSpecificEpithet",
         "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
         "ValidMatchedTaxon"."matchedInfraspecificEpithet",
         "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
         "ValidMatchedTaxon"."identificationQualifier",
         "ValidMatchedTaxon"."morphospeciesSuffix",
         "ValidMatchedTaxon"."taxonomicStatus",
         "ValidMatchedTaxon".accepted_taxon_name_no_author,
         "ValidMatchedTaxon".accepted_author,
         "ValidMatchedTaxon".accepted_taxon_rank,
         "ValidMatchedTaxon"."acceptedScientificNameID",
         "ValidMatchedTaxon".accepted_species_binomial,
         "ValidMatchedTaxon".accepted_family,
         "ValidMatchedTaxon"."*Name_matched.Selected",
         "ValidMatchedTaxon"."*Name_matched.Source",
         "ValidMatchedTaxon"."*Name_matched.Warnings",
         "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
         "ValidMatchedTaxon".taxon_scrub__is_valid_match,
         "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,
      SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
         "ValidMatchedTaxon"."*Name_matched.batch",
         "ValidMatchedTaxon"."concatenatedScientificName",
         "ValidMatchedTaxon"."matchedTaxonName",
         "ValidMatchedTaxon"."matchedTaxonRank",
         "ValidMatchedTaxon"."*Name_matched.Name_score",
         "ValidMatchedTaxon"."matchedScientificNameAuthorship",
         "ValidMatchedTaxon"."matchedScientificNameID",
         "ValidMatchedTaxon"."*Name_matched.Author_score",
         "ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
         "ValidMatchedTaxon"."matchedFamily",
         "ValidMatchedTaxon"."matchedGenus",
         "ValidMatchedTaxon"."matchedGenusConfidence_fraction",
         "ValidMatchedTaxon"."matchedSpecificEpithet",
         "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
         "ValidMatchedTaxon"."matchedInfraspecificEpithet",
         "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
         "ValidMatchedTaxon"."identificationQualifier",
         "ValidMatchedTaxon"."morphospeciesSuffix",
         "ValidMatchedTaxon"."taxonomicStatus",
         "ValidMatchedTaxon".accepted_taxon_name_no_author,
         "ValidMatchedTaxon".accepted_author,
         "ValidMatchedTaxon".accepted_taxon_rank,
         "ValidMatchedTaxon"."acceptedScientificNameID",
         "ValidMatchedTaxon".accepted_species_binomial,
         "ValidMatchedTaxon".accepted_family,
         "ValidMatchedTaxon"."*Name_matched.Selected",
         "ValidMatchedTaxon"."*Name_matched.Source",
         "ValidMatchedTaxon"."*Name_matched.Warnings",
         "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
         "ValidMatchedTaxon".taxon_scrub__is_valid_match,
         "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 ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")
                 WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")

     --
     CREATE VIEW "2013-10-18.Brian_Enquist.Canadensys" AS
      SELECT analytical_stem.scrubbed_morphospecies_binomial AS species,
         analytical_stem."decimalLatitude" AS latitude__deg,
         analytical_stem."decimalLongitude" AS longitude__deg,
      SELECT analytical_stem.scrubbed_morphospecies_binomial AS species,
         analytical_stem."decimalLatitude" AS latitude__deg,
         analytical_stem."decimalLongitude" AS longitude__deg,
         analytical_stem."coordinateUncertaintyInMeters" AS coords__uncertainty__m
        FROM analytical_stem
       WHERE (((((analytical_stem.scrubbed_morphospecies_binomial = ANY (ARRAY['Juniperus scopulorum'::text, 'Picea engelmannii'::text, 'Pinus contorta'::text, 'Pinus edulis'::text, 'Pinus ponderosa'::text, 'Populus tremuloides'::text, 'Pseudotsuga menziesii'::text, 'Quercus gambelii'::text])) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL))
-...
     --
     CREATE VIEW "2013-7-10.Naia.range_limiting_factors" AS
      SELECT analytical_stem.taxon_occurrence__pkey AS occurrence_id,
         analytical_stem.scrubbed_morphospecies_binomial AS species,
         analytical_stem."decimalLatitude" AS latitude__deg,
      SELECT analytical_stem.taxon_occurrence__pkey AS occurrence_id,
         analytical_stem.scrubbed_morphospecies_binomial AS species,
         analytical_stem."decimalLatitude" AS latitude__deg,
         analytical_stem."decimalLongitude" AS longitude__deg
        FROM analytical_stem
       WHERE ((((((NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false)) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem.scrubbed_morphospecies_binomial IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters" <= _km_to_m((10)::double precision)), true));
-...
     --
     CREATE VIEW "2014-3-11.Jeff_Ott.climatic_range_determinants" AS
      SELECT analytical_stem.datasource,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
         analytical_stem.scrubbed_family AS family,
         analytical_stem.scrubbed_genus AS genus,
         analytical_stem.scrubbed_species_binomial AS "speciesBinomial",
      SELECT analytical_stem.datasource,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
         analytical_stem.scrubbed_family AS family,
         analytical_stem.scrubbed_genus AS genus,
         analytical_stem.scrubbed_species_binomial AS "speciesBinomial",
         analytical_stem.scrubbed_taxon_name_with_author AS "scientificName"
        FROM analytical_stem
       WHERE (((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND COALESCE((analytical_stem."isNewWorld_bien")::boolean, false)) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_taxon_name_with_author IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL));
-...
     --
     CREATE VIEW "2014-6-12.Jeff_Ott.climatic_range_determinants" AS
      SELECT analytical_stem.datasource,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem.geovalid_bien,
         analytical_stem."dateCollected",
         analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
         analytical_stem.scrubbed_family AS family,
         analytical_stem.scrubbed_genus AS genus,
         analytical_stem.scrubbed_species_binomial AS "speciesBinomial",
      SELECT analytical_stem.datasource,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem.geovalid_bien,
         analytical_stem."dateCollected",
         analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
         analytical_stem.scrubbed_family AS family,
         analytical_stem.scrubbed_genus AS genus,
         analytical_stem.scrubbed_species_binomial AS "speciesBinomial",
         analytical_stem.scrubbed_taxon_name_with_author AS "scientificName"
        FROM analytical_stem
       WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND COALESCE((analytical_stem."isNewWorld_bien")::boolean, false)) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters" <= _km_to_m((10)::double precision)), true));
-...
     --
     CREATE VIEW analytical_plot AS
      SELECT analytical_stem.datasource,
         analytical_stem.country,
         analytical_stem."stateProvince",
         analytical_stem.county,
         analytical_stem.locality,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem."coordinateSource_bien",
         analytical_stem."georeferenceProtocol_bien",
         analytical_stem.geovalid_bien,
         analytical_stem."isNewWorld_bien",
         analytical_stem."projectID",
         analytical_stem.project_contributors,
         analytical_stem."locationID",
         analytical_stem."locationName",
         analytical_stem.subplot,
         analytical_stem.location__cultivated__bien,
         analytical_stem."eventDate",
         analytical_stem."elevationInMeters",
         analytical_stem."slopeAspect",
         analytical_stem."slopeGradient",
         analytical_stem."plotArea_ha",
         analytical_stem."samplingProtocol",
         analytical_stem."temperature_C",
         analytical_stem.precipitation_m,
         analytical_stem.stratum__name,
         analytical_stem.communities,
         analytical_stem.plot__collectors,
         analytical_stem."recordedBy",
         analytical_stem."recordNumber",
         analytical_stem."dateCollected",
         analytical_stem.family_verbatim,
         analytical_stem."scientificName_verbatim",
         analytical_stem."identifiedBy",
         analytical_stem."dateIdentified",
         analytical_stem."identificationRemarks",
         analytical_stem.family_matched,
         analytical_stem."taxonName_matched",
         analytical_stem."scientificNameAuthorship_matched",
         analytical_stem.scrubbed_family,
         analytical_stem.scrubbed_genus,
         analytical_stem.scrubbed_morphospecies_binomial,
         analytical_stem.scrubbed_taxon_name_no_author,
         analytical_stem.scrubbed_author,
         analytical_stem."growthForm",
         analytical_stem."reproductiveCondition",
         analytical_stem.cultivated_bien,
         analytical_stem."cultivatedBasis_bien",
         analytical_stem."occurrenceRemarks",
         analytical_stem."coverPercent",
         analytical_stem."diameterBreastHeight_cm",
         analytical_stem.height_m,
         analytical_stem.tag,
         analytical_stem."organismX_m",
         analytical_stem."organismY_m",
         analytical_stem."taxonOccurrenceID",
         analytical_stem."authorTaxonCode",
         analytical_stem."aggregateOrganismObservationID",
         analytical_stem."individualObservationID",
         analytical_stem."individualCode",
         analytical_stem."individualCount",
      SELECT analytical_stem.datasource,
         analytical_stem.country,
         analytical_stem."stateProvince",
         analytical_stem.county,
         analytical_stem.locality,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem."coordinateSource_bien",
         analytical_stem."georeferenceProtocol_bien",
         analytical_stem.geovalid_bien,
         analytical_stem."isNewWorld_bien",
         analytical_stem."projectID",
         analytical_stem.project_contributors,
         analytical_stem."locationID",
         analytical_stem."locationName",
         analytical_stem.subplot,
         analytical_stem.location__cultivated__bien,
         analytical_stem."eventDate",
         analytical_stem."elevationInMeters",
         analytical_stem."slopeAspect",
         analytical_stem."slopeGradient",
         analytical_stem."plotArea_ha",
         analytical_stem."samplingProtocol",
         analytical_stem."temperature_C",
         analytical_stem.precipitation_m,
         analytical_stem.stratum__name,
         analytical_stem.communities,
         analytical_stem.plot__collectors,
         analytical_stem."recordedBy",
         analytical_stem."recordNumber",
         analytical_stem."dateCollected",
         analytical_stem.family_verbatim,
         analytical_stem."scientificName_verbatim",
         analytical_stem."identifiedBy",
         analytical_stem."dateIdentified",
         analytical_stem."identificationRemarks",
         analytical_stem.family_matched,
         analytical_stem."taxonName_matched",
         analytical_stem."scientificNameAuthorship_matched",
         analytical_stem.scrubbed_family,
         analytical_stem.scrubbed_genus,
         analytical_stem.scrubbed_morphospecies_binomial,
         analytical_stem.scrubbed_taxon_name_no_author,
         analytical_stem.scrubbed_author,
         analytical_stem."growthForm",
         analytical_stem."reproductiveCondition",
         analytical_stem.cultivated_bien,
         analytical_stem."cultivatedBasis_bien",
         analytical_stem."occurrenceRemarks",
         analytical_stem."coverPercent",
         analytical_stem."diameterBreastHeight_cm",
         analytical_stem.height_m,
         analytical_stem.tag,
         analytical_stem."organismX_m",
         analytical_stem."organismY_m",
         analytical_stem."taxonOccurrenceID",
         analytical_stem."authorTaxonCode",
         analytical_stem."aggregateOrganismObservationID",
         analytical_stem."individualObservationID",
         analytical_stem."individualCode",
         analytical_stem."individualCount",
         analytical_stem."authorStemCode"
        FROM analytical_stem;
-...
     --
     CREATE VIEW analytical_specimen AS
      SELECT analytical_stem.datasource,
         analytical_stem.country,
         analytical_stem."stateProvince",
         analytical_stem.county,
         analytical_stem.locality,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem."coordinateSource_bien",
         analytical_stem."georeferenceProtocol_bien",
         analytical_stem.geovalid_bien,
         analytical_stem."isNewWorld_bien",
         analytical_stem.location__cultivated__bien,
         analytical_stem."eventDate",
         analytical_stem."elevationInMeters",
         analytical_stem."temperature_C",
         analytical_stem.precipitation_m,
         analytical_stem."specimenHolderInstitutions",
         analytical_stem.collection,
         analytical_stem."accessionNumber",
         analytical_stem."occurrenceID",
         analytical_stem."recordedBy",
         analytical_stem."recordNumber",
         analytical_stem."dateCollected",
         analytical_stem.family_verbatim,
         analytical_stem."scientificName_verbatim",
         analytical_stem."identifiedBy",
         analytical_stem."dateIdentified",
         analytical_stem."identificationRemarks",
         analytical_stem.family_matched,
         analytical_stem."taxonName_matched",
         analytical_stem."scientificNameAuthorship_matched",
         analytical_stem.scrubbed_family,
         analytical_stem.scrubbed_genus,
         analytical_stem.scrubbed_taxon_name_no_author,
         analytical_stem.scrubbed_author,
         analytical_stem."growthForm",
         analytical_stem."reproductiveCondition",
         analytical_stem.cultivated_bien,
         analytical_stem."cultivatedBasis_bien",
         analytical_stem."occurrenceRemarks",
         analytical_stem."diameterBreastHeight_cm",
         analytical_stem.height_m,
      SELECT analytical_stem.datasource,
         analytical_stem.country,
         analytical_stem."stateProvince",
         analytical_stem.county,
         analytical_stem.locality,
         analytical_stem."decimalLatitude",
         analytical_stem."decimalLongitude",
         analytical_stem."coordinateUncertaintyInMeters",
         analytical_stem."coordinateSource_bien",
         analytical_stem."georeferenceProtocol_bien",
         analytical_stem.geovalid_bien,
         analytical_stem."isNewWorld_bien",
         analytical_stem.location__cultivated__bien,
         analytical_stem."eventDate",
         analytical_stem."elevationInMeters",
         analytical_stem."temperature_C",
         analytical_stem.precipitation_m,
         analytical_stem."specimenHolderInstitutions",
         analytical_stem.collection,
         analytical_stem."accessionNumber",
         analytical_stem."occurrenceID",
         analytical_stem."recordedBy",
         analytical_stem."recordNumber",
         analytical_stem."dateCollected",
         analytical_stem.family_verbatim,
         analytical_stem."scientificName_verbatim",
         analytical_stem."identifiedBy",
         analytical_stem."dateIdentified",
         analytical_stem."identificationRemarks",
         analytical_stem.family_matched,
         analytical_stem."taxonName_matched",
         analytical_stem."scientificNameAuthorship_matched",
         analytical_stem.scrubbed_family,
         analytical_stem.scrubbed_genus,
         analytical_stem.scrubbed_taxon_name_no_author,
         analytical_stem.scrubbed_author,
         analytical_stem."growthForm",
         analytical_stem."reproductiveCondition",
         analytical_stem.cultivated_bien,
         analytical_stem."cultivatedBasis_bien",
         analytical_stem."occurrenceRemarks",
         analytical_stem."diameterBreastHeight_cm",
         analytical_stem.height_m,
         analytical_stem.tag
        FROM analytical_stem;
-...
     --
     CREATE VIEW "plot.**" AS
      SELECT source.shortname AS datasource,
         COALESCE(geoscrub_output."acceptedCountry", place.country) AS country,
         COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince",
         COALESCE(geoscrub_output."acceptedCounty", place.county) AS county,
         location.locationnarrative AS locality,
      SELECT source.shortname AS datasource,
         COALESCE(geoscrub_output."acceptedCountry", place.country) AS country,
         COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince",
         COALESCE(geoscrub_output."acceptedCounty", place.county) AS county,
         location.locationnarrative AS locality,
             CASE
                 WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg
                 ELSE county_centroids."decimalLatitude"
             END AS "decimalLatitude",
             END AS "decimalLatitude",
             CASE
                 WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg
                 ELSE county_centroids."decimalLongitude"
             END AS "decimalLongitude",
             END AS "decimalLongitude",
             CASE
                 WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m
                 ELSE _km_to_m(county_centroids."*error_km")
             END AS "coordinateUncertaintyInMeters",
             END AS "coordinateUncertaintyInMeters",
             CASE
                 WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource
                 WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'georeferencing'::coordinatesource
                 ELSE NULL::coordinatesource
             END AS "coordinateSource_bien",
             END AS "coordinateSource_bien",
             CASE
                 WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text
                 WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'county centroid'::text
                 ELSE NULL::text
             END AS "georeferenceProtocol_bien",
         (geoscrub_output.geovalid)::integer AS geovalid_bien,
         ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien",
         COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID",
         ARRAY( SELECT project_contributors(project.project_id) AS project_contributors) AS project_contributors,
         COALESCE(location.sourceaccessioncode, NULLIF(concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) AS "locationID",
         COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName",
             END AS "georeferenceProtocol_bien",
         (geoscrub_output.geovalid)::integer AS geovalid_bien,
         ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien",
         COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID",
         ARRAY( SELECT project_contributors(project.project_id) AS project_contributors) AS project_contributors,
         COALESCE(location.sourceaccessioncode, NULLIF(concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) AS "locationID",
         COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName",
             CASE
                 WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode
                 ELSE NULL::text
             END AS subplot,
         location.iscultivated AS location__cultivated__bien,
         locationevent.locationevent_id AS locationevent__pkey,
         COALESCE(locationevent.obsstartdate, parent_event.obsstartdate) AS "eventDate",
         COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters",
         COALESCE(location.slopeaspect_deg, parent_location.slopeaspect_deg) AS "slopeAspect",
         COALESCE(location.slopegradient_fraction, parent_location.slopegradient_fraction) AS "slopeGradient",
         _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha",
         method.name AS "samplingProtocol",
         COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C",
         COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m,
         stratum.stratumname AS stratum__name,
         COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities,
             END AS subplot,
         location.iscultivated AS location__cultivated__bien,
         locationevent.locationevent_id AS locationevent__pkey,
         COALESCE(locationevent.obsstartdate, parent_event.obsstartdate) AS "eventDate",
         COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters",
         COALESCE(location.slopeaspect_deg, parent_location.slopeaspect_deg) AS "slopeAspect",
         COALESCE(location.slopegradient_fraction, parent_location.slopegradient_fraction) AS "slopeGradient",
         _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha",
         method.name AS "samplingProtocol",
         COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C",
         COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m,
         stratum.stratumname AS stratum__name,
         COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities,
         COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS plot__collectors
        FROM (((((((((((((source
        JOIN location USING (source_id))
-...
     --
     CREATE VIEW analytical_stem_view AS
      SELECT "plot.**".datasource,
         "plot.**".country AS "country__@DwC__@vegpath.org",
         "plot.**"."stateProvince" AS "stateProvince__@DwC__@vegpath.org",
         "plot.**".county AS "county__@DwC__@vegpath.org",
         "plot.**".locality AS "locality__@DwC__@vegpath.org",
         "plot.**"."decimalLatitude" AS "decimalLatitude__@DwC__@vegpath.org",
         "plot.**"."decimalLongitude" AS "decimalLongitude__@DwC__@vegpath.org",
         "plot.**"."coordinateUncertaintyInMeters" AS "coordinateUncertaintyInMeters__@DwC__@vegpath.org",
         "plot.**"."coordinateSource_bien" AS "georeferenceSources__@DwC__@vegpath.org",
         "plot.**"."georeferenceProtocol_bien" AS "georeferenceProtocol__@DwC__@vegpath.org",
         "plot.**".geovalid_bien,
         "plot.**"."isNewWorld_bien",
         "plot.**"."projectID" AS "projectID__@VegX__.plotObservation@vegpath.org",
         "plot.**".project_contributors AS "projectContributor[s]__@VegBank__@vegpath.org",
         "plot.**"."locationID" AS "locationID__@DwC__@vegpath.org",
         "plot.**"."locationName" AS "plotName__@VegX__.plot@vegpath.org",
         "plot.**".subplot AS "subplot__@SALVIAS__.Plot_data@vegpath.org",
         "plot.**".location__cultivated__bien,
         "plot.**".locationevent__pkey AS "locationevent.locationevent_id__@VegBIEN__.public@vegpath.org",
         "plot.**"."eventDate" AS "eventDate__@DwC__@vegpath.org",
         "plot.**"."elevationInMeters" AS "(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
         "plot.**"."slopeAspect" AS "slopeAspect[_deg]__@VegX__.plot@vegpath.org",
         "plot.**"."slopeGradient" AS "slopeGradient[_deg]__@VegX__.plot@vegpath.org",
         "plot.**"."plotArea_ha" AS "area[_ha]__@VegX__.plot@vegpath.org",
         "plot.**"."samplingProtocol" AS "samplingProtocol__@DwC__@vegpath.org",
         "plot.**"."temperature_C" AS "temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
         "plot.**".precipitation_m AS "precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
         "plot.**".stratum__name AS "stratumName__@VegX__.stratum@vegpath.org",
         "plot.**".communities AS "communityConcept.name__@VegX__.communityDet@vegpath.org",
         "plot.**".plot__collectors AS "observationContributor[s]__@VegBank__@vegpath.org",
         sourcelist.name AS "[custodial_]institutionCode[s]__@DwC__@vegpath.org",
         specimenreplicate.collectioncode_dwc AS "collectionCode__@DwC__@vegpath.org",
         specimenreplicate.catalognumber_dwc AS "catalogNumber__@DwC__@vegpath.org",
         specimenreplicate.sourceaccessioncode AS "occurrenceID__@DwC__@vegpath.org",
         collector.fullname AS "recordedBy__@DwC__@vegpath.org",
         plantobservation.authorplantcode AS "recordNumber__@DwC__@vegpath.org",
         COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org",
         taxonverbatim.family AS "[verbatim_]family__@DwC__@vegpath.org",
         COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS "[verbatim_]scientificName__@DwC__@vegpath.org",
         identifiedby.fullname AS "identifiedBy__@DwC__@vegpath.org",
         taxondetermination.determinationdate AS "dateIdentified__@DwC__@vegpath.org",
         taxondetermination.notes AS "identificationRemarks__@DwC__@vegpath.org",
         taxon_scrub."matchedFamily" AS "Family_matched__@TNRS__@vegpath.org",
         taxon_scrub."matchedTaxonName" AS "Name_matched__@TNRS__@vegpath.org",
         taxon_scrub."matchedScientificNameAuthorship" AS "Name_matched_author__@TNRS__@vegpath.org",
         family_higher_plant_group.higher_plant_group AS "[higher_plant_group~]higherClassification__@DwC__@vegpath.org",
         taxon_scrub."taxonomicStatus" AS "taxonomicStatus__@DwC__@vegpath.org",
         taxon_scrub.scrubbed_family,
         taxon_scrub.scrubbed_genus,
         taxon_scrub.scrubbed_specific_epithet,
         ((taxon_scrub.scrubbed_genus || ' '::text) || taxon_scrub.scrubbed_specific_epithet) AS scrubbed_species_binomial,
         taxon_scrub.scrubbed_taxon_name_no_author,
         taxon_scrub.scrubbed_author,
         taxon_scrub.scrubbed_taxon_name_with_author,
         taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies__@VegCore__@vegpath.org",
         taxonoccurrence.growthform AS "[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org",
         plantobservation.reproductivecondition AS "reproductiveCondition__@DwC__@vegpath.org",
         ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien,
         (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien,
      SELECT "plot.**".datasource,
         "plot.**".country AS "country__@DwC__@vegpath.org",
         "plot.**"."stateProvince" AS "stateProvince__@DwC__@vegpath.org",
         "plot.**".county AS "county__@DwC__@vegpath.org",
         "plot.**".locality AS "locality__@DwC__@vegpath.org",
         "plot.**"."decimalLatitude" AS "decimalLatitude__@DwC__@vegpath.org",
         "plot.**"."decimalLongitude" AS "decimalLongitude__@DwC__@vegpath.org",
         "plot.**"."coordinateUncertaintyInMeters" AS "coordinateUncertaintyInMeters__@DwC__@vegpath.org",
         "plot.**"."coordinateSource_bien" AS "georeferenceSources__@DwC__@vegpath.org",
         "plot.**"."georeferenceProtocol_bien" AS "georeferenceProtocol__@DwC__@vegpath.org",
         "plot.**".geovalid_bien,
         "plot.**"."isNewWorld_bien",
         "plot.**"."projectID" AS "projectID__@VegX__.plotObservation@vegpath.org",
         "plot.**".project_contributors AS "projectContributor[s]__@VegBank__@vegpath.org",
         "plot.**"."locationID" AS "locationID__@DwC__@vegpath.org",
         "plot.**"."locationName" AS "plotName__@VegX__.plot@vegpath.org",
         "plot.**".subplot AS "subplot__@SALVIAS__.Plot_data@vegpath.org",
         "plot.**".location__cultivated__bien,
         "plot.**".locationevent__pkey AS "locationevent.locationevent_id__@VegBIEN__.public@vegpath.org",
         "plot.**"."eventDate" AS "eventDate__@DwC__@vegpath.org",
         "plot.**"."elevationInMeters" AS "(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
         "plot.**"."slopeAspect" AS "slopeAspect[_deg]__@VegX__.plot@vegpath.org",
         "plot.**"."slopeGradient" AS "slopeGradient[_deg]__@VegX__.plot@vegpath.org",
         "plot.**"."plotArea_ha" AS "area[_ha]__@VegX__.plot@vegpath.org",
         "plot.**"."samplingProtocol" AS "samplingProtocol__@DwC__@vegpath.org",
         "plot.**"."temperature_C" AS "temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
         "plot.**".precipitation_m AS "precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
         "plot.**".stratum__name AS "stratumName__@VegX__.stratum@vegpath.org",
         "plot.**".communities AS "communityConcept.name__@VegX__.communityDet@vegpath.org",
         "plot.**".plot__collectors AS "observationContributor[s]__@VegBank__@vegpath.org",
         sourcelist.name AS "[custodial_]institutionCode[s]__@DwC__@vegpath.org",
         specimenreplicate.collectioncode_dwc AS "collectionCode__@DwC__@vegpath.org",
         specimenreplicate.catalognumber_dwc AS "catalogNumber__@DwC__@vegpath.org",
         specimenreplicate.sourceaccessioncode AS "occurrenceID__@DwC__@vegpath.org",
         collector.fullname AS "recordedBy__@DwC__@vegpath.org",
         plantobservation.authorplantcode AS "recordNumber__@DwC__@vegpath.org",
         COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org",
         taxonverbatim.family AS "[verbatim_]family__@DwC__@vegpath.org",
         COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS "[verbatim_]scientificName__@DwC__@vegpath.org",
         identifiedby.fullname AS "identifiedBy__@DwC__@vegpath.org",
         taxondetermination.determinationdate AS "dateIdentified__@DwC__@vegpath.org",
         taxondetermination.notes AS "identificationRemarks__@DwC__@vegpath.org",
         taxon_scrub."matchedFamily" AS "Family_matched__@TNRS__@vegpath.org",
         taxon_scrub."matchedTaxonName" AS "Name_matched__@TNRS__@vegpath.org",
         taxon_scrub."matchedScientificNameAuthorship" AS "Name_matched_author__@TNRS__@vegpath.org",
         family_higher_plant_group.higher_plant_group AS "[higher_plant_group~]higherClassification__@DwC__@vegpath.org",
         taxon_scrub."taxonomicStatus" AS "taxonomicStatus__@DwC__@vegpath.org",
         taxon_scrub.scrubbed_family,
         taxon_scrub.scrubbed_genus,
         taxon_scrub.scrubbed_specific_epithet,
         ((taxon_scrub.scrubbed_genus || ' '::text) || taxon_scrub.scrubbed_specific_epithet) AS scrubbed_species_binomial,
         taxon_scrub.scrubbed_taxon_name_no_author,
         taxon_scrub.scrubbed_author,
         taxon_scrub.scrubbed_taxon_name_with_author,
         taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies__@VegCore__@vegpath.org",
         taxonoccurrence.growthform AS "[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org",
         plantobservation.reproductivecondition AS "reproductiveCondition__@DwC__@vegpath.org",
         ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien,
         (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien,
             CASE
                 WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis
                 WHEN ("plot.**".location__cultivated__bien IS NOT NULL) THEN NULL::text
                 ELSE NULL::text
             END AS "cultivatedBasis_bien",
         aggregateoccurrence.notes AS "occurrenceRemarks__@DwC__@vegpath.org",
         _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent__@VegX__.attribute.ordinal@vegpath.org",
         _m_to_cm(stemobservation.diameterbreastheight_m) AS "stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org",
         stemobservation.height_m AS "stemHeight[_m]__@VegBank__.stemCount@vegpath.org",
         stemobservation.tag AS "[tag=]identificationLabel__@VegX__.individual@vegpath.org",
         stemobservation.xposition_m AS "relativeX[_m]__@VegX__.individualOrganismObs@vegpath.org",
         stemobservation.yposition_m AS "relativeY[_m]__@VegX__.individualOrganismObs@vegpath.org",
         taxonoccurrence.sourceaccessioncode AS "taxonObservation[.id]__@VegBank__@vegpath.org",
         taxonoccurrence.authortaxoncode AS "authorTaxonCode",
         aggregateoccurrence.sourceaccessioncode AS "aggregateOrganismObservation.id__@VegX__@vegpath.org",
         plantobservation.sourceaccessioncode AS "individualOrganismObservation.id__@VegX__@vegpath.org",
         plantobservation.authorplantcode AS "individualID__@DwC__@vegpath.org",
         aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org",
             END AS "cultivatedBasis_bien",
         aggregateoccurrence.notes AS "occurrenceRemarks__@DwC__@vegpath.org",
         _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent__@VegX__.attribute.ordinal@vegpath.org",
         _m_to_cm(stemobservation.diameterbreastheight_m) AS "stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org",
         stemobservation.height_m AS "stemHeight[_m]__@VegBank__.stemCount@vegpath.org",
         stemobservation.tag AS "[tag=]identificationLabel__@VegX__.individual@vegpath.org",
         stemobservation.xposition_m AS "relativeX[_m]__@VegX__.individualOrganismObs@vegpath.org",
         stemobservation.yposition_m AS "relativeY[_m]__@VegX__.individualOrganismObs@vegpath.org",
         taxonoccurrence.sourceaccessioncode AS "taxonObservation[.id]__@VegBank__@vegpath.org",
         taxonoccurrence.authortaxoncode AS "authorTaxonCode",
         aggregateoccurrence.sourceaccessioncode AS "aggregateOrganismObservation.id__@VegX__@vegpath.org",
         plantobservation.sourceaccessioncode AS "individualOrganismObservation.id__@VegX__@vegpath.org",
         plantobservation.authorplantcode AS "individualID__@DwC__@vegpath.org",
         aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org",
         stemobservation.authorstemcode AS "stemCode__@VegBank__.stemLocation@vegpath.org"
        FROM ((((((((((((((("plot.**"
        LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**".locationevent__pkey)))
-...
     --
     CREATE VIEW geoscrub_input_new AS
      SELECT geoscrub_input.country,
         geoscrub_input."stateProvince",
         geoscrub_input.county,
         geoscrub_input."decimalLatitude",
      SELECT geoscrub_input.country,
         geoscrub_input."stateProvince",
         geoscrub_input.county,
         geoscrub_input."decimalLatitude",
         geoscrub_input."decimalLongitude"
        FROM (geoscrub_input
        LEFT JOIN geoscrub.geoscrub_output ON ((((((geoscrub_output."decimalLatitude" = geoscrub_input."decimalLatitude") AND (geoscrub_output."decimalLongitude" = geoscrub_input."decimalLongitude")) AND (geoscrub_output.country = geoscrub_input.country)) AND (geoscrub_output."stateProvince" = geoscrub_input."stateProvince")) AND (geoscrub_output.county = geoscrub_input.county))))
-...
     --
     CREATE VIEW geoscrub_input_view AS
      SELECT DISTINCT place.country,
         place.stateprovince AS "stateProvince",
         place.county,
         coordinates.latitude_deg AS "decimalLatitude",
      SELECT DISTINCT place.country,
         place.stateprovince AS "stateProvince",
         place.county,
         coordinates.latitude_deg AS "decimalLatitude",
         coordinates.longitude_deg AS "decimalLongitude"
        FROM (place
        JOIN coordinates USING (coordinates_id))
-...
     --
     CREATE VIEW place_visit AS
      SELECT locationevent.locationevent_id,
         locationevent.source_id,
         locationevent.sourceaccessioncode,
         locationevent.accesslevel,
         locationevent.place_visit_id,
         locationevent.parent_id,
         locationevent.location_id,
         locationevent.project_id,
         locationevent.stratum_id,
         locationevent.authoreventcode,
         locationevent.previous_id,
         locationevent.obsstartdate,
         locationevent.obsenddate,
         locationevent.dateaccuracy,
         locationevent.method_id,
         locationevent.temperature_c,
         locationevent.precipitation_m,
         locationevent.autotaxoncover,
         locationevent.originaldata,
         locationevent.effortlevel,
         locationevent.floristicquality,
         locationevent.bryophytequality,
         locationevent.lichenquality,
         locationevent.locationeventnarrative,
         locationevent.landscapenarrative,
         locationevent.homogeneity,
         locationevent.phenologicaspect,
         locationevent.representativeness,
         locationevent.standmaturity,
         locationevent.successionalstatus,
         locationevent.basalarea,
         locationevent.hydrologicregime,
         locationevent.soilmoistureregime,
         locationevent.soildrainage,
         locationevent.watersalinity,
         locationevent.waterdepth_m,
         locationevent.shoredistance,
         locationevent.soildepth,
         locationevent.organicdepth,
         locationevent.soiltaxon_id,
         locationevent.soiltaxonsrc,
         locationevent.percentbedrock,
         locationevent.percentrockgravel,
         locationevent.percentwood,
         locationevent.percentlitter,
         locationevent.percentbaresoil,
         locationevent.percentwater,
         locationevent.percentother,
         locationevent.nameother,
         locationevent.treeht,
         locationevent.shrubht,
         locationevent.fieldht,
         locationevent.nonvascularht,
         locationevent.submergedht,
         locationevent.treecover,
         locationevent.shrubcover,
         locationevent.fieldcover,
         locationevent.nonvascularcover,
         locationevent.floatingcover,
         locationevent.submergedcover,
         locationevent.dominantstratum,
         locationevent.growthform1type,
         locationevent.growthform2type,
         locationevent.growthform3type,
         locationevent.growthform1cover,
         locationevent.growthform2cover,
         locationevent.growthform3cover,
         locationevent.totalcover,
         locationevent.notespublic,
         locationevent.notesmgt,
         locationevent.revisions,
         locationevent.dateentered,
         locationevent.toptaxon1name,
         locationevent.toptaxon2name,
         locationevent.toptaxon3name,
         locationevent.toptaxon4name,
         locationevent.toptaxon5name,
      SELECT locationevent.locationevent_id,
         locationevent.source_id,
         locationevent.sourceaccessioncode,
         locationevent.accesslevel,
         locationevent.place_visit_id,
         locationevent.parent_id,
         locationevent.location_id,
         locationevent.project_id,
         locationevent.stratum_id,
         locationevent.authoreventcode,
         locationevent.previous_id,
         locationevent.obsstartdate,
         locationevent.obsenddate,
         locationevent.dateaccuracy,
         locationevent.method_id,
         locationevent.temperature_c,
         locationevent.precipitation_m,
         locationevent.autotaxoncover,
         locationevent.originaldata,
         locationevent.effortlevel,
         locationevent.floristicquality,
         locationevent.bryophytequality,
         locationevent.lichenquality,
         locationevent.locationeventnarrative,
         locationevent.landscapenarrative,
         locationevent.homogeneity,
         locationevent.phenologicaspect,
         locationevent.representativeness,
         locationevent.standmaturity,
         locationevent.successionalstatus,
         locationevent.basalarea,
         locationevent.hydrologicregime,
         locationevent.soilmoistureregime,
         locationevent.soildrainage,
         locationevent.watersalinity,
         locationevent.waterdepth_m,
         locationevent.shoredistance,
         locationevent.soildepth,
         locationevent.organicdepth,
         locationevent.soiltaxon_id,
         locationevent.soiltaxonsrc,
         locationevent.percentbedrock,
         locationevent.percentrockgravel,
         locationevent.percentwood,
         locationevent.percentlitter,
         locationevent.percentbaresoil,
         locationevent.percentwater,
         locationevent.percentother,
         locationevent.nameother,
         locationevent.treeht,
         locationevent.shrubht,
         locationevent.fieldht,
         locationevent.nonvascularht,
         locationevent.submergedht,
         locationevent.treecover,
         locationevent.shrubcover,
         locationevent.fieldcover,
         locationevent.nonvascularcover,
         locationevent.floatingcover,
         locationevent.submergedcover,
         locationevent.dominantstratum,
         locationevent.growthform1type,
         locationevent.growthform2type,
         locationevent.growthform3type,
         locationevent.growthform1cover,
         locationevent.growthform2cover,
         locationevent.growthform3cover,
         locationevent.totalcover,
         locationevent.notespublic,
         locationevent.notesmgt,
         locationevent.revisions,
         locationevent.dateentered,
         locationevent.toptaxon1name,
         locationevent.toptaxon2name,
         locationevent.toptaxon3name,
         locationevent.toptaxon4name,
         locationevent.toptaxon5name,
         locationevent.numberoftaxa
        FROM locationevent
       WHERE (locationevent.parent_id IS NULL);
-...
     --
     CREATE VIEW plot AS
      SELECT location.location_id AS plot_id,
         location.source_id,
         location.sourceaccessioncode,
         location.plot_location_id AS plot,
         location.parent_id,
         location.authorlocationcode,
         location.place_id,
         location.accesslevel,
         location.accessconditions,
         location.sublocationxposition_m,
         location.sublocationyposition_m,
         location.iscultivated,
         location.authorzone,
         location.authordatum,
         location.authorlocation,
         location.locationnarrative,
         location.azimuth,
         location.shape,
         location.area_m2,
         location.standsize,
         location.placementmethod,
         location.permanence,
         location.layoutnarrative,
         location.elevation_m,
         location.elevationaccuracy_m,
         location.elevationrange_m,
         location.verbatimelevation,
         location.slopeaspect_deg,
         location.minslopeaspect_deg,
         location.maxslopeaspect_deg,
         location.slopegradient_fraction,
         location.minslopegradient_fraction,
         location.maxslopegradient_fraction,
         location.topoposition,
         location.landform,
         location.surficialdeposits,
         location.rocktype,
         location.submitter_surname,
         location.submitter_givenname,
         location.submitter_email,
         location.notespublic,
         location.notesmgt,
         location.revisions,
         location.dateentered,
      SELECT location.location_id AS plot_id,
         location.source_id,
         location.sourceaccessioncode,
         location.plot_location_id AS plot,
         location.parent_id,
         location.authorlocationcode,
         location.place_id,
         location.accesslevel,
         location.accessconditions,
         location.sublocationxposition_m,
         location.sublocationyposition_m,
         location.iscultivated,
         location.authorzone,
         location.authordatum,
         location.authorlocation,
         location.locationnarrative,
         location.azimuth,
         location.shape,
         location.area_m2,
         location.standsize,
         location.placementmethod,
         location.permanence,
         location.layoutnarrative,
         location.elevation_m,
         location.elevationaccuracy_m,
         location.elevationrange_m,
         location.verbatimelevation,
         location.slopeaspect_deg,
         location.minslopeaspect_deg,
         location.maxslopeaspect_deg,
         location.slopegradient_fraction,
         location.minslopegradient_fraction,
         location.maxslopegradient_fraction,
         location.topoposition,
         location.landform,
         location.surficialdeposits,
         location.rocktype,
         location.submitter_surname,
         location.submitter_givenname,
         location.submitter_email,
         location.notespublic,
         location.notesmgt,
         location.revisions,
         location.dateentered,
         location.locationrationalenarrative
        FROM location
       WHERE (location.parent_id IS NULL);
-...
     --
     CREATE VIEW provider_count_view AS
             ( SELECT s.dataprovider,
                 s.dataset,
                 s.records,
                 s.sourcetype,
             ( SELECT s.dataprovider,
                 s.dataset,
                 s.records,
                 s.sourcetype,
                 s.observationtype
                FROM ( SELECT source.shortname AS dataprovider,
                         '(total)'::text AS dataset,
                FROM ( SELECT source.shortname AS dataprovider,
                         '(total)'::text AS dataset,
                         ( SELECT count(*) AS count
                                FROM taxonoccurrence
                               WHERE (taxonoccurrence.source_id = source.source_id)) AS records,
                         source.sourcetype,
                               WHERE (taxonoccurrence.source_id = source.source_id)) AS records,
                         source.sourcetype,
                         source.observationtype
                        FROM source
                       WHERE (EXISTS ( SELECT NULL::unknown AS unknown
-...
                               WHERE (taxonoccurrence.source_id = source.source_id)
                              LIMIT 1))) s
               ORDER BY s.records DESC)
     UNION ALL
             ( SELECT dataprovider.shortname AS dataprovider,
                 s.dataset,
                 s.records,
                 dataset.sourcetype,
     UNION ALL
             ( SELECT dataprovider.shortname AS dataprovider,
                 s.dataset,
                 s.records,
                 dataset.sourcetype,
                 COALESCE(dataset.observationtype, dataprovider.observationtype) AS observationtype
                FROM ((( SELECT specimenreplicate.source_id AS dataprovider_id,
                         sourcename.name AS dataset,
                         sourcename.matched_source_id AS dataset_id,
                FROM ((( SELECT specimenreplicate.source_id AS dataprovider_id,
                         sourcename.name AS dataset,
                         sourcename.matched_source_id AS dataset_id,
                         count(*) AS records
                        FROM ((specimenreplicate
                   JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)))
-...
     --
     CREATE VIEW range_modeling_input AS
      SELECT analytical_stem.datasource AS source,
         analytical_stem."specimenHolderInstitutions" AS first_publisher,
         analytical_stem."decimalLatitude" AS latitude_deg,
         analytical_stem."decimalLongitude" AS longitude_deg,
         analytical_stem.geovalid_bien AS geovalid,
         analytical_stem.scrubbed_morphospecies_binomial AS species_taxon_name,
      SELECT analytical_stem.datasource AS source,
         analytical_stem."specimenHolderInstitutions" AS first_publisher,
         analytical_stem."decimalLatitude" AS latitude_deg,
         analytical_stem."decimalLongitude" AS longitude_deg,
         analytical_stem.geovalid_bien AS geovalid,
         analytical_stem.scrubbed_morphospecies_binomial AS species_taxon_name,
         analytical_stem."higherPlantGroup_bien" AS higher_plant_group
        FROM analytical_stem
       WHERE (analytical_stem."higherPlantGroup_bien" IS NOT NULL);
-...
     --
     CREATE VIEW taxon_trait_view AS
      SELECT taxon_scrub.scrubbed_taxon_name_no_author AS "scientificName",
         trait.name AS "measurementType",
         trait.value AS "measurementValue",
      SELECT taxon_scrub.scrubbed_taxon_name_no_author AS "scientificName",
         trait.name AS "measurementType",
         trait.value AS "measurementValue",
         trait.units AS "measurementUnit"
        FROM (((((trait
        LEFT JOIN taxonoccurrence USING (taxonoccurrence_id))
-...
     --
     CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
      SELECT p.projectname AS project_name,
      SELECT p.projectname AS project_name,
         count(DISTINCT l.plot_id) AS plots
        FROM (((public.plot l
        JOIN public.locationevent le ON ((l.plot_id = le.location_id)))
-...
     --
     CREATE VIEW _plots_05_list_of_plot_codes_by_project AS
      SELECT p.projectname AS project_name,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code
        FROM (((public.plot l
        JOIN public.locationevent le ON ((l.plot_id = le.location_id)))
-...
     --
     CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS
      SELECT DISTINCT project.projectname AS project_name,
      SELECT DISTINCT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code
        FROM ((public.plot
        JOIN public.locationevent USING (plot_id))
-...
     --
     CREATE VIEW _plots_06a_list_of_stems AS
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code,
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code,
         stemobservation.sourceaccessioncode AS stem_id
        FROM ((((((public.plot
        JOIN public.locationevent USING (plot_id))
-...
     --
     CREATE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS
      SELECT DISTINCT project.projectname AS project_name,
      SELECT DISTINCT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code
        FROM ((public.plot
        JOIN public.locationevent USING (plot_id))
-...
     --
     CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
      SELECT project.projectname AS project_name,
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code
        FROM ((public.plot
        JOIN public.locationevent USING (plot_id))
-...
     --
     CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
      SELECT DISTINCT project.projectname AS project_name,
      SELECT DISTINCT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code
        FROM ((public.plot
        JOIN public.locationevent USING (plot_id))
-...
     --
     CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         sum(ao.count) AS individuals
        FROM ((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_10a_aggregate_observation_individual_counts AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         o.sourceaccessioncode AS individual_id,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         o.sourceaccessioncode AS individual_id,
         ao.count AS individuals
        FROM ((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         count(DISTINCT so.stemobservation_id) AS stems
        FROM ((((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         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
        FROM (((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon
        FROM (((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
         sum(ao.count) AS individuals
        FROM ((((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
         sum(ci.coverpercent) AS totalpercentcover
        FROM (((((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
         sum(ao.linecover_m) AS intercept_cm
        FROM ((((((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         count(DISTINCT sub_locationevent.locationevent_id) AS subplots
        FROM ((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         sub_locationevent.authoreventcode AS subplot_code
        FROM ((((public.project p
        JOIN public.source s ON ((p.source_id = s.source_id)))
-...
     --
     CREATE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code,
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code,
         count(*) AS inventories
        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)
        JOIN public.locationevent USING (location_id))
-...
     --
     CREATE VIEW _plots_20_tnrs_names AS
      SELECT taxonverbatim.taxonomicname AS verbatim_name_with_author,
         taxonlabel.taxonomicname AS tnrs_input_name,
         taxon_scrub."taxonomicStatus" AS tnrs_taxonomic_status,
         taxon_scrub.accepted_family AS tnrs_accepted_name_family,
         taxon_scrub.accepted_taxon_name_no_author AS tnrs_accepted_name,
         taxon_scrub.accepted_author AS tnrs_accepted_name_author,
      SELECT taxonverbatim.taxonomicname AS verbatim_name_with_author,
         taxonlabel.taxonomicname AS tnrs_input_name,
         taxon_scrub."taxonomicStatus" AS tnrs_taxonomic_status,
         taxon_scrub.accepted_family AS tnrs_accepted_name_family,
         taxon_scrub.accepted_taxon_name_no_author AS tnrs_accepted_name,
         taxon_scrub.accepted_author AS tnrs_accepted_name_author,
         taxon_scrub.accepted_morphospecies_binomial AS taxon_morphospecies
        FROM ((public.taxonverbatim
        JOIN public.taxonlabel USING (taxonlabel_id))
-...
     CREATE VIEW _specimens_10_count_number_of_records_by_institution AS
      SELECT ( SELECT sourcelist.name
                FROM public.sourcelist
               WHERE (sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)) AS specimen_duplicate_institutions,
               WHERE (sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)) AS specimen_duplicate_institutions,
         count(*) AS records
        FROM public.specimenreplicate
       WHERE (specimenreplicate.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
-...
     --
     CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS
      SELECT DISTINCT place.country,
         place.stateprovince AS "stateProvince",
      SELECT DISTINCT place.country,
         place.stateprovince AS "stateProvince",
         place.county
        FROM public.place
       WHERE (place.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
-...
     CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
      SELECT ( SELECT party.fullname
                FROM public.party
               WHERE (party.party_id = taxonoccurrence.collector_id)) AS "collectorName",
         specimenreplicate.collectionnumber AS "collectionNumber",
         (aggregateoccurrence.collectiondate)::text AS "dateCollected",
               WHERE (party.party_id = taxonoccurrence.collector_id)) AS "collectorName",
         specimenreplicate.collectionnumber AS "collectionNumber",
         (aggregateoccurrence.collectiondate)::text AS "dateCollected",
         count(*) AS "specimenRecords"
        FROM (((public.taxonoccurrence
        JOIN public.aggregateoccurrence USING (taxonoccurrence_id))
-...
     CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
      SELECT ( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude, coordinates.coordsaccuracy_m)) AS count
                FROM public.coordinates
               WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)) OR ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL))))) AS "allLatLongs",
               WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)) OR ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL))))) AS "allLatLongs",
         ( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude, coordinates.coordsaccuracy_m)) AS count
                FROM public.coordinates
               WHERE (((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLatLongs";
-...
     --
     CREATE VIEW _specimens_13a_list_of_all_verbatim_lat_long AS
      SELECT DISTINCT COALESCE(coordinates.verbatimlatitude, (coordinates.latitude_deg)::text) AS "verbatimLatitude",
      SELECT DISTINCT COALESCE(coordinates.verbatimlatitude, (coordinates.latitude_deg)::text) AS "verbatimLatitude",
         COALESCE(coordinates.verbatimlatitude, (coordinates.longitude_deg)::text) AS "verbatimLongitude"
        FROM public.coordinates
       WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)) OR ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL))));
-...
     --
     CREATE VIEW _specimens_13b_list_of_all_decimal_lat_long AS

... This diff was truncated because it exceeds the maximum size that can be displayed.

Also available in: Unified diff

Project

General

Profile

Revision 13800

Added by Aaron Marcuse-Kubitza over 10 years ago