Retour : Page Principale > sommaire applications botaniques > CEL

Requêtes SQL types (nouveau CEL)

ATTENTION (2020-03-30) - Ces requêtes doivent être lancées sur la bonne BD, sur le serveur de prod. Vérifier de temps en temps auprès de l'équipe info si le serveur et la base n'ont pas changé (et actualiser les infos ci-dessous + la date de màj).

Actuellement : Le serveur est festuca-prod / La base est tb_new_cel. Demander à l'équipe info les logins de l'authentification htaccess + le mdp du compte lecteur.

Attention si vous transmettez ces données, enlevez les emails et les coordonnées lat_privée et long_privée

Observations / projet

Nombre d'observations total

Nombre d'obs publiques associées au projet "messicoles"

SELECT count(*) FROM cel_export_total WHERE transmission = 1 AND programme like "%messicoles%"

Nombre d'observations par an

Nombre d'obs publiques par an associées au projet "messicoles"

SELECT year(date_creation), count(*) FROM cel_export_total
WHERE transmission = 1 AND programme like "%messicoles%" 
GROUP BY year(date_creation)

Nombre d'obs publiques associées au projet "mission flore" toutes confondues par an

SELECT year(date_creation), count(*) FROM cel_export_total WHERE transmission = 1 AND mots_cles_texte LIKE "%missions-flore%" GROUP BY year(date_creation);

Nombre d'observations pour une année spécifique

Nombre d'obs publiques en 2019 associées au projet "sauvages"

SELECT year(date_creation), count(*) FROM cel_export_total
WHERE transmission = 1 AND programme like "sauvages" AND year(date_creation) = 2019

Nombre d'obs publiques associées au projet "mission flore" tout confondu par an (ici 2022)

SELECT count(*) FROM cel_export_total WHERE transmission = 1 AND mots_cles_texte LIKE "%missions-flore%" AND year (date_creation)= 2022;

Lichens go

SELECT count(distinct ce_utilisateur) FROM cel_export_total WHERE transmission = 1 AND programme = "tb_lichensgo"  AND year(date_creation) = 2021
SELECT count(distinct zone_geo) FROM cel_export_total WHERE transmission = 1 AND programme = "tb_lichensgo" AND year(date_creation) = 2021 ORDER BY `cel_export_total`.`zone_geo` ASC 
SELECT count(*) FROM cel_export_total WHERE transmission = 1 AND programme = "tb_lichensgo" AND year(date_creation) = 2021 and nom_referentiel = "taxreflich"

Nombre d'observations par observateur

Nombre d'obs publiques par utilisateur associées au projet "messicoles"

SELECT `courriel_utilisateur`, count(*) FROM cel_export_total
WHERE transmission = 1 AND programme like "%messicoles%" 
GROUP BY `courriel_utilisateur`

Nombre d'observations par an pour une région

Nombre d'obs publiques par an associées au projet "messicoles" pour les départements de la région Occitanie

SELECT year(date_creation), count(*) FROM cel_export_total
WHERE `transmission` = 1 AND dept IN (09,11,12,30,34,46,48,65,66,81,31,82,32) AND programme like "%messicoles%"
GROUP BY year(date_creation)

Nombre d'observations par observateur en une année et pour une région

Nombre d'obs publiques par utilisateur créées en 2019 et associées au projet "messicoles" pour les départements de la région Occitanie

SELECT `courriel_utilisateur`, count(*) FROM cel_export_total
WHERE transmission` = 1 AND dept IN (09,11,12,30,34,46,48,65,66,81,31,82,32) AND year(date_creation) = 2019 AND programme like "%messicoles%"
GROUP BY `courriel_utilisateur`

Observations / sites

observations / coordonnées

SELECT count(DISTINCT geometry) FROM cel_export_total WHERE programme = "messicoles" and transmission = 1

observations / communes

SELECT count(DISTINCT zone_geo) FROM cel_export_total WHERE programme = "messicoles" and transmission = 1

Observations ministères

attention si vous transmettez ces données, enlevez les emails et les coordonnées lat_privée et long_privée

Observations publiques sensibles

SELECT * FROM `cel_export_total` WHERE transmission = 1 and `nom_referentiel` = "BDTFX" and `nom_sel_nn` in (SELECT `num_nom_retenu` FROM tb_eflore.`sptb_v2020` WHERE cd_type_statut like "sens%")

mettre cd_type_statut = "SENSDEP" pour les sensibles départementales / SENSNAT pour les sensibles nationales / SENSREG pour les sensibles régionales

SELECT distinct nom_sel_nn, nom_sel FROM `cel_export_total` WHERE transmission = 1 and `nom_referentiel` = "BDTFX" and `nom_sel_nn` in (SELECT `num_nom_retenu` FROM tb_eflore.`sptb_v2020` WHERE cd_type_statut like "sens%")

Trognes

Tous les programmes / France par an

nb observation /observateur

SELECT year(date_creation), count(*), count(distinct `courriel_utilisateur`) FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by year(date_creation);

top 5

SELECT `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by concat(programme, nom_sel) order by nb desc limit 5;

Par programme par an

nb observation /observateur

SELECT programme, year(date_creation) as annee, count(*) as nb_observations, count(distinct `courriel_utilisateur`) as nb_observateurs FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by programme, year(date_creation);

top especes (lignes à supprimer pour top 5 par programme)

SELECT programme, `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by concat(programme, nom_sel) order by programme, nb desc;

TOP 5 des espèces + observées sauvages de ma rue 2023

SELECT distinct `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme = "sauvages" and year(date_observation) = 2023 group by `nom_sel` order by nb desc limit 5;

TOP 5 des contributeurs sauvages de ma rue 2023

SELECT count(distinct `courriel_utilisateur`) as nb FROM `cel_export_total` WHERE transmission = 1 and programme = "sauvages" and year(date_observation) = 2023 group by `nom_sel` order by nb desc limit 5;

TOP 10 des villes qui recensent le plus sauvages de ma rue 2023

SELECT distinct ce_zone_geo, `zone_geo`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme = "sauvages" and year(date_observation) = 2023 group by `zone_geo` order by nb desc limit 10;

nb de familles botaniques sauvages de ma rue 2023

SELECT count(distinct famille) as nb FROM `cel_export_total` WHERE transmission = 1 and programme = "sauvages" and year(date_observation) = 2023;

Observation PNA métropole (valable avec les espèces 2020)

SELECT * FROM `cel_export_total` WHERE transmission = 1 and `nom_referentiel` = "BDTFX" and `nom_sel_nn` in (4449, 6509, 7890, 9842, 9849, 15657, 25398, 36040, 39052, 39083, 39115, 39132, 39494, 40217, 60800, 72288, 76034, 79638, 80581, 830, 837, 864, 3183, 3461, 4439, 5045, 5600, 7266, 9731, 9732, 11102, 11467, 11483, 11637, 11648, 12170, 12364, 12370, 12372, 14897, 15139, 15712, 18318, 18684, 18689, 18695, 18700, 18701, 20318, 21826, 21864, 28677, 29258, 29309, 29386, 30202, 30246, 35207, 35211, 35617, 38199, 38202, 39521, 39744, 39788, 43117, 44066, 44091, 44107, 46423, 46489, 47775, 51120, 51124, 51400, 54767, 56198, 61292, 61723, 64053, 64061, 64197, 64461, 65530, 65684, 67864, 68060, 68582, 69960, 69973, 69983, 69986, 70015, 70031, 70059, 70467, 70655, 70662, 70664, 70695, 71492, 71720, 71837, 72065, 74941, 74943, 74955, 74963, 75008, 75124, 75154, 75276, 75277, 75330, 75380, 75391, 75443, 75451, 75452, 118861)

SELECT distinct `nom_sel`, `nom_sel_nn` FROM `cel_export_total` WHERE transmission = 1 and `nom_referentiel` = "BDTFX" and `nom_sel_nn` in (4449, 6509, 7890, 9842, 9849, 15657, 25398, 36040, 39052, 39083, 39115, 39132, 39494, 40217, 60800, 72288, 76034, 79638, 80581, 830, 837, 864, 3183, 3461, 4439, 5045, 5600, 7266, 9731, 9732, 11102, 11467, 11483, 11637, 11648, 12170, 12364, 12370, 12372, 14897, 15139, 15712, 18318, 18684, 18689, 18695, 18700, 18701, 20318, 21826, 21864, 28677, 29258, 29309, 29386, 30202, 30246, 35207, 35211, 35617, 38199, 38202, 39521, 39744, 39788, 43117, 44066, 44091, 44107, 46423, 46489, 47775, 51120, 51124, 51400, 54767, 56198, 61292, 61723, 64053, 64061, 64197, 64461, 65530, 65684, 67864, 68060, 68582, 69960, 69973, 69983, 69986, 70015, 70031, 70059, 70467, 70655, 70662, 70664, 70695, 71492, 71720, 71837, 72065, 74941, 74943, 74955, 74963, 75008, 75124, 75154, 75276, 75277, 75330, 75380, 75391, 75443, 75451, 75452, 118861)

Observation PNA réunion (valable avec les espèces 2020)

SELECT *  FROM `cel_export_total` WHERE nom_sel_nn in (22, 9004, 1683, 2921, 2953, 2985, 9203, 4104, 4843, 9984, 6069) and nom_referentiel = "BDTRE" and transmission = 1

Observateurs / projet

Nombre d'observateurs

Nombre d'utilisateurs ayant saisi au moins une obs publique associée au projet "messicoles"
NB : la requête est faite sur l'email et pas sur l'user_id car certains user ont un id null (contributeurs non inscrits)

SELECT count(distinct `courriel_utilisateur`) FROM cel_export_total WHERE transmission = 1 AND programme like "%messicoles%"

Nombre d'observateurs par an

Nombre d'utilisateurs par an ayant saisi au moins une obs publique associée au projet "messicoles"

SELECT year(date_creation), count(distinct `courriel_utilisateur`) FROM cel_export_total WHERE transmission = 1 AND programme like "%messicoles%" 
GROUP BY year(date_creation)

Liste des emails utilisateurs

Liste des emails des utilisateurs ayant saisi au moins une obs publique associée au projet "messicoles"

SELECT distinct(`courriel_utilisateur`) FROM cel_export_total WHERE transmission = 1 AND programme like "%messicoles%"
GROUP BY `courriel_utilisateur`SELECT distinct `courriel_utilisateur`, ce_utilisateur, count(`id_observation`) FROM cel_export_total WHERE transmission = 1 AND programme like "%messicoles%" group by `courriel_utilisateur` ORDER BY `count(``id_observation``)` DESC

Liste des emails utilisateurs pour une région

Liste des emails des utilisateurs ayant saisi au moins une obs publique associée au projet "messicoles" pour les départements de la région Occitanie

SELECT distinct(`courriel_utilisateur`) FROM cel_export_total
WHERE `transmission` = 1 AND dept IN (09,11,12,30,34,46,48,65,66,81,31,82,32) AND programme like "%messicoles%"
GROUP BY `courriel_utilisateur`

Liste des emails utilisateurs pour une année

Liste des emails des utilisateurs ayant saisi au moins une obs publique associée au projet "messicoles" en 2019

SELECT distinct(`courriel_utilisateur`) FROM cel_export_total
WHERE `transmission` = 1 AND year(date_creation) = 2019 AND programme like "%messicoles%"
GROUP BY `courriel_utilisateur`

Espèces / projet

Nombre d'espèces par rue par an

>>> à conserver ? (si oui, à adapter à la nouvelle base)

SELECT distinct `valeur`, count(*) FROM `cel_obs_etendues` e WHERE `cle` = 'latitudeDebutRue' AND valeur != ""  AND `id_observation` in (SELECT id_observation FROM cel_obs WHERE year(`date_creation`) = 2016) GROUP BY `valeur`

Nombre d'espèces total

Nombre total d'espèces différentes (num nom retenu + référentiels) saisies dans le cadre du projet "messicoles"

SELECT count(DISTINCT(concat(nom_ret_nn, nom_referentiel))) FROM `cel_export_total`
WHERE transmission = 1 AND programme like "%messicoles%"

Nombre d'espèces par an

Nombre d'espèces différentes (num nom retenu + référentiels) saisies par an dans le cadre du projet "messicoles"

SELECT year(date_creation), count(DISTINCT(concat(nom_ret_nn, nom_referentiel))) as nb_especes FROM `cel_export_total`
WHERE transmission = 1 AND programme like "%messicoles%"
GROUP BY year(date_creation)

Nombre d'espèces par observateur

Nombre d'espèces différentes (num nom retenu + référentiels) saisies par observateur dans le cadre du projet "messicoles"

SELECT courriel_utilisateur, count(DISTINCT(concat(nom_ret_nn, nom_referentiel))) as nb_especes FROM `cel_export_total`
WHERE transmission = 1 AND programme like "%messicoles%"
GROUP BY courriel_utilisateur

Top 10 des espèces du mois de mars

SELECT distinct `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme = "sauvages" and year(date_observation) = 2022 and month(date_observation) = 03 group by `nom_sel` order by nb desc limit 10

Flora data

Nombre d'observations saisies avec le Carnet en Ligne par an

Nombre d'observations (publiques et privées) par an saisies avec le CEL
Autres valeurs possibles pour source : "widget" / " PlantNet" / "autre"

SELECT year(`date_creation`), count(*) FROM `cel_export_total` WHERE `source` = "CEL"  
GROUP BY year(`date_creation`)

Nombre d'observations publiques par an qui sont protocolées (= appartiennent à un prog. de sp

SELECT year(`date_creation`), count(*) FROM cel_export_total
WHERE transmission = 1 AND programme IS NOT NULL
GROUP BY year(`date_creation`)

Nombre d'observations standards saisies par an

Nombre d'observations publiques standards par an
(NB : la requête peut aussi être faite directement dans la table cel_export, qui ne contient que les données publiques et standards)

SELECT year(`date_creation`), count(*) FROM cel_export_total
WHERE transmission = 1 AND donnees_standard = 1
GROUP BY year(`date_creation`)

Nombre de données de grade 4 par an sur les données totales - Réseau de vérificateurs

(NB : Il faut avant ça lancer la requête pour actualiser les données de grade 4 dans la bdd)

SELECT year(`date_transmission`), count(*), sum(case grade when 4 then 1 else 0 end) as nb_grd4, sum(case validation_identiplante when 1 then 1 else 0 end) as nb_val 
FROM tb_new_cel.cel_export_total WHERE transmission = 1 GROUP BY year(`date_transmission`)

Nombre de données de grade 4 par an en Occitanie - Réseau de vérificateurs

(NB : Il faut avant ça lancer la requête pour actualiser les données de grade 4 dans la bdd)

SELECT year(`date_transmission`), count(*), sum(case grade when 4 then 1 else 0 end) as nb_grd4, sum(case validation_identiplante when 1 then 1 else 0 end) as nb_val 
FROM tb_new_cel.cel_export_total WHERE transmission = 1 
AND dept IN (09,11,12,30,34,46,48,65,66,81,31,82,32) GROUP BY year(`date_transmission`)

Nombre d'observateurs par an

Nombre d'utilisateurs ayant saisi au moins une observation publique (tous projets et tous outils confondus) par an
NB : la requête est faite sur l'email et pas sur l'user_id car certains user ont un id null (contributeurs non inscrits des widgets et de plantnet)

SELECT year(`date_creation`), count(DISTINCT courriel_utilisateur) FROM cel_export_total WHERE `transmission` = '1'
GROUP BY year(`date_creation`)

Référentiels d'Afrique

Nombres d'observations par an

Nombre d'observations publiques saisies (CEL + widgets) par an, dans les référentiels d’Afrique

SELECT year(`date_creation`), count(*)  FROM `cel_export_total`
WHERE transmission = 1 AND (`source` = "CEL" OR `source` = "widget") AND (`nom_referentiel` like '%isfan%' OR `nom_referentiel` like '%apd%')
GROUP BY year(`date_creation`)

Nombres d'utilisateurs par an

Nombre d'utilisateurs par an ayant saisi (CEL + widgets) au moins une observation publiques, dans les référentiels d’Afrique

SELECT year(`date_creation`), count(DISTINCT courriel_utilisateur) FROM `cel_export_total`
WHERE transmission = 1 AND (`source` = "CEL" OR `source` = "widget") AND (`nom_referentiel` like '%isfan%' OR `nom_referentiel` like '%apd%')
GROUP BY year(`date_creation`)

Observatoires des Trognes

Nbr d'observations et d'observateurs par an, tout observatoire confondu

SELECT year(date_creation), count(*), count(distinct `courriel_utilisateur`) FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by year(date_creation);

Top 5 des espèces observé, tout observatoire confondu

SELECT `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by concat(programme, nom_sel) order by nb desc limit 5;

Nbr d'observations et d'observateurs par an, par observatoire

SELECT programme, year(date_creation) as annee, count(*) as nb_observations, count(distinct `courriel_utilisateur`) as nb_observateurs FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by programme, year(date_creation);

Top 5 des espèces observé, par observatoire

SELECT programme, `nom_sel`, `nom_sel_nn`, count(*) as nb FROM `cel_export_total` WHERE transmission = 1 and programme in ("arbres-tetards", "arbres-tetards-gatine", "tetards-avesnois", "tetards-basques", "trognes-loire-yonne") group by concat(programme, nom_sel) order by programme, nb desc;

Export de toutes les données publiques sauvages

select guid AS `Identifiant unique`,
`id_observation` AS `Identifiant`, 
`nom_sel` AS `Espèce`,`nom_sel_nn` AS `Numéro nomenclatural`,`nom_ret` AS `Nom retenu`,
`nom_ret_nn` AS `Numéro nomenclatural nom retenu`,`famille` AS `Famille`, `nom_referentiel` AS `Référentiel taxonomique`,
`certitude` AS `Certitude`,
latitude AS `Latitude`, longitude AS `Longitude`,
`pays` AS `Pays`, `zone_geo` AS `Commune`,
`ce_zone_geo` AS `Identifiant Commune`,
`lieudit` AS `Lieu-dit`,
efoside.value as `Côté de la rue`,
efolatstart.value as `Latitude de début de la rue`,
efolatend.value as `Latitude de fin de la rue`,
efolongstart.value as `Longitude de début de la rue`,
efolongend.value as `Longitude de fin de la rue`,
efoaddress.value as `Adresse`,
`milieu` AS `Milieu`, station AS `Station`,
`date_observation` AS `Date`,
`commentaire` AS `Notes`,
programme AS `Programme de sciences participatives ou observatoire citoyen`,
`mots_cles_texte` AS `Mots Clés`,
`pseudo_utilisateur` AS `Auteur`,
`url_identiplante` AS `Lien vers l'observation sur IdentiPlante`,
`images` AS `Image(s)`,
`date_creation` AS `Date Création`,`date_modification` AS `Date Modification`,
`date_transmission` AS `Date Transmission`
from `cel_export_total` cet
join extended_field_occurrence efoside on efoside.occurrence_id = cet.id_observation and efoside.extended_field_id = 62
join extended_field_occurrence efolatstart on efolatstart.occurrence_id = cet.id_observation and efolatstart.extended_field_id = 129
join extended_field_occurrence efolatend on efolatend.occurrence_id = cet.id_observation and efolatend.extended_field_id = 130
join extended_field_occurrence efolongstart on efolongstart.occurrence_id = cet.id_observation and efolongstart.extended_field_id = 131
join extended_field_occurrence efolongend on efolongend.occurrence_id = cet.id_observation and efolongend.extended_field_id = 132
join extended_field_occurrence efoaddress on efoaddress.occurrence_id = cet.id_observation and efoaddress.extended_field_id = 122
where programme = "sauvages" and 
`transmission` = '1';

Lauréat du défi photo

SELECT ce_image, user_pseudo, nom_sel, url, sum(valeur) FROM `del_image_vote` join tb_new_cel.photo on ce_image = id join del_image i on ce_image = id_image join del_observation on ce_observation=id_observation WHERE i.mots_cles_texte like "%dune%" and ce_protocole = 4 group by ce_image ORDER BY `sum(valeur)` DESC;

Reqêtes Manon

accessibles ici, à recopier sur wiki : https://docs.google.com/document/d/1jd6QO6oqEWKR0EWFgQo2vpx6G7ReeaYBhlS6JcaibhI/edit

Besoin d'autres requêtes ?

Pour les non informaticien·es, indiquez ici les requêtes dont vous avez besoin afin qu'on les intègre à cette page et/ou qu'on vous explique comment faire à partir des requêtes ci-dessus.

- le nombre de "stations" ou parcelles inventoriées dans l'odm

Déjà membre

Nouveau membre

Requêtes SQL types (nouveau CEL)

Observations / projet

Nombre d'observations total

Nombre d'observations par an

Nombre d'observations pour une année spécifique

Lichens go

Nombre d'observations par observateur

Nombre d'observations par an pour une région

Nombre d'observations par observateur en une année et pour une région

Observations / sites

Observations ministères

Observations publiques sensibles

Trognes

Tous les programmes / France par an

Par programme par an

Observation PNA métropole (valable avec les espèces 2020)

Observation PNA réunion (valable avec les espèces 2020)

Observateurs / projet

Nombre d'observateurs

Nombre d'observateurs par an

Liste des emails utilisateurs

Liste des emails utilisateurs pour une région

Liste des emails utilisateurs pour une année

Espèces / projet

Nombre d'espèces par rue par an

Nombre d'espèces total

Nombre d'espèces par an

Nombre d'espèces par observateur

Top 10 des espèces du mois de mars

Flora data

Nombre d'observations saisies avec le Carnet en Ligne par an

Nombre d'observations publiques par an qui sont protocolées (= appartiennent à un prog. de sp

Nombre d'observations standards saisies par an

Nombre de données de grade 4 par an sur les données totales - Réseau de vérificateurs

Nombre de données de grade 4 par an en Occitanie - Réseau de vérificateurs

Nombre d'observateurs par an

Référentiels d'Afrique

Nombres d'observations par an

Nombres d'utilisateurs par an

Observatoires des Trognes

Export de toutes les données publiques sauvages

Lauréat du défi photo

Reqêtes Manon

Besoin d'autres requêtes ?