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