| Themes | DiscardLess Case studies Description | ||||||||||||||||||||||||||||||||||||
| Name of the indicator | Trends in catches fo the case study | ||||||||||||||||||||||||||||||||||||
| Definition | Catches declared by gears 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 the time serie selected we sum the cathes by gears and we identify the first gears.
Then we get specific values from these selected gears and we sum the rest of the landigs in a strata called other.
| ||||||||||||||||||||||||||||||||||||
| Reading guide |
For each year you get the landings weigth for each 5 first major gears. Other group landings weight of other gears.
For Mediterranean Sea :
For Ices area
| ||||||||||||||||||||||||||||||||||||
| 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,reg_gear as GROUPE,sum(landings)/1000 as TOTAL from landing_rect_sd
inner join cs_rectangles using(rectangle)
where id_case=1 and reg_gear not 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 | |||||||||||||||||||||||||||||||||||||
