Calcul de la zone de chalandise

Qu’est ce que la zone de chalandise ?

La zone de chalandise d’un atelier correspond à sa zone d’influence. Il s’agit de savoir d’où viennent les adhérer, en calculant la distance qui les sépare de l’atelier. In fine, on découvre la part de ses adhérents qui habitent à moins de 1, 3 et 7 kilomètres de l’atelier.

Pourquoi calculer la zone de chalandise ?

Calculer la zone de chalandise de son atelier permet de saisir l’influence/l’impact de son activité sur un territoire : quelle distance les adhérents sont prêts à réaliser pour venir à l’atelier. Généralement, ce travail démontre la petitesse des distances que l’on peut expliquer par une règle logique : un adhérent ne va pas parcourir 10 km en poussant son vélo pour venir le réparer à l’atelier. Pour aller plus loin :

• il peut être pertinent de faire ce calcul pour appuyer par des chiffres solides votre refus

de déménager à l'autre bout de votre ville (ex : une mairie vous propose un local)

• la mutualisation des résultats peut permettre à de nouveaux ateliers de calculer, en

amont de la création d'un projet, leur « potentiel d'adhérents » en prenant comme base la densité de population de leur territoire et donc d'inclure cette donnée dans leurs dossiers et négociations avec des partenaires

Comment calculer la zone de chalandise ?

Le calcul de la zone de chalandise se synthétise en 3 étapes :

• étape 1 - la récupération du fichier adresse des adhérents et son toilettage (page 3 à 8)
• étape 2 - la génération des coordonnées GPS correspondant aux adresses (pages 8 à 12)
• étape 3 - le calcul des distances à vol d'oiseau entre l'atelier et chacun des adhérents

(pages 12 à 14)

• étape 4 – rapporter les données à la population du territoire (pages 14-15)
• étape 5 – communiquer avec ces données (pages 15)

Les étapes 1 et 2 Aucune connaissance particulière en informatique ne sont nécessaires à leur réalisation. La durée de leur réalisation varie en fonction du nombre d’adhérents : toilettage (20 adhérents / minute) et génération des coordonnées GPS (traitement automatise sur Internet pendant que vous buvez un café ou un galopin : 15 adhérents / minute) Les étapes 3 et 4 Nous utiliserons des formules trigonométrie sphérique et des données INSEE mais une fois de plus, aucune connaissance en amont n'est nécessaire.

la récupération du fichier adresses des adhérents et son toilettage

Récupération du fichier adresses

À partir du fichier informatique renseigné avec la fiche d’adhésion, vous disposez de l’adresse des adhérents.

• Créer un dossier dans lequel vous rangerez tous les éléments de l’analyse.
• Faire une copie complète du fichier adresses dans ce fichier.
• Renommer le fichier « Base de données – zone de chalandise.ods » OU « Base de données –

zone de chalandise.ods ».

• Supprimer les colonnes « nom » et « prénom » des adhérents (l’ensemble de l’analyse et les

résultats doivent être anonymes, par respect de vos adhérents).

• Enregistrer !

Toilettage du fichier adresses

Le fichier « Base de données – zone de chalandise.ods » présentent généralement les éléments suivants :

• adresse postale
• Code postal
• Ville

Si d’autres renseignements (autre que nom et prénom) apparaissent, laissez-les, ils pourraient toujours s’avérer utile à une analyse ultérieure.

Assurez-vous que chaque colonne a un titre explicite à la ligne 1 : si tel n’est pas le cas, insérer une ligne en cliquant avec le bouton droit de la souris sur le chiffre 1 et cliquer sur « Insertion » ; alors remplissez les titres. Le logiciel qui nous permettra de générer les coordonnées GPS n’est pas un être humain doué d’une intelligence inouïe, alors il est nécessaire de lui faciliter la tache : Ajouter une colonne au titre évocateur « Pays » (ligne 1) et renseigner « France » pour tous les adhérents (quelques adhérents habitent à l’étranger, nous nous en occuperons plus tard). Vérifier les codes postaux :

• Si vous travaillez avec Microsoft Office : cliquer avec le bouton droit de la souris sur

la lettre de la colonne (pour nous C), parcourez le menu déroulant et cliquer sur « Format de cellule » ; dans le volet « catégorie : spécial », cliquer sur « code postal » et valider « OK ».

un code postal doit être composé de 5 chiffres. Si un adhérent a mal renseigné le fichier, il est nécessaire de modifier pour lui : « 34 » devient « 34000 », de même que « 3400 » devient « 34000 » ou « 03400 » dépendamment de votre situation géographique. En cas de doute, aidez-vous du nom de la ville. Une fois cette étape finie, enregistrer votre travail ! Vérifier l’écriture des noms de villes

• l’idée est d’enlever les coquilles / les fautes de frappe
• pour simplifier la tâche, nous vous proposons de créer une liste et d’identifier

uniquement les noms écorchés : o Sélectionner toute votre table de données en cliquant sur CTRL et * (sinon, avec le bouton shift) o Créer une liste :

• Dans microsoft Office : cliquer sur le menu « Données », puis

« Liste », puis « Créez une liste ». Sélectionner « ma liste comporte des en-têtes » puis cliquer sur OK.

• Dans OpenOffice : cliquez sur le menu « Données », puis « Filtre »

puis « Autofiltre »

Votre feuille de données comporte alors des petits pictogrammes en forme de flèche sur les cellules de la première ligne : en cliquant dessus, vous faites apparaitre tous les noms qui figurent dans la colonne, mais ceux-ci n’apparaissent qu’une fois. Alors vous visualisez mieux les noms écorchés et pouvez cliquer dessus (ce qui les sélectionne), les modifier, puis recliquer sur la flèche, faire réapparaitre tous les noms et cliquant sur « Tous » puis rechercher un autre nom écorché, et ainsi de suite.

À cette étape, vous pouvez également faire une recherche sur les noms de villes étrangères : alors remplacer « France » de la colonne par le bon pays correspondant. Une fois cette étape finie, enregistrer votre travail ! Vérifier l’écriture des adresses On l’a déjà dit et on commence à comprendre que le logiciel avec lequel on va bosser n’est pas très malin et c’est pas fini. Il ne comprend que les adresses qui sont bien renseignées. Ainsi, il ne pourra vous donner les coordonnées GPS de :

• « chez M. Durand, au 35 rue du Mollet »
• « Appt. 439, résidence 4, rue michel »
• « Avec pierre et sonia, à 2 pas de l’atelier »
• « Bldv. Carnot »

- ... Alors faut nettoyer tout ça pour obtenir (si on reprend les exemples ci-dessus)

• « 35 rue du Mollet »
• « rue michel »
• « [rentrer l’adresse de l’atelier] »
• « Boulevard Carnot »
• …

L’idée est de parcourir la liste des adresses pour faire un fichier propre. Cette étape est d’autant plus longue que le fichier adresse est mal renseigné (ne vous énervez pas !) Problème fréquent (pour le logiciel) […]

RQ : les virgules, les MAJUSCULES n’interfèrent pas (généralement).

RQ2 : pour les champs vides, les laissez comme tel, le logiciel positionnera au centre de la ville

RQ3 : si le numéro et le nom de la voie sont dans 2 cellules différentes, ce n’est pas grave, faites le traitement puis procéder à la jonction des 2 dans une troisième cellule en utilisant la formule CONCATENER (texte1 numéro ; « [VIRGULE][ESPACE] » ; texte2 nom). Cette formule vous permettra d’avoir dans une même cellule : « numéro, rue » et s'écrit donc :

D24=CONCATENER(B24;" , ";C24)

Une fois cette étape finie, enregistrer votre travail ! Ajouter en première ligne de données l'adresse de votre atelier Ajouter une ligne juste après la ligne 1 qui correspond aux titres des colonnes Remplissez manuellement les informations concernant votre atelier (adresse, CP, ville, pays) Créer un identifiant à chaque ligne Ajouter une colonne (le plus à gauche), nommer-la ID Insérer dans la cellule 2A (celle de la première adresse, celle de votre atelier donc) le numéro « 1 » Insérer dans la cellule 3A (celle de la seconde adresse) le numéro « 2 » Sélectionner les 2 cellules et double-cliquer sur le petit carré noir en bas à droite. La formule logique se reproduit sur l’ensemble du tableau : chaque adresse a donc son numéro. Une fois cette étape finie, enregistrer votre travail ! Le fichier sur lequel nous travaillons s’appelle toujours « Base de données – zone de chalandise.ods ». Nous pouvons désormais procéder à la génération des coordonnées GPS.

La génération des coordonnées GPS correspondant aux adresses

Avec Adresse Data Gouv

Cet outil institutionnel est très rapide, sans pub et très facile d'utilisation. (Bravo, c'est pas souvent que le service publique est à la fois efficace, ergonomique et pratique !).

1. Il suffit d'importer un fichier CSV (.csv) qu'on aura préalablement exporté depuis n'importe quel tableur.
2. Un aperçu du fichier est affiché pour vérification.
3. Sélectionnez la ou les colonne servant pour l'adresse, il peut s'agir d'une seule colonne comprennant l'adresse complète comme de plusieurs colonnes (code postale, ville, adresse).
4. Vous lancer le géocodage et quelques secondes après il vous propose de télécharger le fichier CSV contenant les adresses GPS qui aura calculé.
5. Ouvrez le fichier CSV avec votre tableur habituel, redélimité les colonnes si besoin (délimiteur "," virgule), puis enregistrez le sous le format voulu.

Note :

• pensez à supprimer les "," contenues dans votre fichier avant de le transformer en CSV, afin qu'elles ne soient pas prises pour des changements de colonnes.
• pour les adresses qui ne contiendraient que la ville, vérifier que les coordonnées données vous conviennent. Il peut parfois s'agir d'un rond-point, d'un lieu éloigné ou proche de votre atelier, hors ou dans un QPV, etc., ce qui pourrait influer sur votre calcul ou votre analyse par la suite. A vosu de voir si vous les gardez ou si vous ls écartez pour le calcul
• cet outil garde éventuellement en mémoire uniquement la colonne des adresses, aucune autre de votre fichier CSV

Avec Batch Géocodeur

Pour réaliser cette étape, nous utiliserons un logiciel en ligne qui s’appelle Batch Géocodeur. Alors rendez-vous sur le site http://dehaese.free.fr/Gmaps/testGeocoder.htm (car http://www.findlatitudeandlongitude.com/batch-geocode/ ne fonctionne plus).

Il se présente ainsi : Import des données Excel dans le logiciel Sélectionner (Ctrl+A) puis copier (Ctrl+C) les données suivantes de votre tableur EN PRENANT EGALEMENT LA COLONNE AVEC LES TITRES, souvent colonne 1:

• colonne ID
• colonne adresse
• colonne Code Postal
• colonne Ville
• colonne Pays

(vous pouvez ajouter une colonne supplémentaire, mais nous ferons le croisement entre les données issues de votre fichier de base et les coordonnées GPS issues du logiciel à la fin de la manipulation)

Dans BatchGeocodeur,

• cliquer dans le tableau du haut de la page,
• supprimer toutes les informations,
• coller vos données (Ctrl+V), une ligne vide apparait à la suite de vos données, supprimer-la en effectuant un retour chariot (touche retour  ).
• Appuyer sur valider à l’étape suivante : le logiciel vérifie vos données

Choix des champs d’adressage

Une fois cette étape passée, choisissez les champs d’adressage (étape 3) :

• indiquer le nom des colonnes de votre tableau en face des champs recherchés par le logiciel
• indiquer le nom des colonnes de votre tableau en face des champs recherchés par le logiciel Geocodage

Une fois cette étape passée, il est temps de géocoder (étape 4) : cliquer sur le bouton correspondant (et de vous faire un café, cette opération peut prendre un certain temps en fonction de votre connexion Internet et du nombre d’adhérents : certains y ont déjà passé des heures...ne perdez pas patience ! ... c’est très variable, mais vous pouvez compter 2 secondes par ligne).

Le tableau en bas de page se remplit progressivement : n’y toucher pas avant la fin de l’opération.

L’opération est finie lorsque le logiciel indique « traitement : nombre total / nombre total »

On récupère alors les données pour les insérer dans le tableau Excel :

• 1 clic gauche de la souris dans le tableau (du bas, nouvellement renseigné)
• Sélectionner toutes les données : maintenez enfoncé la touche Ctrl et cliquer sur la
• lettre A
• Copier les données (Ctrl+C ou clic droit de la souris puis copier)

Retournez dans votre fichier Excel :

• positionner vous sur une nouvelle feuille (en bas à gauche)
• coller les données

Une fois cette étape finie, enregistrer votre travail !

Liaison avec les données de base (optionnelle)

Si vous aviez davantage de données dans votre fichier de base et que vous souhaitez croiser ultérieurement le lieu d’habitation de vos adhérents avec certaines données (âge, sexe, type de cotisation, ...), il est nécessaire d’ajouter l’étape suivante (autrement, passez directement à l’étape 3 ou 4 sur le traitement des données).

Nous allons intégrer à votre fichier de base les coordonnées GPS issues du logiciel :

• partons des mêmes éléments : dans la feuille 1 se trouvent les colonnes ID, adresse, code postal, ville, pays et toutes les autres annexes (sexe, age, ...) ; dans la feuille 2 se trouvent les colonnes ID, adresse, code postal, ville, pays, Lat et Lng.
• Nous allons utiliser la colonne ID pour faire le lien entre les deux plages de données, grâce à la formule RECHERCHEV

10Feuille 1 : données de base du fichier adhérents

Feuille 2 : données simplifiées avec les

colonnes Lat et Lng (coordonnées GPS)

Pour faire le lien, rien de plus simple (enfin presque) :

• dans la feuille 1, sur la ligne 1, indiquer Lat et Lng dans la 1ère et 2nde colonnes vides à droite
• dans la ligne 2 de la colonne Lat : écrivez « =RECHERCHEV(A2;Feuil2! A1:G17;6;FAUX) »
  • o A2 : la cellule où se trouve l’ID de la ligne sur laquelle nous travaillons
  • o Feuil2!A1:G17 : toutes les données de la feuille 2
  • o 6 : le numéro de la colonne « Lat » dans la feuille 2
  • o FAUX : c’est comme ça
  • o Séparation de tous ces champs avec un [POINT-VIRGULE]

• Pour remplir toute votre tableau, dupliquer la formule vers le bas, en double

cliquant sur le carré noir en bas à droite de la cellule où se trouve la formule : toutes les cellules doivent avoir leur latitude (vous pouvez en vérifier 2 ou 3, histoire d’être sur que vous êtes devenus un vrai expert d’Excel)

• faire la même opération pour remplir la colonne « Lng » de la feuille 1 : tout est

identique, sauf que la colonne Lng dans la feuille 2 sera désormais la 7ème donc « =RECHERCHEV(A2;Feuil2!A1 :G17;7;FAUX) » Une fois cette étape finie, enregistrer votre travail ! Et voilà notre fichier Tableur prêt à être analysé pour le calcul des distances par rapport à l’atelier.

le calcul des distances à vol d'oiseau entre l'atelier et chacun des adhérent.e.s

Nous allons expliquer la manip en prenant comme base ce fichier :

• Ligne 1 : les titres
• Ligne 2 : les infos de mon atelier et donc dans la cellule B2 l'adresse de mon atelier, dans la cellule F2 sa latitude, G2 sa longitude
• Ligne 3 et suivantes : les adhérent.e.s Colonnes L,M,N libres

Insérer la formule de calcul des distances

La formule de Célestion est (PAS DE PANIQUE) : Distance entre a et b = avec R le rayon de la terre a = latA b = latB c = lonA d = lonB

12Célestion, malin, parle en radians.. Alors que notre logiciel nous a donné des latitudes et des longitudes en degrés.

Pour faire la conversion radian-degrés :

• sélectionner toutes les données des colonnes F et G, soit Latitudes et Longitudes
• Appuyer sur CTRL et la lettre F de votre clavier en même temps → s'ouvre une fenêtre « rechercher et remplacer ».
• dans le champ de recherche, indiquer un point « . » et dans le champ de remplacement une virgule « , » puis appuyer sur « Tout remplacer »
• les latitudes et longitudes sont devenues des chiffres
• désormais en L2 écrire « =RADIANS(F2) »
• écrire en M2 « =RADIANS(G2)
• reportez les formules jusqu'à la dernière adresse de votre feuille (clic enfoncé sur le petit carré noir de la cellule L2 ou M2)

Désormais il ne reste plus qu'à insérer la formule de distance entre les 2 points en N2 : coller =6367445*ACOS(SIN($L$2)*SIN(L2)+COS($L$2)*COS(L2)*COS($M$2-M2))

La valeur qui s'affiche en N2 est égale à 0 → l'atelier est situé à 0 mètre de l'atelier !

Reportez la formule jusqu'à la dernière adresse de votre feuille (clic enfoncé sur le petit carré noir de la cellule N2).

Les distances sont calculées en mètres.

Sélectionner enfin toutes la colonne N (titre+données) puis

• cliquez sur « données »
• « tri »
• « tri croissant »
• « étendre la sélection »

Les adhérent.e.s sont désormais classés du plus proche au plus lointain.

En sélectionnant les cellules qui vous intéressent (moins de 1000 mètres, de 1000 à 2000m...), il est indiqué en bas de page le nombre de personnes présentes

Dans notre cas : 15 personnes habitent à moins de 1km de l'atelier à vol d'oiseau (on exclut la première ligne de réponse car elle correspond à l'atelier lui-même), 8 entre 1 et 2km, etc.. et on obtient rapidement un tableau de ce type : moins de 1km 15 39,47% 1-2km 8 21,05% 2-3km 5 13,16% 3-5km 4 10,53% plus de 5 km 6 15,79% 38 100,00% ETAPE 3 FINIE !!!

Rapporter les données à la population de notre territoire

On va faire vite :

• trouvez la données INSEE concernant la densité de population de votre ville (ex sur Wikipedia)
• la densité de population s'exprime généralement en habitant par km2

14Combien y-a-t-il d'habitants dans un rayon de 1 km si la population de ma ville est de 1000 habitants par km2 ?

Nombre d'habitants = (Pi * R2) * densité

Nombre d'habitants = (3,141592653589793 * 12) * 1000 = 3142 habitants

Et après on continue le raisonnement

moins de 1km 15 39,47% adhérents 1-2km 8 21,05% 2-3km 5 13,16% 3-5km 4 10,53% plus de 5 km 6 15,79% 38 100,00% POUR UNE VILLLE de densité 1000 hab/km2 15 23 28 32 adhérents somme habitants dans rayon 1, 2, 3, 5km part de la population adhérente 3142 12566 28274 78540 « = pi * r2 * densité » 0,48% 0,18% 0,10% 0,04% « = sommes adh / habitants *100 » A vous de jouer pour votre ville

Communiquer avec ces données

Évidemment, libre à vous d'inclure ces données dans vos dossiers, dans votre discours, ... Par contre, si vous avez fait le calcul pour votre atelier (même partiellement...jusqu'à l'étape 3), merci de renvoyer vos infos à ce sujet à gabriel.smadja@sfr.fr qui fera une compilation des données à l'échelle nationale. On se voit aux prochaines rencontres de L'Heureux Cyclage pour en parler et améliorer cela avec de la cartographie, des données réelles en distance à la rue et non plus en vol d'oiseau, etc... le groupe de bénévoles de L'Heureux Cyclage cherche des compétences, faites vous connaître ! 15