====== 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 [[stages:2010:sicen|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 : [[http://www.fr.redhat.com/|RHEL 5.4]] virtualisé + PostgreSQL 8.4 / PostGIS 1.5 (données en lambert 93)
    * matériel : IBM...

===== Étapes =====
  * [[http://docs.postgresql.fr/8.4/app-pgdump.html|dump]] & [[http://docs.postgresql.fr/8.4/app-pgrestore.html|restore]] de la bdd selon la méthode présentée [[creation_bdd:dump_restore|ici]]
  * pour toutes les tables contenant une géométrie((le code sql nécessaire à tout cela est généré par interrogation de la table geometry_colmuns))
    * 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(([[http://lambert93.ign.fr/index.php?id=29#c125|en savoir plus sur la gestion du Lambert 93]])) : 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 ======
  * [[http://sig.cenlr.org/integration_donnees/externes/ign#integration_de_la_bdtop|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<code sql>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);
  </code>
  - Création de la table<code sql>
CREATE TABLE tous_point_flore_selon_format_esri AS select * from visu_tous_point_flore_format_esri;</code>
  - Création des contraintes<code sql>ALTER TABLE tous_point_flore_selon_format_esri ADD PRIMARY KEY (id_entite);</code>
  - Création des index<code sql>
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);</code>
  - Ajout des contraintes de géométrie<code sql>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);</code>
  - 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 [[http://postgis.refractions.net/documentation/manual-1.4/Populate_Geometry_Columns.html|Populate_Geometry_Columns()]] répond à ce besoin.