Calcul de la zone de chalandise : Différence entre versions

De Wiklou, le Wiki du Biclou
Aller à : navigation, rechercher
(Ajout d'un second site de geo batch)
(le calcul des distances à vol d'oiseau entre l'atelier et chacun des adhérent.e.s)
Ligne 239 : Ligne 239 :
  
 
===le calcul des distances à vol d'oiseau entre l'atelier et chacun des adhérent.e.s===
 
===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 :
 
Nous allons expliquer la manip en prenant comme base ce fichier :
Ligne 1 : les titres
+
* Ligne 1 : les titres
Ligne 2 : les infos de mon atelier et donc dans la cellule B2 l'adresse de mon atelier, dans la
+
* 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
cellule F2 sa latitude, G2 sa longitude
+
* Ligne 3 et suivantes : les adhérent.e.s Colonnes L,M,N libres
Ligne 3 et suivantes : les adhérent.e.s
+
Colonnes L,M,N libres
 
 
Insérer la formule de calcul des distances
 
Insérer la formule de calcul des distances
La formule de Célestion est (PAS DE PANIQUE) :
+
 
Distance entre a et b =
+
La formule de Célestion est (PAS DE PANIQUE) : Distance entre a et b =
 
avec R le rayon de la terre
 
avec R le rayon de la terre
 
a = latA
 
a = latA
Ligne 253 : Ligne 253 :
 
c = lonA
 
c = lonA
 
d = lonB
 
d = lonB
12Célestion, malin, parle en radians.. Alors que notre logiciel nous a donné des latitudes et des
+
 
longitudes en degrés.
+
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 :
 
Pour faire la conversion radian-degrés :
sélectionner toutes les données des colonnes F et G, soit Latitudes et Longitudes
+
* 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
+
* Appuyer sur CTRL et la lettre F de votre clavier en même temps → s'ouvre une fenêtre « rechercher et remplacer ».
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 »
dans le champ de recherche, indiquer un point « . » et dans le champ de remplacement
+
* les latitudes et longitudes sont devenues des chiffres
une virgule « , » puis appuyer sur « Tout remplacer »
+
* désormais en L2 écrire « =RADIANS(F2) »
les latitudes et longitudes sont devenues des chiffres
+
* écrire en M2 « =RADIANS(G2)
désormais en L2 écrire « =RADIANS(F2) »
+
* reportez les formules jusqu'à la dernière adresse de votre feuille (clic enfoncé sur le petit carré noir de la cellule L2 ou M2)
écrire en M2 « =RADIANS(G2)
+
 
reportez les formules jusqu'à la dernière adresse de votre feuille (clic enfoncé sur le
+
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))
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 !
 
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).
+
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.
 
Les distances sont calculées en mètres.
 +
 
Sélectionner enfin toutes la colonne N (titre+données) puis
 
Sélectionner enfin toutes la colonne N (titre+données) puis
cliquez sur « données »
+
* cliquez sur « données »
« tri »
+
* « tri »
« tri croissant »
+
* « tri croissant »
 +
 
 
13– « étendre la sélection »
 
13– « étendre la sélection »
 +
 
Les adhérent.e.s sont désormais classés du plus proche au plus lointain.
 
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
+
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 :
 
Dans notre cas :
 
15 personnes habitent à moins de 1km de l'atelier à vol d'oiseau (on exclut la première ligne
 
15 personnes habitent à moins de 1km de l'atelier à vol d'oiseau (on exclut la première ligne
Ligne 302 : Ligne 306 :
 
100,00%
 
100,00%
 
ETAPE 3 FINIE !!!
 
ETAPE 3 FINIE !!!
 +
 
===Rapporter les données à la population de notre territoire===
 
===Rapporter les données à la population de notre territoire===
 
On va faire vite :
 
On va faire vite :

Version du 20 octobre 2016 à 11:12

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

Pour réaliser cette étape, nous utiliserons un logiciel en ligne qui s’appelle Batch Géocodeur. Alors rendez-vous sur le site http://www.batchgeocodeur.mapjmz.com/ (19OCT16 : ne semble plus bon, à voir si cette méthode fonctionne avec http://www.findlatitudeandlongitude.com/batch-geocode/).

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 LIGNE AVEC LES TITRES, souvent ligne 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 »

13– « é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