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 $347 authored by fred.perso

Liste d'espèces et sous-espèces vraies depuis des données Visionature et TaxRef

Edited
true_mam_list.sql
Raw
WITH mammif_taxref AS (
    SELECT
        *
    FROM
        referentiel.taxref
    WHERE
        rang IN (
            'ES',
            'SSES' )
        AND group2_inpn = 'Mammifères' )
SELECT
    DISTINCT 
    o.id_species,
    o.name_species,
    st.latin_spec,
    CASE
        WHEN tx.cd_nom IS NULL THEN tx2.cd_nom
        ELSE tx.cd_nom
    END cd_nom_taxref,
    CASE
        WHEN tx.nom_vern IS NULL THEN tx2.nom_vern
        ELSE tx.nom_vern
    END nomvern_taxref,
    st.classe1,
    st.classe2,
    count (
        o.id_species )
    nbdata,
    (
        CASE
            WHEN (
                CASE
                    WHEN tx.cd_nom IS NULL THEN tx2.cd_nom
                    ELSE tx.cd_nom
                END )
            IS NULL THEN FALSE
            ELSE TRUE
        END ) ::boolean esp_vraie
FROM
    obs_mam o
    LEFT JOIN dico.statut_espece st ON st.id_species = o.id_species
    LEFT JOIN mammif_taxref tx ON st.latin_spec = tx.lb_nom
    LEFT JOIN mammif_taxref tx2 ON st.name_speci = tx2.nom_vern 
--WHERE tx.cd_nom=tx.cd_ref OR tx2.cd_nom=tx2.cd_ref
    -- (CASE
    -- 	WHEN tx.cd_nom IS NULL then tx2.cd_nom
    -- 	ELSE tx.cd_nom
    --     END) IS NOT NULL
    -- 
GROUP BY
    o.id_species,
    o.name_species,
    st.latin_spec,
    cd_nom_taxref,
    nomvern_taxref,
    st.classe1,
    st.classe2,
    esp_vraie;
Styling with Markdown is supported
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!