Utilisation de DFI

Nous vous proposons ici une méthode pour utiliser les données ouvertes Documents de Filiation Informatisé des parcelles dans une base de données PostgreSQL. Ces données sont disponibles en #OpenData.

Nous ne traiterons pas ici les changements relatifs à des remaniements ou des remembrements (changement de lettres de sections).

Intégration des données brutes

La première étape consiste à créer un schéma “division” dans notre base de données.

1 2 CREATE SCHEMA division AUTHORIZATION postgres; -- création du schéma division

Il suffit ensuite de créer une table nommée “divisiontest” dans le schéma “division” table qui ne comporte qu’un champ de type texte nommé “ligne” et d’importer le csv dans cette table. Ce soit avec PGadmin ou PLSQL.

Ensuite un peu de travail pour formater la donnée:

 

1 2 3 4 5 6 7 8 9 10 WITH p1 as (SELECT substring(ligne,1,69) as a1, substring(ligne,71) as a2 FROM division.divisiontest) SELECT split_part(a1,';',1) as departement,split_part(a1,';',2) as insee,split_part(a1,';',3) as prefsec, split_part(a1,';',4) as idfi, split_part(a1,';',5) as naturedfi, split_part(a1,';',6) as datedfi, -- AAAAJJMM !!!! Encore un super codage qui respecte les conventions -- on squizze l'anonyme split_part(a1,';',8) as numlotdfi,split_part(a1,';',9) as typeligne, regexp_split_to_table(rtrim(a2,';'),';') as desgparcelle -- rtrim pour supprimer le dernier ; dont on se demande ce qu'il fait là ... INTO division.divisionpart2 FROM p1

Création des tables

Le modèle de données

Conformément au MCD ci-dessous, non conforme à notre MCD Cadastre Global, il existe trois tables à l’origine. La table “commune”, la table “section” et la table “parcelle”. Nous ajoutons la table “reflexparcelle”. “reflex” au sens du miroir, elle contient une référence à la table “parcelle” qui permet de suivre l’historique des changements.

La table “parcelle” contiendra toutes les parcelles existantes ou ayant existé, le champ “active” permet de savoir si la parcelle est celle au plan actuel ou celle existante avant divisions.

Le champ “mere” va nous servir à savoir si la parcelle est la mère ou la fille de la parcelle en référence dans la table parcelle.

idrefelxparcelle est la mère ou la fille de ptrparcelle (idparcelle dans la table parcelle).

Code de création des tables

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- creation des tables conformement au MCD CREATE TABLE IF NOT EXISTS division.commune( idcommune serial, departement character varying(3), insee character varying(3)); CREATE TABLE IF NOT EXISTS division.section( idsection serial, ptrcommune integer, prefsec character varying(3), nom character varying(2)); CREATE TABLE IF NOT EXISTS division.parcelle( idparcelle serial, ptrsection integer, numero integer, active boolean); CREATE TABLE IF NOT EXISTS division.reflexparcelle( idreflexparcelle serial, ptrparcelle integer, ptrfiliation integer, mere boolean, naturedfi character varying(1), datedfi character varying(8), numlotdfi character varying(5), idfi character varying(7)); -- fin création

Intégration des données

Le script est fait pour pouvoir intégrer avec mise à jour. La dernière CTE de chacune des trois requêtes de remplissage se termine par un LEFT JOIN entre le fichier issu de data.gouv et notre base de données, il suffit de conserver les valeurs qui n'ont pas de correspondance droite. Donc les éléments qui sont dans data.gouv mais pas dans notre base.

Amusez vous à intégrer vos données puis supprimez une section et quelques parcelles d'une autre section dans votre base et exécutez à nouveau le script avec le même fichier source(data.gouv) et vous verrez que l'intégrité de vos données est préservée, la preuve d'une mise à jour réussie.

Ce modèle est conçu dans une option "embedded" sans connexion directe avec une base cadastre. L'objet étant de pouvoir à partir d'une parcelle existante OU AYANT existé, d'obtenir  pour une parcelle :

  • la filiation ascendante.

  • la filiation descendante

  • la filiation complète

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 -- Remplissage de la table commune avec mise à jour prévues : seules les nouvelles communes sont intégrées WITH p1 as (SELECT departement,insee FROM division.divisionpart2 GROUP BY departement,insee ORDER BY departement,insee ), p2 as (SELECT p1.*,a2.insee as d FROM p1 LEFT JOIN division.commune as a2 ON p1.departement=a2.departement AND p1.insee=a2.insee ) -- Remplissage section avec mise à jour INSERT INTO division.commune(departement,insee) SELECT departement,insee FROM p2 WHERE d is null; -- Remplissage de la table section idem commune WITH p1 as (SELECT departement,insee,prefsec,replace(substring(desgparcelle,1,2),' ','0') as nom FROM division.divisionpart2), p2 AS (SELECT departement,insee,prefsec,nom FROM p1 GROUP BY departement,insee,prefsec,nom ORDER BY departement,insee,prefsec,nom), p3 AS (SELECT idcommune,p2.departement,p2.insee,p2.prefsec,p2.nom FROM division.commune,p2 WHERE p2.departement=commune.departement AND p2.insee=commune.insee), p4 as (SELECT p3.insee,p3.departement,p3.prefsec,p3.nom,idsection FROM division.section,p3 WHERE p3.idcommune=section.ptrcommune), p5 as (SELECT idcommune,p3.insee,p3.prefsec,p3.nom,idsection FROM p3 LEFT JOIN p4 ON p3.departement=p4.departement AND p4.insee=p3.insee AND p4.prefsec=p3.prefsec AND p4.nom=p3.nom) INSERT INTO division.section (ptrcommune,prefsec,nom) SELECT idcommune,prefsec,nom FROM p5; -- Remplissage parcelle avec mise à jour WITH p1 as (SELECT departement,insee,prefsec,CASE WHEN trim(desgparcelle)='' THEN '' ELSE replace(substring(desgparcelle,1,2),' ','0')END as nom, CASE WHEN trim(desgparcelle)='' THEN '' ELSE substring(desgparcelle,3) END as numero FROM division.divisionpart2), p2 AS (SELECT departement,insee,prefsec,nom,numero FROM p1 GROUP BY departement,insee,prefsec,nom,numero ORDER BY departement,insee,prefsec,nom,numero), p3 AS (SELECT idcommune,idsection,p2.departement,p2.insee,p2.prefsec,p2.nom,p2.numero FROM division.commune,division.section,p2 WHERE ptrcommune=idcommune AND p2.departement=commune.departement AND p2.insee=commune.insee AND p2.prefsec=section.prefsec AND p2.nom=section.nom), p4 as (SELECT p3.insee,p3.departement,p3.prefsec,p3.nom,p3.idsection,p3.idcommune,p3.numero FROM division.parcelle,p3 WHERE p3.idsection=parcelle.ptrsection), p5 as (SELECT p3.idsection,p3.insee,p3.prefsec,p3.nom,p3.numero,p4.idcommune FROM p3 LEFT JOIN p4 ON p3.departement=p4.departement AND p4.insee=p3.insee AND p4.prefsec=p3.prefsec AND p4.nom=p3.nom AND p4.numero=p3.numero) INSERT INTO division.parcelle (ptrsection,numero) SELECT idsection,CASE WHEN trim(numero)='' THEN 0 ELSE numero::integer END as numero FROM p5 WHERE idcommune IS NULL; -- NOTA chaque commune comporte une section absorbante la section nommée : '' c'est l'équivalent de la face 0 en topologie, seront versées dans cette section les parcelles qui passent au domaine non cadastré -- Numero parcelle = 0 et nom section='' -- ****************** Création de la filiation descendante ********************************* WITH -- recherche les parcelles mères typeligne=1 p1 as (SELECT idcommune,idsection,idparcelle,numlotdfi,naturedfi,datedfi,idfi FROM division.divisionpart2 as a1,division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection=ptrsection WHERE typeligne='1' AND a1.departement=commune.departement AND a1.insee=commune.insee AND section.nom=replace(substring(desgparcelle,1,2),' ','0') AND CASE WHEN trim(desgparcelle)='' THEN parcelle.numero=0 ELSE parcelle.numero=substring(desgparcelle,3)::integer END ), -- Recherche des parcelles filles typeligne=2 p2 as (SELECT idcommune,idsection,idparcelle,numlotdfi,naturedfi,datedfi,idfi FROM division.divisionpart2 as a1,division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection=ptrsection WHERE typeligne='2' AND a1.departement=commune.departement AND a1.insee=commune.insee AND section.nom=replace(substring(desgparcelle,1,2),' ','0') AND CASE WHEN trim(desgparcelle)='' THEN parcelle.numero=0 ELSE parcelle.numero=substring(desgparcelle,3)::integer END ) INSERT INTO division.reflexparcelle(ptrparcelle,ptrfiliation,mere,naturedfi,numlotdfi,datedfi,idfi) SELECT p1.idparcelle as ptrparcelle,p2.idparcelle as idreflexparcelle,false as mere,p1.naturedfi,p1.numlotdfi,p1.datedfi,p1.idfi FROM p1,p2 WHERE p1.numlotdfi=p2.numlotdfi AND p1.naturedfi=p2.naturedfi AND p1.datedfi=p2.datedfi AND p1.idcommune=p2.idcommune AND p1.idsection=p2.idsection AND p1.idfi=p2.idfi; -- ****************** Création de la filiation ascendante ********************************* WITH -- recherche les parcelles filles typeligne=2 p1 as (SELECT idcommune,idsection,idparcelle,numlotdfi,naturedfi,datedfi,idfi FROM division.divisionpart2 as a1,division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection=ptrsection WHERE typeligne='2' AND a1.departement=commune.departement AND a1.insee=commune.insee AND section.nom=replace(substring(desgparcelle,1,2),' ','0') AND CASE WHEN trim(desgparcelle)='' THEN parcelle.numero=0 ELSE parcelle.numero=substring(desgparcelle,3)::integer END ), -- Recherche des parcelles filles typeligne=2 p2 as (SELECT idcommune,idsection,idparcelle,numlotdfi,naturedfi,datedfi,idfi FROM division.divisionpart2 as a1,division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection=ptrsection WHERE typeligne='1' AND a1.departement=commune.departement AND a1.insee=commune.insee AND section.nom=replace(substring(desgparcelle,1,2),' ','0') AND CASE WHEN trim(desgparcelle)='' THEN parcelle.numero=0 ELSE parcelle.numero=substring(desgparcelle,3)::integer END ) INSERT INTO division.reflexparcelle(ptrparcelle,ptrfiliation,mere,naturedfi,numlotdfi,datedfi,idfi) SELECT p1.idparcelle as ptrparcelle,p2.idparcelle as idreflexparcelle,true as mere,p1.naturedfi,p1.numlotdfi,p1.datedfi,p1.idfi FROM p1,p2 WHERE p1.numlotdfi=p2.numlotdfi AND p1.naturedfi=p2.naturedfi AND p1.datedfi=p2.datedfi AND p1.idcommune=p2.idcommune AND p1.idsection=p2.idsection AND p1.idfi=p2.idfi;

Consultation des données

Création d’une fonction SQL qui permet de connaître la filiation ascendante ('ASC'), descendante ('DESC') ou l’ensemble ('ALL'). Notez que la structure de données résultantes est un graphe. En effet une parcelle X peut être une parcelle issue d’une division ou d’une réunion.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 --*********************************** --- Groupement d'intéret public pour la reconstitution des titres de propriétés en Corse --- Christophe VERGON 23/01/2018 --*********************************** -- Création d'un type ENUM pour le sens de parcour du graphe : ASC (vers la mère) DESC (vers la fille) ALL les deux CREATE TYPE sens_g AS ENUM ('ASC','DESC','ALL'); --Création du type de retour : inscription dans le schéma division CREATE TYPE division.relation_filiation AS (inseeg text,prefsecg text,nomg text,numerog integer,relation text,inseed text, presecd text,nomd text, numerod integer) ; -- Création de la fonction langage SQL, paramètres et type de retour défini précédement CREATE OR REPLACE FUNCTION division.get_filiation(insee_ text, prefsec_ text, nom_ text, numero_ integer,sens sens_g) RETURNS SETOF division.relation_filiation AS $$ -- Structure SQL de la fonction -- ************************************************************************************************************************************ WITH -- Structure récursive RECURSIVE t(idparcelle,ptrfiliation,numlotdfi,datedfi,mere,profondeur,chemin,boucle,sens) -- profondeur chemin et boucle cf aide postgresql fonctions récursives AS (SELECT idparcelle,ptrfiliation,numlotdfi,datedfi,mere,1,ARRAY[idparcelle],false,sens FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection=ptrsection INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle WHERE insee=insee_ AND prefsec=prefsec_ AND nom=nom_ AND numero = numero_ AND CASE WHEN sens='ASC' THEN reflexparcelle.mere=true ELSE CASE WHEN sens='DESC' THEN reflexparcelle.mere=false ELSE true END END UNION ALL SELECT parcelle.idparcelle,reflexparcelle.ptrfiliation,reflexparcelle.numlotdfi,reflexparcelle.datedfi,reflexparcelle.mere, t.profondeur+1, chemin || parcelle.idparcelle, parcelle.idparcelle=ANY(chemin),sens FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle,t WHERE parcelle.idparcelle=t.ptrfiliation AND NOT boucle AND CASE WHEN sens='ASC' THEN reflexparcelle.mere=true ELSE CASE WHEN sens='DESC' THEN reflexparcelle.mere=false ELSE true END END ), --- fin récursive t1 AS (SELECT idparcelle,ptrfiliation,numlotdfi,datedfi,mere,profondeur FROM t GROUP BY idparcelle,ptrfiliation,numlotdfi,datedfi,mere,profondeur ORDER BY profondeur LIMIT 2000), -- Group BY élimine les doublons (UNION ALL ou UNION peut importe il reste des couples idparcelle,filiation qui n'ont pas le même chemin) t2 AS (SELECT idparcelle FROM t1), --tous les id coté gauche t3 AS (SELECT ptrfiliation FROM t1),-- tous les id coté droit t4 AS (SELECT insee,prefsec,nom,numero,t2.idparcelle as idparcelle FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection = ptrsection, t2 WHERE parcelle.idparcelle=t2.idparcelle GROUP BY insee,prefsec,nom,numero,t2.idparcelle), -- jointure pour rendre lisible les références cadastrales idem en dessous t5 AS (SELECT insee,prefsec,nom,numero,t3.ptrfiliation FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN division.parcelle ON idsection = ptrsection, t3 WHERE parcelle.idparcelle=t3.ptrfiliation GROUP BY insee,prefsec,nom,numero,t3.ptrfiliation) -- reconstitution de la table de relation et traduction en français SELECT t4.insee,t4.prefsec,t4.nom,t4.numero, CASE WHEN mere THEN ' a pour mère ' ELSE ' a pour fille ' END AS relation, t5.insee,t5.prefsec,t5.nom,t5.numero FROM t1,t4,t5 WHERE t1.idparcelle=t4.idparcelle AND t1.ptrfiliation=t5.ptrfiliation GROUP BY t4.insee,t4.prefsec,t4.nom,t4.numero,relation, t5.insee,t5.prefsec,t5.nom,t5.numero ORDER BY t4.insee,t4.prefsec,t4.nom,t4.numero -- Fin Structure SQL de la fonction -- ************************************************************************************************************************************ $$ LANGUAGE SQL;

Un exemple d’utilisation de cette fonction avec une jointure avec la base cadastre issue de intégrateur de données EDiGéO.

Réponses à quelques questions naturelles

Quelles sont les parcelles actives ?

Ce sont les parcelles qui n’ont pas de filles.

1 2 3 WITH p1 AS (SELECT * FROM division.parcelle LEFT JOIN division.reflexparcelle ON idparcelle=ptrparcelle) SELECT insee,prefsec,nom,numero FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN p1 ON idsection=ptrsection WHERE idreflexparcelle IS NULL ORDER BY insee,prefsec,nom,numero

Quelles sont les parcelles primitives ?

Ce sont les parcelles qui n’ont pas de mère.

1 2 3 WITH p1 AS (SELECT * FROM division.parcelle LEFT JOIN division.reflexparcelle ON idparcelle=ptrparcelle) SELECT insee,prefsec,nom,numero FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune INNER JOIN p1 ON idsection=ptrsection WHERE idreflexparcelle IS NULL ORDER BY insee,prefsec,nom,numero

Chacun remarquera qu'il existe pour chaque commune une parcelle dont la section est nulle dont le numéro vaut 0, elle apparaît systématiquement dans chacun des résultats précédents.
Il s'agit du domaine non cadastré ou domaine public pour simplifier. Cette parcelle n'a ni mère ni fille, c'est l'élément absorbant de l'ensemble des parcelles doté de l'opérateur filiation.

Connaître le graphe de division d’un ensemble de parcelles

Ici nous sélectionnons un jeu de parcelle dans notre base cadastre puis nous interrogeons la base division pour connaître la filiation de l'ensemble de ces parcelles. Nous utilisons la puissance des jointures latérales.

1 2 3 4 5 6 7 WITH resparc AS (SELECT commune.nom, commune.insee, section.nom AS nomsec,numero FROM cadastre.commune INNER JOIN cadastre.section ON idcommune=ptrcommune INNER JOIN cadastre.subsection ON idsection=ptrsection INNER JOIN cadastre.parcelle ON idsubsection=ptrsubsection WHERE insee='001' AND section.nom='0B' AND numero::integer<1000) SELECT * FROM resparc CROSS JOIN LATERAL division.get_filiation(insee,'000',nomsec,numero::integer,'ALL')

Visualiser les résultats d’un graphe de division

GraphViz permet de visualiser des graphes en tout genre.
C'est un programme qui prend en entrée du texte décrivant un ou des graphes
et qui donne en sortie des représentations graphiques en plusieurs formats, image, html,...

Avec un peu de manipulation de chaîne, on peut arriver à formater la sortie d'une requête SQL
pour avoir la description du graphe. Vous pouvez utilisez ce site, pour obtenir ce type de résultats.



A venir intégration de l’historique des divisions avec MAJ