create_species_lookup: Pair NESPP3, SVSPP3, Species_itis

In andybeet/dbutils: Utility package for db connectivity and functionality

Pair NESPP3, SVSPP3, Species_itis

Description

Create a table with all species information across databases. Combine NESPP3, SVSPP3, species_itis, common name and scientific name.

Usage

create_species_lookup(
  channel,
  species = NULL,
  speciesType = "NESPP3",
  skipType = NULL
)

Arguments

channel	an Object inherited from DBIConnection-class. This object is used to connect to communicate with the database engine. (see connect_to_database)
species	Character String or numeric. Either NESPP3, SVSPP, Species_itis codes
speciesType	Character string. Type of species code entered for species. Either NESPP3, SVSPP, Species_itis (not case sensitive). Default = "NESPP3"
skipType	Character string. species Type to ignore in join. For example maybe only want to join SVSPP with ITIS. Same as speciesType (Default = NULL, all three codes are joined)

Value

A list is returned containing the result of the data pull as well as the sql statements used in the data pulls and a list of missing species codes, names, that were not present:

data	Tibble. Each row represents a species with columns NESPP3, NAFOSPP, SVSPPcf, SVSPPsv, COMMON_NAME, COMNAME, SCIENTIFIC_NAME, SPECIES_ITIS
sql1	Character string. sql statement used in first table
sql2	Character string. sql statement used in second table
sql3	Character string. sql statement used in third table
missing	List. Codes not found in respective tables
lookupOrder	Character string. The order in which the tables were accessed. Compare with the sqlx output

Database tables used

Three tables are used to compile information:

cfdbs.cfspp and cfdbs.species_itis_ne and svdbs.svspecies_list. The resulting tables are then joined.

The order in which these tables are accessed depends on speciesType. For example, speciesType = "NESPP3"

1. cfdbs.cfssp is accessed and NESPP3, NESPP4, NAFOSPP, SVSPP are pulled.

2. cfdbs.species_itis_ne is accessed using the NESPP4 codes found in step 1 and COMMON_NAME, SCIENTIFIC_NAME, SPECIES_ITIS, NESPP4 are pulled.

3. svdbs.spsvspecies_list is accessed using the SCIENTIFIC_NAME codes found in step2 and COMNAME, SCINAME, SVSPP are pulled.

4. The tables are then joined

andybeet/dbutils documentation built on March 15, 2024, 4:43 p.m.