===== Base de données test pour tester PostGIS =====
<code sql>
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);
</code>

===== Après avoir inséré le référentiel =====

<code sql>
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');
</code>

===== Requêtes à faire par exemple =====
  * surface du site
  * nombre total de données
  * distance de l'observation de héron au site
  * nombre d'observation par année
  * nombre d'observation par espèce
  * nombre d'observation dans le site