Présentation du

Système d'Information integration_donnees:externes

integration_donnees:externes:dgi

Wed, 27 Apr 2011 23:37:55 +0200

Travailler avec le cadastre

Intégration de ces données à notre SI

Création du schéma et des tables dans la base de données

CREATE SCHEMA dgi;
 
CREATETABLE dgi.compte_communal (
    ccodep character varying(2) NOT NULL,
    ccodir character varying(1) NOT NULL,
    ccocom character varying(3) NOT NULL,
    dnupro character varying(6) NOT NULL
);
 
CREATETABLE dgi.parcelle_majic2 (
    ccodep character varying(2) NOT NULL,
    ccodir character varying(1) NOT NULL,
    ccocom character varying(3) NOT NULL,
 
[...]

voir le script complet

Remplissage des tables : exemple des parcelles non bâties

<?php
/*  Connexionn à la base de données */
if($conn = pg_connect("host=192.168.1.40 port=5432 dbname=sicen user=dba password=dbroot")) echo "connecté à la base !\n";
 
/* ouverture du ficjhier contenant les données */
if($fh = fopen('D:/CEN-LR/NBAT.300.txt','r')) echo "fichier ouvert !\n"; else die($php_errormsg);
 
/* Vidage de la table dgi.nbat */
$res = pg_query($conn, "delete from dgi.nbat") or die(pg_last_error());
 
/* initialisation de qq variables */
$i=1;
$nb_ligne=0;
$nb_erreurs=0;
 
/* parcours du fichier */
while($s = fgets($fh,1024)){
	$sql = "INSERT INTO dgi.nbat VALUES ('".$s."');"; /* création d ela requete d'insertion */
	if($i>1 && (substr($s,19,2)=='10')){
		if($res = pg_query($conn, $sql)){
			$nb_lignes++;
			}
		else{$nb_erreurs++;
			$sql_erreur.=$sql;
		}
	}
	$i++;
}
echo $nb_lignes.' insérées !';
echo $nb_erreurs.' erreurs : ';
echo $sql_erreur;
 
$sql_vacuum_nbat="VACUUM dgi.nbat;";
pg_query($conn, $sql_vacuum_nbat) or die(pg_last_error());
echo "vacuum dgi.nbat ok !";
 
$sql_vide_parcelle_non_bati="DELETE FROM dgi.parcelle_non_bati";
pg_query($conn, $sql_vide_parcelle_non_bati) or die(pg_last_error());
 
$sql_ventil="INSERT INTO dgi.parcelle_non_bati(
            ccodep, ccodir, ccocom, ccopre, ccosec, dnupla, filler, cenr, 
            dcntpa, dsrpar, dnupro, jdatat, dreflf, gpdl, cprsecr, ccosecr, 
            dnuplar, dnupdl, gurbpa, dparpi, ccoarp, gparnf, gparbat, parrev, 
            gpardp, fviti, dnvoiri, dindic, ccovoi, ccoriv, ccocif, gpafpd)
SELECT substring (ligne FROM 1 for 2), substring (ligne FROM 3 for 1), substring (ligne FROM 4 for 3), substring (ligne FROM 7 for 3), substring (ligne FROM 10 for 2), substring (ligne FROM 12 for 4), substring (ligne FROM 16 for 4), substring (ligne FROM 20 for 2), substring (ligne FROM 22 for 9), substring (ligne FROM 31 for 1), substring (ligne FROM 32 for 6), substring (ligne FROM 38 for 8), substring (ligne FROM 46 for 5), substring (ligne FROM 51 for 1), substring (ligne FROM 52 for 3), substring (ligne FROM 55 for 2), substring (ligne FROM 57 for 4), substring (ligne FROM 61 for 3), substring (ligne FROM 64 for 1), substring (ligne FROM 65 for 4), substring (ligne FROM 69 for 1), substring (ligne FROM 70 for 1), substring (ligne FROM 71 for 1), substring (ligne FROM 72 for 12), substring (ligne FROM 84 for 01), substring (ligne FROM 85 for 01), substring (ligne FROM 86 for 4), substring (ligne FROM 90 for 1), substring (ligne FROM 91 for 5), substring (ligne FROM 96 for 4), substring (ligne FROM 100 for 4), substring (ligne FROM 104 for 1) FROM dgi.nbat";
pg_query($conn, $sql_ventil) or die(pg_last_error());
 
$sql_vacuum_parcelle_non_bati="VACUUM dgi.parcelle_non_bati";
pg_query($conn, $sql_vacuum_parcelle_non_bati) or die(pg_last_error());
echo "vacuum dgi.parcelle_non_bati ok !";
?>

Exploitation de ces données

Sans affichage cartographique

CREATE OR REPLACE VIEW dgi.analyse_fonciere_fr9101402 AS 
 SELECT ((compte_communal.ccodep::text || compte_communal.ccodir::text) || compte_communal.ccocom::text) || compte_communal.dnupro::text AS compte_propriete, count(parcelle.id_parcelle) AS nb_parcelle, sum(area2d(parcelle.geometrie)) / 10000::double precision AS surface_en_propriete_ha, sum(area2d(parcelle.geometrie)) / 10000::double precision / count(parcelle.id_parcelle)::double precision AS surf_moy_ha, proprietaires.ddenom, proprietaires.dqualp, proprietaires.dprnlp, proprietaires.dnomlp, proprietaires.dlign3, proprietaires.dlign4, proprietaires.dlign5, proprietaires.dlign6, proprietaires.ccopos, proprietaires.epxnee, proprietaires.dnomcp, proprietaires.dprncp
   FROM ign_bd_parcellaire.parcelle
   JOIN dgi.parcelle_majic2 ON parcelle_majic2.ccodep::text = parcelle.code_dep::text AND parcelle_majic2.ccocom::text = parcelle.code_com::text AND REPLACE(parcelle_majic2.ccopre::text, ' '::text, '0'::text) = parcelle.com_abs::text AND REPLACE(parcelle_majic2.ccosec::text, ' '::text, '0'::text) = parcelle.section::text AND parcelle_majic2.dnupla::text = parcelle.numero::text
   JOIN dgi.compte_communal ON compte_communal.ccodep::text = parcelle_majic2.ccodep::text AND compte_communal.ccodir::text = parcelle_majic2.ccodir::text AND compte_communal.ccocom::text = parcelle_majic2.ccocom::text AND compte_communal.dnupro::text = parcelle_majic2.dnupro::text
   JOIN perimetre ON st_intersects(perimetre.geometrie, parcelle.geometrie)
   JOIN dgi.proprietaires ON compte_communal.ccodep::text = proprietaires.ccodep::text AND compte_communal.ccodir::text = proprietaires.ccodir::text AND compte_communal.ccocom::text = proprietaires.ccocom::text AND compte_communal.dnupro::text = proprietaires.dnupro::text
  WHERE perimetre.id_perimetre::text = 'FR9101402'::text AND proprietaires.gdesip::text = '1'::text
  GROUP BY compte_communal.ccodep, compte_communal.ccodir, compte_communal.ccocom, compte_communal.dnupro, proprietaires.ddenom, proprietaires.dqualp, proprietaires.dprnlp, proprietaires.dnomlp, proprietaires.dlign3, proprietaires.dlign4, proprietaires.dlign5, proprietaires.dlign6, proprietaires.ccopos, proprietaires.jdatnss, proprietaires.epxnee, proprietaires.dnomcp, proprietaires.dprncp
  ORDER BY sum(area2d(parcelle.geometrie)) / 10000::double precision DESC;

Avec affichage carto

CREATE OR REPLACE VIEW dgi.foncier_fr9101402_avec_proprio AS 
 SELECT ((compte_communal.ccodep::text || compte_communal.ccodir::text) || compte_communal.ccocom::text) || compte_communal.dnupro::text AS compte_propriete, parcelle.id_parcelle, area2d(parcelle.geometrie) / 10000::double precision AS surf_ha, proprietaires.ddenom, proprietaires.dqualp, proprietaires.dprnlp, proprietaires.dnomlp, proprietaires.dlign3, proprietaires.dlign4, proprietaires.dlign5, proprietaires.dlign6, proprietaires.ccopos, proprietaires.epxnee, proprietaires.dnomcp, proprietaires.dprncp, parcelle.geometrie
   FROM ign_bd_parcellaire.parcelle
   JOIN dgi.parcelle_majic2 ON parcelle_majic2.ccodep::text = parcelle.code_dep::text AND parcelle_majic2.ccocom::text = parcelle.code_com::text AND REPLACE(parcelle_majic2.ccopre::text, ' '::text, '0'::text) = parcelle.com_abs::text AND REPLACE(parcelle_majic2.ccosec::text, ' '::text, '0'::text) = parcelle.section::text AND parcelle_majic2.dnupla::text = parcelle.numero::text
   JOIN dgi.compte_communal ON compte_communal.ccodep::text = parcelle_majic2.ccodep::text AND compte_communal.ccodir::text = parcelle_majic2.ccodir::text AND compte_communal.ccocom::text = parcelle_majic2.ccocom::text AND compte_communal.dnupro::text = parcelle_majic2.dnupro::text
   JOIN perimetre ON st_intersects(perimetre.geometrie, parcelle.geometrie)
   JOIN dgi.proprietaires ON compte_communal.ccodep::text = proprietaires.ccodep::text AND compte_communal.ccodir::text = proprietaires.ccodir::text AND compte_communal.ccocom::text = proprietaires.ccocom::text AND compte_communal.dnupro::text = proprietaires.dnupro::text
  WHERE perimetre.id_perimetre::text = 'FR9101402'::text AND proprietaires.gdesip::text = '1'::text;

integration_donnees:externes:dgi_sql

Wed, 13 Apr 2011 13:40:54 +0200

Création de la structure des tables cadastrales dans SICEN

CREATE SCHEMA dgi;
 
CREATETABLE dgi.compte_communal (
    ccodep character varying(2) NOT NULL,
    ccodir character varying(1) NOT NULL,
    ccocom character varying(3) NOT NULL,
    dnupro character varying(6) NOT NULL
);
 
CREATETABLE dgi.parcelle_majic2 (
    ccodep character varying(2) NOT NULL,
    ccodir character varying(1) NOT NULL,
    ccocom character varying(3) NOT NULL,
    ccopre character varying(3) NOT NULL,
    ccosec character varying(2) NOT NULL,
    dnupla character varying(4) NOT NULL,
    dnupro character varying(6),
    ccovoi character varying(5)
);
 
CREATETABLE dgi.proprietaires (
    id SERIAL NOT NULL,
    ccocif character varying(4),
    ccodep character varying(2),
    dnuper character varying(6),
    ccodir character varying(1),
    ccocom character varying(3),
    dnupro character varying(6),
    dnulp character varying(2),
    ccodro character varying(1),
    ccodem character varying(1),
    gdesip character varying(1),
    gtoper character varying(1),
    ccoqua character varying(1),
    gnexcf character varying(2),
    dtaucf character varying(3),
    dnatpr character varying(3),
    ccogrm character varying(2),
    dsglpm character varying(10),
    dforme character varying(7),
    ddenom character varying(60),
    gtyp3 character varying(1),
    gtyp4 character varying(1),
    gtyp5 character varying(1),
    gtyp6 character varying(1),
    dlign3 character varying(30),
    dlign4 character varying(36),
    dlign5 character varying(30),
    dlign6 character varying(32),
    ccopay character varying(3),
    ccodep1a2 character varying(2),
    ccodira character varying(1),
    ccocom_adr character varying(3),
    ccovoi character varying(5),
    ccoriv character varying(4),
    dnvoiri character varying(4),
    dindic character varying(1),
    ccopos character varying(5),
    dnirpp character varying(10),
    dqualp character varying(3),
    dnomlp character varying(30),
    dprnlp character varying(15),
    jdatnss character varying(10),
    dldnss character varying(58),
    epxnee character varying(3),
    dnomcp character varying(30),
    dprncp character varying(15),
    topcdi character varying(1),
    oriard character varying(1),
    fixard character varying(1),
    datadr character varying(8),
    topdec character varying(1),
    datdec character varying(4),
    dsiren character varying(10),
    ccmm character varying(1),
    topja character varying(1),
    datja character varying(8),
    filler character varying(4),
    anospi character varying(3),
    cblpmo character varying(1),
    gtodge character varying(1),
    gpctf character varying(1),
    gpctsb character varying(1),
    jmodge character varying(2),
    jandge character varying(4),
    jantfc character varying(4),
    jantbc character varying(4),
    filler2 character varying(39)
);
COMMENT ON COLUMN dgi.proprietaires.ccocif IS 'code cdif';
COMMENT ON COLUMN dgi.proprietaires.ccodep IS 'code département';
COMMENT ON COLUMN dgi.proprietaires.dnuper IS 'numéro de personne dans le cdif';
COMMENT ON COLUMN dgi.proprietaires.ccodir IS 'code direction';
COMMENT ON COLUMN dgi.proprietaires.ccocom IS 'code commune INSEE';
COMMENT ON COLUMN dgi.proprietaires.dnupro IS 'compte communal';
COMMENT ON COLUMN dgi.proprietaires.dnulp IS 'numéro de libellé partiel';
COMMENT ON COLUMN dgi.proprietaires.ccodro IS 'code du droit réel ou parti- culier';
COMMENT ON COLUMN dgi.proprietaires.ccodem IS 'code du démembrement/indivi-sion';
COMMENT ON COLUMN dgi.proprietaires.gdesip IS 'indicateur du destinataire de l''avis d''imposition';
COMMENT ON COLUMN dgi.proprietaires.gtoper IS 'indicateur de personne physi-que ou morale';
COMMENT ON COLUMN dgi.proprietaires.ccoqua IS 'Code qualité de personne phy-sique';
COMMENT ON COLUMN dgi.proprietaires.gnexcf IS 'code exo ecf';
COMMENT ON COLUMN dgi.proprietaires.dtaucf IS 'taux exo ecf';
COMMENT ON COLUMN dgi.proprietaires.dnatpr IS 'Code nature de personne phy- sique ou morale';
COMMENT ON COLUMN dgi.proprietaires.ccogrm IS 'Code groupe de personne mora-le';
COMMENT ON COLUMN dgi.proprietaires.dsglpm IS 'sigle de personne morale';
COMMENT ON COLUMN dgi.proprietaires.dforme IS 'forme juridique abrégée majic2';
COMMENT ON COLUMN dgi.proprietaires.ddenom IS 'Dénomination de personne phy-sique ou morale';
COMMENT ON COLUMN dgi.proprietaires.gtyp3 IS 'type de la 3eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.gtyp4 IS 'Type de la 4eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.gtyp5 IS 'type de la 5eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.gtyp6 IS 'type de la 6eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.dlign3 IS '3eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.dlign4 IS '4eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.dlign5 IS '5eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.dlign6 IS '6eme ligne d''adresse';
COMMENT ON COLUMN dgi.proprietaires.ccopay IS 'code de pays étranger et TOM';
COMMENT ON COLUMN dgi.proprietaires.ccodep1a2 IS 'Code département de l''adresse';
COMMENT ON COLUMN dgi.proprietaires.ccodira IS 'Code direction de l''adresse';
COMMENT ON COLUMN dgi.proprietaires.ccocom_adr IS 'Code commune de l''adresse';
COMMENT ON COLUMN dgi.proprietaires.ccovoi IS 'Code majic2 de la voie';
COMMENT ON COLUMN dgi.proprietaires.ccoriv IS 'Code rivoli de la voie';
COMMENT ON COLUMN dgi.proprietaires.dnvoiri IS 'numéro de voirie';
COMMENT ON COLUMN dgi.proprietaires.dindic IS 'indice de répétition de voi- rie';
COMMENT ON COLUMN dgi.proprietaires.ccopos IS 'Code postal';
COMMENT ON COLUMN dgi.proprietaires.dnirpp IS 'zone à blanc';
COMMENT ON COLUMN dgi.proprietaires.dqualp IS 'Qualité abrégée';
COMMENT ON COLUMN dgi.proprietaires.dnomlp IS 'Nom d''usage';
COMMENT ON COLUMN dgi.proprietaires.dprnlp IS 'Prénoms associés au nom d''usage';
COMMENT ON COLUMN dgi.proprietaires.jdatnss IS 'date de naissance';
COMMENT ON COLUMN dgi.proprietaires.dldnss IS 'lieu de naissance';
COMMENT ON COLUMN dgi.proprietaires.epxnee IS 'mention du complément';
COMMENT ON COLUMN dgi.proprietaires.dnomcp IS 'Nom complément';
COMMENT ON COLUMN dgi.proprietaires.dprncp IS 'Prénoms associés au complé-ment';
COMMENT ON COLUMN dgi.proprietaires.topcdi IS 'top transalp';
COMMENT ON COLUMN dgi.proprietaires.oriard IS 'origine adresse';
COMMENT ON COLUMN dgi.proprietaires.fixard IS 'pérennité adresse';
COMMENT ON COLUMN dgi.proprietaires.datadr IS 'date adresse';
COMMENT ON COLUMN dgi.proprietaires.topdec IS 'origine décès';
COMMENT ON COLUMN dgi.proprietaires.datdec IS 'date de décès';
COMMENT ON COLUMN dgi.proprietaires.dsiren IS 'numéro siren';
COMMENT ON COLUMN dgi.proprietaires.ccmm IS 'création compte cadastral';
COMMENT ON COLUMN dgi.proprietaires.topja IS 'indic jeune agriculteur';
COMMENT ON COLUMN dgi.proprietaires.datja IS 'date jeune agriculteur';
COMMENT ON COLUMN dgi.proprietaires.filler IS 'à blanc';
COMMENT ON COLUMN dgi.proprietaires.cblpmo IS 'code blocage caractère personne morale';
COMMENT ON COLUMN dgi.proprietaires.gtodge IS 'top appartenance à la DGE';
COMMENT ON COLUMN dgi.proprietaires.gpctf IS 'top paiement centralisé à la taxe foncière';
COMMENT ON COLUMN dgi.proprietaires.gpctsb IS 'top paiement centralisé à la TSBCS';
COMMENT ON COLUMN dgi.proprietaires.jmodge IS 'mois d''entrée à la DGE';
COMMENT ON COLUMN dgi.proprietaires.jandge IS 'année d''entrée à la DGE';
COMMENT ON COLUMN dgi.proprietaires.jantfc IS 'année d''entrée paiement TF';
COMMENT ON COLUMN dgi.proprietaires.jantbc IS 'année d''entrée paiement TSBCS';
 
CREATETABLE dgi.nbat (
    ligne character varying(150) NOT NULL
);
 
CREATETABLE dgi.parcelle_non_bati (
    id SERIAL NOT NULL,
    ccodep character varying(2),
    ccodir character varying(1),
    ccocom character varying(3),
    ccopre character varying(3),
    ccosec character varying(2),
    dnupla character varying(4),
    filler character varying(4),
    cenr character varying(2),
    dcntpa character varying(9),
    dsrpar character varying(1),
    dnupro character varying(6),
    jdatat character varying(8),
    dreflf character varying(5),
    gpdl character varying(1),
    cprsecr character varying(3),
    ccosecr character varying(2),
    dnuplar character varying(4),
    dnupdl character varying(3),
    gurbpa character varying(1),
    dparpi character varying(4),
    ccoarp character varying(1),
    gparnf character varying(1),
    gparbat character varying(1),
    parrev character varying(12),
    gpardp character varying(1),
    fviti character varying(1),
    dnvoiri character varying(4),
    dindic character varying(1),
    ccovoi character varying(5),
    ccoriv character varying(4),
    ccocif character varying(4),
    gpafpd character varying(1)
);
 
COMMENT ON COLUMN dgi.parcelle_non_bati.ccodep IS 'Code département';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccodir IS 'Code direction';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccocom IS 'Code commune INSEE ou DGI d’arrondissement';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccopre IS 'Préfixe de section ou quartier servi pour les communes associées, &des1.';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccosec IS 'Section cadastrale';
COMMENT ON COLUMN dgi.parcelle_non_bati.dnupla IS 'Numéro de plan';
COMMENT ON COLUMN dgi.parcelle_non_bati.cenr IS 'Code enregistrement (10)';
COMMENT ON COLUMN dgi.parcelle_non_bati.dcntpa IS 'Contenance de la parcelle (en centiares)';
COMMENT ON COLUMN dgi.parcelle_non_bati.dsrpar IS 'Lettre de série-role';
COMMENT ON COLUMN dgi.parcelle_non_bati.dnupro IS 'Compte communal du propriétaire de la parcelle';
COMMENT ON COLUMN dgi.parcelle_non_bati.jdatat IS 'Date de l acte (jjmmaaaa)';
COMMENT ON COLUMN dgi.parcelle_non_bati.dreflf IS 'Référence au Livre Foncier en Alsace-Moselle';
COMMENT ON COLUMN dgi.parcelle_non_bati.gpdl IS 'Indicateur d’appartenance à pdl (cf. détail supra), Identifiant de la pdl (si gpdl =2)';
COMMENT ON COLUMN dgi.parcelle_non_bati.cprsecr IS 'Préfixe de la parcelle de référence';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccosecr IS 'Section de la parcelle de référence';
COMMENT ON COLUMN dgi.parcelle_non_bati.dnuplar IS 'N° de plan de la parcelle de référence';
COMMENT ON COLUMN dgi.parcelle_non_bati.dnupdl IS 'Numéro d’ordre de la pdl (en général, 001)';
COMMENT ON COLUMN dgi.parcelle_non_bati.gurbpa IS 'Caractère Urbain de la parcelle (U, * ou blanc)';
COMMENT ON COLUMN dgi.parcelle_non_bati.dparpi IS 'Numéro de parcelle primitive';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccoarp IS 'Indicateur d’arpentage (A ou blanc)';
COMMENT ON COLUMN dgi.parcelle_non_bati.gparnf IS 'Indicateur de parcelle non figurée au plan (1 = figurée, 0 = non figurée)';
COMMENT ON COLUMN dgi.parcelle_non_bati.gparbat IS 'Indicateur de parcelle référençant un bâtiment (1 = oui, sinon 0)';
COMMENT ON COLUMN dgi.parcelle_non_bati.parrev IS 'Info de la révision (INDISPONIBLE)';
COMMENT ON COLUMN dgi.parcelle_non_bati.gpardp IS 'parcelle n''appartenant pas au domaine public (INDISPONIBLE)';
COMMENT ON COLUMN dgi.parcelle_non_bati.fviti IS 'parcelle au casier viticole  (INDISPONIBLE)';
COMMENT ON COLUMN dgi.parcelle_non_bati.dnvoiri IS 'Numéro de voirie';
COMMENT ON COLUMN dgi.parcelle_non_bati.dindic IS 'Indice de répétition';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccovoi IS 'Code Majic2 de la voie';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccoriv IS 'Code Rivoli de la voie';
COMMENT ON COLUMN dgi.parcelle_non_bati.ccocif IS 'Code du cdif (code topad)';
COMMENT ON COLUMN dgi.parcelle_non_bati.gpafpd IS 'Domanialité, représentation au plan  (INDISPONIBLE)';
 
CREATETABLE dgi.prop (
    ligne character varying(550) NOT NULL
);
 
CREATETABLE dgi.voie (
    ccodep character varying(2) NOT NULL,
    ccodir character varying(1) NOT NULL,
    ccocom character varying(3) NOT NULL,
    ccovoi character varying(5) NOT NULL,
    ccoriv character varying(4)
);
 
ALTER TABLE ONLY dgi.parcelle_non_bati
    ADD CONSTRAINT parcelle_non_bati_pkey PRIMARY KEY (id);
 
ALTER TABLE ONLY dgi.compte_communal
    ADD CONSTRAINT pk_compte_communal PRIMARY KEY (ccodep, ccodir, ccocom, dnupro);
 
ALTER TABLE ONLY dgi.parcelle_majic2
    ADD CONSTRAINT pk_parcelle PRIMARY KEY (ccodep, ccodir, ccocom, ccopre, ccosec, dnupla);
 
ALTER TABLE ONLY dgi.voie
    ADD CONSTRAINT pk_voie PRIMARY KEY (ccodep, ccodir, ccocom, ccovoi);
 
ALTER TABLE ONLY dgi.proprietaires
    ADD CONSTRAINT proprietaires_pkey PRIMARY KEY (id);
 ind_ccocom ON parcelle_majic2 USING btree (ccocom); ind_ccodep ON parcelle_majic2 USING btree (ccodep); ind_ccopre ON parcelle_majic2 USING btree (ccopre); ind_ccopre_tranfo_bd_par ON parcelle_majic2 USING btree (REPLACE((ccopre)::text, ''::text, '0'::text)); ind_ccosec ON parcelle_majic2 USING btree (ccosec); ind_dnupla ON parcelle_majic2 USING btree (dnupla);
 ind_gdesip ON proprietaires USING btree (gdesip);
 ind_lien_compte_communal ON parcelle_majic2 USING btree (ccodep, ccodir, ccocom, dnupro);
 ind_ref_compte_communal ON proprietaires USING btree (ccodep, ccodir, ccocom, dnupro);
 ind_unique_compte_communal ON compte_communal USING btree (ccodep, ccodir, ccocom, dnupro);
 ind_unique_lien_bd_parcellaire_majic2 ON parcelle_majic2 USING btree (ccodep, ccocom, ccopre, ccosec, dnupla);
 ind_unique_parcelle_majic2 ON parcelle_majic2 USING btree (ccodep, ccodir, ccocom, ccopre, ccosec, dnupla);
 
ALTER TABLE ONLY dgi.proprietaires
    ADD CONSTRAINT fk_dgi_proprietaires_ref_compte_communal FOREIGN KEY (ccodep, ccodir, ccocom, dnupro) REFERENCES compte_communal(ccodep, ccodir, ccocom, dnupro);
 
ALTER TABLE ONLY dgi.parcelle_majic2
    ADD CONSTRAINT fk_parcelle_ref_compte_communal FOREIGN KEY (ccodep, ccodir, ccocom, dnupro) REFERENCES compte_communal(ccodep, ccodir, ccocom, dnupro);
 
ALTER TABLE ONLY dgi.parcelle_majic2
    ADD CONSTRAINT fk_parcelle_ref_voie FOREIGN KEY (ccodep, ccodir, ccocom, ccovoi) REFERENCES voie(ccodep, ccodir, ccocom, ccovoi);

integration_donnees:externes:diren

Sat, 06 Feb 2010 13:21:54 +0200

integration_donnees:externes:generalites

Sat, 06 Feb 2010 13:21:54 +0200

integration_donnees:externes:ifn

Thu, 28 Apr 2011 14:15:43 +0200

Intégration des thèmes de l'IFN à notre base de données

Avril 2011

Intégration des régions forestières

http://www.ifn.fr/spip/IMG/zip/rf250_l93_shp.zip

Création d'un schéma IFN

CREATE SCHEMA ifn;
GRANT USAGE ON SCHEMA ifn TO public;

Création du script sql d'insertion des données

shp2pgsql -s 310024140 -g geometrie -I rfifn250_l93.shp ifn.regions_forestieres>rfifn.sql

Intégartion des données

psql -h 192.168.1.230 -U nom_utilisateur sicen
\i rfifn.sql

integration_donnees:externes:ign

Wed, 30 Jan 2013 18:47:00 +0200

Intégration des thèmes de l'IGN à notre base de données

octobre 2010

Calcul de la taille sur disque des tables du schéma ign_bd_topo (5 départements)

pg_total_relation_size(regclass) renvoit l'espace disque total utilisé par la table de cet OID ou nom, incluant les index et les données de la partie TOAST

voici les statistiques pour notre serveur (RHEL 4, diques en ext4) :

SELECT f_table_name, pg_total_relation_size('ign_bd_topo.'||f_table_name), pg_size_pretty(pg_total_relation_size('ign_bd_topo.'||f_table_name))
  FROM geometry_columns
  WHERE f_table_schema='ign_bd_topo'

Au total, l'ensemble des tables prend une place d'environ 2.9 Giga, le mnt n'y est pas encore intégré (http://trac.osgeo.org/postgis/wiki/WKTRaster|piste]])

nom de la table	pg_total_relation_size()	pg_size_pretty(pg_total_relation_size())
aire_triage	172032	168 kB
bati_indifferencie	265838592	254 MB
bati_industriel	15687680	15 MB
bati_remarquable	2703360	2640 kB
canalisation_eau	155648	152 kB
chef_lieu	385024	376 kB
chemin	185024512	176 MB
cimetiere	794624	776 kB
conduite	90112	88 kB
construction_lineaire	19144704	18 MB
construction_ponctuelle	1294336	1264 kB
construction_surfacique	466944	456 kB
emprise	417792	408 kB
gare	90112	88 kB
hydronyme	475136	464 kB
lieu_dit	34013184	32 MB
lieu_dit_habite	4743168	4632 kB
lieu_dit_non_habite	4349952	4248 kB
ligne_electrique	434176	424 kB
ligne_orographique	31105024	30 MB
oronyme	2719744	2656 kB
pai_administratif_militaire	548864	536 kB
pai_culture_loisirs	385024	376 kB
pai_espace_naturel	4685824	4576 kB
pai_gestion_eaux	311296	304 kB
pai_hydrographie	475136	464 kB
pai_industriel_commercial	958464	936 kB
pai_orographie	2719744	2656 kB
pai_religieux	507904	496 kB
pai_sante	122880	120 kB
pai_science_enseignement	393216	384 kB
pai_sport	90112	88 kB
pai_transport	638976	624 kB
pai_zone_habitation	5169152	5048 kB
piste_aerodrome	172032	168 kB
point_eau	1253376	1224 kB
poste_transformation	114688	112 kB
pylone	2088960	2040 kB
reservoir	1941504	1896 kB
reservoir_eau	1662976	1624 kB
route	509304832	486 MB
route_nommee	130162688	124 MB
route_primaire	12091392	12 MB
route_secondaire	270352384	258 MB
surface_activite	2908160	2840 kB
surface_eau	17031168	16 MB
surface_route	483328	472 kB
terrain_sport	1474560	1440 kB
toponyme_communication	237568	232 kB
toponyme_divers	622592	608 kB
toponyme_ferre	81920	80 kB
transport_cable	106496	104 kB
troncon_cours_eau	104742912	100 MB
troncon_laisse	892928	872 kB
troncon_voie_ferree	2121728	2072 kB
zone_vegetation	1361412096	1298 MB
total	3008372736	2869 MB

juillet 2010

Création de la table des lieu-dits pour l'interface de saisie

Les toponymes sont stockés dans 4 tables de la BD TOPO :

lieu_dit_habite
lieu_dit_non_habite
oronyme
hydronyme

De manière à pouvoir proposer efficacement une aide à la saisie d'un lieu-dit selon l'endroit ou la donnée a été localisée sur la carte, nous allons rassembler ces informations dans une seule et même table nommée lieu_dit, dans le schéma public de notre base de données :

lieu_dit.sql

CREATE TABLE lieu_dit
(
  id character varying(24) PRIMARY KEY,
  origin_nom character varying(13),
  nom character varying(70),
  importance character varying(2),
  nature character varying(24),
  code_insee character varying(5), 
  /* Ce dernier champ n'existe pas initialement dans les tables "nymes" de la BD TOPO 
  Nous le rajoutons pour permettre des jointures avec la table commune et accélérer 
  le temps de récupération des lieu-dits proches d'un objet créé sur la carte */
  gid serial UNIQUE -- utile pour QGIS
)
WITH (
  OIDS=FALSE
);
 
/* création de la colonne géométrique de type point en lambert 93 */
 
SELECT AddGeometryColumn('lieu_dit', 'geometrie', 310024140, 'POINT', 2 );
 
/* remplissage de la table */
 
INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.lieu_dit_habite;
INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.lieu_dit_non_habite;
/*INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.hydronyme;
INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.oronyme;*/
 
/* indexation des géométries */
 
CREATE INDEX lieu_dit_geometrie_gist
  ON lieu_dit
  USING gist
  (geometrie);
 
/* mise à jour du code insee */
 
UPDATE lieu_dit
SET code_insee=ign_bd_topo.commune.code_insee
FROM ign_bd_topo.commune
WHERE st_intersects(ign_bd_topo.commune.geometrie, lieu_dit.geometrie);
 
/* indexation du code_insee des lieu-dits */
 
CREATE INDEX ind_lieu_dit_code_insee
  ON lieu_dit
  USING btree
  (code_insee);

Comment lister les lieux-dits situés dans un rayon de 2 km de l'objet créé

SELECT ign_bd_topo.commune.nom||' | '||lieu_dit.nom||' - '||round(st_distance(centroid(st_transform(saisie_faune.geometrie,310024140)), lieu_dit.geometrie))||' m.', round(st_distance(centroid(st_transform(saisie_faune.geometrie,310024140)), lieu_dit.geometrie)), ign_bd_topo.commune.nom
FROM (SELECT * FROM saisie_faune WHERE gid=4) AS saisie_faune JOIN ign_bd_topo.commune ON st_intersects(ign_bd_topo.commune.geometrie, centroid(st_transform(saisie_faune.geometrie,310024140)))
JOIN lieu_dit USING (code_insee)
WHERE st_intersects(st_buffer(st_transform(saisie_faune.geometrie,310024140),2000), lieu_dit.geometrie)
ORDER BY round(st_distance(st_transform(saisie_faune.geometrie,310024140), lieu_dit.geometrie)) ASC, ign_bd_topo.commune.nom

SELECT ign_bd_topo.commune.nom||' | '||lieu_dit.nom||' - '||round(st_distance(centroid(st_transform(geomfromtext(),310024140)), lieu_dit.geometrie))||' m.', round(st_distance(centroid(st_transform(geomfromtext(),310024140)), lieu_dit.geometrie)), ign_bd_topo.commune.nom
FROM ign_bd_topo.commune JOIN lieu_dit USING (code_insee) 
WHERE st_intersects(ign_bd_topo.commune.geometrie, centroid(st_transform(geomfromtext(),310024140)))
AND st_intersects(st_buffer(st_transform(geomfromtext(),310024140),2000), lieu_dit.geometrie)
ORDER BY round(st_distance(st_transform(geomfromtext(),310024140), lieu_dit.geometrie)) ASC, ign_bd_topo.commune.nom

février 2010

integration de la BD PARCELLAIRE dans le schema ign_bd_parcellaire

format : shp
projection : lambert 93

janvier 2010

Intégration de la BDTOPO

Transformation des MIF en shp

La BD topo a été initialement commandée en MIF/MID…

#!/bin/bash
set -x
i=1 # on initialise le compteur
option=''
for rep in `ls -d */`; do
	for mif in `find $rep -iname "*.MIF"`; do
                #echo $i : $PWD : $rep : $(echo $(basename $mif) | cut -d"." -f1) | tr '[A-Z]' '[a-z]'
                echo ogr2ogr $option -a_srs \"+init=IGNF:LAMB93\" -f \"ESRI Shapefile\" SHP/$(echo $(basename $mif) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').shp ./$mif
                #echo ogr2ogr $option -f \"PostgreSQL\" PG:\"host=localhost user=dba dbname=ign password=dbroot\" $PWD/$rep$mif -nln bdtopo.$(echo $mif | cut -d"." -f1) | tr '[A-Z]' '[a-z]' 
		eval ogr2ogr $option -a_srs \"+init=IGNF:LAMB93\" -f \"ESRI Shapefile\" SHP/$(echo $(basename $mif) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').shp ./$mif
	done
	let $[ i+=1 ] # incremente i de 1 a chaque boucle
	option='-update -append' # après la première série de fichier, il faudra ajouter les nouvelles données aux fichiers shp déja existants.
done

Intégration des shp à la base de données

#!/bin/bash
for shp in `ls -R *.shp`; do
	eval shp2pgsql -d -W "latin1" -s 310024140 -g geometrie -I -S $shp ign_bd_topo.$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]')>../SQL/$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').sql
	eval psql -f ../SQL/$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').sql -h localhost -U dba sicen 2>>../erreurs.log
done

février 2009

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”“

- Données de la BD Parcellaire (Département du Gard)

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

integration_donnees:externes:inpn

Fri, 14 Oct 2011 14:56:55 +0200

Intégration des référentiels de l'INPN

site de l'INPN

Référentiels habitat

L'INPN a mis en ligne récemment les référentiels habitats au format excel

Le travail consiste maintenant a intégrer ces référentiels à la base de données, en utilisant les commentaires de table et de colonne pour stocker les métadonnées fournies avec le référentiel (2 premières feuilles de calcul du fichier).

Les étapes de son intégration à la bdd sont :

la création de la table
la création des commentaires sur la table et sur les colonne
l'intégration des données
la création des contraintes

Le premier fichier intégré concerne la typologie NATURA 2000 Le fichier sql correspondant est disponible ici

Le second fichier intégré concerne la typologie corine biotopes Le fichier sql correspondant est disponible ici

Référentiels taxonomiques

La version 4 du référentiel taxonomique de l'INPN a été mise à disposition jour en octobre 2011.

page de téléchargement de l'INPN : http://inpn.mnhn.fr/telechargement/referentielEspece

conditions de mise à disposition

aucune copie du référentiel TAXREF de sera faite et transmise à un tiers sans que celui-ci n'ait rempli le présent formulaire ;
le référentiel TAXREF, ou une de ses composantes telles que décrites dans le document de citation des sources, sera cité dans toute publication ou présentation qui l'utilisera ;
aucune partie du référentiel TAXREF ne sera mise à disposition en ligne sans autorisation préalable du SPN/MNHN ;
en cas de découverte d'erreurs il est prié de bien vouloir en informer les éditeurs de façon à constamment accroitre la qualité du référentiel.

Décompression de l'archive dans le dossier taxref

Placer les 3 fichiers ci dessous (2 à télécharger et 1 à créer) dans ce répertoire. Ce répertoire contiendra donc à terme les fichiers suivants :

1_creation_table.sql
2_insertion_donnees_inpn.sql (fichier créé par le script taxref.sh à partir de TAXREFv4.0.txt)
3_creation_contraintes.sql
taxref.sh
TAXREFv4.0.txt (téléchargé sur le site de l'INPN)

Création de la table dans le schema inpn

1_creation_table.sql

DROP TABLE IF EXISTS temp.taxref;
CREATE TABLE temp.taxref
(
  regne character varying,
  phylum character varying,
  classe character varying,
  ordre character varying,
  famille character varying,
  cd_nom character varying PRIMARY KEY,
  cd_taxsup character varying,
  cd_ref character varying,
  rang character varying,
  lb_nom character varying,
  lb_auteur character varying,
  nom_complet character varying,
  nom_vern character varying,
  nom_vern_eng character varying,
  habitat character varying,
  fr character varying,
  gf character varying,
  mar character varying,
  gua character varying,
  sm character varying,
  sb character varying,
  spm character varying,
  may character varying,
  epa character varying,
  reu character varying,
  taaf character varying,
  pf character varying,
  nc character varying,
  wf character varying,
  cli character varying,
  aphia_id character varying,
  CONSTRAINT fk_cd_ref_est_cd_nom FOREIGN KEY (cd_ref)
      REFERENCES temp.taxref (cd_nom) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
 
COMMENT ON COLUMN temp.taxref.REGNE IS 'Règne auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.PHYLUM IS 'Embranchement auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.CLASSE IS 'Classe à laquelle le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.ORDRE IS 'Ordre auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.FAMILLE IS 'Famille à laquelle le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.CD_NOM IS 'Identifiant unique du nom scientifique';
COMMENT ON COLUMN temp.taxref.CD_TAXSUP IS 'Identifiant (CD_NOM) du taxon supérieur';
COMMENT ON COLUMN temp.taxref.CD_REF IS 'Identifiant (CD_NOM) du taxon de référence (nom retenu)';
COMMENT ON COLUMN temp.taxref.RANG IS 'Rang taxonomique (lien vers TAXREF_RANG)';
COMMENT ON COLUMN temp.taxref.LB_NOM IS 'Nom scientifique du taxon (sans l’autorité)';
COMMENT ON COLUMN temp.taxref.LB_AUTEUR  IS 'Autorité du taxon (Auteur, année, gestion des parenthèses)';
COMMENT ON COLUMN temp.taxref.NOM_COMPLET IS 'Combinaison des champs pour donner le nom complet (~LB_NOM+" "+LB_AUTEUR)';
COMMENT ON COLUMN temp.taxref.NOM_VERN IS 'Noms vernaculaires français';
COMMENT ON COLUMN temp.taxref.NOM_VERN_ENG IS 'Noms vernaculaires anglais';
COMMENT ON COLUMN temp.taxref.HABITAT IS 'Code de l''habitat (lien vers TAXREF_HABITATS)';
COMMENT ON COLUMN temp.taxref.FR IS 'Statut biogéographique en France métropolitaine (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.GF IS 'Statut biogéographique en Guyane (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.MAR IS 'Statut biogéographique en Martinique (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.GUA IS 'Statut biogéographique en Guadeloupe (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SM IS 'Statut biogéographique à Saint-Martin (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SB IS 'Statut biogéographique à Saint-Barthélémy (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SPM IS 'Statut biogéographique à Saint-Pierre et Miquelon (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.MAY IS 'Statut biogéographique à Mayotte (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.EPA IS 'Statut biogéographique aux Iles Eparses (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.REU IS 'Statut biogéographique à la Réunion (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.TAAF IS 'Statut biogéographique aux TAAF (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.PF IS 'Statut biogéographique en Polynésie française (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.NC IS 'Statut biogéographique en Nouvelle-Calédonie (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.WF IS 'Statut biogéographique à Wallis et Futuna (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.CLI IS 'Statut biogéographique à Clipperton (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.APHIA_ID IS 'Identifiant unique de World Register of Marine Species (WoRMS)';

Ajout de la contrainte d'intégrité référentielle entre cd_taxsup et cd_nom, Création des index

nécessite le remplacement par null des valeurs '0', concerne les règnes :

3_creation_contraintes.sql

UPDATE temp.taxref SET cd_taxsup=NULL WHERE cd_taxsup='0';
INSERT INTO "temp".taxref(regne, cd_nom, cd_taxsup, cd_ref) VALUES ('vivant',349525,349525,349525);
ALTER TABLE temp.taxref 
  ADD CONSTRAINT fk_cd_taxsup_est_cd_nom FOREIGN KEY (cd_taxsup)
      REFERENCES temp.taxref (cd_nom) DEFERRABLE INITIALLY DEFERRED;
 
CREATE INDEX cd_ref_ind ON temp.taxref USING btree (cd_ref);
CREATE INDEX classe_ind ON temp.taxref USING btree (classe);
CREATE INDEX famille_ind ON temp.taxref USING btree (famille);
CREATE INDEX lb_nom_ind ON temp.taxref USING btree (lb_nom);
CREATE INDEX nom_complet_ind ON temp.taxref USING btree (nom_complet);
CREATE INDEX ordre_ind ON temp.taxref USING btree (ordre);
CREATE INDEX phylum_ind ON temp.taxref USING btree (phylum);
CREATE INDEX regne_ind ON temp.taxref USING btree (regne);

Création du script sql d'insertion

taxref.sh

## On supprime du fichier les balises html
sed -e "s/<i>//g" TAXREFv4.0.txt > TAXREFv4.0_.txt && mv TAXREFv4.0_.txt TAXREFv4.0.txt 
sed -e "s/<\/i>//g" TAXREFv4.0.txt > TAXREFv4.0_.txt && mv TAXREFv4.0_.txt TAXREFv4.0.txt
## On supprime la première ligne du fichier (nom des colonnes)
sed '/REGNE/d' TAXREFv4.0.txt > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on vérifie que la ligne d'en-tête n'existe plus
head -n 1 TAXREFv4.0.sql
## on échape les simples quote (apostrophe)
sed -e "s/'/''/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on vérifie qu'elles ont été échapées
grep -i "\w'\w" TAXREFv4.0.sql
## on remplace les tabulations (délimiteurs) par ','cartactères)
## erreur ! vire la colonne cli!!!
sed "s/\t/\',\'/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on crée le début de la commande sql
sed "s/^/INSERT INTO temp.taxref VALUES ('/" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on crée la fin de la commande sql
sed "s/\r/');/" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
##on remplace les chaines vides '' par la valeur null
sed "s/''/null/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
 
mv TAXREFv4.0.sql 2_insertion_donnees_inpn.sql
 
# Exécution des 3 scripts sur la base de données
# adapter la commande avec vos paramètres de connexion
psql -h 192.168.1.230 -U dba -d sicen -f 1_creation_table.sql
psql -h 192.168.1.230 -U dba -d sicen -f 2_insertion_donnees_inpn.sql
psql -h 192.168.1.230 -U dba -d sicen -f 3_creation_contraines.sql

Statut des espèces

Intégration du fichier TAXREF_INPN_STATUTS.txt

CREATE TABLE inpn.statut(
statut character(1) PRIMARY KEY,
description character varying);
 
COMMENT ON COLUMN inpn.statut.statut IS 'Abrégé tel qu''il apparaît dans le référentiel pour les champs ci-dessus';
COMMENT ON COLUMN inpn.statut.description IS 'Libellé du statut d''indigénat';
COMMENT ON TABLE inpn.statut IS 'Permet d''interpréter les champ FR, REU, GUA, SMSB, MAR, GF, MAY, TAAF et SPM du référentiel.';
 
INSERT INTO inpn.statut VALUES('A','Absente');
INSERT INTO inpn.statut VALUES('B','Accidentelle / Visiteuse');
INSERT INTO inpn.statut VALUES('C','Cryptogène');
INSERT INTO inpn.statut VALUES('D','Douteux');
INSERT INTO inpn.statut VALUES('E','Endémique');
INSERT INTO inpn.statut VALUES('F','Trouvé en fouille');
INSERT INTO inpn.statut VALUES('I','Introduite');
INSERT INTO inpn.statut VALUES('J','Introduite envahissante');
INSERT INTO inpn.statut VALUES('M','Domestique / Introduite non établie');
INSERT INTO inpn.statut VALUES('P','Présente');
INSERT INTO inpn.statut VALUES('S','Subendémique');
INSERT INTO inpn.statut VALUES('W','Disparue');
INSERT INTO inpn.statut VALUES('X','Eteinte');
INSERT INTO inpn.statut VALUES('Y','Introduite éteinte');
INSERT INTO inpn.statut VALUES('Z','Endémique éteinte');
 
ALTER TABLE inpn.taxref ADD CONSTRAINT fk_fr_est_statut FOREIGN KEY (fr) REFERENCES inpn.statut (statut);

Espèces faisant l'objet d'une réglementation

Page de mise à disposition de ces référentiel sur le site de l'INPN

les différentes réglementations

Le fichier sql correspondant est disponible ici

les espèces protégées

Le fichier sql correspondant est disponible ici

Liste des espèces pour Natura 2000 (412 espèces)

L'inpn mentionne 413 espèces sur la page correspondante mais le Grand cormoran (cd_nom=2440) est mentionné 2 fois

CREATE TABLE inpn.taxons_natura_2000
(
  cd_ref character varying NOT NULL,
  annexe character varying,
  CONSTRAINT pk_taxons_nature2000 PRIMARY KEY (cd_ref),
  CONSTRAINT fk_cd_ref_est_dans_taxref FOREIGN KEY (cd_ref)
      REFERENCES inpn.taxref (cd_nom) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Le fichier sql correspondant (avec les données) est disponible ici

integration_donnees:externes:siglr

Tue, 07 Jun 2011 16:40:41 +0200

Intégration de la base de données d'occupation du sol du Languedoc-Roussillon de SIGLR

Procédure (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;	/* la fonction substring(cde_06 FROM 1 FOR 1) fera l'affaire */
ALTER TABLE occsol_2006 DROP COLUMN niv2_06;	/* la fonction substring(cde_06 FROM 2 FOR 1) fera l'affaire */
ALTER TABLE occsol_2006 DROP COLUMN niv3_06;	/* la fonction substring(cde_06 FROM 3 FOR 1) fera l'affaire */
ALTER TABLE occsol_2006 DROP COLUMN intit_06;	/* on utilisera une jointure sur la table poste_ocsol */
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);

Résolution des problèmes d'intégration de la base d'occupation du sol

Utilisation de la fonction CleanPolygons

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://trac.osgeo.org/postgis/wiki/UsersWikiCleanPolygons

Présentation duSystème d'Information integration_donnees:externes