FsPages laisse la place à Gitlab Pages ! Voici la documentation pour vous en servir : https://docs.gitlab.com/ce/user/project/pages/index.html

Public
Snippet $383 authored by fred.lpo07

Synthèse de l'occupation des gîtes à chiroptères sur une zone d'étude.

Synthese_des_gites_chiropteres.sql
Raw
/*drop
	table
		chiron2kd07_gites;

create
	table
		chiron2kd07_gites as(*/
with bd07datagites as(
	select
		bd07.id_loc,
		bd07.codesp,
		bd07.date,
		bd07.an,
		case
			batspecie.common_name_fr
			when 'Aucune chauve-souris ou trace' then null
			else batspecie.common_name_fr
		end as common_name_fr,
		case
			batspecie.common_name_fr
			when 'Aucune chauve-souris ou trace' then null
			else bd07.n
		end as n,
		bd07.colo_repro,
		bd07.statut,
		bd07.insee,
		bd07.nom_comple,
		bd07.localite,
		bd07.type_gite,
		bd07.geom
	from
		bdchiros.bd07 left join zonages.zones_etudes_multipolygon on
		st_within(
			bd07.geom,
			zones_etudes_multipolygon.geom
		) left join dico.batspecie on
		lower( bd07.codesp )= batspecie.codesp
	where
		type_gite is not null
		and zones_etudes_multipolygon.id = 13
		and contact in(
			'VM',
			'VV',
			'GU'
		)
),
count_session as(
	/*Première table concaténant les résultats des comptages par gites et par sessions*/
	select
		id_loc,
		sum( n ) ntotal,
		statut,
		date
	from
		bd07datagites
	group by
		id_loc,
		statut,
		date
),
eff_max as(
	select
		id_loc,
		statut,
		max( ntotal ) as maxi
	from
		count_session
	group by
		id_loc,
		statut
),
sp_periode as(
	select
		bd07datagites.id_loc,
		bd07datagites.statut,
		maxi,
		string_agg(
			distinct common_name_fr,
			', '
		) as listsp
	from
		bd07datagites left join eff_max on
		(
			bd07datagites.id_loc = eff_max.id_loc
			and bd07datagites.statut = eff_max.statut
		)
	group by
		bd07datagites.id_loc,
		bd07datagites.statut,
		maxi
	having
		maxi > 0
),
sp_periode_concat as(
	select
		id_loc,
		concat( statut, ' : ', maxi, ' ind. max. [', listsp, ']' ) maxandlistsp
	from
		sp_periode
) /*sp_periode as(*/
select
	bd.id_loc,
	bd.nom_comple,
	bd.localite,
	string_agg(
		distinct substring( z.libelle, 51 ),
		', '
	) as site_n2000,
	substring( batsitetype.cat, 5 ) type_gite,
	string_agg(
		distinct bd.statut,
		', '
	) periode_occupation_constatee,
	case
		max( bd.colo_repro )
		when '1' then 'Oui'
		else '-'
	end as reproduction,
	coalesce(
		max( eff_max.maxi )::text,
		'Aucune obs.'
	) maximum,
	string_agg(
		distinct maxandlistsp,
		(concat(',',chr(10)))
	) especes_par_periode,
	string_agg(
		distinct bd.an::text,
		', '
	) annees_suivies,
	bd.geom /*
		concat( bd.statut, ' (max=', eff_max.maxi, ' - ', listsp, ')' )),', ') as especes_periodes
	*/
from
	bd07datagites bd left join sp_periode_concat on
	bd.id_loc = sp_periode_concat.id_loc left join eff_max on
	bd.id_loc = eff_max.id_loc left join dico.batsitetype on
	lower( bd.type_gite )= batsitetype.code left join zonages.zones_etudes_multipolygon z on
	st_dwithin(
		bd.geom,
		z.geom,
		1000
	)
where
	z.libelle like 'Zone d''étude pour la synthèse du site Natura 2000%'
group by
	bd.id_loc,
	bd.nom_comple,
	bd.localite,
	bd.geom,
	batsitetype.cat
order by
	bd.nom_comple,
	bd.localite /*
		 );
		 */
Markdown is supported
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!