Rev 756 | Rev 777 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
Créer une base de données tb_cel avant de lancer les scripts== Sommaire ==1) à propos de la mise à jour de juillet 20132) à propos de la table cel_references==============0) maj-struct-201307.sqlmise à jour des structure de table (les index notamment):1) maj-cleanup-201307.sqluniformisation des données (lon/lat, date, ...)2) TODO: maj-nom-ret.sqlTODO (pas sur) MAJ du référentiel pour les observation ayant un nom_ret_nn3) maj-referentiel-201307.sqlMAJ du référentiel pour les observation ayant un nom_ret_nn4) maj-referentiel-und-201307.sqlMAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom)5) referonosaure.sqlMAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa=== 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écessaire à l'export et à l'import sont massif* or les webservices s'appellent parfois récursivement, sont lents et inadaptésLa conséquence est que la construction d'une table dérivée de bdtfx/bdtxa contenantles 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 clefunique 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ésultats1.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ésultats1.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ésultatsMais 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 pasdes 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 bdtxa3) à 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ésultatsSELECT 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ésultatsSELECT 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ésultatNous 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`