Themes | DiscardLess Case studies Description | ||||||||||||||||||||||||||||||||||||
Name of the indicator | Where the gears are mostly used | ||||||||||||||||||||||||||||||||||||
Definition | Catches declared by gears and statistical rectangle for the case study between 2004 and 2015 | ||||||||||||||||||||||||||||||||||||
Objectives | View how activities by gears are spatially distributed inside the case study | ||||||||||||||||||||||||||||||||||||
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 rectangle. On a subset of the the time serie (last three years) we sum the cathes by gears and we identify the first gears (6).
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 rectangle you get the landings weigth by each 6 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 tmp1 as
(
select distinct year_d as ABSCISSE,rectangle as LIEU,reg_gear as GROUPE,sum(landings) as VAL from
landing_rect_sd inner join cs_rectangles using(rectangle)
where id_case=3 and country in ('ESP','FRA') and year_d>=2004
group by LIEU,GROUPE,ABSCISSE order by LIEU desc
)
,limite as
(select 0.05*max(VALLIM) as VALLIM from (select distinct LIEU,0.005*sum(VAL) as VALLIM from tmp1 group by LIEU) tmp)
,selection_1 as
(select distinct LIEU,sum(VAL) from tmp1,limite group by LIEU,VALLIM having sum(VAL)>VALLIM)
,total as
(select GROUPE,sum(VAL) as tot from tmp1 where ABSCISSE>2015-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 tmp1 inner join selection_1 using(LIEU) left join total using(GROUPE))
select distinct LIEU,GROUPE,sum(VAL)/nb_year as VAL from regroupement,(select count(distinct year_d) as nb_year from landing_rect_sd
inner join cs_rectangles using(rectangle)
where id_case=3 and country in ('ESP','FRA') and year_d>=2004) tmp group by LIEU,GROUPE,nb_year
order by GROUPE
| ||||||||||||||||||||||||||||||||||||
Retour à la fiche |