forked from lpoaura/visionature-data-for-vigie-nature
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path00_init.sql
336 lines (302 loc) · 22 KB
/
00_init.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
/*
INITIALISATION DE LA STRUCTURE DES DONNEES
-----
Créatiop d'un schéma et des tables de centralisation des données VigieNature
*/
DO
$$
BEGIN
-- DROP SCHEMA IF EXISTS pr_vigienature CASCADE;
CREATE SCHEMA IF NOT EXISTS pr_vigienature;
COMMENT ON SCHEMA pr_vigienature IS 'Schéma de gestion des données VigieNature STOC/SHOC de VisioNature.'
CREATE TABLE IF NOT EXISTS pr_vigienature.l_carre_suivi
(
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
carrenat INTEGER,
numnat INTEGER,
geom GEOMETRY(Polygon, 2154)
);
COMMENT ON TABLE pr_vigienature.l_carre_suivi IS 'Liste des Carrés de suivi STOC (mailles 2x2km)';
COMMENT ON COLUMN pr_vigienature.l_carre_suivi.carrenat IS '?';
COMMENT ON COLUMN pr_vigienature.l_carre_suivi.numnat IS '?';
COMMENT ON COLUMN pr_vigienature.l_carre_suivi.geom IS 'Géométrie (SRID:2154)';
CREATE INDEX l_carre_suivi_geom_idx
ON pr_vigienature.l_carre_suivi USING gist (geom);
ALTER TABLE pr_vigienature.l_carre_suivi
ADD CONSTRAINT l_carre_suivi_numnat UNIQUE (numnat);
/* Table dictionnaire des codes espèces */
/*DROP TABLE IF EXISTS pr_vigienature.cor_taxon_referentiels;*/
CREATE TABLE pr_vigienature.cor_taxon_referentiels
(
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
euring_code VARCHAR(20),
euring_num INTEGER,
cd_nom INTEGER,
vn_id_species INTEGER,
ref_tax BOOLEAN
);
COMMENT ON TABLE pr_vigienature.cor_taxon_referentiels IS 'Table de correspondancs des référentiels espèces (EURING, TAXREF, VisioNature)';
COMMENT ON COLUMN pr_vigienature.cor_taxon_referentiels.euring_code IS 'Code EURING';
COMMENT ON COLUMN pr_vigienature.cor_taxon_referentiels.euring_num IS 'Numéro EURING';
COMMENT ON COLUMN pr_vigienature.cor_taxon_referentiels.cd_nom IS 'CD_NOM TaxRef';
COMMENT ON COLUMN pr_vigienature.cor_taxon_referentiels.vn_id_species IS 'Identifiant VisioNature';
COMMENT ON COLUMN pr_vigienature.cor_taxon_referentiels.ref_tax IS 'Est un taxon référence ?';
CREATE INDEX cor_taxon_referentiels_idx_euring_code ON pr_vigienature.cor_taxon_referentiels (euring_code);
CREATE INDEX cor_taxon_referentiels_idx_cd_nom ON pr_vigienature.cor_taxon_referentiels (cd_nom);
CREATE INDEX cor_taxon_referentiels_idx_vn_id ON pr_vigienature.cor_taxon_referentiels (vn_id_species);
/* Table des correspondances des distances */
--DROP TABLE pr_vigienature.dict_distance_code;
CREATE TABLE pr_vigienature.bib_nomenclature_type
(
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
code VARCHAR(20),
libelle VARCHAR(200),
libelle_international VARCHAR(200),
additional_data JSONB
);
COMMENT ON TABLE pr_vigienature.bib_nomenclature_type IS 'Liste des types de nomenclatures utilisées par VigieNature';
COMMENT ON COLUMN pr_vigienature.bib_nomenclature_type.code IS 'Code du type de nomenclature';
COMMENT ON COLUMN pr_vigienature.bib_nomenclature_type.code IS 'Libellé';
COMMENT ON COLUMN pr_vigienature.bib_nomenclature_type.code IS 'Libellé international';
COMMENT ON COLUMN pr_vigienature.bib_nomenclature_type.code IS 'Données additionelles au format JSON';
CREATE INDEX idx_bib_nomenclature_type_id ON pr_vigienature.bib_nomenclature_type (id);
CREATE INDEX idx_bib_nomenclature_type_code ON pr_vigienature.bib_nomenclature_type (code);
CREATE TABLE pr_vigienature.t_nomenclature
(
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
type_id INT REFERENCES pr_vigienature.bib_nomenclature_type (id),
code VARCHAR(100),
code_vn VARCHAR(20),
libelle VARCHAR(200),
libelle_international VARCHAR(200),
additional_data JSONB
);
COMMENT ON TABLE pr_vigienature.t_nomenclature IS 'Table des nomenclatures utilisée dans VigieNature et correspondance avec les codes VisioNature';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.type_id IS 'Clé étrangère vers le type de nomenclature';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.code IS 'Code officiel';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.code_vn IS 'Code VisioNature';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.libelle IS 'Libellé français';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.libelle_international IS 'Libellé international';
COMMENT ON COLUMN pr_vigienature.t_nomenclature.additional_data IS 'Données additionelles au format JSON';
CREATE INDEX idx_nomenclature_id ON pr_vigienature.t_nomenclature (id);
-- CREATE UNIQUE INDEX idx_nomenclature_code ON pr_vigienature.t_nomenclature (type_id, code);
CREATE UNIQUE INDEX idx_nomenclature_code_vn ON pr_vigienature.t_nomenclature (type_id, code_vn);
/* Table des relevés */
-- DROP TABLE IF EXISTS pr_vigienature.t_releve CASCADE
-- ;
CREATE TABLE pr_vigienature.t_releve
(
id SERIAL NOT NULL PRIMARY KEY, -- Clé primaire
date_debut DATE NOT NULL, -- Date de début
heure_debut TIME NOT NULL, -- Heure de debut
date_fin DATE NOT NULL, -- Date de fin
heure_fin TIME NOT NULL, -- Heure de fin
observateur VARCHAR(100), -- Observateur
carre_suivi_id INT REFERENCES pr_vigienature.l_carre_suivi (id), -- Clé étrangère vers le carré de suivi
carre_numnat INTEGER, -- Référence du carré
point_num INTEGER, -- Référence du point
site_name VARCHAR(250), -- Nom du site (pour STOC sites)
passage_mnhn INTEGER, -- Numéro de passage MNHN
altitude INTEGER, -- Altitude
nuage_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Couverture nuageuse
pluie_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Pluie
vent_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Vent
neige_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- neige
visibilite_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Visibilité
p_milieu_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Milieu principal
p_type_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Type du milieu principal
p_cat1_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Catégorie 1 du milieu principal
p_cat2_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Catégorie 2 du milieu principal
p_ss_cat1_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Sous-catégorie 1 du milieu principal
p_ss_cat2_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Sous-catégorie 2 du milieu principal
s_milieu_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Milieu secondaire
s_type_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Type du milieu secondaire
s_cat1_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Catégorie 1 du milieu secondaire
s_cat2_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Catégorie 2 du milieu secondaire
s_ss_cat1_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Sous-catégorie 1 du milieu secondaire
s_ss_cat2_id INT REFERENCES pr_vigienature.t_nomenclature (id), -- Sous catégorie 2 du milieu secondaire
bdd_source VARCHAR(50) NOT NULL, -- Base de donnée source
bdd_source_id INTEGER NOT NULL, -- identifiant dans la base de donnée source
bdd_source_id_universal VARCHAR(250) UNIQUE NOT NULL, -- Identifiant unique universel dans la base de donnée source
type_releve VARCHAR(20) NOT NULL, -- Type de relevé (point vs transect)
nom_protocole VARCHAR(20) NOT NULL, -- Code du protocole (STOC EPS, STOC SITE, SHOC, etc.)
geom_point GEOMETRY(point, 2154) NOT NULL, -- Geométrie de type point
geom_transect GEOMETRY(multilinestring, 2154), -- Géométrie de type transect
source_data JSONB NOT NULL, -- Donnée source au format JSON
update_ts
TIMESTAMP,
CONSTRAINT type_esp_con CHECK (type_releve IN ('point', 'transect') OR type_releve IS NULL)
);
/* Commentaire des champs de la table pr_vigienature.t_releve */
COMMENT ON TABLE pr_vigienature.t_releve IS 'Table des Relevés Vigie-Nature';
COMMENT ON COLUMN pr_vigienature.t_releve.id IS 'Clé primaire';
COMMENT ON COLUMN pr_vigienature.t_releve.date_debut IS 'Date de début';
COMMENT ON COLUMN pr_vigienature.t_releve.heure_debut IS 'Heure de début';
COMMENT ON COLUMN pr_vigienature.t_releve.date_fin IS 'Date de fin';
COMMENT ON COLUMN pr_vigienature.t_releve.heure_fin IS 'Heure de fin';
COMMENT ON COLUMN pr_vigienature.t_releve.observateur IS 'Observateur';
COMMENT ON COLUMN pr_vigienature.t_releve.carre_numnat IS 'Référence du carré';
COMMENT ON COLUMN pr_vigienature.t_releve.point_num IS 'Référence du point';
COMMENT ON COLUMN pr_vigienature.t_releve.site_name IS 'Nom du site (pour STOC sites)';
COMMENT ON COLUMN pr_vigienature.t_releve.passage_mnhn IS 'Numéro de passage MNHN';
COMMENT ON COLUMN pr_vigienature.t_releve.altitude IS 'Altitude';
COMMENT ON COLUMN pr_vigienature.t_releve.nuage_id IS 'Couverture nuageuse';
COMMENT ON COLUMN pr_vigienature.t_releve.pluie_id IS 'Pluie';
COMMENT ON COLUMN pr_vigienature.t_releve.vent_id IS 'Vent';
COMMENT ON COLUMN pr_vigienature.t_releve.visibilite_id IS 'Visibilité';
COMMENT ON COLUMN pr_vigienature.t_releve.p_milieu_id IS 'Milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.p_type_id IS 'Type du milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.p_cat1_id IS 'Catégorie 1 du milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.p_cat2_id IS 'Catégorie 2 du milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.p_ss_cat1_id IS 'Sous-catégorie 1 du milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.p_ss_cat2_id IS 'Sous-catégorie 2 du milieu principal';
COMMENT ON COLUMN pr_vigienature.t_releve.s_milieu_id IS 'Milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.s_type_id IS 'Type du milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.s_cat1_id IS 'Catégorie 1 du milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.s_cat2_id IS 'Catégorie 2 du milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.s_ss_cat1_id IS 'Sous-catégorie 1 du milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.s_ss_cat2_id IS 'Sous catégorie 2 du milieu secondaire';
COMMENT ON COLUMN pr_vigienature.t_releve.bdd_source IS 'Base de donnée source';
COMMENT ON COLUMN pr_vigienature.t_releve.bdd_source_id IS 'identifiant dans la base de donnée source';
COMMENT ON COLUMN pr_vigienature.t_releve.bdd_source_id_universal IS 'Identifiant unique universel dans la base de donnée source';
COMMENT ON COLUMN pr_vigienature.t_releve.type_releve IS 'Type de relevé (point vs transect)';
COMMENT ON COLUMN pr_vigienature.t_releve.nom_protocole IS 'Code du protocole (STOC EPS, STOC SITE, SHOC, etc.)';
COMMENT ON COLUMN pr_vigienature.t_releve.geom_point IS 'Geométrie de type point (SRID 2154)';
COMMENT ON COLUMN pr_vigienature.t_releve.geom_transect IS 'Géométrie de type transect (SRID 2154)';
COMMENT ON COLUMN pr_vigienature.t_releve.source_data IS 'Donnée source brute au format JSON';
COMMENT ON COLUMN pr_vigienature.t_releve.update_ts IS 'Timestamp de la dernière mise à jour dans la base de donnée';
/* Index sur les colonnes carre_numnat, date et point_num */
-- CREATE UNIQUE INDEX ON pr_vigienature.t_releve (carre_numnat, date_debut, point_num);
-- CREATE UNIQUE INDEX ON pr_vigienature.t_releve (bdd_source, bdd_source_id);
CREATE INDEX ON pr_vigienature.t_releve (bdd_source_id_universal);
CREATE INDEX ON pr_vigienature.t_releve
USING gist (geom_transect);
CREATE INDEX ON pr_vigienature.t_releve
USING gist (geom_point);
/* Table des observations */
-- drop table if exists pr_vigienature.t_observation cascade
-- ;
CREATE TABLE pr_vigienature.t_observation
(
id SERIAL NOT NULL PRIMARY KEY,
uuid UUID,
releve_id INTEGER REFERENCES pr_vigienature.t_releve (id)
ON DELETE CASCADE,
taxon_id INTEGER REFERENCES pr_vigienature.cor_taxon_referentiels (id),
nom_cite VARCHAR(300),
nombre INTEGER,
distance_id INT REFERENCES pr_vigienature.t_nomenclature (id),
details JSONB,
bdd_source_id INTEGER NOT NULL,
bdd_source_id_universal VARCHAR(50) NOT NULL,
source_data JSONB NOT NULL,
update_ts TIMESTAMP
);
/**/
COMMENT ON TABLE pr_vigienature.t_observation IS 'Table d''observations des protocoles vigie-nature';
COMMENT ON COLUMN pr_vigienature.t_observation.id IS 'Clé primaire';
COMMENT ON COLUMN pr_vigienature.t_observation.uuid IS 'Identifiant unique universel';
COMMENT ON COLUMN pr_vigienature.t_observation.releve_id IS 'Clé étrangère du relevé';
COMMENT ON COLUMN pr_vigienature.t_observation.taxon_id IS 'Clé étrangère du taxon';
COMMENT ON COLUMN pr_vigienature.t_observation.nom_cite IS 'Nom cité dans VisioNature';
COMMENT ON COLUMN pr_vigienature.t_observation.nombre IS 'Dénombrement';
COMMENT ON COLUMN pr_vigienature.t_observation.distance_id IS 'Classe de distance';
COMMENT ON COLUMN pr_vigienature.t_observation.details IS 'Détails des dénombrements (format JSON)';
COMMENT ON COLUMN pr_vigienature.t_observation.bdd_source_id IS 'identifiant dans la base de donnée source';
COMMENT ON COLUMN pr_vigienature.t_observation.bdd_source_id_universal IS 'Identifiant unique universel dans la base de donnée source';
COMMENT ON COLUMN pr_vigienature.t_observation.source_data IS 'Donnée source brute au format JSON';
COMMENT ON COLUMN pr_vigienature.t_observation.update_ts IS 'Timestamp de la dernière mise à jour dans la base de donnée';
/* INFO: Index de la table pr_vigienature.t_observation */
CREATE INDEX ON pr_vigienature.t_observation (releve_id);
CREATE INDEX ON pr_vigienature.t_observation (taxon_id);
CREATE INDEX ON pr_vigienature.t_observation (bdd_source_id);
CREATE INDEX ON pr_vigienature.t_observation (bdd_source_id_universal);
DROP VIEW IF EXISTS pr_vigienature.v_vigienature_data;
CREATE VIEW pr_vigienature.v_vigienature_data AS
SELECT
obs.id AS id
, rel.bdd_source_id_universal AS code_inventaire
, rel.nom_protocole AS etude
, rel.site_name AS site
, l_areas.area_code AS insee
, l_areas.area_name AS commune
, rel.carre_numnat AS num_carre_eps
, rel.date_debut AS date
, rel.heure_debut AS heure
, rel.heure_fin AS heure_fin
, rel.observateur AS observateur
, rel.point_num AS num_point_eps
, rel.altitude AS altitude
, obs.nom_cite AS taxon_nom_cite
, tax.cd_nom AS taxon_cd_nom
, tax.euring_code AS taxon_code_euring
, taxref.group2_inpn AS taxon_classe
, obs.nombre AS nombre
, ndist.libelle AS distance_de_contact
, st_x(st_transform(geom_point, 4326)) AS longitude
, st_y(st_transform(geom_point, 4326)) AS latitude
, nnuage.code AS eps_nuage
, npluie.code AS eps_pluie
, nvent.code AS eps_vent
, nneige.code AS eps_neige
, nvisibilite.code AS eps_visibilite
, np_milieu.code AS eps_p_milieu
, np_type.code AS eps_p_type
, np_cat1.code AS eps_p_cat1
, np_cat2.code AS eps_p_cat2
, np_ss_cat1.code AS eps_p_ss_cat1
, np_ss_cat2.code AS eps_p_ss_cat2
, ns_milieu.code AS eps_s_milieu
, ns_type.code AS eps_s_type
, ns_cat1.code AS eps_s_cat1
, ns_cat2.code AS eps_s_cat2
, ns_ss_cat1.code AS eps_s_ss_cat1
, ns_ss_cat2.code AS eps_s_ss_cat2
, rel.bdd_source_id_universal AS reference_id_universal_ff_releve
, obs.bdd_source_id_universal AS reference_id_universal_ff_observation
, obs.uuid AS reference_uuid_observation
FROM
pr_vigienature.t_releve rel
JOIN pr_vigienature.l_carre_suivi
ON rel.carre_suivi_id = l_carre_suivi.id
JOIN pr_vigienature.t_observation obs ON rel.id = obs.releve_id
JOIN ref_geo.l_areas ON st_within(st_transform(rel.geom_point, 4326), l_areas.geom)
LEFT JOIN pr_vigienature.cor_taxon_referentiels tax ON obs.taxon_id = tax.id
LEFT JOIN taxonomie.taxref ON tax.cd_nom = taxref.cd_nom
LEFT JOIN pr_vigienature.t_nomenclature ndist ON obs.distance_id = ndist.id
LEFT JOIN pr_vigienature.t_nomenclature nnuage ON rel.nuage_id = nnuage.id
LEFT JOIN pr_vigienature.t_nomenclature npluie ON rel.pluie_id = npluie.id
LEFT JOIN pr_vigienature.t_nomenclature nvent ON rel.vent_id = nvent.id
LEFT JOIN pr_vigienature.t_nomenclature nneige ON rel.neige_id = nneige.id
LEFT JOIN pr_vigienature.t_nomenclature nvisibilite ON rel.visibilite_id = nvisibilite.id
LEFT JOIN pr_vigienature.t_nomenclature np_milieu ON rel.p_milieu_id = np_milieu.id
LEFT JOIN pr_vigienature.t_nomenclature np_type ON rel.p_type_id = np_type.id
LEFT JOIN pr_vigienature.t_nomenclature np_cat1 ON rel.p_cat1_id = np_cat1.id
LEFT JOIN pr_vigienature.t_nomenclature np_cat2 ON rel.p_cat2_id = np_cat2.id
LEFT JOIN pr_vigienature.t_nomenclature np_ss_cat1 ON rel.p_ss_cat1_id = np_ss_cat1.id
LEFT JOIN pr_vigienature.t_nomenclature np_ss_cat2 ON rel.p_ss_cat2_id = np_ss_cat2.id
LEFT JOIN pr_vigienature.t_nomenclature ns_milieu ON rel.s_milieu_id = ns_milieu.id
LEFT JOIN pr_vigienature.t_nomenclature ns_type ON rel.s_type_id = ns_type.id
LEFT JOIN pr_vigienature.t_nomenclature ns_cat1 ON rel.s_cat1_id = ns_cat1.id
LEFT JOIN pr_vigienature.t_nomenclature ns_cat2 ON rel.s_cat2_id = ns_cat2.id
LEFT JOIN pr_vigienature.t_nomenclature ns_ss_cat1 ON rel.s_ss_cat1_id = ns_ss_cat1.id
LEFT JOIN pr_vigienature.t_nomenclature ns_ss_cat2 ON rel.s_ss_cat2_id = ns_ss_cat2.id
WHERE
l_areas.id_type = ref_geo.get_id_area_type('COM');
DROP VIEW IF EXISTS pr_vigienature.v_vigienature_observers;
CREATE VIEW pr_vigienature.v_vigienature_observers AS
SELECT
site
, id AS id_local
, id_universal
, item ->> 'email' AS email
, item ->> 'name' AS nom
, item ->> 'surname' AS prenom
FROM
src_vn_json.observers_json
WHERE
id_universal IN (SELECT observateur::INT FROM pr_vigienature.t_releve);
COMMIT;
END
$$
;