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