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

Le sommaire de droite et la navigation sous le texte vous permettent de vous déplacer dans ce tutoriel.

 

Utilisation cartographique des données de l'INSEE

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)
• Couples - Familles - Ménages (tableau - INSEE)
• Activité des résidents (tableau - INSEE)

À chaque fois au découpage infracommunal (IRIS) et dans leur version les plus récentes.

Utilisez pour cela le site professionnel de l'IGN et le site officiel de l'INSEE.

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.

Vérifiez l'intégrite de votre shape en l'ouvrant sous QGIS.

3) Jointure

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é.

Faîtes quelques thématiques cartographiques par IRIS.

Synthèse puis utilisation cartographique de données clients

Note personnelle année prochaine : refaire export pour 0 absent dans relation, présence de plusieurs congrès obligeant filtres

Nous disposons cette fois de données brutes sur des participants à un salon de professionnels du domaine médical, à Monaco. 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.

Calculons les nombres de clients et chiffres d’affaire par pays.

Le fichier export_commandes liste les commandes effectuées à ce salon. Certains identifiants de clients sont donc en doublons (plusieurs commandes, plusieurs congrès...), et certains montants à 0 euros (invités ou autres).

Le fichier export_soft stocke les information sur tous les clients, dont certains n’étaient pas présents à cet événement.

1) Importer les fichiers Excel dans une base Access.

2) Requêtes de regroupement avec relation sur les deux tables, créez 3 requêtes :

• Requête ca_customers pour lister les clients présents, 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 présents, compter les clients par pays et sommer les CA par pays.

• Une 3ème requête nommée ca_customers_fr pour lister les départements français des clients présents (directement dans la requête, avec une extraction de chaîne), compter les clients par département 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) Ouvrez sous QGIS le fichier/requête ca_country.

4) Liez la requête avec le shape country et faites quelques thématiques sur les clients et CA par pays.

5) Importez dans QGIS le fichier/requête ca_customs_fr.

6) À partir du shape des IRIS, créer un second shape des départements français.

• 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 : le département du Nord semble avoir des erreurs de géométrie (version 2016 de la base Contours...Iris®). Pour avoir une couche complète des départements, vous allez devoir valider les géométries ;  dissoudre sur les géométries valides séparément ; puis fusionner vos 2 shapes péalablement dissous.

7) Importez et liez votre requête ca_customers_fr à votre nouveau shape.

Faîtes 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.

Calcul d'un indicateur de clientèle potentielle

Nous allons créer un indicateur de clientèle potentielle, en partant du principe que les clients potentiels de TelAndCom (chaîne de boutiques de vente de téléphones mobiles et forfaits) sont principalement des actifs, de -40 ans, des familles avec peu d’enfants et des CSP+.

Nous allons procéder en 2 étapes :

• Rapatriement et/ou calcul des variables prises en compte
• Calcul de l’indicateur par IRIS

1) Commencez par ramener toutes les données attributaires fournies dans une seule et même table nommée indicateur dans une base Access :

• Créez des champs dans la table et faîtes des requêtes de mise à jour
• Renommez les champs de la table indicateur si vous subissez un bug d’Access

2) Calcul de la 1^ère variable :

• Créez un champ fam_1max, sommant les ménages avec 0 ou 1 enfant.
• Calculez le pourcentage de ce champ avec le nombre total de ménages, dans un champ nommé var_fam.

3) Calcul de la 2^nd variable var_actif :

• Calculer le pourcentage d’actifs par rapport à la population.

4) Calcul de la 3^ème variable var_age :

• Calculer le pourcentage de population de 15 à 54 ans.

5) Calcul de la 4^ème variable var_csp :

• Calculer le pourcentage de cadres et de professions intermédiaires.

6) Calculer un indicateur prenant en compte nos 4 variables tout en minorant la variable var_fam et en majorant la variable var_csp.

Liez vos résultats au shape des IRIS et faîtes quelques thématiques.

7) Question : Imaginez que vous souhaitiez enrichir votre indicateur d'une variable prenant en compte le nombre d'hypermarchés par IRIS.

• Argumentez ce nouveau besoin.
• Expliquez comment vous allez calculer la variable nécessaire, et pourquoi.
• Expliquez comment vous allez intégrer la nouvelle variable dans l'indicateur, et pourquoi.

Création de zones d'accessibilité

10/01/2021 : Un exercice plus pertinent et plus moderne se trouve ICI.

Nous allons créer des zones d’accessibilité d’environ 7^kms autour de chaque magasin (zones isodistantes + IRIS accélérants).

L’objectif est d’obtenir une couche de ces zones, contenant des polygones IRIS liés aux magasins, certains de ces IRIS pouvant être en double dans le cas des magasins rapprochés.

Des requêtes spatiales ne suffisent donc pas, il faut passer par des jointures spatiales, afn de conserver les doublons et de ramener l’identifiant des magasins dans les IRIS concernés.

Ce type de couche précalculée, illisible en soi puisque les zones se superposeront, pourra ensuite être insérée dans un programme ou une application web, et afficher la zone de chaque magasin sur simple sélection.

De même pour les cartes où les zones d’accessibilité de chaque magasin pourront provenir de la même couche.

1) Ouverture des 5 shapes originaux. Uniquement les autoroutes des routes (ajouter un filtre d'entités sur la couche, via le champ VOCATION, regardez les valeurs uniques avant).

2) Création des zones isodistantes (shape nommé isodistances) via une capture des IRIS de 6 kms autour des magasins.

Vous pouvez tester les différents prédicats géométriques de l'outil Vecteur/Gestion des données/Joindre par localisation. Mais passez ensuite par une zone tampon de 6^kms, afin de vérifier votre accroche.

Observez que la création de zone tampon ramène également les informations attributaires de magasins, une jointure spatiale a donc été faîte dans la foulée.

Vérifications :

• Zoomez sur certaines zones pour vérifier la bonne accroche.
• Sélectionnez un IRIS de la couche créée qui est proche de plusieurs magasins, et regardez dans la table attributaire si les doublons ont été conservés ou pas, vérifiez également que le champ code_magasin est bien présent.

Mais les isodistances ne tiennent pas compte des voies de communication. Nous allons artificiellement les prendre en compte, en ajoutant les IRIS intersectant les voies de communication et les zones isodistantes existantes. Cela va passer par un peu de bricolage et de jonglage avec les données géographiques.

3) Créer un shape nommé voies_communication fusionnant les autoroutes et les chemins de fer (Outil Union).

4) Sélection puis création d’un calque nommé iris_selection contenant tous les IRIS bordant les voies de communication (distance de recherche de 500 mètres).

5) Suppression des IRIS en commun avec les zones isodistantes.

6) Jointure spatiale du calque créé avec les magasins : distance de recherche de 8 kms pour accrocher les IRIS, dans un shape nommé ajout.

Surveillez et au besoin supprimer les champs, afin d’avoir une structure similaire au shape isodistances.

7) Fusionnez les shapes ajout et isodistances dans un shape nommé accessibilites (Outil Fusion de vecteurs, pour cumuler les champs correctement).

9) Joignez votre indicateur de clientèle potentielle à vos zones d'accessibilité et faites quelques thématiques.

10) Critiquez la méthode.

---

20/01/2019 : la couche Contours...Iris® semble avoir des erreurs de géométrie, et pas d'index spatial. Cela ralentit les traitements, corrigez cela :

• Validation des géométries.
• Évictions ou fusion des géométries invalides.
• Ajout d'un index spatial.

Toutefois la couche des 49 000 IRIS reste lourde. L'index spatial stabilise les traitements, théoriquement, mais n'accélère pas forcément leur vitesse :

• J'ai mis 18 minutes à intersecter les 12 592 IRIS compris dans une zone tampon de 5kms autours des 241 magasins, en une seule requête avec le gestionnaire de base de données de QGIS 3.4.3, sur un ordinateur peu performant.
• La même requête sur la même machine a duré 23 minutes sur PgAdmin3/Postgres 9.4.

Vous pouvez allez plus loin en simplifiant les géométries, avec une distance de Douglas-Peucker de 200 mètres par exemple. Vous allez perdre en précision mais gagner un peu de temps de traitement :

• 15 minutes et 50 secondes, la même requête sur la même machine, avec le gestionnaire de base de données de QGIS 3.4.3.
• 22 minutes et 66 secondes, la même requête sur la même machine, avec PgAdmin3/Postgres 9.4.

Il existe d'autres manière d'optimiser des couches sous QGIS (à ce lien par exemple). On peut sans doute aller plus loin en supprimant des champs de la couche IRIS, en testant d'autres types d'index...Mais finalement pour les besoins de l'exercice, mieux vaut travailler avec la précision originelle mais uniquement sur les IRIS, magasins et autres couches impliquées dans la zone francilienne par exemple (ou dans votre région préférée) :

• 27 secondes, la même requête sur la même machine, avec le gestionnaire de base de données de QGIS 3.4.3, uniquement sur les entités franciliennes.
• 27 secondes également, la même requête sur la même machine, avec PgAdmin3/Postgres 9.4, uniquement sur les entités franciliennes.

Création de zone d'accessibilité en SQL spatial

Nos zones d'accessibilité créées précédemment ont plusieurs problèmes :

• Pour avoir une accroche correcte de nos zones isodistantes, nous avons dû passer par des zones tampons intermédiaires (long et pénible...)
• Nos doublons n'ont pas été conservés (#QuidDeLaCannibalisation, impossibilité de sélectionner des zones complètes)
• D'autres manipulations ont été nécessaires pour ramener les IRIS bordant les voies de communications (#PerteDeTemps, #RisquedErreurs)

Nous pouvons sans doute faire mieux en utilisant les Virtal Layers de QGIS et le SQL spatial à notre disposition : outils Bases de données/Gestionnaire de bases de données/Virtal Layers/QGIS. Le 2^nd bouton ouvre un un gestionnaire SQL. Vous pouvez enregistrer vos requêtes et charger des couches géographiques à partir de vos futures sélections.

Pour les besoins de la démonstration, nous procéderons par étapes, mais ces étapes peuvent être cumulées dans une seule requête ou un seul script.

De même pour les besoins de compréhension de la démonstration, et pour analyser visuellement les résultats plus facilement, nous ne travaillerons que sur les données Île-de-France.

1) Requête de sélection attributaire pour ne conserver que les IRIS d'Île-de-France. Cela accélèrera vos traitements. Sélection spatiale ou en SQL par exemple :

Select * from iris where dep in (91,92,93,94,95,75,77,78) ;

Exportez-en un shape sur lequel vous travaillerez. De même pour vos magasins, routes et foies ferrées.

2) Commencez par tester l'accroche d'IRIS par intersection (les IRIS abritant les magasins) :

select iris.INSEE_COM, iris.NOM_COM, iris.CODE_IRIS, iris.NOM_IRIS, iris.dep, iris.geometry
from iris, magasins
WHERE ST_INTERSECTS(iris.geometry,magasins.geometry)

3) Testez la sélection d'un buffer en SQL (autour des magasins, distance de recherche de 5^kms) :

select magasins.code_magas, ST_BUFFER(magasins.geometry,5000)
from magasins

4) Intégrez votre buffer directement dans votre intersection, afin d'accrocher vos IRIS autour des magasins (distance de recherche de 5^kms).

select iris.INSEE_COM, iris.NOM_COM, iris.CODE_IRIS, iris.NOM_IRIS, iris.dep, mon_tampon.code_magas, iris.geometry
from iris, (select magasins.code_magas, ST_BUFFER(magasins.geometry,5000) as ma_geometry from magasins) as mon_tampon
WHERE ST_INTERSECTS(iris.geometry,mon_tampon.ma_geometry)

Quelques modifications dans votre buffer seront nécessaires, ramenez également le code des magasins.

Comparez avec vos 1^ères zones isodistantes créés via les zones tampons, et observez une montée en précision. Vérifiez la bonne conservation des doublons.

Exportez-en un shape nommé isodistances_shp .

5). Sélectionnez maintenant vos autoroutes et vos chemins de fer en une seule requête :

select routes.VOCATION, routes.geometry from routes
where routes.VOCATION = 'Type autoroutier'
union select 'Voies ferrées', fer.geometry from fer

Exportez-en un shape nommé voies_distinctes_shp.

6) Sélectionnez maintenant les IRIS routiers (ceux bordant les voies de communications distinguées, un certain temps de traitement est nécessaire, 2 à 4 minutes) :

select idf.INSEE_COM, idf.NOM_COM, idf.CODE_IRIS, idf.NOM_IRIS, idf.dep, idf.geometry
from idf, voies_distinctes_shp
WHERE ST_INTERSECTS(idf.geometry,voies_distinctes_shp.geometry)

Exportez-en un shape nommé iris_routiers_shp.

7) Évincez maintenant les IRIS communs entre les zones isodistantes et les IRIS routiers, en sélectionnant les IRIS routiers qui ne sont pas communs entre eux.

select iris_routiers_shp.INSEE_COM, iris_routiers_shp.NOM_COM,
iris_routiers_shp.CODE_IRIS, iris_routiers_shp.NOM_IRIS,
iris_routiers_shp.dep,
iris_routiers_shp.geometry
from iris_routiers_shp

where iris_routiers_shp.CODE_IRIS NOT IN
(select isodistances_shp.CODE_IRIS from isodistances_shp) ;

Exportez-en un shape nommé iris_routiers_eviction_shp.

8) Joignez les magasins et les IRIS routiers évincés (distance de recherche 7^kms pour commencer).

Nous pourrions certes poursuivre en utilisant une requête de frontères communes entre les zones isodistantes et les IRIS routiers, mais nous devons conserver le code magasins, encore inexistant chez les IRIS routiers. Une jointure est donc nécessaire.

select iris_routiers_eviction_shp.INSEE_COM, iris_routiers_eviction_shp.NOM_COM, iris_routiers_eviction_shp.CODE_IRIS, iris_routiers_eviction_shp.NOM_IRIS, iris_routiers_eviction_shp.dep, mon_tampon.code_magas, iris_routiers_eviction_shp.geometry
from iris_routiers_eviction_shp, (select magasins_idf.code_magas, ST_BUFFER(magasins_idf.geometry,8500) as ma_geometry from magasins_idf) as mon_tampon
WHERE ST_INTERSECTS(iris_routiers_eviction_shp.geometry,mon_tampon.ma_geometry)

Ajustez la distance de recherche visuellement afin d'obtenir un résultat satisfaisant.

Exportez-en un shape nommé iris_routier_eviction_join_shp.

9) Unissez vos zones isodistantes originelles et vos IRIS routiers déjà joints aux magasins.

select iris_routier_eviction_join_shp.INSEE_COM, iris_routier_eviction_join_shp.NOM_COM, iris_routier_eviction_join_shp.CODE_IRIS, iris_routier_eviction_join_shp.NOM_IRIS, iris_routier_eviction_join_shp.dep, iris_routier_eviction_join_shp.code_magas, iris_routier_eviction_join_shp.geometry
from iris_routier_eviction_join_shp

union select isodistances_shp.INSEE_COM, isodistances_shp.NOM_COM, isodistances_shp.CODE_IRIS, isodistances_shp.NOM_IRIS, isodistances_shp.dep, isodistances_shp.code_magas, isodistances_shp.geometry
from isodistances_shp

Exportez-en un shape.

10) Requête de comptage et listage des magasins cannibales par IRIS.

select NOM_IRIS, count(code_magas) as nbre_mag, GROUP_CONCAT(code_magas) as magasins, geometry
from iris_acces_shp
group by CODE_IRIS
having nbre_mag <> 1
order by nbre_mag desc

11) Sélection de zone d'accessibilité individuelle (pour 1 magasin).

Ce dernier type de sélection est très simpliste, mais dans le cadre d'une application web intégrant les zones d'accessibilité, c'est ce type de sélection par exemple, que déclenchera l'application sur une demande utilisateur (un clic en fin de formulaire : l'utilisateur pose un point sur une carte, ou choisit un magasin, puis lance le calcul. Ensuite le programme attrape le magasin le plus proche de ce point, puis affiche la zone d'accessibilité lié à ce magasin).

Utilisation des graphes routiers

06/02/2020: l'extension Graphes routiers semble indisponible sur QGIS 3.8.

Afin de comprendre la difficulté à créer de vraies zones isochrones, un exercice basé sur l'utilisation de graphes routiers permet de comprendre la diversité des paramètres à prendre en compte.

Afin d'accélérer les traitements, travaillez sur des portions de vos couches issues de votre région préférée.

1) Installez l'Extension de graphes routiers.

2) Cumulez vos tronçons routiers et vos chemins de fer en conservant les champs nous permettant de définir des vitesses.

select 'Chemin de fer ' as type, NATURE as genre, geometry
from fer
union select 'Routes' as xx , VOCATION, geometry
from routes

Si besoin supprimez avant cela les champs problématiques et corrigez le champ vocation avec la calculatrice de champ.

CASE WHEN "VOCATION" = 'Bretelle' THEN 'Bretelle'
WHEN "VOCATION" = 'Liaison locale' THEN 'Liaison locale'
WHEN "VOCATION" = 'Liaison principale' THEN 'Liaison principale'
WHEN "VOCATION" = 'Liaison r?gionale' THEN 'Liaison régionale'
WHEN "VOCATION" = 'Type autoroutier' THEN 'Type autoroutier'
END

Créez un shape nommé voies_vitesse.

3) Utilisez l'extension Group Stats pour faire un résumé statistique de comptage de vos types de voies par genre, afin de visualiser clairement les valeurs avant de leur attribuer une vitesse.

 

4) Utilisez la calculatrice de champs afin de créer un nouveau champ vitesse attribuant des vitesse en fonction du champ genre.

CASE WHEN "genre" = 'Bretelle' THEN '40'
WHEN "genre" = 'Liaison locale' THEN '60'
WHEN "genre" = 'Liaison principale' THEN '80'
WHEN "genre" = 'Liaison régionale' THEN '70'
WHEN "genre" = 'TGV' THEN '150'
WHEN "genre" = 'Type autoroutier' THEN '110'
WHEN "genre" = 'Voie normale' THEN '60'
END

5) Cartographier vos voies de communication en fonction de leur vitesse (par couleurs et épaisseur de trait), et étiquettez-les.

 

6) Paramétrez l'extension Graphes routiers avec votre nouveau champ vitesse, et lancez un calcul en utilisant le critère de Temps.

7) Enlevez votre champ vitesse des paramètres, et relancez le même calcul. Que constatez-vous ?

Création de zones d'accessibilité basées sur les données OpenStreetMap

10/01/2021 : Un exercice plus pertinent et plus moderne se trouve ICI.

06/02/2020: Préférez l'extension ORS Tools, disponible sous QGIS 3.8.

16/01/2019: l'extension OSM Tools semble être obsolète sur QGIS 2.18. Positivement testée sous QGIS 3.4.3.

Maintenant que vous visualisez l'intégration globale des graphes routiers, nous allons utiliser l'extension OSM Tools pour créer des zones d'accessibilité réelles, c'est-à-dire basées sur des temps de trajet.

1) Installez l'extension OSM Tools via le gestionnaire d'extension de QGIS.

L'extension nécessite une clé d'API, vous pouvez en obtenir une gratuitement sur le site officiel (lien disponible dans l'interface de l'extension). La clé gratuite ne vous permettra que de lancer des calculs point par point (un par un). Son utilisation nécessite également d'être en projection 4326.

2) Lancez un calcul de création de zone d'accessibilité en voiture (driving-car) à 25 minutes à partir d'un point (d'un magasin).

3) Requête spatiale pour intersecter vos IRIS avec la zone d'accès de 25 minutes.

Exporter la sélection shape puis joignez-le à vos données INSEE de population.

4) Utilisez l'extension GroupStat pour calculer la population totale au dessus de 15 ans dans les IRIS intersectés.

5) Faîtes une thématique en cercles proportionnels sur la population par IRIS dans la zone d'accessibilité.

Extraction de données géographiques et qualification

Approffondissez les possibilités des SIG en matière de géomarketing dans cet article

Liens externes

• 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