/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_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`; |
100,7 → 100,6 |
-- 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`) \ |
108,28 → 107,13 |
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_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); |
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 != ''; |
-- 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); |
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 != ''; |
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 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) |
SELECT count(1), "résultat théorique: 104422" FROM @dst; |
/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_01 b |
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 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_01 b SET |
UPDATE tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 b SET |
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur), |
c.nom_ret_nn = b.num_nom, |
c.nt = b.num_taxonomique, |
28,13 → 28,9 |
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 sans les restrictions sur famille et SUBSTRING_INDEX() |
-- 31524 avec les restrictions sur famille et SUBSTRING_INDEX() |
-- 31739 |
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, |
44,41 → 40,5 |
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 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 |
*/ |
-- 49 |
/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_01 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_02 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_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; |
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; |
-- 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,8 → 8,3 |
-- 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,6 → 20,3 |
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_01 (`nom_sci`(8)) |
CREATE INDEX i_nom_ret ON bdtfx_v1_02 (`nom_sci`(8)) |
2) regarder les num_nom_ret orphelins de taxon en BDTFX: |
SELECT * FROM bdtfx_v1_01 WHERE num_nom_retenu = 0; # 3960 |
SELECT * FROM bdtfx_v1_02 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_01 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_02 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_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 |
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 |
*/ |
-- 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_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 ) ); |
( 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 ) ); |
-- 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_01 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_02 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_01 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_02 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_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; |
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; |
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_01 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_02 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 |
/trunk/scripts/modules/cel/A_LIRE.txt |
---|
5,7 → 5,7 |
2) à propos de la table cel_references |
============== |
0) maj-struct-201307.sql |
0) maj-referentiel-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_01 b ON (b.nom_sci = c.nom_sel) |
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_02 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_01 b ON (b.nom_sci = c.nom_sel) |
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_02 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_01 b |
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_02 b |
WHERE ( |
b.nom_sci = c.nom_sel |
AND nom_sel IS NOT NULL AND nom_sel != '' |