Atlas Halieutique
Themes Stock
Name of the indicator Spatial distribution of the origin of the landings
Definition Landings described by the spatial origin of the catches and country of the fleet
Objectives Main objective is to analyse spatial distribution of the stock through a proxy (spatial distribution of the catches) and to get an idea of the country of the fleets
View
Calculation description We filter the FDI data for the specific species and list of statistical rectangles defined for the stock. Then we sum the landing values obtained for the last three years to selected 6 major countries. Finally we sum the rest of the landings (if needed) in a new strata Other.
Reading guide We get the value of the landings caught in each statistical rectangle by country for all the time serie
Data sources FDI landings
Comments
Query with tmp1 as ( select distinct year_d,rectangle as LIEU,country_name as GROUPE,sum(landings) as VAL from landing_rect_sd inner join def_stocks_area using (species,rectangle) left join countries using(country) where fishstock like 'bss.27.4bc7ad–h' and year_d>=2004 and annexe not like 'MED' group by LIEU,GROUPE,year_d ) ,limite as (select 0.05*max(VALLIM) as VALLIM from (select distinct year_d,LIEU,0.05*sum(VAL) as VALLIM from tmp1 group by LIEU,year_d) tmp) ,selection_1 as (select distinct year_d,LIEU,sum(VAL) from tmp1,limite group by LIEU,VALLIM,year_d having sum(VAL)>VALLIM) ,total as (select GROUPE,sum(VAL) as tot from tmp1 inner join selection_1 using(LIEU) where tmp1.year_d>2015-3 group by GROUPE order by tot DESC limit 6) ,regroupement as (select distinct tmp1.year_d,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 tmp1 where year_d>=2008) tmp group by LIEU,GROUPE,nb_year order by LIEU,GROUPE
Retour à la fiche