Présentation du

Système d'Information

actubdd

Fri, 14 Oct 2011 15:20:11 +0200

Actualités de la base de données.

Printemps 2011

le CEN L-R présent à la seconde Session PostgreSQL

Automne 2010

En attendant de plus amples explications sur la réorganisation de la base de données (création de schémas), en voici déjà le dictionnaire de données généré avec ireport :

Printemps été 2010

Amélioration, optimisation du parcours des données produites au CEN

Ce travail fait l'objet d'un stage de fin d'étude de Master 2 IFPRU de l'université de Montpellier 2. En lire plus...

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. En lire plus...

Février 2010

Nouveau serveur de base de données

Le serveur de base de données est fonctionnel depuis le début du mois. Postgresql 8.4.2 et postgis 1.5 tournent sur une redhat 5.4. PostGIS gère le lambert 93. Jusqu'à ce que je lise ce post et le site lambert93.ign.fr, je pensais que l'utilisation du code EPSG “2154” suffisait…

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. En lire plus...

Janvier 2010

Intégration de la BDTOPO

Comment intégrer la BD TOPO, livrée per département, en MIF/MID et en lambert 93 à la base de données, dans le schéma ign_bd_topo, en lambert 2 étendu. En lire plus

avril 2009

Création de points à partir de coordonnées en long/lat wgs 84

UPDATE point_flore_a_importer 
SET geometrie=transform(st_multi(geometryfromtext('POINT('||coord_x||' '||coord_y||')',4326)),27572)
WHERE id_entite IS NULL AND geometrie IS NULL;

février 2009

- Création d'une base de données contenant les thèmes de la BDCARTO mis à disposition dans le cadre du SINP

Un renommage des tables et des colonnes pourrait être envisagé!

Transformer les shapes en sql : ”“for /R %F IN (*.shp) DO shp2pgsql -s 27572 -g geometrie -I ”%F” ”%~nF”>“ign_bdcarto_%~nF.sql””“

Chargement dans la base de données : ”“for /R %F IN (*.sql) DO psql -f ”%F” -h addresse_ip_hote -U utilisateur base_de_donnée”“

décembre 2008

- Amélioration de l'import de données faune

- Gestion de l'historique des envois de données aux partenaires

CREATE TABLE structure_a_recu_ese(
id_structure integer,
id_entite integer,
date_envoi date,
CONSTRAINT pk_structure_a_recu_ese PRIMARY KEY (id_structure, id_entite),
CONSTRAINT fk_id_structure_est_structure FOREIGN KEY (id_structure)
      REFERENCES structure (id_structure) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_id_entite_est_entite FOREIGN KEY (id_entite)
      REFERENCES entite_spatiale_ecologique (id_entite) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION);

novembre 2008

- Insertion dans la base de données des cantons de la région. Création de 2 tables et remplissage avec les données INSEE de 2008

CREATE TABLE canton
(
  num_insee character varying(3) NOT NULL, -- Le numéro du canton.
  departement_num_insee character varying(3) NOT NULL, -- le numéro insee du département.
  arrondissement_num_insee character varying(3) NOT NULL,
  chef_lieu_num_insee character varying(5) NOT NULL,
  type_canton character varying(1) NOT NULL,
  toponyme character varying(50),
  CONSTRAINT pk_canton PRIMARY KEY (num_insee, departement_num_insee),
  CONSTRAINT fk_canton_dans_dept FOREIGN KEY (departement_num_insee)
      REFERENCES departement (num_insee) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_chef_lieu_est_commune FOREIGN KEY (chef_lieu_num_insee)
      REFERENCES commune (num_insee) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
COMMENT ON TABLE canton IS 'Permet le stockage des cantons.';
COMMENT ON COLUMN canton.num_insee IS 'Le numéro du canton.';
COMMENT ON COLUMN canton.departement_num_insee IS 'le numéro insee du département.';
 
CREATE TABLE commune_est_sur_canton
(
  commune_num_insee character varying(5) NOT NULL, -- Le numéro insee de la commune.
  departement_num_insee character varying(3) NOT NULL, -- Le numéro insee du département du canton.
  canton_num_insee character varying(3) NOT NULL, -- Le numéro insee du canton.
  CONSTRAINT pk_commune_est_sur_canton PRIMARY KEY (commune_num_insee, departement_num_insee, canton_num_insee),
  CONSTRAINT fk_canton_est_canton FOREIGN KEY (departement_num_insee, canton_num_insee)
      REFERENCES canton (departement_num_insee, num_insee) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_commune_est_commune FOREIGN KEY (commune_num_insee)
      REFERENCES commune (num_insee) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
COMMENT ON TABLE commune_est_sur_canton IS 'Etablit la composition des cantons.';
COMMENT ON COLUMN commune_est_sur_canton.commune_num_insee IS 'Le numéro insee de la commune.';
COMMENT ON COLUMN commune_est_sur_canton.departement_num_insee IS 'Le numéro insee du département du canton.';
COMMENT ON COLUMN commune_est_sur_canton.canton_num_insee IS 'Le numéro insee du canton.';

octobre 2008

- intégration des différentes couches de la BD CARTHAGE

La version mise à disposition sur le site du sandre introduit des accents dans le nommage des colonnes.

Après téléchargement des couches (ici au format mif/mid, si shp alors passer à l'étape suivante), les transformer en shp à l'aide de la console (windows): ”“for /R %F IN (*.MIF) DO ogr2ogr -f “ESRI ShapeFile” -s_srs “EPSG:2154” -t_srs “EPSG:27582” ”%~nF.shp” %F”“

Transformer ces shapes en sql : ”“for /R %F IN (*.shp) DO shp2pgsql -s 27582 -S -g geometrie -I ”%F” “ign_bdc_%~nF”>“prefixe_%~nF_suffixe.sql””“

Les charger dans la base de données : ”“for /R %F IN (*.sql) DO psql -f ”%F” -h addresse_ip_hote -U utilisateur base_de_donnée”“

septembre 2008

- Procédure d'intégration de la base d'occupation du sol ocsol (adaptez les noms de fichiers à votre cas)

Télécharger de l'archive qui nous intéresse sur la page :

http://ocsol.siglr.net/index.php?option=com_docman&task=cat_view&gid=14&Itemid=5

Transformer le shp obtenu aprés décompression de l'archive :

shp2pgsql -s 27572 -g geometrie -I V0_OCSOL_LR_POLY_2006_L2E_SHP_NOBD.shp occsol_2006>occsol_2006.sql puis psql -f occsol_2006.sql -h addresse_ip_hote -U utilisateur nom_base_de_donnée

On peut maintenant supprimer la redondance et les attributs “inutiles” :

CREATE TABLE poste_occsol
(
  id_poste character varying(3) NOT NULL,
  libelle character varying(100),
  description text
);
ALTER TABLE poste_occsol ADD CONSTRAINT libelle_occsol_pkey PRIMARY KEY (id_poste);
 
INSERT INTO poste_occsol (SELECT DISTINCT cde_06, intit_06, NULL FROM occsol_2006);
 
ALTER TABLE occsol_2006 DROP COLUMN bd06_;
ALTER TABLE occsol_2006 DROP COLUMN bd06_id;
ALTER TABLE occsol_2006 DROP COLUMN niv1_06;
ALTER TABLE occsol_2006 DROP COLUMN niv2_06;
ALTER TABLE occsol_2006 DROP COLUMN niv3_06;
ALTER TABLE occsol_2006 DROP COLUMN intit_06;
ALTER TABLE occsol_2006 DROP COLUMN int_06;
ALTER TABLE occsol_2006 RENAME gid TO id_entite_occsol;
ALTER TABLE occsol_2006 ALTER COLUMN cde_06 TYPE character varying(3);
ALTER TABLE occsol_2006 RENAME cde_06 TO id_poste;
ALTER TABLE occsol_2006 ADD CONSTRAINT fk_id_poste_est_occsol FOREIGN KEY (id_poste) REFERENCES poste_occsol(id_poste);

CleanPolygons : résolution des problèmes d'intégration de la base d'occupation du sol (voir le site de siglr ici)

Les données, valides sous mapinfo sont transformées par shp2pgsql. Lors de leur insertion dans la base postgis, un

##select count(*) from occsol where isvalid(geometrie) is false;## retourne le résultat 983!!!

Ce qui présage des erreurs lors de l'exécution de requêtes géographiques. Une première tentative de correction avec la requête suivante :

##update occsol set geometrie=buffer(geometrie,0) where isvalid(geometrie) is false;## nous permet d'avoir 0 comme résultat à la requête précédente.

Cependant, une première requête spatiale calculant la part de chaque poste d'occupation du sol de différents périmètres retourne une surface totale supérieure à la surface de l'aire d'étude.

Les géométries sont corrigées mais la couche contient des objets superposés!

Le wiki de PostGIS et particulièrement cette page nous apportent la solution : http://wiki.postgis.org/support/wiki/index.php?CleanPolygons

brouillon_fonction

Wed, 07 Sep 2011 13:12:08 +0200

CREATE OR REPLACE FUNCTION outils.idx(anyarray, anyelement)
  RETURNS integer AS
$BODY$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION outils.idx(anyarray, anyelement) OWNER TO dba;

CREATE OR REPLACE FUNCTION md.liste_nom_auteur(text)
  RETURNS text AS
$BODY$
DECLARE
	myrec RECORD;
	var_liste_code_observateur ALIAS FOR $1;
		var_liste_nom_observateur character varying DEFAULT '';
BEGIN
FOR myrec IN SELECT nom||' '||prenom AS nom_prenom FROM md.personne WHERE code_personne = ANY(string_to_array(var_liste_code_observateur, '&')) ORDER BY outils.idx(string_to_array(var_liste_code_observateur, '&'), code_personne::text) ASC
	LOOP
		var_liste_nom_observateur:=var_liste_nom_observateur||', '||myrec.nom_prenom;
	END LOOP;
RETURN substring(var_liste_nom_observateur FROM 3 FOR (length(var_liste_nom_observateur))::integer);
--RETURN var_liste_nom_observateur||'-'||(length(var_liste_nom_observateur)-3)::text;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION md.liste_nom_auteur(text) OWNER TO dba;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO public;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO dba;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO rss;

CREATE OR REPLACE FUNCTION md.creation_modification_observateur()
  RETURNS TRIGGER AS
$BODY$
DECLARE
	var_ancien_code_observateur character varying;
	var_ancien_login character varying;
	var_password character varying;
	var_ancien_role md.enum_role;
	var_nouveau_role md.enum_role;
	myrec RECORD;
 
BEGIN
	IF (TG_OP = 'DELETE') THEN -- mise à jour d'un utilisateur (mot de passe et mail non nuls et role différent de observ
		EXECUTE 
			$req$
				DROP VIEW IF EXISTS saisie.$req$||lower(OLD.code_personne)||$req$_saisie_observation;
			$req$;
	END IF;
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		IF (NEW.role<>'observ') THEN
			EXECUTE 
			$req$
				DROP VIEW IF EXISTS saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation;
				CREATE OR REPLACE VIEW saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation AS
				SELECT * FROM saisie.saisie_observation WHERE lower(saisie_observation.numerisateur)::text = lower('$req$||NEW.code_personne||$req$')::text;
				GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation TO sicen_gr_amateur;
				GRANT UPDATE (date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, referentiel_taxonomique, effectif_min, effectif_max, type_effectif, phenologie, id_waypoint, longitude, latitude, elevation, systeme_coordonnees, unite, localisation, observateur, numerisateur, structure, remarque_obs, id_element_paysage, date_ventilation, geometrie, code_insee, id_lieu_dit, id_etude, id_protocole, effectif_textuel, diffusable, "precision", effectif, url_photo, commentaire_photo) ON TABLE saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation TO sicen_gr_amateur;
			$req$;
		RAISE INFO 'vue créée pour %', NEW.code_personne;
 
		RAISE INFO 'Création des règles d''accès à ces vues pour =%', NEW.email;		
		EXECUTE 
			$req$
				-- règle lors de l'ajout de données à la vue : INSERT
				CREATE OR REPLACE RULE insert_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON INSERT TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				INSERT INTO saisie.saisie_observation(date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, referentiel_taxonomique, effectif_min, effectif_max, type_effectif, phenologie, id_waypoint, longitude, latitude, elevation, systeme_coordonnees, unite, localisation, observateur, numerisateur, validateur, structure, remarque_obs, id_element_paysage, date_ventilation, geometrie, code_insee, id_lieu_dit, statut_validation, id_etude, id_protocole, effectif_textuel, diffusable, "precision", effectif, url_photo, commentaire_photo)
				VALUES (NEW.date_obs, NEW.date_debut_obs, NEW.date_fin_obs, NEW.date_textuelle, NEW.regne, NEW.nom_vern, NEW.nom_complet, NEW.cd_nom, NEW.referentiel_taxonomique, NEW.effectif_min, NEW.effectif_max, NEW.type_effectif, NEW.phenologie, NEW.id_waypoint, NEW.longitude, NEW.latitude, NEW.elevation, NEW.systeme_coordonnees, NEW.unite, NEW.localisation, NEW.observateur, NEW.numerisateur, NEW.validateur, NEW.structure, NEW.remarque_obs, NEW.id_element_paysage, NEW.date_ventilation, NEW.geometrie, NEW.code_insee, NEW.id_lieu_dit, NEW.statut_validation, NEW.id_etude, NEW.id_protocole, NEW.effectif_textuel, NEW.diffusable, NEW."precision", NEW.effectif, NEW.url_photo, NEW.commentaire_photo);
 
				-- règle lors de la mise à jour de données de la vue : UPDATE
				CREATE OR REPLACE RULE update_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON UPDATE TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				UPDATE saisie.saisie_observation
				SET date_obs = NEW.date_obs, date_debut_obs = NEW.date_debut_obs, date_fin_obs = NEW.date_fin_obs, date_textuelle = NEW.date_textuelle, regne = NEW.regne, nom_vern = NEW.nom_vern, nom_complet = NEW.nom_complet, cd_nom = NEW.cd_nom, referentiel_taxonomique = NEW.referentiel_taxonomique, effectif_min = NEW.effectif_min, effectif_max = NEW.effectif_max, type_effectif = NEW.type_effectif, phenologie = NEW.phenologie, id_waypoint = NEW.id_waypoint, longitude = NEW.longitude, latitude = NEW.latitude, elevation = NEW.elevation, systeme_coordonnees = NEW.systeme_coordonnees, unite = NEW.unite, localisation = NEW.localisation, observateur = NEW.observateur, numerisateur = NEW.numerisateur, validateur = NEW.validateur, structure = NEW.structure, remarque_obs = NEW.remarque_obs, id_element_paysage = NEW.id_element_paysage, date_ventilation = NEW.date_ventilation, geometrie = NEW.geometrie, code_insee = NEW.code_insee, id_lieu_dit = NEW.id_lieu_dit, statut_validation = NEW.statut_validation, id_etude = NEW.id_etude, id_protocole = NEW.id_protocole, effectif_textuel = NEW.effectif_textuel, diffusable = NEW.diffusable, "precision" = NEW."precision", effectif = NEW.effectif, url_photo = NEW.url_photo, commentaire_photo = NEW.commentaire_photo
				WHERE id_obs = OLD.id_obs;
 
				-- règle lors de la suppression d'une données de la vue : DELETE
				CREATE RULE delete_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON DELETE TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				DELETE FROM saisie.saisie_observation
				WHERE id_obs = OLD.id_obs;
			$req$;
 
		END IF;
	END IF;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION md.creation_modification_observateur() OWNER TO dba;
GRANT EXECUTE ON FUNCTION md.creation_modification_observateur() TO public;
GRANT EXECUTE ON FUNCTION md.creation_modification_observateur() TO dba;

CREATE TRIGGER md_creation_observateur
  AFTER INSERT OR UPDATE OR DELETE
  ON md.personne
  FOR EACH ROW
  EXECUTE PROCEDURE md.creation_modification_observateur();

confidentialite

Fri, 01 Apr 2011 12:50:07 +0200

Suivi des visites de ce site

Nous utilisons piwik pour suivre la consultation de ce site et l'usage qui en est fait par les internautes.

Vous pouvez choisir de ne pas attribuer de cookie d'identification unique à votre ordinateur pour éviter l'agrégation et l'analyse par piwik des données concernant votre navigation sur ce site.

creation_grille

Thu, 07 Jul 2011 19:30:22 +0200

--SELECT gid, id, prec_plani, nom, code_insee, statut, canton, arrondisst, depart, region, popul, multican, geometrie FROM ign_bd_topo.commune;
 
--SELECT ceiling (( ST_xmax ( ST_Extent (geometrie)) - ST_xmin ( ST_Extent (geometrie))) / 533 ) as width, ceiling (( ST_ymax ( ST_Extent (geometrie)) - ST_ymin ( ST_Extent (geometrie))) / 600 ) as height, ST_Extent (geometrie) as thee FROM ign_bd_topo.commune 
 
 TRUNCATE export.grille_500m;
 --CREATE TABLE export.grille_500m(gid SERIAL PRIMARY KEY);
 --SELECT AddGeometryColumn('export', 'grille_500m', 'geometrie', 310024140, 'POLYGON', 2);
 
 INSERT INTO export.grille_500m(geometrie)
 SELECT DISTINCT slice FROM (SELECT ST_translate(ref.boxrep, hor.n*width, ver.n*height) AS slice
 FROM
 (SELECT ST_xmin(ST_Extent(geometrie)) AS xstart, ST_xmin(ST_Extent(geometrie)) AS ystart, ST_SetSRID(CAST('BOX(588681.1 6137110.7,589181.1 6137610.7)' AS box2d), 310024140) AS boxrep,
 ceiling((ST_xmax(ST_Extent(geometrie)) - ST_xmin(ST_Extent(geometrie)))/533) AS width, 
     ceiling((ST_ymax(ST_Extent(geometrie)) - ST_ymin(ST_Extent(geometrie)))/600) AS height
 FROM ign_bd_topo.commune) AS ref, generate_series(1,533) AS hor(n), generate_series(1,600) AS ver(n)) foo INNER JOIN ign_bd_topo.commune
 ON st_intersects(foo.slice, commune.geometrie)-- WHERE commune.code_insee LIKE '34%'
 ;
 
 --CREATE INDEX geom_index_grille_500m ON export.grille_500m USING gist(geometrie);
 
-- SELECT sum(area2d(geometrie))/(500*500) FROM ign_bd_topo.commune;

demo_postgis

Wed, 15 Jun 2011 16:02:52 +0200

Base de données test pour tester PostGIS

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);

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

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');

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

deploiement

Fri, 27 Apr 2012 18:37:59 +0200

officescan : https://192.168.1.16:4343/officescan/console/html/ClientInstall/

http://192.168.1.230/wxsign/wxsign.php/1656704680239991889/inspire/wms

http://192.168.1.230/pwms/ign-gpc.php

gap_analysis

Fri, 08 Apr 2011 14:15:13 +0200

références

hassan

Thu, 08 Sep 2011 17:17:17 +0200

hassan

indexmenu

Fri, 09 Sep 2011 22:36:51 +0200

js thread msort nsort tsort context

interface_bddiag

Wed, 11 Jan 2012 09:36:12 +0200

BDDIAG

objets spatiaux

UAE
- point
- ligne
- polygone
IAE
- point
- ligne
- polygone
Mesures
- UAE
  - point
  - ligne
  - polygone
- IAE
  - point
  - ligne
  - polygone

Accueil
Nuage de tags
derniers ajouts
Actualités
Présentation diverses
Offres de stage
Démarche
Création de la base
Les Données
- Diffusion / Echanges
  - Pistes de reflexion
- Format de données
  - Format Standard de données
- Intégration de données
  - Données internes
    - Problématique
  - Données externes
    - Généralités
    - DGI
    - DIREN L-R
    - IFN
    - IGN
    - INPN
    - SIGLR
- Sources de données
Exploitation de la base
- Commande d'export
- Contenu de la bdd
(Biblio|Webo)graphie
Outils utilisés
- SIG
  - PostgresSQL/PostGIS
    - Ressources
    - Fonds d'écran /Wallpapers
  - PostGIS/Raster
    - Ressources
    - Cas d'utilisation
  - Moskitt/Moskitt
    - Ressources
    - Cas d'utilisation
  - GRASS
  - QGis
  - GVSIG
  - gdal
    - Ressources
    - Cas d'utilisation
  - ogr2ogr
    - Ressources
    - Cas d'utilisation
  - Talend Open Studio / SDI
  - MDWEB2
  - Mapproxy
    - Installation
- Non SIG
  - iReport
  - Expressions régulières
  - JabRef
    - Ressources
    - Cas d'utilisation
  - Dokuwiki
    - Ressources
    - Cas d'utilisation
  - Drupal
    - Cas d'utilisation
- Système d'exploitation
  - Linux
    - commandes Shell
    - Scripts Shell
  - RHEL / CentOS
  - FEDORA
    - Ressources en ligne
Cours
- BTS
bric à brac
- remplacement de codes html
- optimisation de pdf
FCEN
- CTT du 28 janvier 2011
- outil de suivi des actions de gestion
Interne
- gap_analysis
  - ressouirces
Confidentialité

presentation_sig

Fri, 25 Nov 2011 14:51:32 +0200

Diverses présentations réalisées par le CEN relatives à la structuration, à l'exploitation et à la pérennisation des données

décembre 2011

Utilisation du webmapping dans le contexte de l'atlas des libellules et papillons du Languedoc-Roussillon

juin 2011

PostgreSQL Session - session 2, le 23 juin

Voir la présentation

Novembre 2010

Congrès des Conservatoires d'Espaces Naturels

Juin 2010

Forum TIC de l'ATEN

Mettre en ligne sur CARMEN les lots de données produits et catalogués

INSPIRE in Nature Conservation : European Experiences - Montpellier

Octobre 2009

The use of a relational database to store, manage and spread spatial data by a french landscape and biodiversity conservation NGO

Forum des informaticiens, géomaticiens et webmestres de l'ATEN

stage_delphine_montagne

Fri, 02 Mar 2012 10:39:53 +0200

Atlas des papillons de jour et des libellules du Languedoc-Roussillon

Stage MASTER II - Analyse spatiale des données :

Connaissance des espèces et optimisation de l'effort de prospection

Contexte

Le Cen L-R, L'OPIE et les Écologistes de L'Euzière ont entrepris en 2009 de mettre en place un atlas interactif des Papillons de jours et des libellules du Languedoc-Roussillon. Il a pour but d’accroitre et de diffuser les connaissance de ces espèces sur :

leur répartition,
l'évolution de leurs populations,
leurs exigences…

Ces connaissances sont indispensables aux acteurs de l’environnement pour assurer la bonne gestion et à la conservation de ces espèces et de leurs habitats, notamment vis-à-vis des objectifs des Plans nationaux d'actions mis en œuvre en région. La DREAL L-R a souhaité que ce projet constitue une projet pilote du SINP. Dans ce contexte, le projet assume le rôle de pôle thématique régional pour les papillons de jours et les libellules. L'animation du réseau de compétences fait partie des attributions des pôles thématiques. Une phase de concertation a permis de constituer un comité scientifique et technique ainsi que d'identifier des relais locaux, généralement des structures départementales qui sont les représentant du projet dans les différents départements. Ils animent leurs réseaux naturalistes et proposent des formations, des sorties de sensibilisation et des prospections ciblées sur des groupes ou des territoires.

La mise en place de cet atlas a nécessité la mise en place d'un outil web de collecte et de consolidation des données, permettant d'alimenter le site web de diffusion de l'atlas proprement dit.

L'outil de saisie mis en place est une interface web cartographique, qui alimente une base de données spatiale. Au cours de l'année 2010, les données des coordinateurs du projets ont été consolidées au sein de cette base de données et de nombreuses données ont été informatisées (carnet de terrains d'experts locaux).

Ce sont donc 18 000 données qui seront d'ores et déjà mobilisables au sein de la base de données lors de son lancement ce printemps.

Objectifs

L'atlas a une double finalité :

diffuser la connaissance / améliorer la prise en compte des enjeux de conservation de ces espèces dans les politiques de conservation
servir d’appui technique aux gestionnaires dans les problématiques de gestion des milieux naturels

Pour atteindre ces objectifs, l'analyse des données va revêtir toute son importance.

analyse spatiale

Les résultats de ces analyses feront l'objet de restitutions cartographiques interactives et/ou statiques

Diffusion / amélioration de la connaissance

Quelles sont les lacunes de prospection ?

espèces méconnues
territoires sous-prospectés

Quelles sont les stations anciennes qui mériteraient une actualisation ?

Optimisation des prospections, "détection de niche"

Quelles sont les préférences écologiques des espèces ?

altitude
exposition
pente

Quelle est la phénologie de l'espèce ?

Quand dois-je planifier une sortie de terrain ciblée sur une espèce ?

Quelles zones prospecter préférentiellement ?

Outils utilisés

L'environnement logiciel libre sera privilégié

PostgreSQL/PostGIS
R
Mapserver
Inkscape

Mutualisation

Dans le cadre de la mise en œuvre du SINP et de l'utilisation par d'autres structures de l'application de saisie, une attention particulière sera portée sur la faisabilité de décliner les méthodes utilisés pour d'autres groupes taxonomiques ou d'autres outils. Cela passera par une démarche démonstrative et l'utilisation d'un format de données documenté, facile à mettre en œuvre.

Conditions d'accueil

Le/la stagiaire sera co-encadré par les 2 personnes en charge du projet au sein de l'OPIE et du CEN L-R. Le/la stagiaire sera accueillie dans les locaux du CEN L-R dans le centre de Montpellier. Des jours de travail seront planifiés dans les locaux de l'OPIE, situés au Campus international de Baillarguet à Montpellier.

Indemnité mensuelle forfaitaire de 417 euros
poste de travail mis à disposition
éventuels frais de déplacement pris en charge

start

Tue, 05 Jun 2012 08:18:05 +0200

Le wiki fait peau neuve

Visitez http://si.cenlr.org

Download Flash Player 9 or better for full experience.

Bienvenue !

Ce wiki est mis en place pour présenter le SIG développé au CEN L-R depuis fin 2006 sur la plate-forme libre PostgreSQL/PostGIS pour en améliorer la documentation, en favoriser la diffusion et l'utilisation et pourquoi pas son amélioration collective.

Cette base de données spatiales permet la gestion et l'exploitation de données relatives :

à la faune, la flore et aux habitats naturels
aux limites des collectivités
aux périmètres de protection et d'inventaire
aux observateurs et aux partenaires
aux études réalisées et aux protocoles employés
aux référentiels taxonomiques
aux référentiels habitats
aux référentiels cartographiques vecteurs

La particularité de ce SIG repose depuis 2006 sur l'utilisation d'un système de gestion de bases de données relationnelles (utilisation des contraintes de domaine, de colonne, de table) pour la gestion de données spatiales et à thématique environnementale. L'utilisation de SGBD spatiaux est aujourd'hui de plus en plus répandue mais mérite de se généraliser¹⁾.

La mise en œuvre logicielle de ce système d'information repose sur l'utilisation de logiciels open source. La base de données est gérée par le couple PostgreSQL/PostGIS.

Vous trouverez sur ce site :

une présentation générale du SIG du CEN (ici)
les attentes qui ont guidées la mise en oeuvre de ce SI (ici à venir)
les choix logiciels effectués (ici)
la description des différents modules de la base de données (ici)
les codes des fonctions plpgsql et plpython développées et utilisées pour exploitation de la base (ici)
les applications logicielles libres utilisées pour mettre en œuvre ce SI (ici)

Mathieu BOSSAERT (sig à cenlr point org) Responsable du Système d'Information du CEN L-R

— Mathieu Bossaert 2011/08/18 12:34

¹⁾ Système de Gestion de Bases de Données Relationnelles

time_tracker

Tue, 08 Mar 2011 17:48:19 +0200

Vues pour reporting

Suivi de l'ensemble de l’équipe

SELECT
CONCAT(SUBSTRING_INDEX(u_name,' ',-1),' ',SUBSTRING_INDEX(u_name,' ',1)) AS u_name,
p_name,
a_name,
DAYNAME(al_date) AS jour,
extract(year FROM al_date) AS annee,
CASE extract(month FROM al_date)
WHEN 1 THEN '01 - Janvier'
WHEN 2 THEN '02 - Février'
WHEN 3 THEN '03 - Mars'
WHEN 4 THEN '04 - Avril'
WHEN 5 THEN '05 - Mai'
WHEN 6 THEN '06 - Juin'
WHEN 7 THEN '07 - Juillet'
WHEN 8 THEN '08 - Août'
WHEN 9 THEN '09 - Septembre'
WHEN 10 THEN '10 - Octobre'
WHEN 11 THEN '11 - Novembre'
WHEN 12 THEN '12 - Décembre'
END AS mois,
round(time_to_sec(al_duration)/3600/(CASE WHEN DAYNAME(al_date)='FRIDAY' THEN 7 ELSE 8 END),2) AS duree
FROM cenlr_temps_travail.activity_log
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
WHERE extract(year FROM al_date) = 2011
AND al_activity_id <> 402 AND al_activity_id <> 388
ORDER BY trim(substring(p_name FROM 1 FOR (locate('-', p_name)-1))), 2

Suivi par salarié

SELECT  
u_name, 
p_name, 
extract(month FROM al_date) AS mois,
round(sum(time_to_sec(al_duration))/3600/7.8,2),
prev_duration,
prev_tot
FROM cenlr_temps_travail.activity_log 
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
JOIN cenlr_temps_travail.prev ON prev_user_id = u_id AND prev_proj_id = p_id
JOIN (SELECT prev_year AS prev_annee, prev_user_id AS prev_u_id, sum(prev_duration) AS prev_tot FROM cenlr_temps_travail.prev 
GROUP BY prev_year, prev_user_id) prev_tot ON prev_annee = extract(year FROM al_date) AND prev_u_id = prev_user_id
WHERE u_name = 'Mathieu Bossaert'
AND extract(year FROM al_date) = 2011 AND prev_year = 2011
GROUP BY u_name, p_name, extract(month FROM al_date)
ORDER BY 3

Suivi par projet

SELECT  
u_name, 
p_name, 
CASE extract(month FROM al_date) 
WHEN 1 THEN 'Janvier'
WHEN 2 THEN 'Février'
WHEN 3 THEN 'Mars'
WHEN 4 THEN 'Avril'
WHEN 5 THEN 'Mai'
WHEN 6 THEN 'Juin'
WHEN 7 THEN 'Juillet'
WHEN 8 THEN 'Août'
WHEN 9 THEN 'Septembre'
WHEN 10 THEN 'Octobre'
WHEN 11 THEN 'Novembre'
WHEN 12 THEN 'Décembre'
END AS mois,
round(sum(time_to_sec(al_duration))/3600/7.8,2),
prev_duration
FROM cenlr_temps_travail.activity_log 
JOIN cenlr_temps_travail.projects ON al_project_id = p_id
JOIN cenlr_temps_travail.activities ON al_activity_id = a_id
JOIN cenlr_temps_travail.users ON al_user_id = u_id
JOIN cenlr_temps_travail.prev ON prev_user_id = u_id AND prev_proj_id = p_id
AND extract(year FROM al_date) = 2011 AND prev_year = 2011
WHERE p_id = 293
GROUP BY u_name, p_name, extract(month FROM al_date)
ORDER BY 3

Présentation duSystème d'Information

actubdd

brouillon_fonction

confidentialite

creation_grille

demo_postgis

deploiement

gap_analysis

hassan

indexmenu

interface_bddiag

menu

presentation_sig

stage_delphine_montagne

start

time_tracker

Présentation du

Système d'Information