| 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`; |