Subversion Repositories eFlore/Projets.eflore-projets

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
741 raphael 1
Créer une base de données tb_cel avant de lancer les scripts
2
 
755 raphael 3
== Sommaire ==
4
1) à propos de la mise à jour de juillet 2013
5
2) à propos de la table cel_references
6
==============
741 raphael 7
 
822 raphael 8
1) cel_references.sql
9
création de la table `cel_references`
10
 
11
2) maj-struct-201307.sql
755 raphael 12
mise à jour des structure de table (les index notamment):
13
 
822 raphael 14
3) maj-cleanup-201307.sql
755 raphael 15
uniformisation des données (lon/lat, date, ...)
16
 
822 raphael 17
4) fix-utilisateur-32.sql
777 raphael 18
 
822 raphael 19
5) dedup-ordre-201307.sql
20
=====
21
6) TODO: maj-nom-ret.sql
865 raphael 22
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
23
 ne match pas le nom_sci en BDTFX (car en BDTFX nom_ret_nn peut être égal à 0 !)
755 raphael 24
 
822 raphael 25
7) maj-referentiel-201307.sql
865 raphael 26
fix le référentiel pour les observation ayant un nom_sel_nn sans nom_referentiel
755 raphael 27
 
822 raphael 28
8) maj-referentiel-und-201307.sql
755 raphael 29
MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom)
30
 
822 raphael 31
9) referonosaure.sql
756 raphael 32
MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa
755 raphael 33
 
34
 
35
 
36
 
37
 
38
 
39
 
40
 
41
=== 2: À propos de la table cel_references ===
42
Celle-ci existe car:
741 raphael 43
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...)
44
* les données nécessaire à l'export et à l'import sont massif
45
* or les webservices s'appellent parfois récursivement, sont lents et inadaptés
46
 
47
La conséquence est que la construction d'une table dérivée de bdtfx/bdtxa contenant
48
les informations utiles pour CEL s'avère nécessaire.
822 raphael 49
cel_references.sql construit une telle table.
741 raphael 50
 
51
Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation:
52
 
53
1) Détermination des nom vernaculaires meilleurs et uniques:
54
 
55
Ce sont ceux qui ont le num_statut le plus élevés pour un num_taxon donné dans nvjfl_v2007.
56
Plusieurs méthodes sont exposées ci-dessous, sachant que le couple (référentiel, num_nom) est la clef
57
unique de cel_references.
58
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.
59
1.1:
60
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;
61
# 12311 résultats
62
 
63
1.2:
64
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;
65
# 2680 résultats
66
 
67
1.3:
68
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;
69
# 2680 résultats
70
Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
71
 
72
 
73
 
74
2) à propos de l'insertion dans cel_references proprement dit:
75
Le modèle simplifié théorique de base est le suivant:
76
 
77
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
78
       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 \
79
       UNION \
80
       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 \
81
       UNION \
82
       SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
83
 
84
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
85
des indexes adaptés pour effectuer des JOIN efficace dans le cadre de ce script d'intégration particulier.
86
 
87
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):
88
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
89
       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 );
90
 
91
 
92
À noter:
93
SELECT b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci FROM bdtfx_v1_01 b where b.num_taxonomique = '';
94
# 3968, c'est à dire des num_taxon vides, pourtant INDEX et NOT NULL.
95
Idem pour bdtxa
96
 
97
 
98
3) à propos de baseveg/baseflor:
99
Note au 16/07/2013: les schémas sont susceptibles de changer à l'avenir.
100
La jointure entre bdtfx et baseflor se fait sur le référentiel ("bdtfx") et num_nom.
101
À partir de là nous disposons d'un catminat_code qui correspond au code_catminat dans baseveg, afin d'obtenir le syntaxon.
102
Quelques exemples:
103
 
104
SELECT code_catminat, syntaxon, lumiere, hum_atmos, temperature, oceanite, ph_sol, hum_edaph, texture_sol FROM baseveg_v2013_01_09;
105
SELECT * from baseflor_v2012_12_31 where cle = 1174;
106
 
107
SELECT * from baseveg_v2013_01_09 where code_catminat = '05/3.0.1.0.2' AND niveau = 'ALL';
108
# 7 résultats
109
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;
110
# 7 résultats
111
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;
112
# 1 résultat
822 raphael 113
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`