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