Themes | DiscardLess Case studies Description |
Name of the indicator | Trends in catches fo the case study |
Definition | Catches declared by vessel length for the case study between 2011 and 2014 |
Objectives | Catch major trends and changes in the case study fisheries |
View | |
Calculation description |
We used the landings description (aggregation of annexe detailled in section Introduction of the website) filtered on each case studies (ie for Easter Channel we use data where specon='NONE' and id_case=2 and country like 'FRA' and species in ('SOL','PLE','COD','WHE','BSS','HER','MAC','HOM','SCE','JAX','SQL','SQR','CTC','MUR') and reg_gear not like 'PEL%')
With these selected data we aggregated available data by years. On a subset of the the time serie (last three years) we sum the cathes by vessel_length and we identify the first vessel_length (6).
Then we get specific values from these selected length and we sum the rest of the landigs in a strata called other if needed.
|
Reading guide | For each year you get the landings weigth for each 5 first major vessel length. Other group landings weight of other vessel length category. |
Data sources |
Data used to produce figures comes from the STECF dissemination tool . Details on data can be found in the Fisheries Dependent Information working group report and from the Mediterranean and Black sea report |
Comments | Please note that time series might be incomplete for some countries/indicators. |
Query |
with part1 as
(
select distinct year_d as ABSCISSE,vessel_length as GROUPE,sum(landings)/1000 as TOTAL from landing_rect_sd
inner join cs_rectangles using(rectangle)
where id_case=1 and reg_gear in ('PEL_TRAWL') group
by ABSCISSE,GROUPE order by ABSCISSE
)
,selection as
(
select distinct GROUPE,sum(TOTAL) as tot from part1 where ABSCISSE>2015-3 group by GROUPE order by tot DESC limit 6
)
,regroupement as
(
select distinct ABSCISSE,case when selection.GROUPE is null then 'Other' else selection.GROUPE end as GROUPE,part1.TOTAL
from part1 left join selection using(GROUPE)
)
select distinct ABSCISSE,GROUPE, sum(TOTAL) as TOTAL from regroupement group by ABSCISSE,GROUPE order by ABSCISSE,GROUPE
|
Retour à la fiche |