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")))
| 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)
| 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)
