Introduction

## Loading required package: DBI
## Linking to GEOS 3.6.1, GDAL 2.1.3, PROJ 4.9.3

Pour le trajet 65, on affiche les points en trajet (rouge) et les points prédits en pêches (Vert)

library(leaflet)
library(ggplot2)
library(sf)

trajet_points_init <- st_read(con,query = "select idpos,geom_spatial from trajet_epure  where no_trajet=924 ")

trajet_recalibre_points <- st_read(con,query = "select idpos,geom_spatial from trajet_epure  where no_trajet=924 and etat_agrege like 'P%'")

pal_diff   <- colorFactor(c("Green","red"), domain = c('trajet','peche'))

leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addCircleMarkers(data=trajet_points_init,radius = 4,
    color = 'red',
    stroke = FALSE, fillOpacity = 0.5) %>%
  addCircleMarkers(data=trajet_recalibre_points,color='green',radius = 5,
    stroke = FALSE, fillOpacity = 1) %>% 
  addLegend("bottomright", pal =pal_diff, values = c('trajet','peche'),
    title = "Statut",
    opacity = 1)

Bon allez on commmence par regarder les fréquences de réponses des GPS sur l’ensemble des données dans un premier temps et pour le trajet n0 924 ensuite

library(ggplot2)
data_eng<-dbGetQuery(con, "
select *,EXTRACT(EPOCH FROM (duree)) as duree_sec from prepa_predict_etat 
");   
## Warning in postgresqlExecStatement(conn, statement, ...): RS-DBI driver warning:
## (unrecognized PostgreSQL field type geometry (id:18415291) in column 30)
kable(head(data_eng))
no_trajet idpos pecheur_id date_heure distance duree etat_detaille etat_agrege speed2 speed3 speed4 speed5 conv5p conv6p conv7p conv8p conv9p conv10p conv5 conv6 conv7 conv8 conv9 conv10 cercle20 cercle50 cercle80 cercle110 cercle150 cercle180 geom_spatial duree_sec
0 238578 CAC_PA_0005 2019-04-19 07:42:09 290.486343 00:05:00 NA NA 2.3743028 1.7892339 1.4955031 1.3747832 0.0104874 0.0106263 0.0124764 0.0147201 0.0153639 0.0153639 92035.792217 93257.019647 1.097166e+05 1.301388e+05 136133.02782 136133.02782 2 2 3 4 7 7 0101000020E6100000E7FFDF3ED40C2EC0070040E8BA422640 300
0 245115 KCK_FMDk_0006 2019-04-21 00:00:37 8.148836 00:05:00 NA NA 0.0768784 0.0422011 0.0428141 0.0323609 0.0483157 0.0479598 0.0479598 0.0597013 0.0597013 0.0597013 76.670651 77.110365 7.711037e+01 1.094237e+02 109.42370 109.42370 1 1 1 1 1 1 0101000020E61000001B00207D02242EC01700D0EEEBC62540 300
0 515570 BGL_PALI_0006 2018-12-19 16:12:08 54.816200 00:00:19 NA NA 4.9123953 2.5865755 2.6023248 2.4242021 0.0028942 0.0072536 0.0102415 0.0127877 0.0144882 0.0151034 118.718243 372.069761 6.553793e+02 1.001906e+03 1341.17933 1656.71741 1 2 2 7 12 15 0101000020E6100000F1FFCFCFFD9F2CC0E5FFCF1DAA622440 19
0 670554 MTK_FMCg_0005 2018-09-11 17:27:08 2.379433 00:00:14 NA NA 0.2007262 0.1928261 0.3049416 0.3652033 0.0327290 0.0359677 0.0454215 0.0563362 0.0526603 0.0368414 16.544279 43.500512 6.285008e+01 1.439950e+02 176.49016 311.13355 9 10 61 68 73 78 0101000020E6100000FFFF6F3A16D92AC0000020DEF78D2240 14
0 680336 MTK_FMCl_0003 2018-12-25 10:00:25 1.186340 00:00:10 NA NA 0.5513598 0.0901791 0.1064490 0.0969615 0.0001524 0.0001854 0.0003866 0.0004832 0.0005254 0.0007078 7525.455635 9167.413466 1.922124e+04 2.411649e+04 26329.52883 35669.09215 5 5 5 20 25 27 0101000020E6100000F1FFAF82F7D82AC0FFFF7FAEF48D2240 10
0 687024 MTK_FMCy_0004 2018-07-04 14:31:55 1.159372 00:00:14 NA NA 0.2258583 0.1377964 0.1432720 0.1530258 0.0092635 0.0142604 0.0132694 0.0112944 0.0148115 0.0199278 1.557291 4.323128 6.423183e+00 8.277383e+00 16.55705 28.58688 17 40 102 147 191 212 0101000020E6100000ECFF0FCA7F212BC00000604BC0722240 14 
ggplot(data_eng[data_eng$duree_sec<500,],aes(duree_sec),title='Distribution des fréquences de durées pour gps IG')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de durées pour  ')

ggplot(data_eng[data_eng$no_trajet==924,],aes(duree_sec),title='Distribution des fréquences de durées pour gps IG')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de durées pour le trajet 924 ')

Comme il y a beaucoup de différence entre les GPS (entre 25 seconde et 5 mn), je recalibre le tout

data_eng<-dbGetQuery(con, "
select *,EXTRACT(EPOCH FROM (duree)) as duree_sec from recalibration.prepa_predict_etat 
");   
## Warning in postgresqlExecStatement(conn, statement, ...): RS-DBI driver warning:
## (unrecognized PostgreSQL field type geometry (id:18415291) in column 30)
kable(head(data_eng))
no_trajet idpos_init pecheur_id date_heure distance duree etat_detaille etat_agrege speed2 speed3 speed4 speed5 conv5p conv6p conv7p conv8p conv9p conv10p conv5 conv6 conv7 conv8 conv9 conv10 cercle20 cercle50 cercle80 cercle110 cercle150 cercle180 geom_spatial idpos duree_sec
2 11 CAC_FMD_0004 2019-04-20 19:33:32 228.8220 00:02:00 NA 3.813705 2.681602 2.185232 1.937047 0.0007151 0.0008776 0.0318499 0.0462251 0.0471365 0.0435142 1770.112 3034.367 132704.91 262374.61 399249.88 543331.1 5398 8939 11084 12645 13960 14781 0101000020E6100000000020D6270F2EC0F3FF1F09D1492640 808356 120
2 12 CAC_FMD_0004 2019-04-20 19:35:32 194.8991 00:02:00 NA 3.531009 2.718932 2.329121 2.044964 0.0085467 0.0075167 0.0067000 0.0279576 0.0435840 0.0479055 23715.277 28631.874 33548.54 163651.87 327302.88 498119.6 5449 8751 10990 12444 13896 14586 0101000020E61000001A0020697A0E2EC0080020F86B4A2640 808357 120
2 13 CAC_FMD_0004 2019-04-20 19:37:32 194.8987 00:02:00 NA 3.248314 2.577582 2.354006 2.152879 0.0120674 0.0113022 0.0105134 0.0097798 0.0251894 0.0408985 34011.461 47430.879 57264.15 67097.55 197633.19 395265.5 5464 8434 10897 12346 13950 14761 0101000020E6100000FCFF1FFCCC0D2EC01D0020E7064B2640 808358 120
2 14 CAC_FMD_0004 2019-04-20 19:39:32 163.3255 00:02:00 NA 2.985201 2.304680 2.172070 2.105766 0.0104667 0.0123988 0.0122857 0.0118053 0.0112693 0.0233909 25183.101 49683.662 69027.35 83214.52 97401.89 227937.5 5341 8483 10455 12200 13653 14534 0101000020E6100000FEFFEF813E0D2EC0170070C98B4B2640 808359 120
2 15 CAC_FMD_0004 2019-04-20 19:41:32 131.8637 00:02:00 NA 2.459910 2.042033 1.902742 1.903769 0.0012742 0.0101500 0.0125733 0.0128437 0.0125788 0.0121983 2390.931 33376.821 64363.51 88845.83 106824.11 124802.6 5325 8282 10336 12091 13490 14245 0101000020E6100000210090FACE0C2EC0F6FF0F9FFA4B2640 808360 120
2 16 CAC_FMD_0004 2019-04-20 19:43:32 131.8635 00:02:00 NA 2.197727 1.779386 1.727643 1.701772 0.0015456 0.0015362 0.0096456 0.0124086 0.0130051 0.0129463 2390.838 4098.839 41570.79 79043.64 108664.65 130434.1 5240 8006 10301 11904 13454 14273 0101000020E61000000C0030735F0C2EC00E00B074694C2640 808361 120 
ggplot(data_eng[data_eng$duree_sec<500,],aes(duree_sec),title='Distribution des fréquences de durées pour gps IG')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de durées pour  ')

trajet_points <- st_read(con,query = "select idpos,geom_spatial from recalibration.trajet_epure_proc_r  where no_trajet=924 ")

trajet_recalibre_points <- st_read(con,query = "select idpos,geom_spatial from recalibration.trajet_epure_proc_r  
                                   where no_trajet=924 and etat_agrege like 'P%'")

pal_diff   <- colorFactor(c("orange","Green","red"), domain = c('peche','trajet','Anciennes Positions'))

leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addCircleMarkers(data=trajet_points,radius = 4,
    color = 'red',
    stroke = FALSE, fillOpacity = 0.5) %>%
  addCircleMarkers(data=trajet_recalibre_points,color='green',radius = 5,
    stroke = FALSE, fillOpacity = 1) %>% 
  addCircleMarkers(data=trajet_points_init,color='orange',radius = 2,
    stroke = FALSE, fillOpacity = 1) %>% 
  addLegend("bottomright", pal =pal_diff,
    title = "Statut",values = c('peche','trajet','Anciennes Positions'),
    opacity = 1)

Et on peut aussi lui demander de faire des cartes en fonction d’un paramètre supplémentaire

eng<-dbGetQuery(con, "
select distinct code_engin,count(distinct no_trajet) from trajet_epure where etat_agrege like 'P%' group by code_engin
");   

kable(eng)
code_engin count
FMCl 6
FMCy 5
FMD 2
FMEg 1
YO 15 
trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where etat_agrege like 'P%'),
part1 as (select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
where code_engin like 'YO' order by no_trajet,idpos)
                        select no_trajet,st_makeline(geom_spatial) as geom from part1 group by no_trajet")

trajet_ligne_peche <- st_read(con,query = "select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r where code_engin like 'YO' and etat_agrege like 'P%' order by no_trajet,idpos
                        ")



leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addPolylines(data = trajet_ligne,group=~no_trajet)%>%
 addCircleMarkers(data=trajet_ligne_peche,color='red',radius = 2,
    stroke = FALSE, fillOpacity = 1)

Mais je devrais avoir 15 trajet pour cet engin et cela ne semble pas le cas. Creusons

library(classInt)
library(RColorBrewer)

trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where etat_agrege like 'P%'),
part1 as (select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
where code_engin like 'YO' order by no_trajet,idpos)
                        select no_trajet,st_makeline(geom_spatial) as geom from part1 group by no_trajet")

pal <- colorRampPalette(brewer.pal(8, "Accent"))


leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addPolylines(data = trajet_ligne, label=~no_trajet,color =pal(length(trajet_ligne$no_trajet)),
               highlightOptions = highlightOptions(color = "white", weight = 2,
      bringToFront = TRUE))

On peut se poser la question du découpage pertinent des marées

library(classInt)
library(RColorBrewer)

trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where no_trajet in (1053,1054)),
part1 as (select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
where code_engin like 'YO' order by no_trajet,idpos)
                        select no_trajet,st_makeline(geom_spatial) as geom from part1 group by no_trajet")

pal <- colorRampPalette(brewer.pal(8, "Accent"))


leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addPolylines(data = trajet_ligne, label=~no_trajet,color =pal(length(trajet_ligne$no_trajet)),
               highlightOptions = highlightOptions(color = "white", weight = 2,
      bringToFront = TRUE))
eng<-dbGetQuery(con, "select distinct no_trajet,min(date_heure),max(date_heure) from trajet_epure where no_trajet in (1053,1054)
group by no_trajet
")

kable(eng)
no_trajet min max
1053 2019-08-07 08:36:18 2019-08-07 21:28:05
1054 2019-08-08 08:10:34 2019-08-09 00:00:17

Donc ce sont bien des marées distinctes (pas le même jour et avec suffisemment de temps entre les 2)

trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where  no_trajet in (1053,1054)),
part1 as (select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
where code_engin like 'YO' order by no_trajet,idpos)
                        select no_trajet,st_makeline(geom_spatial) as geom from part1 group by no_trajet")

trajet_ligne_peche <- st_read(con,query = "select distinct no_trajet,idpos,geom_spatial from recalibration.trajet_epure_proc_r where code_engin like 'YO' and etat_agrege like 'P%' and no_trajet in (1053,1054)order by no_trajet,idpos
                        ")

pal <- colorRampPalette(brewer.pal(2, "Accent"))
## Warning in brewer.pal(2, "Accent"): minimal value for n is 3, returning requested palette with 3 different levels
leaflet() %>% addProviderTiles("Esri.WorldImagery")  %>%
  addPolylines(data = trajet_ligne,group=~no_trajet,color =pal(length(trajet_ligne$no_trajet)))%>%
 addCircleMarkers(data=trajet_ligne_peche,color='red',radius = 2,
    stroke = FALSE, fillOpacity = 1)

Reste que les trajets semblent partiels (a discuter avec Mohamed).

Si on considère que c’est bon, pour l’engin YO on a 15 marées observées. Est ce suffisant ? A voir avec MP

Un excercice avec GGPlot

trajet_ligne_tout <- st_read(con,query = "with part1 as (select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r 
 order by no_trajet,idpos)
                        select no_trajet,code_engin,st_makeline(geom_spatial) as geom from part1 group by no_trajet,code_engin")


trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where etat_agrege like 'P%'),
part1 as (select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
 order by no_trajet,idpos)
                        select no_trajet,code_engin,st_makeline(geom_spatial) as geom from part1 group by no_trajet,code_engin")

trajet_ligne_peche <- st_read(con,query = "select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r where etat_agrege like 'P%' order by no_trajet,idpos
                        ")




ggplot() + 
  geom_sf(data = trajet_ligne_tout,col='grey', lwd = 0.1)+
  geom_sf(data = trajet_ligne,col='black', lwd = 0.1)+
  geom_sf(data = trajet_ligne_peche,color="Red", lwd = 0.2)+
  facet_wrap(. ~ code_engin)+
  ggtitle("Trajectoires totales et observées par engins")

Et si on ne prend que ceux ou il ya des onservations

trajet_ligne_tout <- st_read(con,query = "with selection as 
(select distinct code_engin from recalibration.trajet_epure_proc_r where etat_agrege like 'P%'
),part1 as (select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection
using (code_engin)
 order by no_trajet,idpos)
                        select no_trajet,code_engin,st_makeline(geom_spatial) as geom from part1 group by no_trajet,code_engin")


trajet_ligne <- st_read(con,query = "with selection as
(select distinct no_trajet from trajet_epure where etat_agrege like 'P%'),
part1 as (select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r inner join selection using(no_trajet)
 order by no_trajet,idpos)
                        select no_trajet,code_engin,st_makeline(geom_spatial) as geom from part1 group by no_trajet,code_engin")

trajet_ligne_peche <- st_read(con,query = "select distinct no_trajet,code_engin,idpos,geom_spatial from recalibration.trajet_epure_proc_r where etat_agrege like 'P%' order by no_trajet,idpos
                        ")




ggplot() + 
  geom_sf(data = trajet_ligne_tout,col='grey', lwd = 0.1)+
  geom_sf(data = trajet_ligne,col='black', lwd = 0.1)+
  geom_sf(data = trajet_ligne_peche,color="Red", lwd = 0.2)+
  facet_wrap(. ~ code_engin)+
  ggtitle("Trajectoires totales et observées par engins")

La vraie utlité est ensuite de passer à des cartes d’aggrégation par rectangles statistiques comme cela

Et là ce sont bien des KG de poissons récifaux par carrées stats

trajets_poids <- st_read(con,query = "select distinct cells.id as LIEU,code_village,geom,count(*) as VAL_P from cells inner join recalibration.trajet_epure_proc_r on st_intersects(geom_spatial,geom) where code_engin like 'YO'
              group by LIEU,geom,code_village order by LIEU desc")



ggplot(trajets_poids[trajets_poids$val_p>0,]) + 
  geom_sf(data = trajets_poids[trajets_poids$val_p>0,],aes(fill=val_p), lwd = 0.1)+scale_fill_gradientn(colours = brewer.pal(8,"Reds")) + 
  borders(reg="Guinea", color="white", fill="#7f7f7f", alpha=1/4)
## Warning: Duplicated aesthetics after name standardisation: colour

  ggtitle("Positions ") 
## $title
## [1] "Positions "
## 
## attr(,"class")
## [1] "labels"
trajets_poids <- st_read(con,query = "select distinct cells.id as LIEU,code_village,geom,count(*) as VAL_P from cells inner join recalibration.trajet_epure_proc_r on st_intersects(geom_spatial,geom) where code_engin like 'YO' and etat_agrege like 'P%'
              group by LIEU,geom,code_village order by LIEU desc")



ggplot(trajets_poids[trajets_poids$val_p>0,]) + 
  geom_sf(data = trajets_poids[trajets_poids$val_p>0,],aes(fill=val_p), lwd = 0.1)+scale_fill_gradientn(colours = brewer.pal(8,"Reds")) + 
  borders(reg="Guinea", color="white", fill="#7f7f7f", alpha=1/4)
## Warning: Duplicated aesthetics after name standardisation: colour

  ggtitle("Etats en pêches") 
## $title
## [1] "Etats en pêches"
## 
## attr(,"class")
## [1] "labels"

Si on regarde ensuite les paramètres (pour le modèle) et les observations

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,speed2 from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(speed2,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de durées entre 2 derniers pointspour les observations ')

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,speed4 from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(speed4,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de durées entre les 4 denriers points pour les observations ')

Et sur les paramètre d’aires

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,conv5p from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(conv5p,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de perimètre de l enveloppe des 5 deniers points ')

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,conv10p from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(conv10p,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de perimètre de l enveloppe des 10 deniers points ')

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,cercle20 from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(cercle20,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de perimètre de l enveloppe des 5 deniers points ')

parametres <- st_read(con,query = "with part1 as
(select distinct no_trajet from recalibration.prepa_predict_etat where pecheur_id like '%YO%' and etat_agrege like 'P%')
select distinct idpos,etat_agrege,cercle180 from recalibration.prepa_predict_etat inner join part1 using(no_trajet)")
## Warning in st_read.DBIObject(...): Could not find a simple features geometry
## column. Will return a `data.frame`.
ggplot(parametres,aes(cercle180,color=etat_agrege),title='Distribution des fréquences de vitesses selon état observé')+geom_histogram(bins=200)+
  ggtitle('Distribution des fréquences de perimètre de l enveloppe des 10 deniers points ')