| Themes | DiscardLess Case studies Description |
| Name of the indicator | Where the species are mostly discarded inside the case studies |
| Definition | Dsiacrds estimated by species and statistical rectangle for the case study in the last available year. |
| Objectives | View how discards by species are spatially distributed inside the case study |
| View | |
| Calculation description |
We used the landings weigth given by statistical rectangle and the discards ratio at the regulated area level (for each species/vessel length/gear/country/year strata)
Using discards ratio and landings we estimate a total discards for each rectangle / species for the specific year using calculation described in the presentation part of the website.
|
| Reading guide | For each rectangle you get the Discards weigth for each of the 5 first major species. Other, merge discards weight of other species. |
| 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 country,annexe,reg_area,reg_gear_cod,reg_gear,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,species,year_d,reg_gear,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 discards>0 group by reg_gear,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,rectangle as LIEU,english_name as GROUPE,sum(((1/(1-tx_discards))*landings)-landings) as VAL from part2
group by LIEU,GROUPE,year_d
)
,total as
(select GROUPE,sum(VAL) as tot from part4 where year_d>2014-3 group by GROUPE order by tot DESC limit 6)
,regroupement as
(select distinct LIEU,case when total.GROUPE is null then 'Other' else GROUPE end as GROUPE,VAL as VAL
from part4 left join total using(GROUPE) where year_d=2014)
select distinct LIEU,GROUPE,sum(VAL)/nb_year as VAL from regroupement,
(select count(distinct year_d) as nb_year from part1) tmp group by LIEU,GROUPE,nb_year
order by LIEU,GROUPE
|
 Retour à la fiche | |
