Création Indicateurs Méthode Elio

Base de données Elio
Calcul Patterns Route Commune
Requete OSM selon nomenclature prédéfinie
Transmission de données via PuTTY
Indicateurs délinquance
Atlas Délinquance Tutoriel

Base de données Elio

Nom Data	Lien	Region BD	Poids	Contenu	Dictionnaire des Variables
BAAC	https://www.data.gouv.fr/datasets/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2024	Nationale	20 Mo	Accidents de la route comprend tous les lieux, les dates, les types de véhicules et la façon dont l'accident s'est produit	https://www.data.gouv.fr/datasets/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2024?resource_id=8ef4c2a3-91a0-4d98-ae3a-989bde87b62a
Délinquance	https://www.data.gouv.fr/datasets/bases-statistiques-communale-departementale-et-regionale-de-la-delinquance-enregistree-par-la-police-et-la-gendarmerie-nationales	Nationale	545 Mo	Entièreté des données sur la délinquance sur l'année passée, à l'échelle communale, départementale ou régionale	https://www.data.gouv.fr/datasets/bases-statistiques-communale-departementale-et-regionale-de-la-delinquance-enregistree-par-la-police-et-la-gendarmerie-nationales?resource_id=c7ea847c-3ba3-4569-b7b3-17d662468e98
Parcelles	https://geoservices.ign.fr/parcellaire-express-pci	Départementale/Régionale	6 Go PACA	Contient l'ensemble des parcelles	https://geoservices.ign.fr/documentation/donnees/parcellaire/parcellaire-express-pci
Sirene	https://www.data.gouv.fr/datasets/base-sirene-des-entreprises-et-de-leurs-etablissements-siren-siret	Nationale	1 Go PACA/ 10 Go Nation	Ensemble des entreprises établissements possédant un SIREN	https://www.data.gouv.fr/datasets/base-sirene-des-entreprises-et-de-leurs-etablissements-siren-siret?resource_id=65946639-3150-4492-a988-944e2633531e
Geofabrik	https://download.geofabrik.de/europe/france.html	Régionale	1.55 Go	Ensemble des données OSM à l'échelle régionale.	https://download.geofabrik.de/osm-data-in-gis-formats-free.pdf
RP2022	https://www.insee.fr/fr/statistiques/8647104?sommaire=8590191	Regionale	1 Go	Recensement de population des individus à l'échelle communale	https://www.insee.fr/fr/statistiques/8647104?sommaire=8590191#dictionnaire
MobSCO/MobPRO	MobSCO : https://www.insee.fr/fr/statistiques/8589945?sommaire=8590191 MobPRO : https://www.insee.fr/fr/statistiques/8589904?sommaire=8590191	Nationale	MobSCO:321 Mo MobPRO: 745 Mo	Mobilité scolaire entre commune de résidence et commune de scolarisation	MobSCO : https://www.insee.fr/fr/statistiques/8589945?sommaire=8590191#dictionnaire MobPRO : https://www.insee.fr/fr/statistiques/8589904?sommaire=8590191#dictionnaire
BDTOPO	https://geoservices.ign.fr/bdtopo	Départementale/Régionale	20 Go : PACA 5 Go : 13	Ensemble des données cadastrales	https://geoservices.ign.fr/documentation/donnees/vecteur/bdtopo
Vaccination Rougeole	https://www.sirsepaca.org/#bbox=737476,6459745,402138,251535&c=indicator&f=13&i=cv_ror1.tx_cv_ror1&s=Cohorte%20de%20naissance%202020&t=A01&view=map3	Regionale : PACA	30 Ko	Données sur la couverture vaccinale ROR en PACA chez les moins de 21 mois	∅
Vaccination Grippe	https://www.sirsepaca.org/#bbox=737476,6459745,402138,251535&c=indicator&view=map3	Regionale : PACA	20 Ko	Données sur la couverture vaccinale grippe chez les + de 65 ans	∅
DVF	https://www.data.gouv.fr/datasets/demandes-de-valeurs-foncieres	Nationale	3,5 Go Nation 80 Mo Dep	Données sur les valeurs foncières à l'année.	https://www.data.gouv.fr/datasets/demandes-de-valeurs-foncieres?resource_id=d573456c-76eb-4276-b91c-e6b9c89d6656
Elections	https://www.data.gouv.fr/elections	Nationale	400 Mo	Data élections en fonction	∅

Calcul Patterns Route Commune

Carte Patterns : Capture d'écran 2026-03-23 160116.png

Pour obtenir ce résultat il faut d'abord avoir l'extension QuickOSM sur QGIS. Ouvrez QuickOSM et allez sur requete rapide :

Ensuite rentrez dans requetes sur toutes les clés highway puis en ce qui concerne dans "Un village, une ville" rentrez le nom de votre commune :

Exécutez la requête et vous obtenez une couche temporaire lines, nous ne gardons que les vecteurs que vous enregistrerez sous la dénomination de votre choix. Attention cela peut fréquemment planter, il suffit de réessayer jusqu'à ce que ça marche.

Ensuite après avoir envoyé votre couche dans PostGIS, récupérez la requête sauvegardez sous le nom de Patterns Route dans le Query Tool de PGadmin. Si la couche a disparu voici le code :


DROP TABLE IF EXISTS zones_motifs_filaires_commune; --Remplacez commune par le nom de votre commune ou l'InseeCom
CREATE TABLE zones_motifs_filaires_commune AS
WITH extraction AS (
-- Étape 1 : On lit la géométrie et on extrait les informations clés du JSON
SELECT
osm_id,
highway AS type_osm,
lanes AS nb_voies,
maxspeed AS vitesse_max,
ST_Transform(geom, 2154) AS geom_ligne -- Projection en Lambert 93 (mètres)
FROM --Insérez le nom de votre fichier sans le .csv ou .gpkg etc... par exemple routes_communes et pas routes_communes.gpkg
WHERE highway IS NOT NULL
),
classification AS (
-- Étape 2 : On applique VOS règles d'urbanisme (L'entonnoir Alteris)
SELECT
osm_id, type_osm, geom_ligne,
CASE
-- 1. TRANSIT (F_T)
WHEN type_osm IN ('motorway', 'motorway_link') THEN 'F_T1'
WHEN type_osm IN ('trunk', 'trunk_link') THEN 'F_T2'
WHEN type_osm IN ('primary', 'secondary')
AND (nb_voies IN ('3', '4', '5', '6', '7', '8')
OR vitesse_max IN ('70', '80', '90', '110', '130'))
THEN 'F_T3'

-- 2. DESSERTE (F_D)
-- Note : Si une route 'primary' arrive ici, c'est qu'elle a échoué au test F_T3 au-dessus !
WHEN type_osm = 'primary' THEN 'F_D1'
WHEN type_osm = 'secondary' THEN 'F_D2'
WHEN type_osm IN ('pedestrian', 'living_street') THEN 'F_D3'

-- 3. LOCAL (F_L)
WHEN type_osm IN ('tertiary', 'residential') THEN 'F_L1'
WHEN type_osm = 'unclassified' THEN 'F_L2'
ELSE 'F_L3'
END as code_pattern
FROM extraction
)
-- Étape 3 : On génère les emprises spatiales (buffers) avec les bonnes largeurs
SELECT
osm_id,
code_pattern,
ST_Buffer(geom_ligne,
CASE
WHEN code_pattern = 'F_T1' THEN 100
WHEN code_pattern = 'F_T2' THEN 75
WHEN code_pattern = 'F_T3' THEN 50
WHEN code_pattern = 'F_D1' THEN 50
WHEN code_pattern = 'F_D2' THEN 35
WHEN code_pattern = 'F_D3' THEN 35
WHEN code_pattern = 'F_L1' THEN 25
WHEN code_pattern = 'F_L2' THEN 10
WHEN code_pattern = 'F_L3' THEN 10
ELSE 10
END
) as geom
FROM classification;

-- Étape 4 : L'index spatial pour rendre les futurs calculs ultra-rapides
CREATE INDEX idx_zones_motifs_commune_geom ON zones_motifs_filaires_commune USING GIST(geom);-- Encore une fois remplacer commune dans les DEUX noms de fichier

Dans le code pensez à remplacer les champs du nom de fichier : zones_motifs_filaires_commune par le nom zones_motifs_filaires_NOM_DE_VOTRE_COMMUNE. Il faut le remplacer trois fois deux fois au début (ligne 2 et 3) et une fois à la fin (ligne 62), en pensant aussi à la fin (ligne 62) à remplacer idx_zones_motifs_commune_geom par idx_zones_motifs_NOM_DE_VOTRE_COMMUNE_geom. Après cela il vous faudra insérer le nom du fichier ligne 12.

Requete OSM selon nomenclature prédéfinie

On cherche à obtenir l'ensemble des ponts OSM qui correspondent à ces catégories :

-- C : Restaurations
('C', 'amenity', 'bar'), ('C', 'amenity', 'biergarten'), ('C', 'amenity', 'cafe'), ('C', 'amenity', 'fast_food'),
('C', 'amenity', 'food_court'), ('C', 'amenity', 'ice_cream'), ('C', 'amenity', 'pub'), ('C', 'amenity', 'restaurant'),
-- E : Equipements Publics
('E', 'amenity', 'college'), ('E', 'amenity', 'dancing_school'), ('E', 'amenity', 'driving_school'), ('E', 'amenity', 'first_aid_school'),
('E', 'amenity', 'kindergarten'), ('E', 'amenity', 'language_school'), ('E', 'amenity', 'library'), ('E', 'amenity', 'surf_school'),
('E', 'amenity', 'toy_library'), ('E', 'amenity', 'research_institute'), ('E', 'amenity', 'training'), ('E', 'amenity', 'music_school'),
('E', 'amenity', 'school'), ('E', 'amenity', 'traffic_park'), ('E', 'amenity', 'university'),
-- F : Services financiers
('F', 'amenity', 'atm'), ('F', 'amenity', 'bank'),
-- S : Santé
('S', 'amenity', 'clinic'), ('S', 'amenity', 'dentist'), ('S', 'amenity', 'doctors'), ('S', 'amenity', 'hospital'), ('S', 'amenity', 'pharmacy'),
-- L : Cultures et Loisirs
('L', 'amenity', 'casino'), ('L', 'amenity', 'cinema'), ('L', 'amenity', 'conference_centre'), ('L', 'amenity', 'events_venue'),
('L', 'amenity', 'exhibition_centre'), ('L', 'amenity', 'gambling'), ('L', 'amenity', 'music_venue'), ('L', 'amenity', 'nightclub'),
('L', 'amenity', 'planetarium'), ('L', 'amenity', 'stage'), ('L', 'amenity', 'theatre'),
-- I : Services Publics (Note: inclut building pour horloges, gares, etc. selon votre liste)
('I', 'amenity', 'courthouse'), ('I', 'amenity', 'police'), ('I', 'amenity', 'post_office'), ('I', 'amenity', 'townhall'),
('I', 'building', 'clock_tower'), ('I', 'building', 'college'), ('I', 'building', 'government'), ('I', 'building', 'museum'), ('I', 'building', 'train_station'),
-- X : Points Urbains Structurants
('X', 'amenity', 'internet_cafe'), ('X', 'amenity', 'marketplace'), ('X', 'amenity', 'place_of_worship'),
-- T : Bâtiments tertiaires
('T', 'building', 'commercial'), ('T', 'building', 'kiosk'), ('T', 'building', 'office'), ('T', 'building', 'retail'), ('T', 'building', 'supermarket'),
-- M : Magasins (L'intégralité de votre liste Food, Mode, Beauté, Culture, Hobbies)
('M', 'shop', 'alcohol'), ('M', 'shop', 'bakery'), ('M', 'shop', 'beverages'), ('M', 'shop', 'brewing_supplies'), ('M', 'shop', 'butcher'),
('M', 'shop', 'cheese'), ('M', 'shop', 'chocolate'), ('M', 'shop', 'coffee'), ('M', 'shop', 'confectionery'), ('M', 'shop', 'convenience'),
('M', 'shop', 'dairy'), ('M', 'shop', 'deli'), ('M', 'shop', 'farm'), ('M', 'shop', 'food'), ('M', 'shop', 'frozen_food'),
('M', 'shop', 'greengrocer'), ('M', 'shop', 'health_food'), ('M', 'shop', 'ice_cream'), ('M', 'shop', 'nuts'), ('M', 'shop', 'pasta'),
('M', 'shop', 'pastry'), ('M', 'shop', 'seafood'), ('M', 'shop', 'spices'), ('M', 'shop', 'tea'), ('M', 'shop', 'tortilla'),
('M', 'shop', 'water'), ('M', 'shop', 'wine'), ('M', 'shop', 'department_store'), ('M', 'shop', 'general'), ('M', 'shop', 'kiosk'),
('M', 'shop', 'mall'), ('M', 'shop', 'supermarket'), ('M', 'shop', 'baby_goods'), ('M', 'shop', 'bag'), ('M', 'shop', 'boutique'),
('M', 'shop', 'clothes'), ('M', 'shop', 'fabric'), ('M', 'shop', 'fashion'), ('M', 'shop', 'fashion_accessories'), ('M', 'shop', 'jewelry'),
('M', 'shop', 'leather'), ('M', 'shop', 'sewing'), ('M', 'shop', 'shoes'), ('M', 'shop', 'shoe_repair'), ('M', 'shop', 'tailor'),
('M', 'shop', 'watches'), ('M', 'shop', 'wool'), ('M', 'shop', 'charity'), ('M', 'shop', 'second_hand'), ('M', 'shop', 'variety_store'),
('M', 'shop', 'beauty'), ('M', 'shop', 'chemist'), ('M', 'shop', 'cosmetics'), ('M', 'shop', 'erotic'), ('M', 'shop', 'hairdresser'),
('M', 'shop', 'hairdresser_supply'), ('M', 'shop', 'hearing_aids'), ('M', 'shop', 'herbalist'), ('M', 'shop', 'massage'), ('M', 'shop', 'medical_supply'),
('M', 'shop', 'nutrition_supplements'), ('M', 'shop', 'optician'), ('M', 'shop', 'perfumery'), ('M', 'shop', 'piercing'), ('M', 'shop', 'tattoo'),
('M', 'shop', 'florist'), ('M', 'shop', 'hardware'), ('M', 'shop', 'houseware'), ('M', 'shop', 'locksmith'), ('M', 'shop', 'antiques'),
('M', 'shop', 'candles'), ('M', 'shop', 'carpet'), ('M', 'shop', 'curtain'), ('M', 'shop', 'furniture'), ('M', 'shop', 'household_linen'),
('M', 'shop', 'interior_decoration'), ('M', 'shop', 'lighting'), ('M', 'shop', 'computer'), ('M', 'shop', 'electronics'), ('M', 'shop', 'hifi'),
('M', 'shop', 'mobile_phone'), ('M', 'shop', 'printer_ink'), ('M', 'shop', 'vacuum_cleaner'), ('M', 'shop', 'art'), ('M', 'shop', 'camera'),
('M', 'shop', 'collector'), ('M', 'shop', 'craft'), ('M', 'shop', 'frame'), ('M', 'shop', 'games'), ('M', 'shop', 'model'),
('M', 'shop', 'music'), ('M', 'shop', 'musical_instrument'), ('M', 'shop', 'photo'), ('M', 'shop', 'trophy'), ('M', 'shop', 'video'),
('M', 'shop', 'video_games'), ('M', 'shop', 'anime'), ('M', 'shop', 'books'), ('M', 'shop', 'gift'), ('M', 'shop', 'lottery'),
('M', 'shop', 'newsagent'), ('M', 'shop', 'stationery'), ('M', 'shop', 'ticket'), ('M', 'shop', 'cannabis'), ('M', 'shop', 'copyshop'),
('M', 'shop', 'dry_cleaning'), ('M', 'shop', 'e-cigarette'), ('M', 'shop', 'laundry'), ('M', 'shop', 'outpost'), ('M', 'shop', 'party'),
('M', 'shop', 'pet'), ('M', 'shop', 'pet_grooming'), ('M', 'shop', 'pyrotechnics'), ('M', 'shop', 'religion'), ('M', 'shop', 'tobacco'),
('M', 'shop', 'toys'), ('M', 'shop', 'travel_agency'), ('M', 'shop', 'weapons');

Pour cela on a besoin de l'extension QuickOSM et plus précisément de la rubrique requête de cette extension :

Ensuite dans cet onglet copiez collez ce code en pensant à changer ligne 3 $commune par $Le nom de votre commune ( Par exemple : $La Ciotat). :

[out:json][timeout:120];
// 1. Définition de la zone de recherche
{{geocodeArea:$commune}}->.searchArea;

(
  // Catégories C, E, F, S, L, I, X (Amenity)
  node["amenity"~"bar|biergarten|cafe|fast_food|food_court|ice_cream|pub|restaurant|college|dancing_school|driving_school|first_aid_school|kindergarten|language_school|library|surf_school|toy_library|research_institute|training|music_school|school|traffic_park|university|atm|bank|clinic|dentist|doctors|hospital|pharmacy|casino|cinema|conference_centre|events_venue|exhibition_centre|gambling|music_venue|nightclub|planetarium|stage|theatre|courthouse|police|post_office|townhall|internet_cafe|marketplace|place_of_worship"](area.searchArea);
  way["amenity"~"bar|biergarten|cafe|fast_food|food_court|ice_cream|pub|restaurant|college|dancing_school|driving_school|first_aid_school|kindergarten|language_school|library|surf_school|toy_library|research_institute|training|music_school|school|traffic_park|university|atm|bank|clinic|dentist|doctors|hospital|pharmacy|casino|cinema|conference_centre|events_venue|exhibition_centre|gambling|music_venue|nightclub|planetarium|stage|theatre|courthouse|police|post_office|townhall|internet_cafe|marketplace|place_of_worship"](area.searchArea);
  
  // Catégories I, T (Building)
  node["building"~"clock_tower|college|government|museum|train_station|commercial|kiosk|office|retail|supermarket"](area.searchArea);
  way["building"~"clock_tower|college|government|museum|train_station|commercial|kiosk|office|retail|supermarket"](area.searchArea);

  // Catégorie M (Shop) - On prend tous les commerces pour être exhaustif
  node["shop"](area.searchArea);
  way["shop"](area.searchArea);
);

// Sortie des données
out body;
>;
out skel qt;

Exécutez la requête et maintenant vous n'avez plus qu'à convertir votre couche temporaire en couche définitive puis la transmettre via PuTTY pour pouvoir l'exploiter en VPS.

Transmission de données via PuTTY

Transmission de fichiers GPKG :

export PGPASSWORD='Alteris2026'
ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 user=alteris_admin dbname=alteris_geo" \
"/home/debian/data/Regions/93/13/200054807/13028/Highway_La_Ciotat.gpkg" \
-nln Roads_La_Ciotat \
-lco GEOMETRY_NAME=geom \
-lco FID=gid \
-nlt PROMOTE_TO_MULTI \
-overwrite

Remplacer le chemin ligne 3 par votre chemin.

Indicateurs délinquance

On cherche à construire une base de données qui renvoie une ligne pour chaque commune et qui nous permet d'avoir un indicateur de la délinquance sur chaque type de crimes sauf homicides tout en nous permettant de connaitre la significativité de la différence entre l'indicateur obtenu à l'échelle communale et celui qu'on obtient à l'échelle départementale ou encore l'échelle régionale.

Choix de la base de données :

Bases statistiques communale, départementale et régionale de la délinquance enregistrée par la police et la gendarmerie nationales. Cette base de données se structure par communes en ayant une ligne pour chaque infraction pour chaque commune avec comme liste d'infractions :

Infraction commise	Abréviation	Unité de compte
Violences Physiques Intrafamiliales sur personnes de 15 ans ou plus	VPI	Victime
Violences Physiques Hors Cadre Familial sur personnes de 15 ans ou plus	VPHCF	Victime
Violences sexuelles	VS	Victime
Vols avec armes (armes à feu, armes blanches ou par destination)	VAA	Infraction
Vols violents sans arme	VVSA	Infraction
Vols sans violence contre des personnes	VSVCP	Victime entendue
Cambriolages de logements	CL	Infraction
Vols de véhicules	VV	Véhicule
Vols dans les véhicules	VDV	Véhicule
Vols d'accessoires sur véhicules	VAV	Véhicule
Destructions et dégradations volontaires	DDV	Infraction
Usage de stupéfiants	US	Mis en cause
Usage de stupéfiants dont amendes forfaitaires délictuelles (AFD)	USAFD	Mis en cause
Trafic de stupéfiants	TS	Mis en cause
Escroqueries et fraudes aux moyens de paiement	EFMP	Victime

Pour obtenir le choix on va d'abord définir le nom des colonnes

Identifiant Commune	codgeo
Identifiant Departement	coddep
Identifiant région	codreg
Identifiant crime échelle communale	i_"Abreviation de l'infraction en minuscule"_com
Identifiant crime échelle départementale	i_"Abreviation de l'infraction en minuscule"_dep
Identifiant crime échelle régionale	i_"Abreviation de l'infraction en minuscule"_reg
Significativité crime entre commune et département	i_sign_"Abréviation de l'infraction en minuscule"_com_dep
Significativité crime entre commune et région	i_sign_"Abréviation de l'infraction en minuscule"_com_reg
Significativité crime entre département et région	i_sign_"Abréviation de l'infraction en minuscule"_dep_reg
Fiabilité des données sur le crime à l'échelle communale ( y'a t'il une donnée manquante sur une année, au quel cas on a pris la moyenne départementale sur cette année )	i_"Abréviation de l'infraction en minuscule"_com_fiabilite

Pour obtenir ces colonnes on va donc traiter la base de données via PGadmin dans lequel on va effectuer deux requetes, tout d'abord on va effectuer une première requette qu'on appellera i_deli_nat :

DROP TABLE IF EXISTS i_deli_nat;

CREATE TABLE i_deli_nat AS
WITH 

-- 1. Nettoyage hyper-sécurisé de la base
base_clean AS (
    SELECT 
        codgeo_2025 AS codgeo,
        LEFT(codgeo_2025, 2) AS coddep,
        annee,
        indicateur,
        
        -- Nettoyage de insee_pop
        CASE 
            WHEN TRIM(insee_pop) IN ('', 'NA', 'N/A', 'NULL', 'error') OR insee_pop IS NULL THEN NULL 
            ELSE REPLACE(insee_pop, ',', '.')::numeric 
        END AS insee_pop,
        
        -- Nettoyage de taux_pour_mille et application de la règle
        CASE 
            WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN 
                CASE 
                    WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
                    ELSE REPLACE(complement_info_taux, ',', '.')::numeric 
                END
            ELSE REPLACE(taux_pour_mille, ',', '.')::numeric 
        END AS taux_retenu,
        
        -- Règle : Fiabilité
        CASE 
            WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN false 
            ELSE true 
        END AS est_fiable,
        
        -- Préparation pour le département
        CASE 
            WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
            ELSE REPLACE(complement_info_taux, ',', '.')::numeric 
        END AS comp_taux_num
    FROM delinquance_nat
),

-- 2. Moyenne par COMMUNE
stats_com AS (
    SELECT 
        codgeo,
        coddep,
        indicateur,
        AVG(taux_retenu) AS i_com,
        AVG(insee_pop) AS pop_com,
        BOOL_AND(est_fiable) AS i_com_fiabilite
    FROM base_clean
    GROUP BY codgeo, coddep, indicateur
),

-- 3. Valeurs pour le DEPARTEMENT
premiere_valeur_dep AS (
    SELECT DISTINCT ON (coddep, indicateur, annee) 
        coddep, indicateur, annee, comp_taux_num
    FROM base_clean
    WHERE comp_taux_num IS NOT NULL
    ORDER BY coddep, indicateur, annee, codgeo
),
stats_dep AS (
    SELECT 
        c.coddep,
        c.indicateur,
        COALESCE(
            AVG(p.comp_taux_num), 
            AVG(c.taux_retenu)    
        ) AS i_dep
    FROM base_clean c
    LEFT JOIN premiere_valeur_dep p 
        ON c.coddep = p.coddep AND c.indicateur = p.indicateur AND c.annee = p.annee
    GROUP BY c.coddep, c.indicateur
),

-- 4. Jointure et nettoyage population dep/reg (au cas où il y ait des 'NA' ici aussi)
stats_dep_enrichies AS (
    SELECT 
        sd.coddep,
        sd.indicateur,
        sd.i_dep,
        pd.codreg,
        CASE 
            WHEN TRIM(pd.ptot::text) IN ('', 'NA', 'N/A', 'NULL', 'error') OR pd.ptot IS NULL THEN NULL 
            ELSE REPLACE(pd.ptot::text, ',', '.')::numeric 
        END AS pop_dep,
        CASE 
            WHEN TRIM(pd.ptotreg::text) IN ('', 'NA', 'N/A', 'NULL', 'error') OR pd.ptotreg IS NULL THEN NULL 
            ELSE REPLACE(pd.ptotreg::text, ',', '.')::numeric 
        END AS pop_reg
    FROM stats_dep sd
    JOIN pop_dep pd ON sd.coddep = pd.coddep
),

-- 5. Moyenne par REGION
stats_reg AS (
    SELECT 
        codreg,
        indicateur,
        AVG(i_dep) AS i_reg
    FROM stats_dep_enrichies
    GROUP BY codreg, indicateur
)

-- 6. ASSEMBLAGE FINAL ET CALCULS DE SIGNIFICATIVITE
SELECT 
    sc.codgeo,
    sc.coddep,
    sde.codreg,
    sc.indicateur,
    sc.i_com,
    sde.i_dep,
    sr.i_reg,
    sc.i_com_fiabilite,
    
    -- Z-Score Com vs Dep
    CASE 
        WHEN (sc.i_com/1000) = (sde.i_dep/1000) THEN 'non significatif'
        WHEN ABS((sc.i_com/1000) - (sde.i_dep/1000)) / NULLIF(SQRT(
            ((sde.i_dep/1000) * (1 - (sde.i_dep/1000)) / NULLIF(sc.pop_com, 0)) + 
            ((sde.i_dep/1000) * (1 - (sde.i_dep/1000)) / NULLIF(sde.pop_dep, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sc.i_com > sde.i_dep THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_com_dep,

    -- Z-Score Com vs Reg
    CASE 
        WHEN (sc.i_com/1000) = (sr.i_reg/1000) THEN 'non significatif'
        WHEN ABS((sc.i_com/1000) - (sr.i_reg/1000)) / NULLIF(SQRT(
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sc.pop_com, 0)) + 
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_reg, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sc.i_com > sr.i_reg THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_com_reg,

    -- Z-Score Dep vs Reg
    CASE 
        WHEN (sde.i_dep/1000) = (sr.i_reg/1000) THEN 'non significatif'
        WHEN ABS((sde.i_dep/1000) - (sr.i_reg/1000)) / NULLIF(SQRT(
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_dep, 0)) + 
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_reg, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sde.i_dep > sr.i_reg THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_dep_reg

FROM stats_com sc
JOIN stats_dep_enrichies sde ON sc.coddep = sde.coddep AND sc.indicateur = sde.indicateur
JOIN stats_reg sr ON sde.codreg = sr.codreg AND sc.indicateur = sr.indicateur;

Cette première requete permet de créer les indicateurs que l'on va calculer, elle est déjà exploitable mais chaque ligne représente une infraction par commune, on ne peut donc pas utiliser cette dernière pour obtenir la table que l'on recherche.

Pour cela on va donc effectuer une deuxième requete :

DROP TABLE IF EXISTS I_Deli_TX;

CREATE TABLE I_Deli_TX AS
SELECT 
    codgeo, 
    coddep, 
    codreg,

    -- 1. Violences physiques intrafamiliales (VPI)
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_com END) AS I_VPI_Com,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_dep END) AS I_VPI_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_reg END) AS I_VPI_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_com_dep END) AS I_Sign_VPI_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_com_reg END) AS I_Sign_VPI_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_dep_reg END) AS I_Sign_VPI_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_com_fiabilite ELSE true END) AS I_VPI_Com_Fiabilite,

    -- 2. Violences physiques hors cadre familial (VPHCF)
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_com END) AS I_VPHCF_Com,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_dep END) AS I_VPHCF_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_reg END) AS I_VPHCF_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_com_dep END) AS I_Sign_VPHCF_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_com_reg END) AS I_Sign_VPHCF_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_dep_reg END) AS I_Sign_VPHCF_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_com_fiabilite ELSE true END) AS I_VPHCF_Com_Fiabilite,

    -- 3. Violences sexuelles (VS)
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_com END) AS I_VS_Com,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_dep END) AS I_VS_Dep,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_reg END) AS I_VS_Reg,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_com_dep END) AS I_Sign_VS_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_com_reg END) AS I_Sign_VS_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_dep_reg END) AS I_Sign_VS_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences sexuelles' THEN i_com_fiabilite ELSE true END) AS I_VS_Com_Fiabilite,

    -- 4. Vols avec armes (VAA)
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_com END) AS I_VAA_Com,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_dep END) AS I_VAA_Dep,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_reg END) AS I_VAA_Reg,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_com_dep END) AS I_Sign_VAA_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_com_reg END) AS I_Sign_VAA_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_dep_reg END) AS I_Sign_VAA_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols avec armes' THEN i_com_fiabilite ELSE true END) AS I_VAA_Com_Fiabilite,

    -- 5. Vols violents sans arme (VVSA)
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_com END) AS I_VVSA_Com,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_dep END) AS I_VVSA_Dep,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_reg END) AS I_VVSA_Reg,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_com_dep END) AS I_Sign_VVSA_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_com_reg END) AS I_Sign_VVSA_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_dep_reg END) AS I_Sign_VVSA_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_com_fiabilite ELSE true END) AS I_VVSA_Com_Fiabilite,

    -- 6. Vols sans violence contre des personnes (VSVCP)
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_com END) AS I_VSVCP_Com,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_dep END) AS I_VSVCP_Dep,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_reg END) AS I_VSVCP_Reg,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_com_dep END) AS I_Sign_VSVCP_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_com_reg END) AS I_Sign_VSVCP_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_dep_reg END) AS I_Sign_VSVCP_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_com_fiabilite ELSE true END) AS I_VSVCP_Com_Fiabilite,

    -- 7. Escroqueries et fraudes aux moyens de paiement (EFMP)
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_com END) AS I_EFMP_Com,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_dep END) AS I_EFMP_Dep,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_reg END) AS I_EFMP_Reg,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_com_dep END) AS I_Sign_EFMP_Com_Dep,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_com_reg END) AS I_Sign_EFMP_Com_Reg,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_dep_reg END) AS I_Sign_EFMP_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_com_fiabilite ELSE true END) AS I_EFMP_Com_Fiabilite,

    -- 8. Cambriolages de logement (CL)
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_com END) AS I_CL_Com,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_dep END) AS I_CL_Dep,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_reg END) AS I_CL_Reg,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_com_dep END) AS I_Sign_CL_Com_Dep,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_com_reg END) AS I_Sign_CL_Com_Reg,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_dep_reg END) AS I_Sign_CL_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_com_fiabilite ELSE true END) AS I_CL_Com_Fiabilite,

    -- 9. Vols de véhicule (VV)
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_com END) AS I_VV_Com,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_dep END) AS I_VV_Dep,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_reg END) AS I_VV_Reg,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_com_dep END) AS I_Sign_VV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_com_reg END) AS I_Sign_VV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_dep_reg END) AS I_Sign_VV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols de véhicule' THEN i_com_fiabilite ELSE true END) AS I_VV_Com_Fiabilite,

    -- 10. Vols dans les véhicules (VDV)
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_com END) AS I_VDV_Com,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_dep END) AS I_VDV_Dep,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_reg END) AS I_VDV_Reg,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_com_dep END) AS I_Sign_VDV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_com_reg END) AS I_Sign_VDV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_dep_reg END) AS I_Sign_VDV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_com_fiabilite ELSE true END) AS I_VDV_Com_Fiabilite,

    -- 11. Vols d'accessoires sur véhicules (VAV)
    -- Attention ici : on met deux apostrophes ('') pour dire à SQL qu'il s'agit du texte "d'accessoires" et non de la fin de la chaîne
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_com END) AS I_VAV_Com,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_dep END) AS I_VAV_Dep,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_reg END) AS I_VAV_Reg,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_com_dep END) AS I_Sign_VAV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_com_reg END) AS I_Sign_VAV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_dep_reg END) AS I_Sign_VAV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_com_fiabilite ELSE true END) AS I_VAV_Com_Fiabilite,

    -- 12. Destructions et dégradations volontaires (DDV)
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_com END) AS I_DDV_Com,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_dep END) AS I_DDV_Dep,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_reg END) AS I_DDV_Reg,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_com_dep END) AS I_Sign_DDV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_com_reg END) AS I_Sign_DDV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_dep_reg END) AS I_Sign_DDV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_com_fiabilite ELSE true END) AS I_DDV_Com_Fiabilite,

    -- 13. Usage de stupéfiants (US)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_com END) AS I_US_Com,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_dep END) AS I_US_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_reg END) AS I_US_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_com_dep END) AS I_Sign_US_Com_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_com_reg END) AS I_Sign_US_Com_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_dep_reg END) AS I_Sign_US_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_com_fiabilite ELSE true END) AS I_US_Com_Fiabilite,

    -- 14. Usage de stupéfiants (AFD) (USAFD)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_com END) AS I_USAFD_Com,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_dep END) AS I_USAFD_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_reg END) AS I_USAFD_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_com_dep END) AS I_Sign_USAFD_Com_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_com_reg END) AS I_Sign_USAFD_Com_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_dep_reg END) AS I_Sign_USAFD_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_com_fiabilite ELSE true END) AS I_USAFD_Com_Fiabilite,

    -- 15. Trafic de stupéfiants (TS)
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_com END) AS I_TS_Com,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_dep END) AS I_TS_Dep,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_reg END) AS I_TS_Reg,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_com_dep END) AS I_Sign_TS_Com_Dep,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_com_reg END) AS I_Sign_TS_Com_Reg,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_dep_reg END) AS I_Sign_TS_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_com_fiabilite ELSE true END) AS I_TS_Com_Fiabilite

FROM i_deli_nat
GROUP BY codgeo, coddep, codreg
ORDER BY codgeo;

Cette requête permet d'avoir une table où chaque ligne représente une commune et chaque colonne représente une des colonnes définies préalablement.

NE PAS OUVRIR SANS FILTRE

Pour pouvoir exploiter cette base de données, il faut suivre un protocole, une fois que vous avez ajouté cette table à QGIS vous remarquerez qu'elle n'a pas de géométrie, c'est normal. Pour pouvoir ouvrir la table attributaire il vous faudra filtrer la table avec une requête du type

codgeo = 'Insee_com_de_votre_commune'

Ou alors

coddep = 'Numéro_de_département_de_votre_commune'

Vous pourrez ensuite joindre cette table à une couche avec géométrie correspondant.

Une mise à jour existe mais est difficilement exploitable due au poids de la table cette dernière rajoute des colonnes elle se trouve dans la colonne i_deli_tx_complete

Identifiant Commune	codgeo	TEXT
Identifiant Département	coddep	TEXT
Identifiant région	codreg	TEXT
Taux pour mille en fonction du crime échelle communale	i_"Abréviation de l'infraction en minuscule"_com	Numeric
Taux pour mille en fonction du crime échelle départementale	i_"Abréviation de l'infraction en minuscule"_dep	Numeric
Taux pour mille en fonction du crime échelle régionale	i_"Abréviation de l'infraction en minuscule"_reg	Numeric
Significativité crime entre commune et département	i_sign_"Abréviation de l'infraction en minuscule"_com_dep	Numeric
Significativité crime entre commune et région	i_sign_"Abréviation de l'infraction en minuscule"_com_reg	Numeric
Significativité crime entre département et région	i_sign_"Abréviation de l'infraction en minuscule"_dep_reg	Numeric
Fiabilité des données sur le crime à l'échelle communale ( y'a t'il une donnée manquante sur une année, au quel cas on a pris la moyenne départementale sur cette année )	i_"Abréviation de l'infraction en minuscule"_com_fiabilite	Boolean
Taux pour mille en fonction du crime et de l'année (2016 ou 2024 ) échelle communale	i_"Abréviation de l'infraction en minuscule"_com_"Année sélectionnée"	Numeric
Taux pour mille en fonction du crime et de l'année (2016 ou 2024 ) échelle départementale	i_"Abréviation de l'infraction en minuscule"_dep_"Année sélectionnée"	Numeric
Taux pour mille en fonction du crime et de l'année (2016 ou 2024 ) échelle régionale	i_"Abréviation de l'infraction en minuscule"_reg_"Année sélectionnée"	Numeric
Évolution de taux pour mille en fonction du crime entre 2016 et 2024 (2024/2016)	i_"Abréviation de l'infraction en minuscule"_ratio_24_16	Numeric

Pour cela on effectue deux nouvelles requetes la première permet d'obtenir toutes les données pour les années 2016 et 2024 elle se trouve dans la base de données i_deli_tx_evol :

DROP TABLE IF EXISTS I_Deli_TX_Evol;

CREATE TABLE I_Deli_TX_Evol AS
WITH base_clean AS (
    SELECT 
        codgeo_2025 AS codgeo,
        LEFT(codgeo_2025, 2) AS coddep,
        annee,
        indicateur,
        CASE WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN 
                 CASE WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
                      ELSE REPLACE(complement_info_taux, ',', '.')::numeric END
             ELSE REPLACE(taux_pour_mille, ',', '.')::numeric END AS taux_retenu,
        CASE WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
             ELSE REPLACE(complement_info_taux, ',', '.')::numeric END AS comp_taux_num
    -- /!\ VÉRIFIEZ BIEN QUE C'EST LE NOM DE VOTRE TABLE BRUTE ICI :
    FROM delinquance_nat 
    WHERE annee IN ('2016', '2024')
),
stats_com_yr AS (
    SELECT codgeo, coddep, indicateur, annee, AVG(taux_retenu) AS i_com_yr
    FROM base_clean GROUP BY codgeo, coddep, indicateur, annee
),
premiere_valeur_dep_yr AS (
    SELECT DISTINCT ON (coddep, indicateur, annee) coddep, indicateur, annee, comp_taux_num
    FROM base_clean WHERE comp_taux_num IS NOT NULL ORDER BY coddep, indicateur, annee, codgeo
),
stats_dep_yr AS (
    SELECT c.coddep, c.indicateur, c.annee, COALESCE(AVG(p.comp_taux_num), AVG(c.taux_retenu)) AS i_dep_yr
    FROM base_clean c
    LEFT JOIN premiere_valeur_dep_yr p ON c.coddep = p.coddep AND c.indicateur = p.indicateur AND c.annee = p.annee
    GROUP BY c.coddep, c.indicateur, c.annee
),
stats_dep_yr_enrichies AS (
    SELECT sd.coddep, sd.indicateur, sd.annee, sd.i_dep_yr, pd.codreg
    FROM stats_dep_yr sd JOIN pop_dep pd ON sd.coddep = pd.coddep
),
stats_reg_yr AS (
    SELECT codreg, indicateur, annee, AVG(i_dep_yr) AS i_reg_yr
    FROM stats_dep_yr_enrichies GROUP BY codreg, indicateur, annee
),
deli_inter_yr AS (
    SELECT sc.codgeo, sc.indicateur, sc.annee, sc.i_com_yr, sde.i_dep_yr, sr.i_reg_yr
    FROM stats_com_yr sc
    JOIN stats_dep_yr_enrichies sde ON sc.coddep = sde.coddep AND sc.indicateur = sde.indicateur AND sc.annee = sde.annee
    JOIN stats_reg_yr sr ON sde.codreg = sr.codreg AND sc.indicateur = sr.indicateur AND sc.annee = sr.annee
)

-- LE PIVOT DES 105 COLONNES
SELECT 
    codgeo,

    -- VPI (Violences physiques intrafamiliales)
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_com_yr END) AS I_VPI_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_dep_yr END) AS I_VPI_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_reg_yr END) AS I_VPI_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_com_yr END) AS I_VPI_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_dep_yr END) AS I_VPI_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_reg_yr END) AS I_VPI_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_com_yr END), 0) AS I_VPI_Ratio_24_16,

    -- VPHCF (Violences physiques hors cadre familial)
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_com_yr END) AS I_VPHCF_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_dep_yr END) AS I_VPHCF_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_reg_yr END) AS I_VPHCF_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_com_yr END) AS I_VPHCF_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_dep_yr END) AS I_VPHCF_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_reg_yr END) AS I_VPHCF_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_com_yr END), 0) AS I_VPHCF_Ratio_24_16,

    -- VS (Violences sexuelles)
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_com_yr END) AS I_VS_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_dep_yr END) AS I_VS_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_reg_yr END) AS I_VS_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_com_yr END) AS I_VS_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_dep_yr END) AS I_VS_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_reg_yr END) AS I_VS_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_com_yr END), 0) AS I_VS_Ratio_24_16,

    -- VAA (Vols avec armes)
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_com_yr END) AS I_VAA_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_dep_yr END) AS I_VAA_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_reg_yr END) AS I_VAA_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_com_yr END) AS I_VAA_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_dep_yr END) AS I_VAA_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_reg_yr END) AS I_VAA_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_com_yr END), 0) AS I_VAA_Ratio_24_16,

    -- VVSA (Vols violents sans arme)
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_com_yr END) AS I_VVSA_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_dep_yr END) AS I_VVSA_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_reg_yr END) AS I_VVSA_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_com_yr END) AS I_VVSA_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_dep_yr END) AS I_VVSA_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_reg_yr END) AS I_VVSA_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_com_yr END), 0) AS I_VVSA_Ratio_24_16,

    -- VSVCP (Vols sans violence contre des personnes)
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_com_yr END) AS I_VSVCP_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_dep_yr END) AS I_VSVCP_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_reg_yr END) AS I_VSVCP_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_com_yr END) AS I_VSVCP_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_dep_yr END) AS I_VSVCP_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_reg_yr END) AS I_VSVCP_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_com_yr END), 0) AS I_VSVCP_Ratio_24_16,

    -- CL (Cambriolages de logement)
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_com_yr END) AS I_CL_Com_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_dep_yr END) AS I_CL_Dep_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_reg_yr END) AS I_CL_Reg_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_com_yr END) AS I_CL_Com_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_dep_yr END) AS I_CL_Dep_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_reg_yr END) AS I_CL_Reg_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_com_yr END), 0) AS I_CL_Ratio_24_16,

    -- VV (Vols de véhicule)
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_com_yr END) AS I_VV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_dep_yr END) AS I_VV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_reg_yr END) AS I_VV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_com_yr END) AS I_VV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_dep_yr END) AS I_VV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_reg_yr END) AS I_VV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_com_yr END), 0) AS I_VV_Ratio_24_16,

    -- VDV (Vols dans les véhicules)
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_com_yr END) AS I_VDV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_dep_yr END) AS I_VDV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_reg_yr END) AS I_VDV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_com_yr END) AS I_VDV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_dep_yr END) AS I_VDV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_reg_yr END) AS I_VDV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_com_yr END), 0) AS I_VDV_Ratio_24_16,

    -- VAV (Vols d'accessoires sur véhicules)
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_com_yr END) AS I_VAV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_dep_yr END) AS I_VAV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_reg_yr END) AS I_VAV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_com_yr END) AS I_VAV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_dep_yr END) AS I_VAV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_reg_yr END) AS I_VAV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_com_yr END), 0) AS I_VAV_Ratio_24_16,

    -- DDV (Destructions et dégradations volontaires)
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_com_yr END) AS I_DDV_Com_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_dep_yr END) AS I_DDV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_reg_yr END) AS I_DDV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_com_yr END) AS I_DDV_Com_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_dep_yr END) AS I_DDV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_reg_yr END) AS I_DDV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_com_yr END), 0) AS I_DDV_Ratio_24_16,

    -- US (Usage de stupéfiants)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_com_yr END) AS I_US_Com_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_dep_yr END) AS I_US_Dep_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_reg_yr END) AS I_US_Reg_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_com_yr END) AS I_US_Com_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_dep_yr END) AS I_US_Dep_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_reg_yr END) AS I_US_Reg_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_com_yr END), 0) AS I_US_Ratio_24_16,

    -- USAFD (Usage de stupéfiants (AFD))
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_com_yr END) AS I_USAFD_Com_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_dep_yr END) AS I_USAFD_Dep_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_reg_yr END) AS I_USAFD_Reg_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_com_yr END) AS I_USAFD_Com_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_dep_yr END) AS I_USAFD_Dep_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_reg_yr END) AS I_USAFD_Reg_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_com_yr END), 0) AS I_USAFD_Ratio_24_16,

    -- TS (Trafic de stupéfiants)
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_com_yr END) AS I_TS_Com_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_dep_yr END) AS I_TS_Dep_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_reg_yr END) AS I_TS_Reg_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_com_yr END) AS I_TS_Com_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_dep_yr END) AS I_TS_Dep_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_reg_yr END) AS I_TS_Reg_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_com_yr END), 0) AS I_TS_Ratio_24_16,

    -- EFMP (Escroqueries et fraudes aux moyens de paiement)
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_com_yr END) AS I_EFMP_Com_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_dep_yr END) AS I_EFMP_Dep_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_reg_yr END) AS I_EFMP_Reg_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_com_yr END) AS I_EFMP_Com_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_dep_yr END) AS I_EFMP_Dep_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_reg_yr END) AS I_EFMP_Reg_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_com_yr END), 0) AS I_EFMP_Ratio_24_16

FROM deli_inter_yr
GROUP BY codgeo;

Cette base de données est lisible mais NE PAS OUVRIR SANS FILTRAGE PRÉALABLE les bases de données de ce fichier sont trop grosses elle feront planter SYSTÉMATIQUEMENT Qgis.

Filtrer de la même manière que au préalable.

Ensuite on effectue une dernière manipulation pour obtenir une base de données conjointe qui s'appelle i_deli_tx_complete

DROP TABLE IF EXISTS I_Deli_TX_Complete;

CREATE TABLE I_Deli_TX_Complete AS
SELECT 
    t1.*, 
    -- On rajoute toutes les colonnes de l'annexe sauf "codgeo" pour éviter les doublons
    t2.I_VPI_Com_2016, t2.I_VPI_Dep_2016, t2.I_VPI_Reg_2016, t2.I_VPI_Com_2024, t2.I_VPI_Dep_2024, t2.I_VPI_Reg_2024, t2.I_VPI_Ratio_24_16,
    t2.I_VPHCF_Com_2016, t2.I_VPHCF_Dep_2016, t2.I_VPHCF_Reg_2016, t2.I_VPHCF_Com_2024, t2.I_VPHCF_Dep_2024, t2.I_VPHCF_Reg_2024, t2.I_VPHCF_Ratio_24_16,
    t2.I_VS_Com_2016, t2.I_VS_Dep_2016, t2.I_VS_Reg_2016, t2.I_VS_Com_2024, t2.I_VS_Dep_2024, t2.I_VS_Reg_2024, t2.I_VS_Ratio_24_16,
    t2.I_VAA_Com_2016, t2.I_VAA_Dep_2016, t2.I_VAA_Reg_2016, t2.I_VAA_Com_2024, t2.I_VAA_Dep_2024, t2.I_VAA_Reg_2024, t2.I_VAA_Ratio_24_16,
    t2.I_VVSA_Com_2016, t2.I_VVSA_Dep_2016, t2.I_VVSA_Reg_2016, t2.I_VVSA_Com_2024, t2.I_VVSA_Dep_2024, t2.I_VVSA_Reg_2024, t2.I_VVSA_Ratio_24_16,
    t2.I_VSVCP_Com_2016, t2.I_VSVCP_Dep_2016, t2.I_VSVCP_Reg_2016, t2.I_VSVCP_Com_2024, t2.I_VSVCP_Dep_2024, t2.I_VSVCP_Reg_2024, t2.I_VSVCP_Ratio_24_16,
    t2.I_CL_Com_2016, t2.I_CL_Dep_2016, t2.I_CL_Reg_2016, t2.I_CL_Com_2024, t2.I_CL_Dep_2024, t2.I_CL_Reg_2024, t2.I_CL_Ratio_24_16,
    t2.I_VV_Com_2016, t2.I_VV_Dep_2016, t2.I_VV_Reg_2016, t2.I_VV_Com_2024, t2.I_VV_Dep_2024, t2.I_VV_Reg_2024, t2.I_VV_Ratio_24_16,
    t2.I_VDV_Com_2016, t2.I_VDV_Dep_2016, t2.I_VDV_Reg_2016, t2.I_VDV_Com_2024, t2.I_VDV_Dep_2024, t2.I_VDV_Reg_2024, t2.I_VDV_Ratio_24_16,
    t2.I_VAV_Com_2016, t2.I_VAV_Dep_2016, t2.I_VAV_Reg_2016, t2.I_VAV_Com_2024, t2.I_VAV_Dep_2024, t2.I_VAV_Reg_2024, t2.I_VAV_Ratio_24_16,
    t2.I_DDV_Com_2016, t2.I_DDV_Dep_2016, t2.I_DDV_Reg_2016, t2.I_DDV_Com_2024, t2.I_DDV_Dep_2024, t2.I_DDV_Reg_2024, t2.I_DDV_Ratio_24_16,
    t2.I_US_Com_2016, t2.I_US_Dep_2016, t2.I_US_Reg_2016, t2.I_US_Com_2024, t2.I_US_Dep_2024, t2.I_US_Reg_2024, t2.I_US_Ratio_24_16,
    t2.I_USAFD_Com_2016, t2.I_USAFD_Dep_2016, t2.I_USAFD_Reg_2016, t2.I_USAFD_Com_2024, t2.I_USAFD_Dep_2024, t2.I_USAFD_Reg_2024, t2.I_USAFD_Ratio_24_16,
    t2.I_TS_Com_2016, t2.I_TS_Dep_2016, t2.I_TS_Reg_2016, t2.I_TS_Com_2024, t2.I_TS_Dep_2024, t2.I_TS_Reg_2024, t2.I_TS_Ratio_24_16,
    t2.I_EFMP_Com_2016, t2.I_EFMP_Dep_2016, t2.I_EFMP_Reg_2016, t2.I_EFMP_Com_2024, t2.I_EFMP_Dep_2024, t2.I_EFMP_Reg_2024, t2.I_EFMP_Ratio_24_16
FROM I_Deli_TX t1
LEFT JOIN I_Deli_TX_Evol t2 ON t1.codgeo = t2.codgeo;

NE JAMAIS OUVRIR CA PLANTE AUTOMATIQUEMENT.

La base de données fait 250 colonnes pour 34000 lignes elle est absolument impossible à ouvrir. De manière générale elle n'est ouvrable et gérable que sur une seule commune pour ouvrir la table attributaire.

Atlas Délinquance Tutoriel

On cherche à faire un Atlas pour une commune spécifique qui résume l'ensemble des indicateurs sélectionnés de la délinquance

Pour cela tout d'abord on va sélectionner dans le NAS le fichier QGIS_Delinquance il se trouve dans /Alteris/Production/Atlas_Indicateurs_2016_2024. On aura aussi besoin à cet emplacement de Symbologie_indicateur_2016_2024.qml. Téléchargez les deux et une fois téléchargez ouvrez les.

Une fois ouverts récupérez la base de données i_deli_tx_complete dans le PostGIS, récupérez aussi le fichier communes_fr dans PostGIS et enfin récupérez la couche Pilote_Atlas. Une fois ces derniers récupérés vous devez effectuer une filtration à l'échelle communale pour cela clique droit sur votre couche puis rentrez sur communes_fr insee= 'Code_Insee_de_votre_commune'

Ensuite faites la même chose sur i_deli_tx_complete mais cette fois ci renseignez codgeo = 'Code_Insee_de_votre_commune'.

Après cela on va effectuer une jointure attributaire allez dans les propriétés de la couche commune_fr et allez sur jointures, ajoutez en une nouvelle et choisissez comme champ de jointure respectivement codgeo et insee.

Pensez à personnaliser le préfixe du nom de champ : ce dernier doit etre _ c'est essentiel sans ça rien ne marchera.

Votre jointure est donc réalisée il vous reste donc à importer un style pour ça en bas à gauche vous avez une petite style, sélectionnez charger le style et choisissez le fichier Symbologie_indicateur_2016_2024.qml appliquez le style et vous pourrez passer à l'atlas.

Allez dans le gestionnaire de mise en pages et sélectionnez Atlas_Nom_Ville_Indicateurs dans cet atlas tout est automatisé vous n'avez que quelques petits changements à effectuer

Tout d'abord normalement l'atlas est généré mais si ce n'est pas le cas sélectionnez comme couche de couverture la couche Pilote_Atlas et comme nom de la page la colonne abrev.

Vous devrez supprimer la carte et la remettre ( je ne sais pas pourquoi c'est la seule manière pour que ça fonctionne ).

Vous devrez donc modifier Nom_Ville par le nom de votre commune, et le nom de layer par le nom de votre couche dans laquelle sont vos données.

Attention ce processus est à répéter sur les trois blocs.

Ensuite il vous restera à modifier la légende, tout d'abord faites mise à jour auto et ne gardez que votre couche communes_fr puis copiez collez ceci pour la couche principale :

Comparaison du taux de

[%attribute(@atlas_feature, 'nom')%]

pour mille personne

par rapport à la moyenne départementale

Pour le rouge vous pourrez rentrer ceci :

Significativement supérieur

à la moyenne départementale

Pour le bleu vous pourrez rentrer ceci :

Significativement inférieur

à la moyenne départementale

Pour le blanc vous pourrez rentrer ceci :

Différence non significative

par rapport à la moyenne départementale

Une fois ça vous n'avez plus qu'à exporter l'atlas en PDF :