Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 759 → Rev 760

/trunk/scripts/modules/cel/A_LIRE.txt
5,7 → 5,7
2) à propos de la table cel_references
==============
 
0) maj-referentiel-201307.sql
0) maj-struct-201307.sql
mise à jour des structure de table (les index notamment):
 
1) maj-cleanup-201307.sql
/trunk/scripts/modules/cel/redempteur.sql
42,7 → 42,7
 
Et effectuons une jointure sur bdtfx:
SELECT id_observation, nom_sel, b.num_nom, b.famille
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_02 b ON (b.nom_sci = c.nom_sel)
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.nom_sci = c.nom_sel)
WHERE (
nom_sel IS NOT NULL AND nom_sel != ''
AND (
68,7 → 68,7
 
Nous obtenons donc ainsi les 69 observations à mettre à jour:
SELECT id_observation, nom_sel, nom_ret, nom_ret_nn, nt, c.famille, b.num_nom, b.nom_sci, b.num_taxonomique, b.famille
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_02 b ON (b.nom_sci = c.nom_sel)
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.nom_sci = c.nom_sel)
WHERE (
nom_sel IS NOT NULL AND nom_sel != ''
AND (
91,7 → 91,7
 
CREATE TEMPORARY TABLE T_bis ( INDEX(`id_observation`)) AS
SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 b
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b
WHERE (
b.nom_sci = c.nom_sel
AND nom_sel IS NOT NULL AND nom_sel != ''
/trunk/scripts/modules/cel/cel_references.sh
76,7 → 76,7
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_02, 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_01, nvjfl_v2007 et bdtxa_v1_00';
 
-- tables temporaires
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
100,6 → 100,7
 
 
-- INSERTIONS
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')
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`) \
107,13 → 108,28
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 != '';
FROM bdtfx_v1_01 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);
 
 
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
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 != '';
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);
 
 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM isfan_v2013 b;
 
 
 
 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
 
SELECT count(1), "résultat théorique: 104422" FROM @dst;
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \
(SELECT COUNT(1) AS a FROM tb_eflore.bdtfx_v1_01 UNION ALL \
SELECT COUNT(1) AS a FROM tb_eflore.bdtxa_v1_00 UNION ALL \
SELECT COUNT(1) AS a FROM tb_eflore.isfan_v2013 \
) AS theorie, \
(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique;
-- bdtfx+bdtxa+isfan: 141181 (2013/07/23)
 
/trunk/scripts/modules/cel/referonosaure.sql
9,7 → 9,7
/*
-- SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille
SELECT id_observation, nom_ret, nom_ret_nn, nt, c.famille
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 b
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b
WHERE (
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0
AND nom_referentiel like 'bdtfx%'
19,7 → 19,7
*/
 
--- l'update ---
UPDATE tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 b SET
UPDATE tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b SET
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur),
c.nom_ret_nn = b.num_nom,
c.nt = b.num_taxonomique,
28,9 → 28,13
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0
AND nom_referentiel like 'bdtfx%'
AND nom_sel_nn = num_nom
AND LOWER(c.famille) = LOWER(b.famille)
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(b.nom_sci, ' ', 1)
);
-- 31739
-- 31739 sans les restrictions sur famille et SUBSTRING_INDEX()
-- 31524 avec les restrictions sur famille et SUBSTRING_INDEX()
 
 
UPDATE tb_cel.cel_obs c, tb_eflore.bdtxa_v1_00 a SET
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur),
c.nom_ret_nn = a.num_nom,
40,5 → 44,41
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0
AND nom_referentiel like 'bdtxa%'
AND nom_sel_nn = num_nom
AND LOWER(c.famille) = LOWER(a.famille)
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(a.nom_sci, ' ', 1)
);
-- 49
-- 49 sans les restrictions sur famille et SUBSTRING_INDEX()
-- 47 avec les restrictions sur famille et SUBSTRING_INDEX()
 
UPDATE tb_cel.cel_obs c, tb_eflore.isfan_v2013 a SET
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur),
c.nom_ret_nn = a.num_nom,
c.nt = a.num_taxonomique,
c.famille = a.famille
WHERE (
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0
AND nom_referentiel like 'isfan%'
AND nom_sel_nn = num_nom
);
 
 
/*
Pour observer les différences:
wdiff -w '$(tput bold;tput setaf 1)' -x '$(tput sgr0)' -y '$(tput bold;tput setaf 2)' -z '$(tput sgr0)' pre.log post.log | \
ansi2html.sh --palette=solarized | \
sed '/^[0-9]/{/span/!d}' > diff.html
 
# extract les familles ayant changé: sed '/^[0-9]/{/<\/span>$/!d}'
# lowercase toutes les familles: awk '{ NF=tolower($NF); print }'
 
 
# filtre sed: changements de famille "normaux"
/aceraceae.*sapindaceae/d
/scrophulariaceae.*plantaginaceae/d
/globulariaceae.*plantaginaceae/d
/Famille inconnue.*null/d
 
# changement "anormaux"
/rosaceae.*caprifoliaceae/d
/valerianaceae.*caprifoliaceae/d
*/
/trunk/scripts/modules/cel/maj-referentiel-201307.sql
21,12 → 21,12
DROP PROCEDURE IF EXISTS majreferentiel;
 
CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b UNION ALL \
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b UNION ALL \
SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a;
 
/* Donc nous JOINons:
-- INNER JOIN sur bdtfx: 69719
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM cel_obs c INNER JOIN tb_eflore.bdtfx_v1_02 b ON (b.num_nom = c.nom_sel_nn) WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel_nn IS NOT NULL;
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.num_nom = c.nom_sel_nn) WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel_nn IS NOT NULL;
-- INNER JOIN sur bdtxa: 79471 dont 9752 en dup (bdtxa + bdtfx), eg: 1005047
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM cel_obs c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel_nn IS NOT NULL;
*/
/trunk/scripts/modules/cel/maj-struct-201307.sql
8,3 → 8,8
 
 
-- TODO: créer une clef unique sur (id_utilisateur, ordre)
-- suppress dup:
-- SELECT ce_utilisateur, ordre FROM cel_obs GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;
-- >= 115 au 22/07/2013
 
-- CREATE UNIQUE INDEX couple_user_ordre ON tb_cel.cel_obs(ce_utilisateur, ordre);
/trunk/scripts/modules/cel/maj-cleanup-201307.sql
20,3 → 20,6
c.nt = NULL,
c.famille = NULL;
WHERE nom_sel = '' OR nom_sel IS NULL;
 
-- problème n°1
UPDATE tb_cel.cel_obs SET nom_referentiel = 'bdtxa' WHERE lieudit = 'Grenade';
/trunk/scripts/modules/cel/maj-nom-ret.sql
6,25 → 6,25
nom_ret == 0 est VALIDE (car bdtfx.num_nom_retenu == 0 est valide) [ 3800 nom_retenu "orphelins" de taxon ]
 
1) créer un index pour les jointures:
CREATE INDEX i_nom_ret ON bdtfx_v1_02 (`nom_sci`(8))
CREATE INDEX i_nom_ret ON bdtfx_v1_01 (`nom_sci`(8))
2) regarder les num_nom_ret orphelins de taxon en BDTFX:
SELECT * FROM bdtfx_v1_02 WHERE num_nom_retenu = 0; # 3960
SELECT * FROM bdtfx_v1_01 WHERE num_nom_retenu = 0; # 3960
3) regarder les num_nom_ret orphelins de taxon en BDTFX:
SELECT * FROM bdtxa_v1_00 WHERE num_nom_retenu = 0; # 0
4) regarder les orphelins équivalents dans cel_obs:
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740
 
Donc ceux dont le nom_ret à été POSTé manuellement et qui matchent le nom_sci de BDTFX : on les conserve.
Mais les autres, qui ont un nom_ret probablement erroné et un nom_ret_nn à 0, on NULLify les données censées être (correctement) autogénérées !
 
Cela concerne:
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_02 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_01 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960
*/
-- D'où la requête :
UPDATE cel_obs SET nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL WHERE id_observation IN
( SELECT id_observation FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_02 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) );
( SELECT id_observation FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_01 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) );
 
-- TODO
-- UPDATE cel_obs SET nom_ret_nn = NULL WHERE nom_ret_nn = 0;
/trunk/scripts/modules/cel/maj-referentiel-und-201307.sql
14,7 → 14,7
-- obtient le nombre de matches sur nom_sel = nom_sci
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT)
BEGIN
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req;
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_01 b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req;
END
|
-- retourne les paramètres d'une match
21,7 → 21,7
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
BEGIN
SELECT * INTO param1, param2, param3, param4, param5 FROM
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b WHERE nom_sci = _nom
UNION ALL
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req;
END
31,7 → 31,7
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est ''
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT)
BEGIN
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req;
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_01 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req;
END
|
-- retourne les paramètres d'une match
38,7 → 38,7
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
BEGIN
SELECT * INTO param1, param2, param3, param4, param5 FROM
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != ''
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != ''
UNION ALL
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req;
END