Mots-clefs : géomarketing, IRIS, INSEE, IGN, OSM, SIG, BDD, traitement de données, SQL, zones de chalandise, indicateur, requête de regroupement, zones isochrones, Python
Enseignants responsables : Georges Hinot
Pré-requis : des notions sur QGIS ou autres SIG, des notions sur Excel ou autres tableurs.
Compétences visées : maîtrise théorique et technique des notions de base du géomarketing (zones isochrones, zones de chalandise, indicateurs, jointures et requêtes de regroupement).
Enjeux du cours : rendre autonome les étudiants sur la recherche et l’usage de données autour de problématiques géomarketing, faire « parler » la donnée, créer des indicateurs, veiller à la pertinence des données représentées.
Programme pédagogique : mise en situation autour d'une commande géomarketing impliquant la récupération, le traitement et la représentation de données. Selon l'avancée du cours, nous irons jusqu'à utiliser des fonctionnalités avancées de QGIS (SQL spatial) et Python (création de zones isochrones via une API et customisation du comportement de QGIS).
À savoir :
- Le sommaire de droite et la navigation sous le texte vous permettent de vous déplacer dans ce tutoriel.
- Des captures d'écran peuvent être obsolètes par rapport aux sites actuels INSEE/IGN/... Nous verrons ensemble si les sites ont changé.
- Certaines pièces jointes sont présentes uniquement en cas de souci de téléchargement de données plus récentes, elles peuvent donc être obsolètes ou anciennes, mais à chaque fois que c'est possible nous téléchargerons les dernières données.
Utilisation cartographique des données de INSEE/IGN
L'INSEE fournit à différents échelons des données statistiques dont certaines intéressent particulièrement le géomarketing. À des fins de mises-à-jour entre autre, les données cartographiques sont séparées des données de recensement. Il nous faut les fusionner pour pouvoir les utiliser dans les cartographies.
Nous allons ici rapatrier quelques données statistiques de l'INSEE sur un shape des IRIS.
1) Téléchargement des fichiers
Téléchargez les 4 bases :
- Contours...Iris® (shape - IGN)
- Nombre d'équipements et de services dans le domaine du commerce (tableau - INSEE) (il semble qu'aujourd'hui, 2024-2025, elle n'existe plus. La BPE, Base Permanente des Équipements semble la remplacer et avec de bien meilleures MAJ (quoique j'ai relevé des incohérences, l'ajout d'équipements semble propre, mais pas le retrait). Téléchargez-la mais en mode dénombrement, chiffres détaillés, par IRIS et en CSV).
- Couples - Familles - Ménages (tableau - INSEE), en mode chiffres détaillés, par IRIS et au format Excel.
- Activité des résidents (tableau - INSEE), en mode chiffres détaillés, et par IRIS et au format Excel.
À chaque fois au découpage infracommunal (IRIS) et dans leur version la plus récente.
Utilisez pour cela le site Géoservice de l'IGN et le site officiel de l'INSEE (et notamment leur catalogue de données).
2) Nettoyage des fichiers
Pour chaque fichier tableau Excel de l'INSEE, repérez l’onglet contenant les données, libérez les volets si besoin et supprimez les lignes de description du haut.
Conservez une trace des métadonnées.
Pour la BPE, il faudra explorer les métas pour récupérer les codes concernant le commerce (B) et faire une requête de regroupement pour avoir le nombre d'équipements pour chaque IRIS unique. Nous utiliserons Access pour cela.
Vous pouvez aussi faire une 1ère jointure sur les métas de la BPE afin d'afficher les libellés détaillés des classifications disponibles dans une autre requête, ça peut donner des idées...
Vérifiez l'intégrite de votre shape des IRIS en l'ouvrant sous QGIS. Ajoutez-lui un index spatial.
3) Jointures avec le shape des IRIS
Les données INSEE sont ici des données synthétiques par IRIS.
Ouvrez-les sous QGIS, après exploration des données, modifiez l'encodage si besoin.
Liez, sous QGIS, les données attributaires aux données vectorielles.
Ne ramenez pas tous les champs, mais seulement :
- Le nombre d'hypermarchés
- La population de 15 à 64 ans
- Les champs des nombres de ménages en couple sans enfant et de ménages en couple avec enfant(s)
Et personnalisez les préfixes de champs joints, pour plus de lisibilité.
Faites quelques thématiques cartographiques par IRIS.
Synthèse puis utilisation cartographique de données clients
Note personnelle : un fichier SQL pour filtrer/mettre à jour/exporter les données est présent en PJ (_PourExporterDonnées.sql).
Nous disposons cette fois de données brutes sur des participants à un salon de professionnels du domaine médical. Pour des raisons analytiques ou prédictives, il est intéressant de connaître la typologie de ces clients (sa cible, target), et leur comportement (nous parlons parfois de personas, les grands profils clients d'une activité).
Les clients s'inscrivent sur place ou à l'avance dans une application. Il peuvent déjà avoir un profil dans l'application et participer à d'autres congrès. Ils peuvent aussi payer plusieurs entrées à un même congrès.
Une base SQL est derrière et pour des raisons applicatives tout-à-fait standards, les données vous sont accessibles uniquement sous la forme de tableaux occurentiels (des tables SQL). Ici la table des profils des clients et la table des commandes effectuées.
Pour les utiliser dans des thématiques cartographiques, il nous faut d'abord les synthétiser (calculer les chiffres d’affaire par pays, départements, spécialités...).
Le fichier soft_orders liste les commandes effectuées aux congrès. Le congrès qui nous intéresse (AMWC 2024), mais aussi d'autres qui ne nous intéressent pas. Certains identifiants de clients sont donc en doublons (plusieurs commandes, plusieurs congrès...) et ces doublons sont souhaitables. Certains montants sont à 0 euros (invités ou autres) et c'est normal.
Le fichier soft_customers stocke les information sur tous les clients, dont certains n’ont pas été facturé pour cet événement. Nous avons aussi des informations de localisation (liées à leurs adresses), possiblement redondantes, et donc non-souhaitables selon l'usage que l'on souhaite en faire.
1) Importer les fichiers Excel dans une base Access
Il faudra poser une clé primaire sur l'id_customer, et donc peut-être dédoublonner les customers.
Clé primaire également sur id_order.
Il faudra gérer les types de champs dans les deux tables en fonction de l'usage que l'on souhaite en faire.
2) Requêtes de regroupement et jointures
Nous allons calculer les chiffres d'affaire (CA). Créez 3 requêtes :
- Requête ca_customers pour lister les clients facturés, sommer leurs montants en euros, compter le nombre de commandes et ramener les informations géographiques. Nommez correctement chaque champ directement dans la requête et organiser l'ordre des champs logiquement.
- Requête ca_country pour lister les pays d’origine des clients facturés, compter les clients par pays, leurs commandes, et sommer les CA par pays. Formater les pays au format title/proper case (première lettre de chaque mot en majuscule, le reste en minuscule).
- Requête nommée ca_france pour lister les départements français des clients facturés (directement dans la requête, avec une extraction de chaîne), compter les clients par département, leurs commandes, et sommer les CA par département.
À chaque requête, renommez vos champs de regroupement en gardant le format requête, et vérifiez vos résultats sur un ou deux clients ayant plus d'une commande.
3) Cartographie
Exportez en CSV, séparateur point-virgile et délimité par des guillemets double, la requête ca_country. Ouvrez-le sous QGIS.
Liez-le avec le shape country et faites quelques thématiques sur les clients et CA par pays.
Notes : bien qu'il soit possible de lier directement une base Access avec QGIS, nous ne le ferons pas, car cela n'a que peu d'intérêt. Si vous avez un jour ce type de besoin, vos données seront mieux stockées dans une vraie base SQL.
4) Cartographie avec fusion d'entités
Importez dans QGIS le fichier/requête ca_france.
Exportez en CSV, séparateur tabulation et sans délimiteur, la requête ca_country. Ouvrez-le sous QGIS.
À partir du shape des IRIS, créer un second shape des départements français.
Cela nécessitera la création d’un nouveau champ contenant le code département (là aussi extraction de chaîne : utilisez le champ des codes commune), puis dissolution sur ce champ.
Attention : si des départements ont des erreurs de géométrie, pour avoir une couche complète des départements, vous allez devoir valider les géométries, puis dissoudre sur les géométries valides séparément, puis fusionner vos 2 shapes péalablement dissous.
Importez et liez votre requête ca_france à votre nouveau shape.
Faites quelques thématiques sur le nombre de commandes et leur montant en surface de couleurs et en symboles proportionnels.
Attention : certains polygones sont multiparties (les îles), ce qui fausse certaines cartographies en cercles proportionnelle. Vous allez donc devoir extraire les centroïdes des polygones.
5) SQL spatial
Notez qu'il aurait été possible de générer les géométries de nos départements plus efficacement en SQL spatial !
Ouvrez la fenêtre SQL de l'outil Bases de données/Gestionnaire de bases de données/Couches virtuelles/Couches du projet.
Puis testez cette commande :
SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_UNION(geometry) AS geometry FROM IRIS GROUP BY SUBSTR(INSEE_COM,1,2) ;
Et même directement les centroïdes, sans passer par un export des départements polygonaux :
SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_CENTROID(ST_UNION(geometry)) AS geometry FROM IRIS GROUP BY SUBSTR(INSEE_COM,1,2) ;
Et même, allons-y gaiement, avec une jointure sur la table ca_france ! D'une pierre trois coups.
Attention : pour délimiter les objets de bases de données (tables et champs) contenant des caractères spéciaux, il faudra utiliser des guillemets obliques (AltGroup+7+espace). Remplacez-les au bon endroit dans la requête ci-dessous, sinon elle ne marchera pas.
SELECT dep, 'Nombre de commandes', geometry FROM (SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_CENTROID(ST_UNION(geometry)) AS geometry FROM IRIS GROUP BY SUBSTR(INSEE_COM,1,2)) AS sous_requete LEFT JOIN ca_france ON dep = 'Département' ;
Via une sous-requête car la table ca_france n'a aucune raison de faire l'objet d'une agrégation. Comme quoi on aurait pu importer les tables orders et customers et faire la totale en SQL, sans même passer par la requête ca_customer...
La même requête en partant du principe qu'aucun nom de champ ne contient de caractères spéciaux, et avec une gestion du caractère de séparation des décimaux et des valeurs nulles :
SELECT dep, COALESCE( REPLACE( REPLACE( nombre_de_commandes , ",", ".") , ".00", "") ,0) AS nb_com, geometry FROM ( SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_CENTROID(ST_UNION(geometry)) AS geometry FROM IRIS GROUP BY SUBSTR(INSEE_COM,1,2) ) AS sous_requete LEFT JOIN ca_france ON dep = departement ;
Calcul d'un indicateur de clientèle potentielle
Nous allons créer un indicateur de clientèle potentielle. Admettons que les clients potentiels d'une chaîne de boutiques de vente de smartphones sont principalement des actifs occupés, d'une tranche d'âge intermédiaire, des familles avec peu d’enfants et des CSP+.
L'idée, discutable, étant que les clients potentiels :
- ont des besoins de connectivité moderne (soit des actifs occupés).
- ne sont pas trop jeunes - pour un minimum de moyen - ni trop âgés - pour un minimum d'intérêt à une connectivité moderne (soit une tranche d'âge intermédiaire).
- ont certains moyens financiers (soit des CSP+).
- ont un besoin concrêt de budgétisation pour cette connectivité (des familles, mais peu nombreuses).
Nous utiliserons Access et QGIS et travaillerons sur la base des IRIS (pour la disponibilité et précision des chiffres et des géographies). Nous utiliserons uniquement des données en open-source, avec la base Contours...Iris® de l'IGN, les fichiers Couples - Familles - Ménages et Activité des résidents de l'INSEE et la BPE (Base Permanente des Équipements).
Nous respecterons plusieurs étapes/principes :
- Rapatriement/calcul des variables prises en compte.
- Agrégation/pondération de ces variables.
- Neutralisation/substitution des valeurs aberrantes.
Cette fusion nous donnera un indicateur.
Variables
1) Calcul de la 1ère variable var_fam
- Créez un champ sommant les familles avec 0 ou 1 enfant.
- Calculez le pourcentage de ce champ avec le nombre total de familles, dans un champ nommé var_fam.
Exemple :
var_fam: ROUND((([C21_NE24F0]+[C21_NE24F1])/[C21_FAM])*100;2)
2) Calcul de la 2nd variable var_actif
- Calculer le pourcentage d’actifs occupés par rapport à la population de la même tranche d'âge.
Exemple :
var_actif: ROUND(([P21_ACTOCC15P]/[P21_POP15P])*100;2)
3) Calcul de la 3ème variable var_age
Calculer le pourcentage de population de 25 à 54 ans.
Exemple :
var_age: ROUND(([P21_POP2554]/[C21_PMEN])*100;2)
4) Calcul de la 4ème variable var_csp
Calculer le pourcentage de cadres et de professions intermédiaires.
Exemple :
var_csp: ROUND((([C21_ACT1564_CS3]+[C21_ACT1564_CS4])/[C21_ACT1564])*100;2)
Indicateur
Pour n'oublier aucun IRIS dans les futures cartographies et gérer des valeurs éventuellement vides, commencez par exporter un Excel de vos IRIS cartographiques, qui constituera le socle de nos calculs.
Créez, depuis ces IRIS dans Access, une requête joignant les requêtes calculant les variables avec l'équivalent d'un LEFT JOIN.
Calculez ensuite un indicateur prenant en compte nos 4 variables tout en minorant la variable var_fam et en majorant la variable var_csp.
Exemple :
indicateur: ROUND((([var_fam]*1) + ([var_actif]*2) + ([var_age]*2) + ([var_csp]*3))/8;2)
Neutralisation/substitution
Mais parfois nous avons des valeurs aberrantes ou vides dans les variables. Exemples :
In ('040500000';'060040113';'060040109';'595080104')Nous allons d'abord les neutraliser dans les variables :
var_csp: VraiFaux([C21_ACT1564]<>0;Round((([C21_ACT1564_CS3]+[C21_ACT1564_CS4])/[C21_ACT1564])*100;2);Null)
var_actif: IIf([P21_POP15P]<>0;Round(([P21_ACTOCC15P]/[P21_POP15P])*100;2);null)
var_age: IIf([C21_PMEN]<>0;Round(([P21_POP2554]/[C21_PMEN])*100;2);null)
var_fam: IIf([C21_FAM]<>0;Round((([C21_NE24F0]+[C21_NE24F1])/[C21_FAM])*100;2);null)
Puis les neutraliser dans l'indicateur et substituer le diviseur :
pre_indicateur:
IIF(
ISNULL([var_age]);
(([var_fam]*1)+([var_actif]*2)+([var_csp]*3))/6;
IIF(
ISNULL(
[var_actif]);
(([var_fam]*1)+([var_age]*2)+([var_csp]*3))/6;
IIF(
ISNULL(
[var_csp]
);(([var_fam]*1)+([var_actif]*2)+([var_age]*2))/5;
IIF(
ISNULL(
[var_fam]);
(([var_actif]*2)+([var_age]*2)+([var_csp]*3))/7;
(([var_fam]*1)+([var_actif]*2)+([var_age]*2)+([var_csp]*3))/8
))
))
Notez qu'en utilisant un vrai langage de programmation, comme Python, nous aurions pu substituer les variables directement (par... d'autres variables), ou créer un vrai diviseur dynamique... Mais vous avez compris le principe.
Reste donc ici un problème : nous ne gérons pas les cas ou plusieurs variables sont vides.
Valeurs aberrantes
Restent donc des valeurs de l'indicateur supérieures à 100 ou vides. Nous allons les gérer :
indicateur: IIF(ISNULL([pre_indicateur]);0;IIF([pre_indicateur]>100;100;[pre_indicateur]))
Indicateur augmenté
Nous allons maintenant enrichir notre indicateur d'une variable prenant en compte le nombre de commerce spécialisés en téléphonie par IRIS ! Ceci afin d'éviter la cannibalisation entre magasins.
Utilisez la BPE avec les codes B321 et B322 par exemple, pour compter les magasins spécialisés en électronique/communication par IRIS.
Mais si cela est intéressant, c'est un problème, car quand les variables actuelles tirent l'indicateur vers le haut (on parle de corrélation positive), une telle 5ème variable le tirerait vers le bas (corrélation négative). Il nous faudra donc la prendre en compte de façon plus complexe.
Une possibilité serait de diviser l'indicateur simple par une variable corrélée négativement préalablement divisée par son propre maximum : la division par son maximum amène à 1 la valeur la plus forte, ce qui ne fera pas bouger l'indicateur une fois divisé par 1 ; tandis qu'une division par un chiffre inférieur à 1 augmentera l'indicateur. Autrement dit : une faible variable corrélée négativement augmentera l'indicateur, quand une plus forte l'abaissera, le tour est joué.
Exemple en SQL :
SELECT Indicateur.CODE_IRIS, Indicateur.indicateur, iris_commerce_communication.nombre AS mag_spe, (SELECT MAX(nombre) FROM iris_commerce_communication) AS max_mag_spe, IIF(ISNULL(mag_spe), ROUND(((SELECT MIN(nombre) FROM iris_commerce_communication)/max_mag_spe)/2,5), ROUND(mag_spe/max_mag_spe,5)) AS diviseur, ROUND(indicateur/diviseur,2) AS 'Indicateur augmenté' FROM Indicateur LEFT JOIN iris_commerce_communication ON Indicateur.CODE_IRIS = iris_commerce_communication.iris ;
Attention à un éventuel maximum aberrant dans la variable corrélée négativement, qui risque de fausser les comparaisons. Idéalement ce maximum doit être lissé par rapport à la répartition des valeurs disponibles. Vous pouvez modifier manuellement les maximums aberrants dans les données brutes et rafraîchir votre calcul jusqu'à satisfaction.
Attention au valeur nulles dans la variables corrélée négativement, ici nous prenons soin de leur attribuer une valeur moitié moindre que le minum calculé (divisée par 2), afin d'avoir une logique dans les comparaisons (en forçant l'indicateur à augmenter fortement quand il n'y a aucun magasin spécialisé dans l'IRIS). Mais ceci est exagéré ! Modifiez la valeur de réduction des valeurs nulles afin d'obtenir quelque chose de plus lisse.
Rationalisation
Nous avons maintenant un indice qu'il nous faut ramener entre 0 et 100. Nous utiliserons un quotient de rationalisation que nous obtiendrons en divisant 100 par le maximum de l'indicateur augmenté.
Une fois obtenu, nous multiplions notre indicateur augmenté par ce quotient :
SELECT CODE_IRIS, indicateur, mag_spe, [Indicateur augmenté], 100/(SELECT MAX([Indicateur augmenté]) FROM [Indicateur augmenté]) AS quotient, ROUND([Indicateur augmenté]*quotient,2) AS 'Indicateur rationnalisé' FROM [Indicateur augmenté] ;
Cartographie
Liez vos résultats au shape des IRIS et faites quelques thématiques.
Pour être parfait, nous devrions agrémenter l'indicateur d'une composante géographique, la population complète de l'IRIS, sa taille ou mieux... sa densité.
Vous avez compris le principe, alors à vous de jouer !
Création de zones d'accessibilité en SQL spatial
En guise d'introduction à la création de vraies zones isochrones (un peu de patience !), nous pouvons nous amuser à créer des zones dites d'accessibilité, soit des zones dans lesquelles l'accès à un point donnée est facilité.
Et pour pimenter le tout, au lieu d'utiliser les classiques plugins QGIS, nous allons faire tous les géo-traitements en SQL spatial. L'intérêt est d'être capable de routiniser nos besoins et de ré-utiliser/optimiser nos process. Un autre intérêt, non des moindre, est que le code SQL produit sera standard et valable sur MySQL ou Postgres par exemple.
Allez dans l'onglet Bases de données / Gestionnaire / Couches virtuelles / Couches du projet. Une fois la liste de ce dernier menu déroulé, cliquez sur l'outil Fenêtre SQL, puis :
-- SÉLECTION ÎLE-DE-FRANCE -- PUIS EXPORT EN SHAPE (IRIS_IDF) ET CRÉATION D'UN INDEX SPATIAL SELECT * FROM IRIS WHERE dep IN (91,92,93,94,95,75,77,78) ;
-- SÉLECTION DES IRIS D'IDF INTERSECTANT UN BUFFER 5KMS AUTOUR DES MAGASINS -- PUIS EXPORT EN SHAPE (IRIS_BUFFER) ET CRÉATION D'UN INDEX SPATIAL SELECT DISTINCT IRIS_IDF.CODE_IRIS, IRIS_IDF.geometry FROM IRIS_IDF, ( SELECT Telandcom.code_magas, ST_BUFFER(Telandcom.geometry,5000) AS ma_geometry FROM Telandcom ) AS monBuffer WHERE ST_INTERSECTS(IRIS_IDF.geometry, monBuffer.ma_geometry) ;
-- INTERSECTION ROUTES ET VOIES FERRÉES AVEC ST_UNION SUR IRIS_IDF -- EXPORT EN SHAPE (RoutesEtFer) ET CRÉATION D'UN INDEX SPATIAL SELECT VOCATION, ST_INTERSECTION(monUnion.geometry, unionIrisIdf.geometry) AS geometry FROM ( SELECT TRONCON_ROUTE.VOCATION, TRONCON_ROUTE.geometry FROM TRONCON_ROUTE WHERE TRONCON_ROUTE.VOCATION = 'Type autoroutier' UNION SELECT 'Voies ferrées', TRONCON_VOIE_FERREE.geometry FROM TRONCON_VOIE_FERREE ) AS monUnion, ( SELECT ST_UNION(geometry) AS geometry FROM IRIS_IDF ) AS unionIrisIdf WHERE ST_INTERSECTS(monUnion.geometry, unionIrisIdf.geometry) = 1 ;
-- SÉLECTION DES IRIS_IDF INTERSECTANT RoutesEtFer -- PUIS EXPORT EN SHAPE (IRIS_ACCES) ET CRÉATION D'UN INDEX SPATIAL SELECT DISTINCT IRIS_IDF.CODE_IRIS, IRIS_IDF.geometry FROM IRIS_IDF, RoutesEtFer WHERE ST_INTERSECTS(IRIS_IDF.geometry, RoutesEtFer.geometry) ;
-- ÉVICTION DES IRIS_ACCES DANS IRIS_BUFFER -- SIMPLE TEST POUR L'INSTANT SELECT CODE_IRIS, geometry FROM IRIS_ACCES WHERE CODE_IRIS NOT IN (SELECT CODE_IRIS FROM IRIS_BUFFER) ;
-- UNION IRIS_BUFFER AVEC LES IRIS_ACCES ÉVINCÉS SELECT CODE_IRIS, geometry FROM IRIS_BUFFER UNION SELECT CODE_IRIS, geometry FROM IRIS_ACCES WHERE CODE_IRIS NOT IN (SELECT CODE_IRIS FROM IRIS_BUFFER) ;
Bien, maintenant passons à la création de vraies zones isochrones, et même à leur optimisation avec Python, rendez-vous ici !
Liens externes
- Zones de chalandise avec QGIS, Python, l'API ORS et les données OSM, IGN et INSEE
- Application de création de zones de chalandise
- Application Owlapps sur la création de zones isochrones selon Hyperdrive (OSM) et Sharp (Navteq) - KML exportables
- Application de calcul de zones ischrones en transports en commun (SDIS)
- Géoportail
- FreeMapTools
- GrapHopperMaps
- OpenRouteService (API utilisée par l'extension OSM Tools - Université d'Heildergerg)
- Pgrouting : Analyse comparative d’Isodistances de 500m à partir de Nantes métropole
- Mise en place d’un système de routage et de calcul d’itinéraires intégrable à PostgreSQL/PostGIS : Pgrouting
  Activité des résidents | [Activité des résidents] | 0 Ko |
 BPE | [ ] | 0 Ko |
| country.zip | [Countries] | 0 Ko |
| Couples - Familles - Ménages | [Couples - Familles - Ménages] | 0 Ko |
| IRIS | [ ] | 0 Ko |
| mag_telandcom.zip | [mag_telandcom] | 22 Ko |
 soft_customers | [Clients] | 4591 Ko |
| soft_orders | [Commandes effectuées] | 96 Ko |
| TRONCON_HYDROGRAPHIQUE | [ ] | 0 Ko |
| TRONCON_ROUTE | [ ] | 0 Ko |
| TRONCON_VOIE_FERREE | [ ] | 0 Ko |
 _PourExporterDonnées.sql | [Pour filtrer/mettre à jour/exporter les données] | 1 Ko |