| Themes | DiscardLess Case studies Description |
| Name of the indicator | Total discards by regulated gears |
| Definition | Estimated discards for the case study between 2012 and 2014 for each major gears (based on the total estimated discards on the time series) |
| Objectives | Catch major trends and changes in 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.
We use then the total discards by years and gears
|
| Reading guide | For each year you get the estimated discards (see Data combination for calculation details) for each gears. |
| 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,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 inner join discard_landings_sd B
using(country,annexe,reg_area,reg_gear_cod,reg_gear,species,year_d,vessel_length,specon) inner join cs_rectangles using(rectangle) left join asfis on (species=a_code)
where id_case=6 and annexe like 'MED' and country like 'GRC' and year_d>=2012 group by country,annexe,reg_gear,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,reg_gear,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,reg_gear as GROUPE,sum(((1/(1-tx_discards))*landings)-landings) as TOTAL from part2 left join countries using(country)
group by ABSCISSE,GROUPE
)
,selection as
(
select distinct GROUPE,sum(TOTAL) as tot from part4 where ABSCISSE>2014-3 and TOTAL is not null 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,part4.TOTAL
from part4 left join selection using(GROUPE)
),
part5 as
(
select ABSCISSE,GROUPE,100*sum(TOTAL) as TOTAL from regroupement group by ABSCISSE,GROUPE order by ABSCISSE,GROUPE
)
select ABSCISSE,GROUPE,round(TOTAL,1) as TOTAL from part5
|
 Retour à la fiche | |
