nettoyage scripts SQL

[landryb]

je crée un ticket séparé de #426 (comment) pour noter les incohérences / oublis que je trouve.. je ferais une PR pour ce nettoyage, mais je préfère en discuter d'abord...

• a priori il manque des DELETE FROM dans cadastre/scripts/plugin/majic3_purge_donnees.sql pour les 2 tables pevexoneration_imposable et pevexoneration_imposee
• on doit pouvoir supprimer cadastre/scripts/plugin/majic_recuperation_locaux_par_parcelle.sql et cadastre/scripts/plugin/majic_recuperation_subdivisions_par_parcelle.sql

[landryb]

il 'ya une incohérence sur les scripts annuels de manipulation des données, sur les doublons de la table voie.

• pour 2011

cadastre/scripts/plugin/2011/majic3_formatage_donnees.sql:-- purge des doublons : voie;
cadastre/scripts/plugin/2011/majic3_formatage_donnees.sql-DELETE FROM ${PREFIXE}voie WHERE codvoi IN (SELECT codvoi FROM ${PREFIXE}voie WHERE annee='${ANNEE}' GROUP BY codvoi HAVING COUNT(*) > 1) AND annee='${ANNEE}';

• pour tous les millésimes suivants, le commentaire est resté, mais on crée un index a la place

cadastre/scripts/plugin/2012/majic3_formatage_donnees.sql:-- purge des doublons : voie
cadastre/scripts/plugin/2012/majic3_formatage_donnees.sql-CREATE INDEX idxan_voie ON voie (annee);

le champ annee contient l'année courante et codvoi est NULL pour tous les enregistrements de la table voie (et ce depuis le passage a TOPO), donc a mon avis le commentaire est erroné, il n'y a plus de doublons a purger ? ou alors faire revenir cette déduplication (et la faire correctement) corrigerait #465 ?

[landryb]

incohérence sur les indexes, certains crées en double ?

avant le remplissage de parcelle_info, ces 10 indexes 'temporaires' sont crées (cf https://github.com/3liz/QgisCadastrePlugin/blob/2.1.1/cadastre/scripts/plugin/edigeo_create_table_parcelle_info_majic.sql#L29), j'imagine pour optimiser la jointure:

CREATE INDEX aa ON ${PREFIXE}parcelle (parcelle );
CREATE INDEX bb ON ${PREFIXE}parcelle (comptecommunal );
CREATE INDEX cc ON ${PREFIXE}parcelle (ccocom );
CREATE INDEX dd ON ${PREFIXE}parcelle (ccodep );
CREATE INDEX ee ON ${PREFIXE}parcelle ((SUBSTR(voie, 1, 6) || SUBSTR(voie, 12, 4)));
CREATE INDEX ff ON ${PREFIXE}proprietaire (comptecommunal );
CREATE INDEX gg ON ${PREFIXE}geo_parcelle (geo_parcelle );
CREATE INDEX hh ON ${PREFIXE}commune (ccocom );
CREATE INDEX ii ON ${PREFIXE}commune (ccodep );
CREATE INDEX jj ON ${PREFIXE}voie ((SUBSTR(voie, 1, 6) || SUBSTR(voie, 12, 4)));

alors que la majorité/certains de ceux-la sont déjà crées:

• idx_proprietaire_comptecommunal
• geo_parcelle_geo_parcelle
• idx_commune_ccocom
• idx_parcelle_comptecommunal (et est aussi crée avec le meme nom ici ??)
• idx_parcelle_voie_substr

en pratique, on pourrait supprimer ces indexes temporaires, et rajouter aux indexes globaux:

• CREATE INDEX idx_commune_ccodep ON ${PREFIXE}commune (ccodep);
• CREATE INDEX idx_voie_voie_substr ON ${PREFIXE}voie ((SUBSTR(voie, 1, 6) || SUBSTR(voie, 12, 4)));
• CREATE INDEX idx_parcelle_parcelle ON ${PREFIXE}parcelle (parcelle);
• CREATE INDEX idx_parcelle_ccocom ON ${PREFIXE}parcelle (ccocom);
• CREATE INDEX idx_parcelle_ccodep ON ${PREFIXE}parcelle (ccodep);

de plus, pour le remplissage de la table parcelle_info, ci-dessous les timings (sur cette table uniquement) pour l'import complet de 12 depts en 2024, on voit bien que c'est de plus en plus long de faire le remplissage de la table, alors que s'il était fait correctement de manière incrémentielle lot par lot, ca prendrait moins longtemps globalment..

• 01: 5123-5029 = 96s
• 03: 3460-3297 = 163s
• 07: 3248-2996 = 252s
• 15: 3069-2787 = 282s
• 26: 4841-4402 = 439s
• 38: 10181-9501 = 680s
• 42: 7576-6783 = 793s
• 43: 7831-6732 = 1099s
• 63: 13496-11996 = 1500s
• 69: 14020-12422 = 1598s
• 73: 16619-14285 = 2334s
• 74: 20149-17351 = 2708s

pour 2024, j'ai 14.8M entrées dans parcelle_info.

[landryb]

pour parcelle_info, je suis tenté de déplacer la création de table/commentaires/indexes dans les fichiers commun, mais a priori les champs ne sont pas les memes selon qu'on aie ou pas majic, ce qui je suppose explique que cette table soit traitée dans un fichier sql a part... ce qui ne doit pas empécher de la convertir au traitement 'par lot'. D'ailleurs, le remplissage de la table geo_unite_fonciere le fait d'ailleurs correctement.

[landryb]

@mdouchin autre amélioration possible, si on part du principe qu'on part d'un schéma vierge a chaque import (est-ce une possibilité ?), on pourrait grandement nettoyer cadastre/scripts/plugin/edigeo_create_indexes.sql en supprimant tous les DROP, et remplacer les CREATE INDEX par des CREATE INDEX IF NOT EXISTS.... quand je vois le temps passé a recréer des index a chaque lot, pour les voir supprimer/recréer par le suivant...

[landryb]

une fois importé mes 12 départements, dont les 5 derniers en skippant le reset/re-remplissage compliet de la table parcelle_info, c'est bien plus efficace de faire le remplissage lot par lot avec becfd18..

[22/07 08:46] landry@build.fluela:/data/temp/integration-donnees/majic/2025 $time sh fill_parcelle_info.sh
Tue Jul 22 08:47:02 CEST 2025
filling parcelle_info with lot='dep43'
INSERT 0 1235173
Time: 308263.698 ms (05:08.264)
Tue Jul 22 08:52:10 CEST 2025
filling parcelle_info with lot='dep63'
INSERT 0 1572424
Time: 215412.883 ms (03:35.413)
Tue Jul 22 08:55:46 CEST 2025
filling parcelle_info with lot='dep69'
INSERT 0 951501
Time: 168066.417 ms (02:48.066)
Tue Jul 22 08:58:34 CEST 2025
filling parcelle_info with lot='dep73'
INSERT 0 1752274
Time: 208166.805 ms (03:28.167)
Tue Jul 22 09:02:02 CEST 2025
filling parcelle_info with lot='dep74'
INSERT 0 1550723
Time: 194786.016 ms (03:14.786)
Tue Jul 22 09:05:17 CEST 2025
real    18m15.246s

et le nombre d'entrées par lot est 'cohérent' avec ce que j'avais en 2024:

[db:5432] qadastre@qadastre=> select lot,count(*) from qad2024.parcelle_info group by lot ;
  lot  |  count
-------+---------
 dep01 | 1368316
 dep03 |  733562
 dep07 | 1291497
 dep15 |  624218
 dep26 |  961769
 dep38 | 1695120
 dep42 | 1074634
 dep43 | 1234452
 dep63 | 1571128
 dep69 |  949924
 dep73 | 1750445
 dep74 | 1547325
(12 rows)

[db:5432] qadastre@qadastre=> select lot,count(*) from qad2025.parcelle_info group by lot ;
  lot  |  count  
-------+---------
 dep01 | 1370986
 dep03 |  734656
 dep07 | 1292839
 dep15 |  624846
 dep26 |  963573
 dep38 | 1698547
 dep42 | 1076476
 dep43 | 1235173
 dep63 | 1572424
 dep69 |  951501
 dep73 | 1752274
 dep74 | 1550723
(12 rows)