Le portail francophone de la géomatique

Jean-Michel · Mon 15 January 2018 10:59

Bonjour,
Une nouvelle donnée est disponible depuis le 11/01 sur data.gouv : https://www.data.gouv.fr/fr/datasets/do … parcelles/

Documents de filiation informatisés (DFI) des parcelles
Les fichiers départementaux des documents de filiation informatisés (DFI) des parcelles permettent de consulter l'historique des parcelles cadastrales.

Ces fichiers recensent les modifications parcellaires réalisées depuis l'informatisation de leur procédure de mise à jour qui, selon les départements, est intervenue entre les années 1980 à 1990. L'origine des différentes mises à jour (documents d'arpentage, croquis de conservation, remaniement...) ainsi que leurs dates sont renseignées.

Les modifications issues des aménagements fonciers ruraux n'y figurent pas car il n'existe alors aucune correspondance géographique entre les anciennes et les nouvelles dénominations des parcelles.

Faites en bon usage !

Géomatiquement

ChristopheV · Mon 15 January 2018 11:41

Bonjour,

Génial a priori,il ne manque plus que le pdf du croquis ou la chemise et la plan du DA pour avoir ce qu'il faut !!

ATTENTION :

Ces fichiers recensent les modifications parcellaires réalisées depuis l'informatisation de leur procédure de mise à jour

Doit être compris comme l'arrivée de l'application MAJIC dans les CDIF, non pas l'arrivée des DA numériques.
Avant l’arrivée de MAJIC les croquis, esquisses, et DA étaient gérés par communes sur deux petits carnets que l'on nomme modèle40. C'est donc la lecture de ces documents puis leur saisie par des agents titulaires ou contractuels qui conduit à la production de la couche initiale MAJIC.

Conséquence :
Risques d'erreur.

Autre conséquence : La rénovation cadastrale ayant commencée dans les années 1930, tous les modèles 40 n'existent pas forcément, donc la notion de primitive présente dans MAJIC est relative. Celle indiquée n'est pas forcement la primitive mais parfois une parcelle fille de la primitive.
L'examen des plans de rénovation permet d'en faire le constat et d'obtenir la véritable primitive donc toute la chaîne de filiation.

Notion division dite de mauvaises pratiques : C'est au premier janvier 1956 que s'applique l'obligation de recours à un professionnel agréé pour diviser une parcelle dans une commune à cadastre rénové. Donc les DA, croquis et autres créés entre 1930 et 1955 ne sont pas forcément orthodoxes. Exemple la parcelle A 55 est divisée en A 55 et A 388, avec la deuxième A 55 qui est plus petite que la première et qui reste appartenir au proprio de la première.

Les modifications issues des aménagements fonciers ruraux n'y figurent pas car il n'existe alors aucune correspondance géographique entre les anciennes et les nouvelles dénominations des parcelles.

Tu parles des remembrements ? Car là il y a obligation de publication, et publication de la table de correspondance aux hypos non ?

Jean-Michel · Mon 15 January 2018 12:19

Re,

Tu parles des remembrements ?

Ce n'est pas moi qui le dis, c'est la fiche mise en ligne sur Data.gouv...

Car là il y a obligation de publication, et publication de la table de correspondance aux hypos non ?

Bien sûr, mais comme tu le sais, un remembrement redistribue des "masses" foncières après que les propriétés aient été démembrées.
Une parcelle nouvelle issue d'un aménagement foncier n'a donc pas de lien "logique" avec une parcelle ancienne, sauf cas particulier.
Mais, on peut toujours espérer qu'un jour ou l'autre ces PV de remembrements soient diffusés en Opendata, avec les Plans Minute bien sûr... (çà fera du boulot pour scanner, saisir, les milliers de pages des milliers de remembrements divers intervenus depuis 60 ans...)

ChristopheV · Mon 15 January 2018 14:14

Bonjour,

Une parcelle nouvelle issue d'un aménagement foncier n'a donc pas de lien "logique" avec une parcelle ancienne, sauf cas particulier.

Sur l'aspect topographique oui, mais sur l'aspect chaîne de propriété non. Car il n'y a plus d'effet relatif pour une parcelle remembrée, c'est le juge qui attribue la propriété, et c'est cette décision qui débute la chaîne.

Je viens de faire un test sur un département. Attention comme dit plus haut la chaîne n'est pas complète, on a pas forcement la primitive mais une des parcelles mère. Les DA sont enregistrés dans l'ordre (donc pas d'ordre de type nom section et numéro de plan.
Comme à l'accoutumée le codage est aléatoire et nous avons les noms de sections qui sont codés sur deux caractères : la section "A" devient " A" et pas "0A", donc faites attention :
" C0467" et "C00467" : le premier c'est la parcelle numéro 467 de la section C, la seconde c'est la 467 de la section CO.

Dommage de télécharger toute la France pour avoir deux départements.

Dernière modification par ChristopheV (Mon 15 January 2018 14:15)

Jean-Michel · Mon 15 January 2018 14:35

Re,

Sur l'aspect topographique oui, mais sur l'aspect chaîne de propriété non. Car il n'y a plus d'effet relatif pour une parcelle remembrée, c'est le juge qui attribue la propriété, et c'est cette décision qui débute la chaîne.

Oui, mais ici on parle d'opendata. Donc notamment de données non nominatives, et neutre de toute référence au droit de propriété.

ChristopheV · Mon 15 January 2018 15:34

RE,

Partit plein de bonnes intentions jusqu'à poster un script permettant l'intégration et l'exploitation de ces données dans une BD postgresql/postgis.

Très rapidement coupé dans mon élan :

descriptif a écrit:

Le fichier est au format txt. Le point-virgule est le caractère séparateur. La taille des champs
est fixe.

Magnifique c'est exactement le genre de format à la c*** qui embête (je suis poli) les informaticiens.
La longueur des lignes n'est pas fixe et en plus le nombre de champs dans une ligne n'est jamais le même. Le séparateur utilisé pour dissocier les différentes parcelles est aussi le point virgule !!!!!!!!!
Il existait deux solutions correctes :
1 ) je sais que j'ai 175 caractères maxi de dispo, je sais qu'une parcelle se code section +6 numéro soit 2 +4 = 6 caractères, soit maxi 26*6+24 = 174 (il y a un intervalle de moins que de bornes ...) donc si ma division a donner deux parcelles : A0208;A029;;;;;;;;;;;;;;;; (je mets pas les 23 ; ).
Plus tordu encore

puis
au maximum
175 cellules de 6 caractères, pouvant contenir chacune un
identifiant de parcelle (section (2) + numéro de plan (4))

Pourquoi 175 et pas 174 comme calculé précédemment ? ben car c'est 175 à partir de l'avant dernier "champ" le ; séparateur (après le type ligne) inclus.

2) j'utilise un autre séparateur pour ne faire qu'un champ exemple
;A0208,209[CR/LF]

Là le choix c'est de mélanger les deux donc de faire n'importe quoi et ceci m'oblige à passer par du code donc de ne rien publier ici. Après on dit que je suis impulsif mais les mecs qui ont pondu ce format il n'ont pas du mettre leur logiciel à jour depuis 1980.

Bref ne pas respecter une norme aussi basique que le csv c'est balèze !! Bon deux lignes de python et c'est réglé mais c'est vraiment un signe de bonne volonté du producteur de données ...C'est à ce demander si c'est pas fait exprès.

Dernière modification par ChristopheV (Mon 15 January 2018 15:35)

Jean-Michel · Mon 15 January 2018 15:49

A mon avis,
Si on associe/mutualise les compétences de Christophe + Christian + Jérôme, je subodore que dans quelques heures on a des fichiers restructurés, découpés, et prêts à l'emploi...
Il faudra ensuite qu'il y ait des ré-utilisations pertinentes et judicieuses ! (à partager bien sûr)

ChristopheV · Mon 15 January 2018 16:02

Bonjour,

Bon j'ai une solution publiable ([edit] avant que 3615 CQuest ne réponde ). Je mets plusieurs message ici dans l'ordre des opérations :

1) Sous pgadmin créer une table nommée "divisiontest" dans un schema.
Cette table ne comprend qu'un champ nommé "ligne"

2) clic droit sur la table, importer, choisir le fichier et le format .csv
Importez
Résultat une table avec chaque ligne du fichier texte = un enregistrement.

3) Ajoutez une colonne "idligne" de type serial : génération de la clef primaire.

La suite au prochain message, ce qui vient ce n'est plus que du SQL.

Dernière modification par ChristopheV (Mon 15 January 2018 16:02)

ChristopheV · Mon 15 January 2018 16:37

Ensuite pour obtenir une table : "divisionpart2" qui est exploitable :

Code:

WITH p1 as (SELECT substring(ligne,1,69) as a1, substring(ligne,71) as a2 FROM christophe_vergon.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 christophe_vergon.divisionpart2

FROM p1

Maintenant on va réfléchir à un modèle de données qui ne soit pas le strict reflet de la donnée brute. Et là il faut prendre un peu de temps pour fixer les choses. Car nous abordons la notion de tables réflexives et c'est pas simple, avec en plus la notion de mise à jour régulières ... Donc à demain pour la suite.

Pour ceux qui voudraient y réfléchir quelques use cases :

Une parcelle fille peut avoir :
0 mère : extraction du DP
1 mère : division
n mère : réunion

Une parcelle mère peut avoir :
pas de mère : je suis une primitive
pas de fille : je passe au DP
plusieurs fille : division
plusieurs filles mais somme(contenancedefille)<>contenance(mère) : division avec une partie au DP (cas type expro route)

Dernière modification par ChristopheV (Mon 15 January 2018 16:47)

ChristopheV · Mon 15 January 2018 19:57

Bonjour,

J'ai fais le choix de proposer une solution construite uniquement autour de logiciels et solutions open source.
Pour la modélisation je vous conseille d'utiliser UMLetino, c'est simple efficace et gratuit.

Je vous propose le modèle suivant (cf pièce jointe).

Il 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 :
la filiation ascendante.
la filiation descendante.
la filiation complète.

Il doit pouvoir supporter efficacement des mises à jour régulières.

Sur la base de ce qui a été montré ici

Une commune est composée de sections. Aucun liens direct avec la base cadastre donc on squeeze la subsection en codant le préfixe de section comme attribut de l'objet section. Attention dans le cas de communes fusionnées, les idsection et prefsec seront distincts mais le nom de section peut être identique.

Une section est composée de parcelle. Nous avons dit pas de lien direct avec le cadastre, mais bon c'est possible avec une clef composite : insee+prefsec+nom+numero. Si on ajoute un attribut vrai/faux nommé active, nous aurons connaissance des parcelles actives à la dernière mise à jour, qui sont celles qui n'ont pas de filles.
Cette table parcelle contient TOUTES LES PARCELLES CONSULTABLES, donc toutes les parcelles existantes ou ayant existé (active = vrai ou active = faux).

Nous créons ensuite une table nommée Reflexparcelle, Reflex pour réflexive, au sens du miroir, car elle contient une référence : ptrparcelle à la table parcelle.

Un attribut mere de type vrai faux, si je ne suis pas la mère je suis la fille.
je : idreflexparcelle est
la mère ou la fille de : idparcelle (de la table parcelle) représenté par ptrparcelle dans la table reflexparcelle.

C'est clair ? Nous y reviendrons au moment des requêtes d'interrogation de la base

Nous codons ensuite dans les attribut la nature des éléments qui ont conduit à cette filiation.
donc naturedfi, datedfi, nous n'avons pas le producteur, et numlotdfi on ne sait jamais ça peu servir.

Voilà
A bientôt

Dernière modification par ChristopheV (Mon 15 January 2018 20:05)

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

cquest · Mon 15 January 2018 22:17

Très bon le 3615 CQUEST... j'y avais pas pensé, mais vu mes tarifs ça serait plutôt du 3613

La DGFiP est coutumière des fichiers texte en format fixe. FANTOIR est comme ça, je l'importe en deux temps... en "raw", puis des substring/trim pour récupérer les champs et mettre ça dans une table normale.

J'avoue ne pas encore avoir regardé ce fichier de filiations.

ChristopheV · Tue 16 January 2018 09:34

Bonjour,

Très bon le 3615 CQUEST... j'y avais pas pensé

Rede caesare quae sunt caesaris et dio dei :

https://georezo.net/forum/viewtopic.php … 30#p303630

La DGFiP est coutumière des fichiers texte en format fixe

Oui et même pas ! Oui elle est coutumière c'est le poids de l'histoire, l'appli MAJIC c'est du hiérarchique AS400 des années 80, vous avez pas travaillé sur les écrans verts avec les lettres orange ... et l'impression qu'auraient ceux qui voient les applis actuelles est complètement fausse, l'illusion du relationnel et d'un interface graphique moderne, sont créés par une surcouche logiciel, mais la surcouche elle attaque toujours MAJIC avec une techno des années 1980. Et même pas car là il est pas "fixe", en fait leur problème c'est d'arriver a générer un fichier texte avec le bon encoding, eux ils sont en 1980 hein, donc ANSII 255 caractères, de gérer les fins de lignes et retour chariot (bon ils ont progressé souvenez vous des premiers fichiers MAJIC), bref d'être des magiciens pour maintenir à bout de bras une application obsolète dont les rouages techniques ne sont plus connus que par quelques princes locaux qui partent un à un à la retraite.

Bon trêve d'histoire y'a du boulot

A+

Dernière modification par ChristopheV (Tue 16 January 2018 09:51)

cquest · Tue 16 January 2018 10:51

Une mauvaise langue aurait pu comparer les fichiers texte en format fixe avec... les cartes perforées

Ps: mon premier écran était à lettres vertes, le second oranges...

ChristopheV · Tue 16 January 2018 15:08

Bonjour,

Une étape supplémentaire :

Code:

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

-- 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,
mere boolean,
naturedfi character varying(1),
datedfi character varying(2),
numlotdfi character varying(7));
-- fin création
-- 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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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é

Première chose on instancie un nouveau schéma nommé division. Cette partie est a exécuter UNE FOIS, après il faut pas le faire si non ça plante, on n'a pas de create schema if exists.

Pour le reste 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 mon fichier issu de data.gouv et ma 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 ma base.
Ceci permet d'être indépendant du format sur data.gouv au sens du contenu, car demain ils vont vite s'apercevoir que générer tous les trois mois le fichier complet sur toute la france ça coute. Donc on risque d'avoir uniquement les derniers changements dans quelques temps.
Nous aurions pu considérer la date de la dfi pour palier à ce souci, mais comme nous l'avons vu le format n'est pas standard et l'expérience prouve qu'il ne faut jamais faire de supposition sur le producteur de données (la seules supposition valable étant de considérer le pire).

Voilà avec ces éléments nous avons une partie du modèle qui est remplie, la suite :
Création des liens entre les données et remplissage de la table reflexparcelle.

[EDIT] : 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 .

A bientôt

Dernière modification par ChristopheV (Tue 16 January 2018 15:14)

ChristopheV · Tue 16 January 2018 17:21

Bonjour,

Une petite modification importante (et il y en aura d'autres ...) il faut ajouter un champ à la table reflexparcelle, le champ ptrfiliation qui fera référence à la parcelle "fille". Nous aurions pu le coder dans idreflexparcelle (à ce moment pas SERIAL mais simplement INTEGER), mais j'ai eu peur de compliquer les choses.
Je ferais un récapitulatif définitif à la fin. (c'est beau comme du Proust).

Code:

- ******************  Création de la filiation descendante *********************************
WITH -- recherche les parcelles mères typeligne=1
p1 as (SELECT idcommune,idsection,idparcelle,numlotdfi,naturedfi,datedfi FROM christophe_vergon.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 FROM christophe_vergon.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)
SELECT p1.idparcelle as ptrparcelle,p2.idparcelle as idreflexparcelle,false as mere,p1.naturedfi,p1.numlotdfi,p1.datedfi 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

Voilà vous avez maintenant l'ensemble intégré et utilisable. Ce pose maintenant la question de savoir si :
On ne conserve que la filiation descendante et on l'inverse pour avoir l'ascendante, soit on écrit directement dans la table reflexparcelle la filiation ascendante.
Solution 1 : requête plus compliquée mais gain en volume
Solution 2 : nombre de lignes de la table reflexparcelle doublé, requête plus simple ...

A vous de voir.

ChristopheV · Tue 16 January 2018 17:24

RECAPITULATIF

Après avoir intégrer le fichier texte comme indiqué dans le premier post voici le script SQL complet.
Je posterai ce soir la modif du modèle UML (ajout d'un champ table reflexparcelle et quelques modif de longueur de champ texte, c'est quand on va trop vite ... !!). NB : l'insertion des filiations n'est pas encore vérifiée pour l'intégration des mises à jour.

Code:

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

-- 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 as integer,
mere boolean,
naturedfi character varying(1),
datedfi character varying(8),
numlotdfi character varying(5));
-- fin création
-- 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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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 FROM christophe_vergon.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 FROM christophe_vergon.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)
SELECT p1.idparcelle as ptrparcelle,p2.idparcelle as idreflexparcelle,false as mere,p1.naturedfi,p1.numlotdfi,p1.datedfi 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

J'ai essayé tant que faire ce peu, d'expliciter la démarche et de bien montrer les moments où il faut perdre du temps à réfléchir, non pas pour gagner des millisecondes, mais des heures de casse tête de maintien et mise à jour. De montrer combien il est important de ne pas bêtement dépendre du producteur de données, et de démontrer qu'il vaut mieux perdre du temps à l'intégration qu'à l’exécution des requêtes.

J'invite les concepteurs et intégrateurs de données MAJIC, DVF, "EDIGEO" (pas ogr hein !!) à appliquer la même méthode.

Dernière modification par ChristopheV (Tue 16 January 2018 17:36)

ChristopheV · Tue 16 January 2018 18:10

En prime une requête pour avoir la filiation descendante complète d'une parcelle:

Code:

WITH RECURSIVE t(idparcelle,ptrfiliation,numlotdfi,datedfi,mere) AS
(SELECT idparcelle,ptrfiliation,numlotdfi,datedfi,mere FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle WHERE idparcelle=200
UNION ALL
SELECT parcelle.idparcelle,reflexparcelle.ptrfiliation,reflexparcelle.numlotdfi,reflexparcelle.datedfi,reflexparcelle.mere 
FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle,t WHERE parcelle.idparcelle=t.ptrfiliation)

SELECT * FROM t LIMIT 100

ChristopheV · Mon 22 January 2018 11:24

Bonjour,

Désolé il y a un grosse erreur dans la requête d'intégration de création des filiations descendantes.

la condition p1.numdfi=p2.numdfi doit être remplacée par p1.idfi=p2.idfi

Je poste un rectificatif complet dès que possible (fonction de mon temps et du fonctionnement de GeoRezo (courage Yves !! Je compatis) ).

christian · Mon 22 January 2018 16:02

ChristopheV a écrit:

Génial a priori,il ne manque plus que le pdf du croquis ou la chemise et la plan du DA pour avoir ce qu'il faut !!

C'est en cours, les geomètres experts et la dgfip ont signé un accord : ceux ci scannent tous les DAs des services (par ex dans le 38 c'est en cours) : à terme ceux ci seront donc en ligne sur le site des geomètres experts (https://www.geofoncier.fr/), et consultables par les services du cadastre. Rien n'est prévu pour les DAs qui arriveraient par la suite (sic!) : on nous dit que les geomètres experts eux mêmes les mettront en ligne, mais il n'y a pas qu'eux qui peuvent faire des DAs (re-sic!)...
On aurait pu en profiter pour enfin numeriser toute cette paperasse, mais l'occasion était trop belle...

Comme des idées flottent dans l'air comme quoi la mise à jour du bati peut se faire à partir des photos aeriennes, et qu'il n'est plus besoin d'aller sur le terrain faire des mesures, je crois qu'après ça, on pourra se débarrasser ENFIN d'un service qui coute vraiment trop cher à notre état

(c'était sur le ton de l'ironie, bien sur !)

ChristopheV · Mon 22 January 2018 16:31

Bonjour,

Salut Christian, peux tu me donner un contact officiel qui s'occupe de ce genre de chose ? Car nous avions envisager de la faire à nos frais, la DGFiP nous ayant opposé une fin de non recevoir il y a deux ans.
L'idée allait plus loin car nous voulions indexer (Num DA, nom géomètre ET identifiant cadastral) le tout mis en ligne sur une solution GED. Cela permettait aux abonnés (à leur frais mais c'est pas cher) de consulter ces DA où ils veulent et quand ils veulent ... un gain de temps et d'argent pour les services du cadastre, les GE, les collectivités ... bref un truc utile à tout le monde.

ChristopheV · Mon 22 January 2018 17:10

Bonjour,

Ci-dessous la requête modifiée ... désolé pour l'erreur.

Code:

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

-- 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
-- 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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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 christophe_vergon.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;

ChristopheV · Mon 22 January 2018 17:11

Re,

et ici la requête pour avoir la filiation complète d'une parcelle :

Code:

WITH RECURSIVE t(idparcelle,ptrfiliation,numlotdfi,datedfi,mere) AS
(SELECT idparcelle,ptrfiliation,numlotdfi,datedfi,mere FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle WHERE idparcelle=200 AND mere=true
UNION ALL
SELECT parcelle.idparcelle,reflexparcelle.ptrfiliation,reflexparcelle.numlotdfi,reflexparcelle.datedfi,reflexparcelle.mere 
FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle,t WHERE parcelle.idparcelle=t.ptrfiliation AND reflexparcelle.mere=false )

SELECT * FROM t ORDER BY idparcelle LIMIT 2000

ChristopheV · Mon 22 January 2018 21:38

@Christian
Comprends pour la publication des DA c'est pas juste un flux numérique, il y a quand même le problème de la signature, ou alors on considère que c'est le SPF qui publie ? Ou on transmet la balle au voisin ? Le mieux serrait de sous traiter ça à un consortium privé, non ?
Je rigole.
Au sujet de la notion de mesure du bâti, l'argument ne tient plus à l'heure de l'ortho à quelques dizaines de centimètres. Le cadastre de par son histoire, sa technicité, deux siècles de savoir quand même, ça cause ! Doit continuer à vivre mais comme tout être vivant condamné à s'adapter. Il faut laisser aux GE la partie du parcellaire privé ET la gestion des etats descriptifs de division au sens du plan en volume.
L'ensemble étant vérifié par le cadastre, qui conformément aux Décrets de 1955, est LE référentiel en matière de désignation des biens immeubles.
Le cadastre gère aussi le plan à grande échelle, et ce dont souffre nombre de collectivités, de personnes, c'est l'absence d'une codification et d'une gestion réelle du domaine non cadastré. Là doit être le rôle et les compétences de ses techniciens. Ceci lui permet de remplir son rôle en fournissant aux collectivités la délim avec le privé, la cohérence du référentiel, la possibilité de gérer un fichier du DP au même titre que le parcellaire. On constitue des îlots de parcelles, et les GE se débrouillent pour gérer l’intérieur, en restant dans les limites

On fusionne avec le SPF, (on prend en main leur informatique car c'est la cata, entre nous pour aller d'une parcelle x à un acte, chez nous depuis un clic du plan le temps que le pdf s'ouvre ) .
Comme cela l'ensemble fourni un référentiel de l'ensemble des biens immeubles et des portions de territoire gérées par les collectivités, et leur chaîne de propriété opposable.

Notes que l'histoire de la gestion du DP ça permet de s'harmoniser avec les référentiels à grande échelle, et puis les géodésiens, c'est des cousins

C'était une vision à l'adresse d'un fin connaisseur du sujet, et ça méritait un partage.

ChristopheV · Tue 23 January 2018 11:24

Bonjour,

Voici une fonction SQL, nommée get_filiation qui permet d'obtenir la filiation complète d'une parcelle.
Le post suivant vous donnera une image du résultat.

Code:

--***********************************
--- Groupement d'intéret public pour la reconstitution des titres de propriétés en Corse
--- Christophe VERGON 23/01/2018
--***********************************

--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) 
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) -- profondeur chemin et boucle cf aide postgresql fonctions récursives
AS    (SELECT idparcelle,ptrfiliation,numlotdfi,datedfi,mere,1,ARRAY[idparcelle],false 
    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_
 
UNION ALL

    SELECT parcelle.idparcelle,reflexparcelle.ptrfiliation,reflexparcelle.numlotdfi,reflexparcelle.datedfi,reflexparcelle.mere,
    t.profondeur+1, chemin || parcelle.idparcelle, parcelle.idparcelle=ANY(chemin)
    FROM division.parcelle INNER JOIN division.reflexparcelle ON idparcelle=ptrparcelle,t 
    WHERE parcelle.idparcelle=t.ptrfiliation AND NOT boucle),

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

ChristopheV · Tue 23 January 2018 11:51

Voilà comment elle s'utilise et le résultat, on voit ici que l'on peut faire par exemple une jointure avec la table cadastre.commune pour récupérer le nom de la commune et sa géométrie (et/ou topologie).

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

ChristopheV · Tue 23 January 2018 15:33

Bonjour,

Une amélioration de la fonction précédente. J'ai ajouter un paramètre pour le sens du parcourt :
ASC : je "remonte" vers la mère
DESC : je descends vers la fille
ALL : L'ensemble du graphe dans les deux sens

Code:

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

ChristopheV · Tue 23 January 2018 16:24

Maintenant répondons à quelques questions naturelles :

Quelles sont les parcelles actives : ce sont les parcelles qui n'ont pas de filles:

Code:

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 ie quelles sont les parcelles qui n'ont pas de mère

Code:

WITH p1 as (SELECT * FROM division.reflexparcelle WHERE mere=false),
p2 AS (SELECT * FROM division.parcelle LEFT JOIN p1 ON idparcelle=ptrfiliation)

SELECT insee,prefsec,nom,numero FROM division.commune INNER JOIN division.section ON idcommune=ptrcommune
INNER JOIN p2 ON idsection=ptrsection WHERE idreflexparcelle IS NULL ORDER BY insee,section,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.

Il ne reste plus qu'à gérer la mise à jour des relations de filiation en modifiant légèrement les requêtes d'intégration et c'est opérationnel.

En plus mon petit doigt me dit que la visualisation graphique du tout est facile est pas vraiment loin ...

Dernière modification par ChristopheV (Tue 23 January 2018 20:06)

yartostout · Wed 24 January 2018 08:47

Grand merci pour ce partage ChristopheV ! Pas encore pu tester mais je le prévois d'ici peu B-)

tumasgiu · Wed 24 January 2018 10:07

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 qui va bien.

Un exemple de description:

Code:

digraph G {
"0010B2834"[color=green,style=filled];
"0010B9"->"0010B2805"
"0010B22"->"0010B2805"
"0010B23"->"0010B2805"
"0010B24"->"0010B2805"
"0010B25"->"0010B2805"
"0010B69"->"0010B2805"
"0010B70"->"0010B2805"
"0010B2805"->"0010B2833"
"0010B2805"->"0010B2834"
"0010B2833"->"0010B2853"
"0010B2833"->"0010B2854"
"0010B2833"->"0010B2855"
}

Vous pouvez utiliser le site http://webgraphviz.com/ pour visualiser le résultat,
ou télécharger la sortie en pièce jointe.

Dernière modification par tumasgiu (Wed 24 January 2018 10:09)

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

ChristopheV · Wed 24 January 2018 10:46

Bonjour,

Pour compléter le post de Tumasgiu et donner une indication pour arriver au résultat, et illustrer la puissance des jointure latérales.
Ce type de jointure permet d'utiliser les résultats d'une requête comme paramètres de la fonction écrite précédemment : get_patcellefiliation()

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.

Code:

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

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Mon 15 January 2018 10:59

Nouveauté Opendata : Cadastre, documents de filiation

#2 Mon 15 January 2018 11:41

Re: Nouveauté Opendata : Cadastre, documents de filiation

#3 Mon 15 January 2018 12:19

Re: Nouveauté Opendata : Cadastre, documents de filiation

#4 Mon 15 January 2018 14:14

Re: Nouveauté Opendata : Cadastre, documents de filiation

#5 Mon 15 January 2018 14:35

Re: Nouveauté Opendata : Cadastre, documents de filiation

#6 Mon 15 January 2018 15:34

Re: Nouveauté Opendata : Cadastre, documents de filiation

#7 Mon 15 January 2018 15:49

Re: Nouveauté Opendata : Cadastre, documents de filiation

#8 Mon 15 January 2018 16:02

Re: Nouveauté Opendata : Cadastre, documents de filiation

#9 Mon 15 January 2018 16:37

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#10 Mon 15 January 2018 19:57

Re: Nouveauté Opendata : Cadastre, documents de filiation

#11 Mon 15 January 2018 22:17

Re: Nouveauté Opendata : Cadastre, documents de filiation

#12 Tue 16 January 2018 09:34

Re: Nouveauté Opendata : Cadastre, documents de filiation

#13 Tue 16 January 2018 10:51

Re: Nouveauté Opendata : Cadastre, documents de filiation

#14 Tue 16 January 2018 15:08

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#15 Tue 16 January 2018 17:21

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#16 Tue 16 January 2018 17:24

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#17 Tue 16 January 2018 18:10

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#18 Mon 22 January 2018 11:24

Re: Nouveauté Opendata : Cadastre, documents de filiation

#19 Mon 22 January 2018 16:02

Re: Nouveauté Opendata : Cadastre, documents de filiation

#20 Mon 22 January 2018 16:31

Re: Nouveauté Opendata : Cadastre, documents de filiation

#21 Mon 22 January 2018 17:10

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#22 Mon 22 January 2018 17:11

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#23 Mon 22 January 2018 21:38

Re: Nouveauté Opendata : Cadastre, documents de filiation

#24 Tue 23 January 2018 11:24

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#25 Tue 23 January 2018 11:51

Re: Nouveauté Opendata : Cadastre, documents de filiation

#26 Tue 23 January 2018 15:33

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#27 Tue 23 January 2018 16:24

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

Code:

#28 Wed 24 January 2018 08:47

Re: Nouveauté Opendata : Cadastre, documents de filiation

#29 Wed 24 January 2018 10:07

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

#30 Wed 24 January 2018 10:46

Re: Nouveauté Opendata : Cadastre, documents de filiation

Code:

Pied de page des forums