New file |
0,0 → 1,117 |
Créer une base de données tb_cel avant de lancer les scripts |
|
== Sommaire == |
1) à propos de la mise à jour de septembre 2013 |
2) à propos de la table cel_references |
============== |
|
1) cel_references.sql |
création de la table `cel_references` |
|
2) maj-struct-201307.sql |
mise à jour des structure de table (les index notamment): |
|
3) maj-cleanup-201307.sql |
uniformisation des données (lon/lat, date, ...) |
et des NULL vs 0 (pour nom_sel_nn et nom_ret_nn) |
|
4) fix-utilisateur-32.sql |
|
5) dedup-ordre-201307.sql |
|
6) maj-referentiel-201307.sql |
fix le référentiel pour les observation ayant un nom_sel_nn sans nom_referentiel en se |
basant sur une match exact de CONCAT(nom_sci, auteur) parmi bdtfx, bdtxa et isfan |
|
7) referonosaure.sql |
MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa/isfan |
|
===== |
8) TODO: maj-nom-ret.sql |
TODO (pas sûr) MAJ du référentiel pour les observation ayant un nom_ret sans nom_ret_nn mais dont le nom_ret |
ne match pas le nom_sci en BDTFX (car en BDTFX nom_ret_nn peut être égal à 0 !) |
|
9) maj-referentiel-und-201307.sql |
MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom) |
|
|
|
|
|
|
|
|
=== 2: À propos de la table cel_references === |
Celle-ci existe car: |
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...) |
* les données nécessaires à l'export et à l'import sont massives |
* 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/isfan contenant |
les informations utiles pour CEL s'avère nécessaire. |
cel_references.sql construit une telle table. |
|
Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation: |
|
1) Détermination des noms vernaculaires meilleurs et uniques: |
|
Ce sont ceux qui ont le num_statut le plus élevé 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'existent que pour bdtfx[nvjfl], bdtxa[nva], 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 efficaces 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 KEY de `cel_references` |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |