CREATE SCHEMA md;
CREATE SCHEMA inpn;
CREATE SCHEMA donnees;
CREATE TYPE donnees.enum_statut_validation AS ENUM ('validée','à valider','non valide');
CREATE TYPE donnees.enum_precision AS ENUM ('GPS', '0 à 10m', '10 à 100m', '100 à 500m', 'lieu-dit', 'commune');
/* création des sites */
CREATE TABLE md.site
(
id_site serial NOT NULL,
toponyme character varying(254),
altitude_min integer,
altitude_max integer,
altitude_moy integer,
type_site character varying(20), -- Le type de site selon la classification de la FCEN.
code_site character varying(5),
surface_ha numeric,
maitrise character varying(30),
type_milieu character varying(30),
referentiel_numerisation character varying(40),
echelle_numerisation character varying(10),
proprietaire character varying(50),
acquisition character varying(20),
commune character varying(30),
cadastre character varying(10),
id_site_parent integer,
CONSTRAINT pk_site PRIMARY KEY (id_site),
CONSTRAINT fk_site_parent_est_site FOREIGN KEY (id_site_parent)
REFERENCES md.site (id_site) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION);
SELECT AddGeometryColumn('md', 'site', 'geometrie', 2154, 'MULTIPOLYGON', 2);
CREATE INDEX site_geom_gist_index ON md.site USING gist (geometrie);
/* création de la table qui stockera le référentiel taxonomique */
CREATE TABLE inpn.taxref
(
regne character varying, -- nom scientifique du règne du taxon
phylum character varying, -- nom scientifique de l'embranchement du taxon
classe character varying, -- nom scientifique de la classe du taxon
ordre character varying, -- nom scientifique de l'ordre du taxon
famille character varying, -- nom scientifique de la famille du taxon
cd_nom character varying(8) NOT NULL, -- identifiant unique
lb_nom character varying, -- nom scientifique du taxon
lb_auteur character varying, -- autorité
nom_complet character varying, -- nom scientifique complet du taxon (généralement lb_nom + lb_auteur)
cd_ref character varying(8), -- renvoi au cd_nom du taxon de référence
nom_valide character varying, -- nom scientifique complet du taxon de référence
rang character varying, -- rang taxonomique
nom_vern character varying, -- nom vernaculaire du taxon en français
nom_vern_eng character varying, -- nom vernaculaire du taxon en anglais
fr character varying, -- présence / indigénat du taxon en france métropolitaine
cd_taxsup character varying,
habitat character varying,
CONSTRAINT taxref_pk PRIMARY KEY (cd_nom),
CONSTRAINT fk_cd_ref_est_cd_nom FOREIGN KEY (cd_ref)
REFERENCES inpn.taxref (cd_nom) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_cd_taxsup_est_cd_nom FOREIGN KEY (cd_taxsup)
REFERENCES inpn.taxref (cd_nom) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT unique_cd_nom_nom_complet UNIQUE (cd_nom, nom_complet)
);
/* Création d'une table de points d'espèce */
CREATE TABLE donnees.point_espece
(
id_obs serial NOT NULL,
date_obs date,
date_debut_obs date,
date_fin_obs date,
date_textuelle character varying(30),
nom_complet character varying,
cd_nom character varying(8),
effectif_min bigint,
effectif_max bigint,
effectif_textuel character varying(10),
type_effectif character varying(30),
phenologie character varying(50),
id_waypoint integer,
localisation character varying(254),
observateur character varying(100),
numerisateur character varying(50),
validateur character varying(50),
structure character varying(35),
remarque_obs character varying(254),
statut_validation donnees.enum_statut_validation,
diffusable BOOLEAN DEFAULT true,
"precision" donnees.enum_precision,
CONSTRAINT point_espece_pkey PRIMARY KEY (id_obs),
CONSTRAINT fk_sp_pt_espce_ref_sp FOREIGN KEY (cd_nom)
REFERENCES inpn.taxref (cd_nom) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
SELECT AddGeometryColumn('donnees', 'point_espece', 'geometrie', 2154, 'POINT', 2);
CREATE INDEX point_espece_geom_gist_index ON donnees.point_espece USING gist (geometrie);
INSERT INTO donnees.point_espece (id_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, nom_complet, cd_nom, effectif_min, effectif_max, effectif_textuel, type_effectif, phenologie, id_waypoint, localisation, observateur, numerisateur, validateur, structure, remarque_obs, statut_validation, diffusable, "precision", geometrie) VALUES (2, '2011-06-15', NULL, NULL, NULL, 'Corvus corone Linnaeus, 1758', '4503', 3, NULL, NULL, 'individu(s)', NULL, NULL, NULL, 'Mathieu Bossaert', 'BOSMA', 'CENLR', NULL, NULL, NULL, true, NULL, '01010000206A080000B0F3D3C43AC02B41562DC61008725941');
INSERT INTO donnees.point_espece (id_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, nom_complet, cd_nom, effectif_min, effectif_max, effectif_textuel, type_effectif, phenologie, id_waypoint, localisation, observateur, numerisateur, validateur, structure, remarque_obs, statut_validation, diffusable, "precision", geometrie) VALUES (1, '1998-12-05', NULL, NULL, NULL, 'Ardea cinerea Linnaeus, 1758', '2506', 1, NULL, NULL, 'individu(s)', NULL, NULL, NULL, 'Mathieu Bossaert', 'BOSMA', 'CENLR', NULL, NULL, NULL, true, NULL, '01010000206A080000EE20FB8D2FAA2B41BDBBFA1A54725941');
INSERT INTO donnees.point_espece (id_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, nom_complet, cd_nom, effectif_min, effectif_max, effectif_textuel, type_effectif, phenologie, id_waypoint, localisation, observateur, numerisateur, validateur, structure, remarque_obs, statut_validation, diffusable, "precision", geometrie) VALUES (3, '2011-06-15', NULL, NULL, NULL, 'Passer domesticus (Linnaeus, 1758)', '4525', 10, 20, NULL, 'mâle(s)', NULL, NULL, NULL, 'Mathieu Bossaert', 'BOSMA', 'CENLR', NULL, NULL, NULL, true, NULL, '01010000206A080000B62ADF3E5ACE2B41D4CC23DFD26E5941');
INSERT INTO donnees.point_espece (id_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, nom_complet, cd_nom, effectif_min, effectif_max, effectif_textuel, type_effectif, phenologie, id_waypoint, localisation, observateur, numerisateur, validateur, structure, remarque_obs, statut_validation, diffusable, "precision", geometrie) VALUES (4, '2011-06-15', NULL, NULL, NULL, 'Myocastor coypus (Molina, 1782)', '61667', 1, NULL, NULL, 'jeune', NULL, NULL, NULL, 'Mathieu Bossaert', 'BOSMA', 'CENLR', NULL, NULL, NULL, true, NULL, '01010000206A0800006450ED7B65B52B41197D1793C4705941');
INSERT INTO md.site (id_site, toponyme, altitude_min, altitude_max, altitude_moy, type_site, code_site, surface_ha, maitrise, type_milieu, referentiel_numerisation, echelle_numerisation, proprietaire, acquisition, commune, cadastre, id_site_parent, geometrie) VALUES (1, 'site du cen fc', NULL, NULL, NULL, 'FCEN1', 'XA120', NULL, NULL, NULL, NULL, '1/25000', NULL, NULL, NULL, NULL, NULL, '01060000206A080000010000000103000000010000000A000000A4C71C99C7B32B410054596DED725941A93A931482A72B41F2153F2A3D7059417DDC68D259BB2B41125126AB1B6F5941067D50FF70C82B410FB14371DC6E5941CA8E5122C4D12B41569F22586C7059415D816F05F0D62B4130667656457259412533C1808CD72B41C00811A8FB7259416977B8F028D82B410C7622F4B17359410046AB5DFBBE2B41DAB1F69195735941A4C71C99C7B32B410054596DED725941');
