Pair NESPP3, SVSPP3, Species_itis

Source: R/create_species_lookup.R

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

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