Se connecter à la base de donnée de campagne (PRESH)

#On vide les variables préexistantes
rm(list=ls())

#Je désigne le répertoire de mon fichier Rmd comme étant le répertoire de travail

setwd(".")

library(DBI)
library(odbc)
## Warning: package 'odbc' was built under R version 3.6.2
liaisons <- dbConnect(odbc::odbc(), .connection_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=./CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb")


dbListTables(liaisons)
##   [1] "public_biblio"                                    
##   [2] "public_biologie"                                  
##   [3] "public_campagne"                                  
##   [4] "public_capture"                                   
##   [5] "public_descrip_table"                             
##   [6] "public_descrip_table1"                            
##   [7] "public_engin_peche"                               
##   [8] "public_engin_peche_utilise"                       
##   [9] "public_environement"                              
##  [10] "public_geometry_columns"                          
##  [11] "public_groupe_esp"                                
##  [12] "public_habitat"                                   
##  [13] "public_lf"                                        
##  [14] "public_list_especes"                              
##  [15] "public_liste_camp"                                
##  [16] "public_niveau_troph"                              
##  [17] "public_pays"                                      
##  [18] "public_projet"                                    
##  [19] "public_spatial_ref_sys"                           
##  [20] "public_station"                                   
##  [21] "public_table_jointure"                            
##  [22] "MSysAccessStorage"                                
##  [23] "MSysAccessXML"                                    
##  [24] "MSysACEs"                                         
##  [25] "MSysComplexColumns"                               
##  [26] "MSysDataCollection"                               
##  [27] "MSysIMEXColumns"                                  
##  [28] "MSysIMEXSpecs"                                    
##  [29] "MSysNavPaneGroupCategories"                       
##  [30] "MSysNavPaneGroups"                                
##  [31] "MSysNavPaneGroupToObjects"                        
##  [32] "MSysNavPaneObjectIDs"                             
##  [33] "MSysObjects"                                      
##  [34] "MSysQueries"                                      
##  [35] "MSysRelationships"                                
##  [36] "MSysResources"                                    
##  [37] "Administration"                                   
##  [38] "Aire_tot_bail"                                    
##  [39] "biologie"                                         
##  [40] "campagne"                                         
##  [41] "CapStA"                                           
##  [42] "capture"                                          
##  [43] "engin_peche"                                      
##  [44] "engin_peche_utilise"                              
##  [45] "environement"                                     
##  [46] "habitat"                                          
##  [47] "lf"                                               
##  [48] "list_especes"                                     
##  [49] "liste_echelle_vitesse_vent"                       
##  [50] "Liste_espece_Nord"                                
##  [51] "Moy_Ouvertur_Chalut"                              
##  [52] "Navire"                                           
##  [53] "Nb_indiv_LF"                                      
##  [54] "niveau_troph"                                     
##  [55] "pays"                                             
##  [56] "projet"                                           
##  [57] "St_camp"                                          
##  [58] "StatDD"                                           
##  [59] "station"                                          
##  [60] "Station_Avk_Heure_Init"                           
##  [61] "Stratification"                                   
##  [62] "tbl_chemat"                                       
##  [63] "tbl_liste_tables"                                 
##  [64] "Trawl Net Description"                            
##  [65] "wrkSpecies"                                       
##  [66] "zmediane"                                         
##  [67] "znord"                                            
##  [68] "zsud"                                             
##  [69] "\"Capture\" et \"Habitat\" sans correspondance"   
##  [70] "\"Capture\" et \"wrkSpecies\" sans correspondance"
##  [71] "01_req_cap_tmp_ed"                                
##  [72] "01_req_cap_tmp_ed_valid"                          
##  [73] "01_req_cap_tmp_tr"                                
##  [74] "01_req_cap_tmp_tr_Analyse croisée"                
##  [75] "02_req_cap_tmp_ed"                                
##  [76] "02_req_cap_tmp_ed_Analyse croisée"                
##  [77] "02_req_cap_tmp_ed_Analyse croisée1"               
##  [78] "02_req_cap_tmp_tr"                                
##  [79] "03_req_cap_tmp_Biomas"                            
##  [80] "03_req_cap_tmp_Densite"                           
##  [81] "03_req_cap_tmp_ed"                                
##  [82] "03_req_cap_tmp_ed_01"                             
##  [83] "03_req_cap_tmp_ed_Analyse croisée"                
##  [84] "03_req_cap_tmp_tr"                                
##  [85] "04_req_cap_tmp_ed"                                
##  [86] "04_req_cap_tmp_ed_2"                              
##  [87] "04_req_cap_tmp_tr"                                
##  [88] "05_req_cap_tmp_ed"                                
##  [89] "05_req_cap_tmp_tr"                                
##  [90] "05_req_cap_tmp_tr_verif2"                         
##  [91] "06_ed"                                            
##  [92] "06_ed_Analyse croisée"                            
##  [93] "06_ed_verif"                                      
##  [94] "06_req_cap_tmp_ed"                                
##  [95] "06_req_cap_tmp_tr"                                
##  [96] "06_req_cap_tmp_tr2"                               
##  [97] "06_req_cap_tmp_tr3"                               
##  [98] "06_req_cap_tmp_tr3-A"                             
##  [99] "06_req_cap_tmp_tr3-B"                             
## [100] "06_req_cap_tmp_tr3Bis"                            
## [101] "06_req_cap_tmp_tr3-C"                             
## [102] "06_req_cap_tmp_tr3-D"                             
## [103] "06_req_cap_variat"                                
## [104] "06_req_densite"                                   
## [105] "06_Req_densite_strate"                            
## [106] "06_req_ed1_bis"                                   
## [107] "06_req_ed2_bis"                                   
## [108] "06_req_tmp_6"                                     
## [109] "06_req_tmp_6_E"                                   
## [110] "06_req_tmp_6_F"                                   
## [111] "06_req_tmp_6_F_Strate"                            
## [112] "06_req_tmp_6_strate"                              
## [113] "06_req_tmp_6-A"                                   
## [114] "06_req_tmp_6-B"                                   
## [115] "06_req_tmp_6-D"                                   
## [116] "06_req_tmp_6-E"                                   
## [117] "07_ed"                                            
## [118] "07_ed_Analyse croisée"                            
## [119] "07_req_cap_tmp_tr"                                
## [120] "07req_cap_tmp_ed"                                 
## [121] "08_ed"                                            
## [122] "08_ed_Analyse croisée"                            
## [123] "08_ed_valid"                                      
## [124] "08_ed_verif"                                      
## [125] "08_req_cap_tmp_tr"                                
## [126] "09_req_cap_tmp_tr"                                
## [127] "10_req_cap_tmp_tr"                                
## [128] "11_req_cap_tmp_tr"                                
## [129] "12_req_cap_tmp_tr"                                
## [130] "13_Freq_taille"                                   
## [131] "15_req_Freq_Taille"                               
## [132] "16_req_Freq_Taille_tot"                           
## [133] "Aire_ballaye"                                     
## [134] "Aire_ballaye1"                                    
## [135] "biomasse"                                         
## [136] "Biomasse_famille"                                 
## [137] "capture_famille"                                  
## [138] "Capture_Station"                                  
## [139] "Capture_Zone_verif"                               
## [140] "Diversite_pays"                                   
## [141] "Diversite_strate"                                 
## [142] "Diversite_strate_pays"                            
## [143] "Exo1"                                             
## [144] "Freq_ mod_Analyse croisée"                        
## [145] "Freq_Classe_Mod"                                  
## [146] "Freq_Classe_Mod_A"                                
## [147] "Freq_max_mod"                                     
## [148] "Freq_Taille_Station"                              
## [149] "indice_abondance"                                 
## [150] "indice_abondance_2"                               
## [151] "Liste_table_campagne"                             
## [152] "MisJrSurface_Cal"                                 
## [153] "mod"                                              
## [154] "Ouverture_chalut"                                 
## [155] "R_01"                                             
## [156] "R_02"                                             
## [157] "R_03"                                             
## [158] "Rechercher les doublons pour Campagne"            
## [159] "Rechercher les doublons pour capture_corig"       
## [160] "Rechercher les doublons pour Capture_ZN"          
## [161] "Rechercher les doublons pour LF_ZN_Avk_Doub"      
## [162] "Rechercher les doublons pour Liste_espece_Nord"   
## [163] "Rechercher les doublons pour Station_ZN2"         
## [164] "Req_001_capture_temp_nbr_trait"                   
## [165] "Req_003_Densit_Moy_Pond_Surf_Chalut"              
## [166] "Req_003_Densit_Moy_Pond_Surf_Chalut_bis"          
## [167] "req_01_bis"                                       
## [168] "Req_01_capture"                                   
## [169] "Req_01_capture_1"                                 
## [170] "Req_01_MRT"                                       
## [171] "Req_02_bis"                                       
## [172] "Req_03"                                           
## [173] "Req_03_densite_surface_chalute"                   
## [174] "Req_03_densite_surface_chalute_02"                
## [175] "Req_03_densite_surface_chalute_03"                
## [176] "Req_03_densite_surface_chalute_tot_strate"        
## [177] "Req_05"                                           
## [178] "Req_07"                                           
## [179] "Req_biomase"                                      
## [180] "Req_capture_surface"                              
## [181] "Req_d_011"                                        
## [182] "Req_d_021"                                        
## [183] "Req_d_03"                                         
## [184] "Req_d_04"                                         
## [185] "Req_extraction_station"                           
## [186] "Req_freq2"                                        
## [187] "req_frequence_1b"                                 
## [188] "Req_Long"                                         
## [189] "Req_Long2"                                        
## [190] "Req_Longueur_01"                                  
## [191] "Req_longueur_02"                                  
## [192] "Req_mod_max"                                      
## [193] "Req_nbtrait"                                      
## [194] "Req_Nivo_Trop"                                    
## [195] "Req_Nivo_Trop1"                                   
## [196] "Req_Nivo_Trop1_2"                                 
## [197] "Req_Nivo_Trop2"                                   
## [198] "req_sortie"                                       
## [199] "Req_surface"                                      
## [200] "Req_surface_totale_strate"                        
## [201] "Req_Tot"                                          
## [202] "Req_tot_famille"                                  
## [203] "Req_tot_group"                                    
## [204] "Req_tot_taxon"                                    
## [205] "Req_totale"                                       
## [206] "Req22bis"                                         
## [207] "Req23bis"                                         
## [208] "req24bis"                                         
## [209] "Requete_select_champs"                            
## [210] "Requête1"                                         
## [211] "Requête2"                                         
## [212] "Requête3"                                         
## [213] "stat_transl"                                      
## [214] "stat_transl2"                                     
## [215] "Station_Analyse croisée"                          
## [216] "Structure_Taile_Req"                              
## [217] "Structure_Taille"                                 
## [218] "Structure_taille2"                                
## [219] "surface_balaille"                                 
## [220] "Surface_balaille_strate"                          
## [221] "test03"                                           
## [222] "Test1"
#Obtenir la liste des champs 

dbListFields(liaisons ,"projet")
##  [1] "code_pays"      "code_projet"    "nom_project"    "adresse_projet"
##  [5] "maitre_oeuvre"  "couverture_geo" "objectifs"      "institutions"  
##  [9] "model"          "reference"      "remarque"
#Passer une requete 

head(dbGetQuery(liaisons ,"select * from projet"))
##   code_pays  code_projet
## 1        BN  PRESH-ZS-DM
## 2        BN PRESH-ZS-PEL
## 3        CI  PRESH-ZS-DM
## 4        CI PRESH-ZS-PEL
## 5        GH  PRESH-ZS-DM
## 6        GH PRESH-ZS-PEL
##                                                             nom_project
## 1                                    Evaluation des stocks d<e9>mersaux
## 2 Projet d'Evaluation des Stocks Halieutiques (P<e9>lagiques)  ZONE SUD
## 3                                    Evaluation des stocks d<e9>mersaux
## 4 Projet d'Evaluation des Stocks Halieutiques (P<e9>lagiques)  ZONE SUD
## 5                                    Evaluation des stocks d<e9>mersaux
## 6 Projet d'Evaluation des Stocks Halieutiques (P<e9>lagiques)  ZONE SUD
##   adresse_projet maitre_oeuvre    couverture_geo
## 1          UEMOA         UEMOA         ZEE BENIN
## 2          UEMOA         UEMOA ZEE-Cote d'Ivoire
## 3          UEMOA         UEMOA ZEE COTE D'IVOIRE
## 4          UEMOA         UEMOA ZEE-Cote d'Ivoire
## 5          UEMOA         UEMOA         ZEE GHANA
## 6          UEMOA         UEMOA ZEE-Cote d'Ivoire
##                                                     objectifs institutions
## 1  Evaluation des stocks d<e9>mersaux de la zone sud du PRESH        CNSHB
## 2 Evaluation des stocks p<e9>lagiques de la zone sud du PRESH         CROD
## 3  Evaluation des stocks d<e9>mersaux de la zone sud du PRESH        CNSHB
## 4 Evaluation des stocks p<e9>lagiques de la zone sud du PRESH         CROD
## 5  Evaluation des stocks d<e9>mersaux de la zone sud du PRESH        CNSHB
## 6 Evaluation des stocks p<e9>lagiques de la zone sud du PRESH         CROD
##   model reference remarque
## 1  <NA>      <NA>     <NA>
## 2  <NA>      <NA>     <NA>
## 3  <NA>      <NA>     <NA>
## 4  <NA>      <NA>     <NA>
## 5  <NA>      <NA>     <NA>
## 6  <NA>      <NA>     <NA>

Se connecter méthode 2

#On vide les variables préexistantes
rm(list=ls())

#Je désigne le répertoire de mon fichier Rmd comme étant le répertoire de travail

setwd(".")

library(RODBC) #loads the RODBC package 
dta <-odbcConnectAccess2007("./CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb") 


head(sqlTables(dta))
##                                    TABLE_CAT TABLE_SCHEM            TABLE_NAME
## 1 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>         public_biblio
## 2 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>       public_biologie
## 3 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>       public_campagne
## 4 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>        public_capture
## 5 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>  public_descrip_table
## 6 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA> public_descrip_table1
##   TABLE_TYPE REMARKS
## 1    SYNONYM    <NA>
## 2    SYNONYM    <NA>
## 3    SYNONYM    <NA>
## 4    SYNONYM    <NA>
## 5    SYNONYM    <NA>
## 6    SYNONYM    <NA>
#Obtenir la liste des champs 

head(sqlColumns(dta,'projet'))
##                                    TABLE_CAT TABLE_SCHEM TABLE_NAME
## 1 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
## 2 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
## 3 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
## 4 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
## 5 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
## 6 .\\CAMPAGNE_PRESH_NORD_SUD_2012_2015.accdb        <NA>     projet
##      COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
## 1      code_pays        12   VARCHAR           4             8             NA
## 2    code_projet        12   VARCHAR          12            24             NA
## 3    nom_project        12   VARCHAR         100           200             NA
## 4 adresse_projet        12   VARCHAR         150           300             NA
## 5  maitre_oeuvre        12   VARCHAR         200           400             NA
## 6 couverture_geo        12   VARCHAR         200           400             NA
##   NUM_PREC_RADIX NULLABLE                     REMARKS COLUMN_DEF SQL_DATA_TYPE
## 1             NA        1                   Code pays       <NA>            12
## 2             NA        1                 Code projet       <NA>            12
## 3             NA        1               Nom du projet       <NA>            12
## 4             NA        1           Adresse du projet       <NA>            12
## 5             NA        1 Institution maître d'oeuvre       <NA>            12
## 6             NA        1     Couverture geographique       <NA>            12
##   SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE ORDINAL
## 1               NA                 8                1         YES       1
## 2               NA                24                2         YES       2
## 3               NA               200                3         YES       3
## 4               NA               300                4         YES       4
## 5               NA               400                5         YES       5
## 6               NA               400                6         YES       6
#Passer une requete 

kable(head(sqlQuery(dta ,"select * from projet")))
code_pays code_projet nom_project adresse_projet maitre_oeuvre couverture_geo objectifs institutions model reference remarque
BN PRESH-ZS-DM Evaluation des stocks démersaux UEMOA UEMOA ZEE BENIN Evaluation des stocks démersaux de la zone sud du PRESH CNSHB NA NA NA
BN PRESH-ZS-PEL Projet d’Evaluation des Stocks Halieutiques (Pélagiques) ZONE SUD UEMOA UEMOA ZEE-Cote d’Ivoire Evaluation des stocks pélagiques de la zone sud du PRESH CROD NA NA NA
CI PRESH-ZS-DM Evaluation des stocks démersaux UEMOA UEMOA ZEE COTE D’IVOIRE Evaluation des stocks démersaux de la zone sud du PRESH CNSHB NA NA NA
CI PRESH-ZS-PEL Projet d’Evaluation des Stocks Halieutiques (Pélagiques) ZONE SUD UEMOA UEMOA ZEE-Cote d’Ivoire Evaluation des stocks pélagiques de la zone sud du PRESH CROD NA NA NA
GH PRESH-ZS-DM Evaluation des stocks démersaux UEMOA UEMOA ZEE GHANA Evaluation des stocks démersaux de la zone sud du PRESH CNSHB NA NA NA
GH PRESH-ZS-PEL Projet d’Evaluation des Stocks Halieutiques (Pélagiques) ZONE SUD UEMOA UEMOA ZEE-Cote d’Ivoire Evaluation des stocks pélagiques de la zone sud du PRESH CROD NA NA NA

Faire quelques interrogations à partir de cette connexion

library(ggplot2)
## Warning: package 'ggplot2' was built under R version 3.6.2
library(leaflet)
## Warning: package 'leaflet' was built under R version 3.6.2
kable(sqlColumns(dta,'station')$COLUMN_NAME)
x
code_pays
code_projet
code_campagne
code_engin
code_Station
Date
latitude_deb
longitude_deb
latitude_fin
longitude_fin
profond_deb
profond_fin
heure_debut
heure_fin
vitesse_chalutage
direction
vitesse_vent
direction_vent
vitesse_courant
direction_courant
nature_fond
strate
temperature_surface
salinite_surface
do_surface
temperature_fond
salinite_fond
do_fond
long_fune
jour_nuit
nbre_espece
ouverture_chalut
remarques
request1<-sqlQuery(dta ,"select distinct year(Date) as annee,count(*) as nb_stations from station group by year(Date)")

ggplot(request1,aes(x=annee,y=nb_stations))+geom_bar(stat="identity")+ggtitle("Nombre de stations de la base par année")

request2<-sqlQuery(dta ,"select year(Date) as annee,code_station,replace(longitude_deb,',','.') as longitude,replace(latitude_deb,',','.') as latitude from station")



leaflet(data = request2) %>% addTiles() %>%
  addMarkers(~longitude, ~latitude)
ggplot(request2)+ geom_point( aes(longitude,latitude))

Faire quelques interrogations un chouia plus compliquées

request2<-sqlQuery(dta ,"select year(Date) as annee,A.code_station,replace(longitude_deb,',','.') as longitude,replace(latitude_deb,',','.') as latitude,sum(total_capture) as capture  from station A
                   inner join capture B on (A.code_pays=B.code_pays and A.code_projet=B.code_projet and A.code_campagne=B.code_campagne and A.code_station=B.code_station) group by year(Date),A.code_station,replace(longitude_deb,',','.'),replace(latitude_deb,',','.')")


library(viridisLite)
## Warning: package 'viridisLite' was built under R version 3.6.2
# get domain of numeric data
(domain <- range(request2$capture))
## [1]    0.00 2416.14
# make palette
pal <- colorNumeric(palette = viridis(100), domain = domain)

leaflet(data = request2) %>% addTiles() %>%
  addCircleMarkers(~longitude, ~latitude, radius = ~ sqrt(capture),color=~pal(capture))
ggplot(request2)+ geom_point(color='Red',shape=21, aes(longitude,latitude,size=capture))

ggplot(request2)+ geom_point(color='Red',shape=21, aes(longitude,latitude,size=capture))+ 
  facet_grid(~annee)

On rajoute de l’espèce

request3<-sqlQuery(dta ,"select year(Date) as annee,ucase(nom_taxonomique) as nom_taxo,A.code_station,replace(longitude_deb,',','.') as longitude,replace(latitude_deb,',','.') as latitude,sum(total_capture) as capture  from station A
                   inner join capture B on (A.code_pays=B.code_pays and A.code_projet=B.code_projet and A.code_campagne=B.code_campagne and A.code_station=B.code_station) 
where ucase(nom_taxonomique) like 'PSEUDOTOLITHUS%'                   
group by year(Date),A.code_station,ucase(nom_taxonomique),replace(longitude_deb,',','.'),replace(latitude_deb,',','.')")





ggplot(request3)+ geom_point(color='Red',shape=21, aes(longitude,latitude,size=capture))+
  facet_wrap(~nom_taxo)