Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 740 → Rev 741

/trunk/scripts/modules/cel/A_LIRE.txt
1,0 → 0,0
Créer une base de données tb_cel avant de lancer les scripts
Créer une base de données tb_cel avant de lancer les scripts
 
 
À propos de la table cel_references: celle-ci existe car:
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...)
* les données nécessaire à l'export et à l'import sont massif
* or les webservices s'appellent parfois récursivement, sont lents et inadaptés
 
La conséquence est que la construction d'une table dérivée de bdtfx/bdtxa contenant
les informations utiles pour CEL s'avère nécessaire.
cel_references.sh construit une telle table.
 
Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation:
 
1) Détermination des nom vernaculaires meilleurs et uniques:
 
Ce sont ceux qui ont le num_statut le plus élevés pour un num_taxon donné dans nvjfl_v2007.
Plusieurs méthodes sont exposées ci-dessous, sachant que le couple (référentiel, num_nom) est la clef
unique de cel_references.
Il existe à ce jour 16146 nom communs français distincts, 12312 num_taxon pour code_lang = fra et aucun num_statut NULL en français.
1.1:
SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut, n2.num_statut FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon) WHERE n.num_taxon < 32 AND n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n2.num_statut) LIMIT 100;
# 12311 résultats
 
1.2:
SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n INNER JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' GROUP BY n.num_taxon;
# 2680 résultats
 
1.3:
SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' AND n2.num_statut IS NOT NULL GROUP BY num_taxon;
# 2680 résultats
Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
 
 
 
2) à propos de l'insertion dans cel_references proprement dit:
Le modèle simplifié théorique de base est le suivant:
 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, MAX(n.num_statut) FROM bdtfx_v1_01 b LEFT JOIN nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom \
UNION \
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, NULL FROM bdtxa_v1_00 b LEFT JOIN nva_v2013_06 n ON (b.num_tax = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom \
UNION \
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
 
Mais évidemment, les noms communs n'existe que pour bdtfx, de même que les données baseflor/baseveg. Plusieurs tables temporaires sont donc nécessaires en particulier puisque toutes les colonnes n'ont pas
des indexes adaptés pour effectuer des JOIN efficace dans le cadre de ce script d'intégration particulier.
 
Une version plus aboutie, mais spécifique à bdtfx, après création préalable de T_nvjfl_v2007, était la suivante (présence des noms communs):
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon );
 
 
À noter:
SELECT b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci FROM bdtfx_v1_01 b where b.num_taxonomique = '';
# 3968, c'est à dire des num_taxon vides, pourtant INDEX et NOT NULL.
Idem pour bdtxa
 
 
3) à propos de baseveg/baseflor:
Note au 16/07/2013: les schémas sont susceptibles de changer à l'avenir.
La jointure entre bdtfx et baseflor se fait sur le référentiel ("bdtfx") et num_nom.
À partir de là nous disposons d'un catminat_code qui correspond au code_catminat dans baseveg, afin d'obtenir le syntaxon.
Quelques exemples:
 
SELECT code_catminat, syntaxon, lumiere, hum_atmos, temperature, oceanite, ph_sol, hum_edaph, texture_sol FROM baseveg_v2013_01_09;
SELECT * from baseflor_v2012_12_31 where cle = 1174;
 
SELECT * from baseveg_v2013_01_09 where code_catminat = '05/3.0.1.0.2' AND niveau = 'ALL';
# 7 résultats
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat) WHERE f.BDNT = "BDTFX" and f.cle = 1174;
# 7 résultats
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL') WHERE f.BDNT = "BDTFX" and f.cle = 1174;
# 1 résultat
Nous utilisons v.niveau = ALL pour nous assurer la présence d'un seul f.num_nomen dans `T_basevegflor` et donc assurer l'unicité de la PRIMARY KET de `cel_references`
/trunk/scripts/modules/cel/cel_references.sh
1,21 → 1,32
#!/bin/bash
 
# cf l'historique SVN pour plus de commentaires
# lancer:
# bash cel_references.sh |mysql --show-warnings tb_eflore
DB='`tb_cel`.`cel_references`';
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;" "$0"
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;g" "$0"
exit
 
-- DEBUT DE SCRIPT SQL substitué: ne pas retirer, ne pas modifier cette ligne
-- ci dessous ne doit exister que du SQL valide
 
-- TODO:
-- fix référentiel: suppression n° de version et uniformisation
-- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
/*
TODO:
* fix référentiel: suppression n° de version et uniformisation
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
* ajout INDEX nom_referentiel(5) sur cel_obs
* ajout INDEX catminat_code sur baseflor_v2012_12_31
* ajout INDEX num_taxon sur nva_v2013_06
* fix date: set NULL pour les dates dans le futur
SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '')
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)
 
-- fix date: NULL pour les dates dans le futur
-- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
CREATE INDEX i_nom_referentiel ON tb_cel.cel_obs (`nom_referentiel(5)`);
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
*/
 
 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
-- SET @destdb = 'tb_cel';
-- SET @desttable = 'cel_references';
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
29,7 → 40,7
`num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
 
-- bdtfx + nvjfl_v2007 + nva_v2013_06
`num_taxon` int(9) NOT NULL COMMENT 'depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun)',
`num_taxon` int(9) NOT NULL COMMENT "depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun), les noms non-associés ne sont pas intégrés pour l\'instant",
 
-- bdtfx
`nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
43,46 → 54,33
-- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
-- mais NULL à cause de nva
`nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
PRIMARY KEY (`referentiel`, `num_nom`)
 
-- baseflor_v2012_12_31
`catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis baseflor_v2012_12_31',
`ve_lumiere` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_temperature` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_continentalite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_humidite_atmos` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_humidite_edaph` int(2) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_reaction_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_nutriments_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_salinite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_texture_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
`ve_mat_org_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
 
-- baseveg_v2013_01_09
`syntaxon` varchar(255) NULL COMMENT 'depuis baseveg_v2013_01_09',
PRIMARY KEY (`referentiel`, `num_nom`),
INDEX (`referentiel`(5)),
INDEX (`num_nom`),
INDEX (`num_taxon`)
) ENGINE=MyISAM \
DEFAULT CHARSET=utf8 \
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_01, nvjfl_v2007 et bdtxa_v1_00';
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_02, nvjfl_v2007 et bdtxa_v1_00';
 
-- tables temporaires
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
 
/*
way 1: theory
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, MAX(n.num_statut) FROM bdtfx_v1_01 b LEFT JOIN nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom \
UNION \
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, NULL FROM bdtxa_v1_00 b LEFT JOIN nva_v2013_06 n ON (b.num_tax = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom;
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
*/
 
 
/*
Détermination des nom vernaculaires meilleurs et uniques:
 
way 1:
-- SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut, n2.num_statut FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon) WHERE n.num_taxon < 32 AND n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n2.num_statut) LIMIT 100;
-- 12311
-- # distinct=
 
way 2:
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n INNER JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' GROUP BY n.num_taxon;
-- 2680
 
way 2":
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' AND n2.num_statut IS NOT NULL GROUP BY num_taxon;
-- 2680, Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
 
 
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français
*/
 
 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
-- ( SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) );
( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon HAVING n.num_statut = MAX(n.num_statut) );
92,9 → 90,28
( SELECT n.num_taxon, n.nom_vernaculaire FROM nva_v2013_06 n WHERE n.code_langue = 'fra' /* DB pb */ AND n.num_taxon IS NOT NULL /* /DB pb */ GROUP BY n.num_nom); -- aggrégat arbitraire car pas de num_statut
 
 
-- JOIN ON num_taxon_originel car INDEX
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
CREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS \
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol, \
v.syntaxon \
FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL' AND v.syntaxon IS NOT NULL) WHERE f.BDNT = "BDTFX" \
GROUP BY f.num_nomen, f.num_taxon; -- group by car plusieurs couple (f.num_nomen, f.num_taxon) peuvent exister dans baseveg_v2013_01_09 or num_nom est PRIMARY dans cel_references
 
 
-- INSERTIONS
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \
`catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \
`ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
 
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
FROM bdtfx_v1_02 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen) WHERE b.num_taxonomique != '';
 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
UNION \
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtxa_v1_00 b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon);
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtxa_v1_00 b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon) \
WHERE b.num_tax IS NULL AND b.num_tax != '';
 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;