Themes | DiscardLess Case studies Description |
Name of the indicator | Landings+Discards=Catches by Years |
Definition | Landings declared and estimated discards for the case study between 2012 and 2014 |
Objectives | Catch major trends and changes in landings and Discards |
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. (Landings + Discards ratio by gears, species, vessel length, country) and we calculate (sum) the landings for the case study and we estimate the total discards.
|
Reading guide | For each year you get the landings weigth and the estimated discards (see Data combination for calculation details). |
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. We only use available data after 2011 for discards despite data exists before but scientists do not trust in the data before 2012. |
Query |
with part1 as
(
select distinct country,annexe,reg_area,reg_gear_cod,species,year_d,rectangle,english_name,sum(A.landings) as landings,sum(discards)/(sum(discards)+sum(B.landings)) as tx_discards
from landing_rect_sd A left join discard_landings_sd B
using(country,annexe,reg_area,reg_gear,reg_gear_cod,species,year_d,vessel_length,specon) inner join cs_rectangles using(rectangle) left join asfis on (species=a_code)
where 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%' and year_d>=2012
group by country,annexe,reg_area,reg_gear_cod,species,year_d,rectangle,english_name order by year_d,sum(A.landings)
)
,part2 as
(
select country,annexe,reg_gear_cod,species,year_d,rectangle,english_name,landings,reg_area,case when tx_discards=1 then 0.99 else tx_discards end as tx_discards from part1
)
,part4 as
(
select distinct year_d as ABSCISSE,round(sum(landings)/1000,1) as landings,round(sum(((1/(1-tx_discards))*landings)-landings)/1000,1) as TOTAL from part2 left join countries using(country)
group by ABSCISSE
)
,part5 as
(select distinct ABSCISSE,'Landings' as GROUPE,landings as TOTAL from part4
union
select distinct ABSCISSE,'Observed Discards' as GROUPE,TOTAL as TOTAL from part4
)
select * from part5 order by ABSCISSE,GROUPE
|
Retour à la fiche |