Présentation du

Système d'Information creation_bdd

creation_bdd:creation_base

Sun, 10 Oct 2010 14:53:37 +0200

Mise en œuvre de la base de données

les mots en italiques sont à adapter selon vos choix.

La configuration utilisée pour ce tutoriel est la suivante : hôte : localhost, utilisateur : dba, base modèle : template1, base de données à créer : sicen, encodage : UTF8, projection : lambert II étendu (code EPSG 27572)

Créer une base de données spatiales "vide"

Solution 1 : vous n'avez pas de modèle de base de données spatiale

Se connecter à la base de maintenance avec psql

(toutes les étapes mentionnées ci-dessous peuvent être réalisées “graphiquement” avec pgadmin)

psql -h //localhost// -U //dba// template1

dans psql taper :

template1=# CREATE DATABASE //sicen// TEMPLATE=template1;

Connexion à la base de données nouvellement créée

template1=# \c sicen;

L'invite de commande devient : sicen=#

le script de création de la base est encodé en LATIN1, il faut le préciser (voir)==

template1=# set client_encoding=latin1;

Remarque : les noms des objets de la bdd ne possèdent pas de caractère accentués, ce sont les commentaires qui posent un problème d'encodage. Une version UTF8 du script est disponible ici.

Ajouter le language plpgsql à la base de données :

sicen=# create language plpgsql;

Charger les fonctionnalités de ”“PostGis”” Dans la base de données :

sicen=# \i ///usr/share/postgresql-8.3-postgis///lwpostgis.sql;
sicen=# \i ///usr/share/postgresql-8.3-postgis///spatial_ref_sys.sql;

Solution 2 : vous avez modèle de base de données spatiale (ex. template_postgis)

Se connecter à la base de maintenance avec psql

(toutes les étapes mentionnées ci-dessous peuvent être réalisées “graphiquement” avec pgadmin)

psql -h localhost -U dba template1

dans psql taper :

template1=# CREATE DATABASE //sicen// TEMPLATE=template_postgis;

Créer les tables et les fonctions de la base de données

si vous souhaitez changer de projection, remplacer dans le fichier toutes les occurrences de “27572” par le code EPSG de votre choix

sicen=# \i  /home/mathieu/Desktop/sicen_xxx.sql]];

Le dictionnaire de données correspondant est disponible ici

Notes de version

Tous les scripts, depuis la version 0 sont disponibles ici

28/10/2008

correction de l'absence des tables taxon_animal_est_protege et taxon_vegetal_est_protege
suppression de la vue cen_export_ligne_flore_selon_format (les vues seront proposées sur le site, comme les fonctions plpgsql
suppression des commandes d'attribution de propriétaire
le script correspondant est disponible ici

20/09/2009

suppression des attributs “envahissantes” dans les tables “referentiel_taxon_animal” et “referentiel_taxon_vegetal”
création deux deux table “taxon_animal_est_envahissant” et “taxon_vegetal_est_envahissant”
le script correspondant est disponible ici

creation_bdd:dump_restore

Tue, 25 May 2010 15:39:58 +0200

Sauvegarde des données de la base postgis

Sauvegarde avec pg_dump

pg_dump

pg_dump -Fc nom_base -h localhost -U nom_utilisateur -f nom_fichier.backup

Restauration avec pg_restore

pg_restore

l'option -Fc (format custom) nous est utile afin de lister et trier si nécessaire les objets à restaurer. On peut ainsi ne pas souhaiter restaurer les objets de postgis, pour installer la bdd sur une version récente
l'option -l permet de créer la liste des objets à restaure (regiriger la sortie ver le fichier de son choix : > ma_liste.txt) et n'effectue pas de restauration
l'option -L ma_liste.txt effectue la restauration des objets correspondants aux lignes non commentées
l'option -1 permet d'effectuer la restauration au sein d'une transaction
l'option -s ne restaure que le schéma
l'option -d ne restaure que les données
l'option –disable-triggers permet de désactiver les contraintes avant insertion et de les remettre en œuvre après

Création de la liste des objets composant le dump

Pour créer cette liste : utiliser la commande suivante :

pg_restore -l ~/nom_fichier.backup>~/liste_objets.txt

L'édition de cette liste donne ceci (une sélection des lignes à été faite pour montrer les différents types d'objet listés):

[...]
6; 2615 2200 SCHEMA - public dba
4255; 0 0 COMMENT - SCHEMA public dba
4256; 0 0 ACL - public dba
1636; 2612 138574 PROCEDURAL LANGUAGE - plpgsql dba
976; 0 0 SHELL TYPE public geometry dba
2848; 1259 139344 TABLE public point_flore dba
2849; 1259 139353 VIEW public affiche_point_flore dba
4260; 0 0 COMMENT public TABLE amenagements dba
3149; 1259 207233 SEQUENCE public bati_isole_2006_gid_seq dba
4261; 0 0 SEQUENCE SET public bati_isole_2006_gid_seq dba
4267; 0 0 ACL public biblio_concerne_site dba
682; 1255 140572 FUNCTION public cen_nomme_auteur_ese(integer) dba
1639; 1255 138849 AGGREGATE public accum(geometry) dba
2363; 2617 138727 OPERATOR public &< dba
3619; 2605 139045 CAST pg_catalog CAST (public.box2d AS public.box3d)
4175; 0 139563 TABLE DATA public taxon_vegetal_indique_habitat dba
3752; 2606 205914 CONSTRAINT public amenagements_pkey dba
3926; 2606 205976 CONSTRAINT public pk_commune dba
3750; 1259 206173 INDEX public amenagements_geometrie_gist dba
3922; 1259 206178 INDEX public commune_geometrie_gist dba
3929; 1259 206190 INDEX public in_id_structure dba
4111; 2606 206272 FK CONSTRAINT public code_habitat_ref_habitat dba
[...]

Dans un éditeur de texte, on va pouvoir commenter les lignes correspondant aux objets que l'on ne veut pas restaurer. On va aussi pouvoir ordonner ces lignes. Par exemple, je souhaite restaurer sur une nouvelle version de postgis.

Je vais commenter tout ce que postgis va créer dans la base :

le language pl/pgsql nécessaire à l'installation de postgis
les types de données
les aggregats
les opérateurs
les fonctions
les cast
…

sans pour autant commenter certains les objets que j'aurai moi-même créé et préfixé (cas des fonctions par exemple).

Restauration de la base en tenant compte de cette liste d'objets à restaurer

pg_restore -d -s -h localhost -U dba -d sicen -L liste_objets.txt -1 nom_fichier.backup

creation_bdd:evolution

Wed, 12 May 2010 09:54:47 +0200

Migration de la bdd SICEN vers le nouveau serveur et postgis 1.5

Cette migration est l'occasion de mener une réflexion sur la structuration et les évolutions à apporter à la structure de la base et d'opérer un nettoyage de printemps (tables temporaires et vues “de travail”)… Le travail mené sur la gestion de métadonnées et sur le parcours des données sera l'occasion d'y réfléchir vraiment.

Contexte ancien serveur
- logiciel : windows XP pro ancien serveur PostgreSQL 8.3 / PostGIS 1.4 (données en lambert 2 étendu)
- matériel :
Contexte nouveau serveur
- logiciel : RHEL 5.4 virtualisé + PostgreSQL 8.4 / PostGIS 1.5 (données en lambert 93)
- matériel : IBM…

Étapes

dump & restore de la bdd selon la méthode présentée ici
pour toutes les tables contenant une géométrie¹⁾
- créer une colonne geom_l2c
- mettre à jour cette colonne avec la valeur de la colonne géométrie
- changer le SRID de la colonne géométrie en Lambert 93 (code EPSG/IGNF²⁾ : 310024140)
- mettre la à jour la colonne “geometrie” avec la reprojection de la valeur de geom_l2c (st_transform(geom_l2c,310024140)

Première réalisation avec ce serveur

intégration de la BDTOPO

Création de tables de consultation des données en remplacement des vues

Les vues “export_tous_*_*_selon_format_esri” sont gourmandes et ne sont pas compatible avec une visualisation en direct depuis GVSIG ou QGIS. Nous allons donc procéder à la création de tables destinées à recueillir le résultat de ces vues dés lors que de nouvelles données seront ventilées dans la base de données. Nous prendrons l'exemple de la table contenant l'ensemble des observations ponctuelles de flore.

Création des vues de mise en forme des données

CREATE OR REPLACE VIEW visu_tous_point_flore_format_esri AS 
 SELECT entite_spatiale_ecologique.id_entite, NULL::text AS nom_vernac, referentiel_taxon_vegetal.nom_scientifique AS nom_latin, 'INPN'::text AS ref_tax, point_flore.date_obs, point_flore.date_debut_obs AS date_debut, point_flore.date_fin_obs AS date_fin, point_flore.date_textuelle AS date_text, point_flore.effectif, point_flore.effectif_min AS eff_min, point_flore.effectif_max AS eff_max, point_flore.type_effectif AS type_eff, point_flore.phenologie AS pheno, commune.toponyme AS commune, point_flore.coord_x, point_flore.coord_y, point_flore.coord_z, point_flore.systeme_coordonnees AS syst_coord, point_flore.unite, point_flore."precision", cen_liste_personne_auteur_ese(entite_spatiale_ecologique.id_entite) AS personnes, protocole.libelle AS protocole, structure.nom_structure AS structure, lot_donnee.libelle AS lot, point_flore.id_taxon AS cd_tax_ref, point_flore.remarque, point_flore.transmis_cbnmp AS trans_cbnmp, point_flore.taxon_mentionne AS mention, point_flore.date_ventilation AS ventil, referentiel_taxon_vegetal.id_taxon_ref, cen_liste_protection_taxon_vegetal(id_taxon_ref) AS statut, point_flore.geometrie
   FROM entite_spatiale_ecologique
   JOIN protocole ON entite_spatiale_ecologique.id_protocole = protocole.id_protocole
   JOIN lot_donnee ON entite_spatiale_ecologique.id_lot = lot_donnee.id_lot
   JOIN structure_est_auteur_donnee ON entite_spatiale_ecologique.id_entite = structure_est_auteur_donnee.id_entite
   JOIN structure ON structure_est_auteur_donnee.id_structure = structure.id_structure
   JOIN point_flore ON entite_spatiale_ecologique.id_entite = point_flore.id_entite
   JOIN referentiel_taxon_vegetal ON point_flore.id_taxon::text = referentiel_taxon_vegetal.id_taxon::text, commune
  WHERE st_intersects(point_flore.geometrie, commune.geometrie);

Création de la table

CREATE TABLE tous_point_flore_selon_format_esri AS SELECT * FROM visu_tous_point_flore_format_esri;

Création des contraintes

ALTER TABLE tous_point_flore_selon_format_esri ADD PRIMARY KEY (id_entite);

Création des index

CREATE INDEX ind_pt_flore_nom_scientifique
   ON tous_point_flore_selon_format_esri USING btree (nom_latin ASC NULLS FIRST);
CREATE INDEX ind_pt_flore_commune ON tous_point_flore_selon_format_esri USING btree (commune ASC NULLS FIRST);

Ajout des contraintes de géométrie

ALTER TABLE tous_point_flore_selon_format_esri ADD CONSTRAINT enforce_dims_geometrie CHECK (ndims(geometrie) = 2);
ALTER TABLE tous_point_flore_selon_format_esri ADD CONSTRAINT enforce_geotype_geometrie CHECK (geometrytype(geometrie) = 'MULTIPOINT'::text OR geometrie IS NULL);
ALTER TABLE tous_point_flore_selon_format_esri ADD CONSTRAINT enforce_srid_geometrie CHECK (srid(geometrie) = 27582);

Référencement de la table dans la table geomtry_columns

A la main avant PostGIS 1.4.
A partir de PostGIS 1.4, la fonction Populate_Geometry_Columns() répond à ce besoin.

¹⁾ le code sql nécessaire à tout cela est généré par interrogation de la table geometry_colmuns

²⁾ en savoir plus sur la gestion du Lambert 93

creation_bdd:fonctions

Sun, 07 Aug 2011 20:12:13 +0200

PostgreSQL, PostGIS, plpgsql, plpython, WebServcies, elevation, sh

Fonctions pl/python

Fonctions pl/pgsql

Mises en place

Comment créer des fonctions postgresql

Utilisation du référentiel

Liste de tous les taxons fils d'un taxon donné

la fonction de correction de géometries

Création de points avec distance et angles

A développer

creation_bdd:modules

Sat, 03 Jul 2010 10:56:38 +0200

Les différents modules de la base de données

données naturalistes (observations faune, flore, habitats naturels)
référentiels taxonomiques
sites (d'étude par exemple)
éléments du paysage
personnes, structures et collectivités territoriales
périmètres d'inventaires et réglementaires
bibliographie et cartes papier
informations relatives aux études et métadonnées
système de fichier
suivi des actions de gestion

creation_bdd:requetes

Thu, 11 Aug 2011 12:56:06 +0200

requêtes, SQL, récursivité

Exemples de requêtes

recursivité

Affiches toutes les branches de l'arbre taxonomique des lépidoptères et des odonates

WITH RECURSIVE arbre_taxonomique AS
(SELECT cd_nom, lb_nom, cd_taxsup, lb_nom::text AS lb_nom_fullname
FROM inpn.taxref
WHERE cd_nom IN ('185356','185214') OR cd_taxsup IN ('185356','185214')
UNION ALL
SELECT enfant.cd_nom,enfant.lb_nom,
	enfant.cd_taxsup,
	parent.lb_nom_fullname||'->'||enfant.lb_nom AS lb_nom_fullname
FROM inpn.taxref AS enfant
	INNER JOIN arbre_taxonomique AS parent
	ON (enfant.cd_taxsup = parent.cd_nom)
)
SELECT *
FROM arbre_taxonomique
ORDER BY lb_nom_fullname;

Présentation du

Système d'Information creation_bdd

creation_bdd:creation_base

Mise en œuvre de la base de données

Créer une base de données spatiales "vide"

Solution 1 : vous n'avez pas de modèle de base de données spatiale

Solution 2 : vous avez modèle de base de données spatiale (ex. template_postgis)

Créer les tables et les fonctions de la base de données

Notes de version

28/10/2008

20/09/2009

creation_bdd:dump_restore

Sauvegarde des données de la base postgis

Sauvegarde avec pg_dump

Restauration avec pg_restore

Création de la liste des objets composant le dump

Restauration de la base en tenant compte de cette liste d'objets à restaurer

creation_bdd:evolution

Migration de la bdd SICEN vers le nouveau serveur et postgis 1.5

Étapes

Première réalisation avec ce serveur

Création de tables de consultation des données en remplacement des vues

creation_bdd:fonctions

Fonctions pl/python

Fonctions pl/pgsql

Mises en place

Utilisation du référentiel

Insertion des nouvelles données

Métadonnées

Fonctions diverses

Fonctions de correction des géométries

Création de points avec distance et angles

A développer

creation_bdd:modules

Les différents modules de la base de données

creation_bdd:requetes

Exemples de requêtes

recursivité

Présentation duSystème d'Information creation_bdd

creation_bdd:creation_base

creation_bdd:dump_restore

creation_bdd:evolution

creation_bdd:fonctions

creation_bdd:modules

creation_bdd:requetes

Présentation du

Système d'Information creation_bdd