1 Background

This script updates the taxa table in the calcofi database to the latest valid taxonomic codes.

2 Update to latest columns

Code 
librarian::shelf(
  DBI, dplyr, glue, here, ritis, taxadb)

source(here("../apps_dev/libs/db.R")) # con: database connection

3 Show Entity Relationship Diagram (ERD)

---
title: Order example
---
erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE-ITEM : contains
    CUSTOMER }|..|{ DELIVERY-ADDRESS : uses


erDiagram
tctd_bottles{ntt~integer~ cast_countntt~integer~ btl_cntntt~character varying(20)~ sta_idntt~character varying(50)~ depth_idntt~numeric~ t_degcntt~numeric~ salinityntt~numeric~ o2ml_lntt~numeric~ sthetantt~numeric~ o2satntt~numeric~ oxy_umolperkgntt~integer~ btlnumntt~integer~ recindntt~integer~ t_precntt~integer~ t_qualntt~integer~ s_precntt~integer~ s_qualntt~integer~ p_qualntt~integer~ o_qualntt~integer~ sthtaqntt~integer~ o2satqntt~numeric~ chlorantt~integer~ chlquantt~numeric~ phaeopntt~integer~ phaquantt~numeric~ po4umntt~integer~ po4qntt~numeric~ sio3umntt~integer~ sio3quntt~numeric~ no2umntt~integer~ no2qntt~numeric~ no3umntt~integer~ no3qntt~numeric~ nh3umntt~integer~ nh3qntt~numeric~ c14as1ntt~integer~ c14a1pntt~integer~ c14a1qntt~numeric~ c14as2ntt~integer~ c14a2pntt~integer~ c14a2qntt~numeric~ darkasntt~integer~ darkapntt~integer~ darkaqntt~numeric~ meanasntt~integer~ meanapntt~numeric~ meanaqntt~interval~ inctimntt~numeric~ lightpntt~numeric~ r_depthntt~numeric~ r_tempntt~numeric~ r_salntt~numeric~ r_dynhtntt~numeric~ r_nutsntt~numeric~ r_oxy_umolkgntt~numeric~ dic1ntt~numeric~ dic2ntt~numeric~ ta1ntt~numeric~ ta2ntt~numeric~ ph1ntt~numeric~ ph2ntt~character varying(500)~ dic_q_commentntt~numeric~ depth_mn}
tstations_old{ntt~numeric~ longitudentt~numeric~ latitudentt~numeric~ linentt~numeric~ stationntt~geometry(Point,4326)~ geomntt~character varying(10)~ cruisentt~character varying(5)~ shipntt~integer~ orderoccn}
tlarvae_counts_backup{ntt~integer~ netidntt~integer~ spccodentt~integer~ tallyn}
tdatasets{ntt~text~ labelntt~text~ titlentt~text~ abstractntt~date~ startdatentt~date~ enddatentt~integer~ idn}
tdataset_methods{ntt~integer~ dataset_idntt~integer~ method_idntt~integer~ idn}
tidw_stats{ntt~text~ hashntt~text~ ply_geontt~text~ rast_tifntt~json~ argsntt~timestamp with time zone~ dtime_createdntt~timestamp with time zone~ dtime_last_viewedntt~timestamp with time zone~ dtime_last_downloadedntt~integer~ n_views_plyntt~integer~ n_views_rastntt~integer~ n_downloads_plyntt~integer~ n_downloads_rastn}
ttows_old{ntt~numeric~ shfntt~numeric~ volsampledntt~numeric~ percsortedntt~timestamp without time zone~ datetimentt~character varying(10)~ cruisentt~date~ datentt~character varying(10)~ shipntt~integer~ orderoccntt~character varying(5)~ towtypentt~integer~ townumntt~character varying(5)~ netlocn}
tlarvae_counts_old{ntt~integer~ countntt~character varying(10)~ cruisentt~character varying(10)~ shipntt~integer~ orderoccntt~character varying(5)~ towtypentt~integer~ townumntt~character varying(5)~ netlocntt~integer~ spccoden}
ttows{ntt~integer~ towidntt~integer~ stationidntt~character varying(5)~ towtypentt~integer~ townumberntt~timestamp with time zone~ starttimen}
tfield_labels{ntt~boolean~ activentt~text~ categoryntt~text~ table_fieldntt~text~ plot_titlentt~text~ plot_labelntt~text~ plot_colorntt~text~ color_paletten}
tnetcastdict{ntt~integer~ netidntt~integer~ cast_countn}
tdataset_tables{ntt~integer~ dataset_idntt~text~ table_namentt~text~ table_descriptionntt~integer~ idn}
tmethods{ntt~text~ titlentt~text~ descriptionntt~text~ instrumentation1ntt~text~ instrumentation2ntt~text~ instrumentation3ntt~integer~ idn}
tegg_counts_backup{ntt~integer~ netidntt~integer~ spccodentt~integer~ tallyn}
tgrd_mer{ntt~raster~ rastntt~integer~ ridn}
teffort_zones{ntt~text~ zone_keyntt~text~ sta_patternntt~text~ sta_shorentt~integer~ sta_dposntt~integer~ sta_lin_minntt~integer~ sta_lin_maxntt~integer~ sta_pos_minntt~integer~ sta_pos_maxntt~geometry~ geomn}
tctd_dic{ntt~integer~ idntt~integer~ cast_indexntt~integer~ bottle_indexntt~character varying(10)~ cruisentt~character varying(15)~ line_sta_idntt~character varying(50)~ depth_idntt~numeric~ dic1ntt~numeric~ dic2ntt~numeric~ ta1ntt~numeric~ ta2ntt~numeric~ ph1ntt~numeric~ ph2ntt~numeric~ salinity1ntt~numeric~ salinity2ntt~numeric~ temp_degcntt~numeric~ btlsalinityntt~numeric~ btlo2_ml_per_lntt~numeric~ btlo2_umol_per_kgntt~numeric~ sigma_thetantt~numeric~ dic_btl_id1ntt~numeric~ dic_btl_id2ntt~character varying(200)~ dic_quality_commentntt~numeric~ depth_mn}
tdataset_keywords{ntt~integer~ dataset_idntt~integer~ keyword_idntt~integer~ idn}
tkrill_abundances{ntt~character varying(45)~ cruisentt~character varying(50)~ shipntt~character varying(5)~ regionntt~character varying(15)~ stationntt~character varying(15)~ linentt~numeric~ maxdepthntt~numeric~ latitudentt~numeric~ longitudentt~timestamp without time zone~ towbeginntt~timestamp without time zone~ towendntt~smallint~ daytowntt~character varying(20)~ phasentt~character varying(20)~ stagentt~numeric~ abundance_m2ntt~integer~ spccoden}
ttsn_list{ntt~character varying(10)~ tsnntt~character varying(20)~ rankntt~character varying(50)~ namentt~integer~ idn}
tegg_counts{ntt~integer~ netidntt~integer~ spccodentt~integer~ tallyn}
tspecies_codes_new{ntt~integer~ spccodentt~character varying(50)~ scientific_namentt~character varying(10)~ itis_tsnntt~character varying(50)~ common_namentt~character varying(20)~ taxon_rankntt~integer~ idn}
ttow_types{ntt~character varying(5)~ codentt~character varying(100)~ descriptionn}
tctd_profiles{ntt~integer~ cast_countntt~integer~ cast_cntntt~numeric~ event_numntt~character varying~ projectntt~character varying~ studyntt~character varying~ ord_occntt~character varying~ cast_idntt~timestamp without time zone~ date_time_utcntt~timestamp without time zone~ date_time_pstntt~numeric~ lat_decntt~numeric~ lon_decntt~character varying~ sta_idntt~numeric~ linentt~numeric~ stantt~numeric~ depthntt~numeric~ pressurentt~character varying~ prqntt~numeric~ temp1ntt~character varying~ temp1qntt~numeric~ temp2ntt~character varying~ temp2qntt~numeric~ tempaventt~numeric~ salt1ntt~character varying~ salt1qntt~numeric~ salt1_corrntt~numeric~ salt2ntt~character varying~ salt2qntt~numeric~ salt2_corrntt~numeric~ saltave_corrntt~numeric~ ox1ntt~character varying~ ox1qntt~numeric~ ox1_cruisecorrntt~numeric~ ox1_stacorrntt~numeric~ ox2ntt~character varying~ ox2qntt~numeric~ ox2_cruisecorrntt~numeric~ ox2_stacorrntt~numeric~ oxave_stacorrntt~numeric~ ox1umntt~numeric~ ox1um_cruisecorrntt~numeric~ ox1um_stacorrntt~numeric~ ox2umntt~numeric~ ox2um_cruisecorrntt~numeric~ ox2um_stacorrntt~numeric~ oxaveum_stacorrntt~numeric~ fluorvntt~character varying~ fluorqntt~numeric~ estchl_cruisecorrntt~numeric~ estchl_stacorrntt~numeric~ isusvntt~character varying~ isusqntt~numeric~ estno3_cruisecorrntt~numeric~ estno3_stacorrntt~numeric~ sigthetats1ntt~character varying~ sigthetats1qntt~numeric~ sigthetats2ntt~character varying~ sigthetats2qntt~numeric~ batntt~numeric~ xmissntt~character varying~ transqntt~numeric~ phntt~character varying~ phqntt~numeric~ sparntt~character varying~ sparqntt~numeric~ parntt~character varying~ parqntt~numeric~ pot1ntt~numeric~ pot2ntt~numeric~ dynhtntt~numeric~ svantt~numeric~ oxsat1ntt~numeric~ oxsat2ntt~numeric~ btl_depthntt~numeric~ btl_tempntt~numeric~ saltbntt~numeric~ oxbntt~numeric~ chl_antt~numeric~ no3ntt~numeric~ no2ntt~numeric~ nh4ntt~numeric~ po4ntt~numeric~ silntt~character varying~ oxbumntt~character varying~ phaeon}
tr_sta_cnt{ntt~text[]~ band_namesntt~character varying~ r_classntt~character varying~ r_proj4ntt~raster~ rastntt~integer~ ridn}
tctd_casts{ntt~integer~ cast_countntt~character varying(20)~ cruiseidntt~character varying(10)~ cruisentt~character varying(20)~ cruz_stantt~character varying(10)~ dbsta_idntt~character varying(50)~ castidntt~character varying(15)~ sta_idntt~integer~ quarterntt~character varying(5)~ sta_codentt~numeric~ distancentt~date~ datentt~integer~ yearntt~integer~ monthntt~integer~ juliandatentt~integer~ juliandayntt~time without time zone~ timentt~numeric~ latitudentt~integer~ latdegntt~numeric~ latminntt~character(1)~ lathemntt~numeric~ longitudentt~integer~ londegntt~numeric~ lonminntt~character(1)~ lonhemntt~numeric~ rptlinentt~numeric~ stlinentt~numeric~ aclinentt~numeric~ rptstantt~numeric~ ststantt~numeric~ acstantt~numeric~ bottomdepthntt~integer~ secchintt~integer~ foreiuntt~character varying(50)~ shipnamentt~character varying(10)~ shipcodentt~character varying(5)~ datatypentt~integer~ orderoccntt~integer~ eventnumntt~integer~ cruzlegntt~character varying(15)~ origstaidntt~character varying(10)~ dataorntt~character(10)~ cruznumntt~numeric~ intchlntt~numeric~ intc14ntt~time without time zone~ incstrntt~time without time zone~ incendntt~time without time zone~ pstlanntt~time without time zone~ civiltntt~integer~ timezonentt~integer~ wavedirntt~numeric~ wavehtntt~integer~ waveprdntt~integer~ winddirntt~numeric~ windspdntt~numeric~ barometerntt~numeric~ drytntt~numeric~ wettntt~integer~ weantt~integer~ cloudtypentt~integer~ cloudamountntt~integer~ visibilityntt~geometry(Point,4326)~ geomntt~timestamp without time zone~ datetimentt~geometry(Point,3857)~ geom_3857n}
tstations_hull{ntt~geometry~ geomn}
tr_mer100km{ntt~raster~ rastntt~integer~ ridn}
tstations_order{ntt~double precision~ ORDER OCCntt~double precision~ LINEntt~double precision~ STAntt~double precision~ LAT (DEC)ntt~double precision~ LAT (DEG)ntt~double precision~ LAT (MIN)ntt~text~ LAT (DEG MIN)ntt~double precision~ LON (DEC)ntt~double precision~ LON (DEG)ntt~double precision~ LON (MIN)ntt~text~ LON (DEG MIN)ntt~double precision~ EST DEPTHntt~text~ STA TYPEn}
tcruises{ntt~integer~ cruise_idntt~character varying(10)~ cruise_ymdntt~character varying(5)~ shipn}
tspecies_codes{ntt~integer~ spccodentt~character varying(10)~ itis_tsnntt~character varying(50)~ common_namentt~character varying(50)~ scientific_namentt~character varying(20)~ taxon_rankntt~integer~ idn}
teffort_ctrs{ntt~text~ sta_keyntt~integer~ sta_linntt~integer~ sta_posntt~integer~ sta_dposntt~text~ sta_shorentt~text~ sta_patternntt~geometry~ geomntt~text~ zone_keyn}
teffort_grid{ntt~text~ sta_keyntt~integer~ sta_linntt~integer~ sta_posntt~integer~ sta_dposntt~text~ sta_shorentt~text~ sta_patternntt~geometry~ geomntt~text~ zone_keyn}
tnets{ntt~integer~ towidntt~character(1)~ netsidentt~numeric~ shfntt~numeric~ volsampledntt~numeric~ propsortedntt~integer~ netidn}
tgrd_gcs{ntt~integer~ ridntt~text~ namentt~raster~ rastn}
taoi_fed_sanctuaries{ntt~text~ sanctuaryntt~text~ nmsntt~geometry(Geometry,4326)~ geomn}
tpersons{ntt~integer~ typentt~text~ prefixntt~text~ firstntt~text~ middleinitntt~text~ lastntt~text~ organizationntt~text~ emailntt~text~ orcidntt~integer~ idn}
tdataset_persons{ntt~integer~ dataset_idntt~integer~ person_idntt~text~ rolentt~text~ descriptionntt~integer~ idn}
tnets_backup{ntt~integer~ towidntt~integer~ netidntt~character(1)~ netsidentt~numeric~ shfntt~numeric~ volsampledntt~numeric~ propsortedn}
teffort_areas{ntt~text~ area_keyntt~text~ sta_patternntt~text~ sta_shorentt~integer~ sta_dposntt~integer~ sta_lin_minntt~integer~ sta_lin_maxntt~integer~ sta_pos_minntt~integer~ sta_pos_maxntt~geometry~ geomn}
tlarvae_counts{ntt~integer~ netidntt~integer~ spccodentt~integer~ tallyn}
ttest_rast{ntt~raster~ rastntt~integer~ ridn}
tgrd_a{ntt~integer~ ridntt~raster~ rastn}
tscrippscast{ntt~character varying(10)~ cruisentt~character varying(5)~ shipntt~integer~ orderoccntt~integer~ castcntn}
ttmp_out{ntt~oid~ loidn}
tscrippscast_new{ntt~integer~ stationidntt~integer~ castcountn}
tships{ntt~character varying(5)~ shipcodentt~character varying(50)~ shipnamentt~character varying(10)~ shipnodcn}
tkeywords{ntt~text~ keywordntt~integer~ idn}
tstations{ntt~integer~ orderoccntt~numeric~ latitudentt~numeric~ longitudentt~integer~ stationidntt~numeric~ linentt~numeric~ stationntt~integer~ cruise_idntt~numeric~ gebco_depthntt~geometry(Point,4326)~ geomn}
tegg_counts_old{ntt~integer~ countntt~character varying(10)~ cruisentt~character varying(10)~ shipntt~integer~ orderoccntt~character varying(5)~ towtypentt~integer~ townumntt~character varying(5)~ netlocntt~integer~ spccoden}
ttaxa_hierarchy{ntt~character varying(10)~ parentntt~character varying(10)~ tsnntt~character varying(50)~ scientific_namentt~character varying(25)~ taxon_rankntt~character varying(100)~ lsidntt~ltree~ pathntt~integer~ idn}
tspatial_ref_sys{ntt~integer~ sridntt~character varying(256)~ auth_namentt~integer~ auth_sridntt~character varying(2048)~ srtextntt~character varying(2048)~ proj4textn}
tz_idw{ntt~text~ args_hashntt~json~ args_jsonntt~raster~ rastntt~integer~ ridn}
tplaces{ntt~text~ categoryntt~text~ keyntt~text~ namentt~geometry(Geometry,4326)~ geomn}
tspecies_groups{ntt~text~ spp_groupntt~double precision~ spccoden}
ttow_types_new{ntt~character varying(5)~ codentt~character varying(100)~ descriptionn}
larvae_counts }|..|| nets : lc_fk
egg_counts }|..|| nets : ec_fk
nets }|..|| tows : nt_fk
tows }|..|| stations : tst_fk
stations }|..|| cruises : stc_fk