Trier des adresses par quartier : quels outils ?

Dans le cadre de la gestion d’une association, je me suis posé la problématique suivante :

Comment, à partir de la base de données des adhérents (gérée avec Galette), répartir ces derniers par zones géographiques afin de permettre à des bénévoles de leur distribuer le bulletin d’information de l’association ?

Note : cet article est un peu technique et nécessite une base Galette avec des champs personnalisés. Si vous voulez l’adapter à votre cas, il vous faudra quelques compétences techniques. Une connaissance minimale du langage SQL, du shell et de sed est indispensable (et suffisante : mes propres connaissances ne vont pas très loin dans le domaine).

En fait, je pense que cet article est trop spécifique à mon cas pour servir un jour à quelqu’un. Mais il peut au moins donner quelques idées, et il aura le mérite d’être un bon aide-mémoire pour moi ce qui est une motivation suffisante pour le rédiger.

Principe

La résolution de ce problème va se faire en plusieurs étapes.

Étape 1 :

Extraction des données depuis Galette.

On va récupérer l’identifiant, le nom et les coordonnées de l’adhérent dans la base de données.

Étape 2 :

Localiser les adhérents.

Savoir qu’un adhérent habite au 3 rue Beauregard, c’est bien. Savoir où est le 3 rue Beauregard, c’est mieux. Pour une ou deux personnes, il suffit de regarder une carte en papier. Pour 250 adhérents, demander à l’ordinateur de faire ce travail devient nécessaire.

Pour cela, il nous faut un moyen de convertir une adresse postale en coordonnées géographiques.

Étape 3 :

Regrouper spatialement (par exemple, par quartiers) les adhérents.

Dans un premier temps, une sélection à la souris à main levée peut être une bonne solution. Mais pouvoir l’automatiser est l’objectif à atteindre.

Étape 4 :

Stocker dans Galette l’information de regroupement obtenue. En effet, un de mes collègues a déjà développé tout le nécessaire pour réaliser avec Galette la suite des opérations (édition des étiquettes, avec impression regroupée selon les zones géographiques que nous cherchons à obtenir ici…).

Réalisation :

Cette méthode n’est pas automatisée à 100% mais fonctionne et constitue un gain de temps important par rapport à l’ancienne méthode (totalement manuelle). Il n’est pas forcément souhaitable de vouloir aller plus loin dans l’automatisation, car la définition des zones géographiques dépend fortement de critères non techniques (tels que l’envie, pour un bénévole, d’aller loin ou d’affronter le relief… envie qui varie d’un bénévole à un autre).

Étape 1 :

Le script suivant va extraire les données de Galette et les mettre dans un fichier texte, en séparant les champs par des tabulations.

Astuce : L’adresse est dans le dernier champ. Cela simplifie grandement la gestion des espaces, et permet de mettre l’un au bout de l’autre les champs « adresse1 » et « adresse2 » (une absurdité qui ne devrait pas exister dans une base de données !) sans se poser de question…

#!/bin/sh

# Script qui recupere les adresses des adherents dans Galette

# Identifiant d'access a la BDD de Galette
MYSQL_LOGNAME="identifiant"
# Mot de passe d'acces a la BDD de Galette
MYSQL_PASSWD='motdepasse'
# Nom de la base de donnees de Galette
MYSQL_BDD="nom_base"

# Requete qui selectionne tous les adherents de Galette.
# 
REQUETE="SELECT id_adh, nom_adh, cp_adh, ville_adh, DIST.field_val as distributeur, adresse_adh, adresse2_adh
         FROM galette_adherents as ADH
         LEFT JOIN galette_dynamic_fields as TYPE ON TYPE.item_id = ADH.id_adh AND TYPE.field_id = '12'
         LEFT JOIN galette_dynamic_fields as DIST ON DIST.item_id = ADH.id_adh AND DIST.field_id = '7'
         LEFT JOIN galette_dynamic_fields as NOMS ON NOMS.item_id = ADH.id_adh AND NOMS.field_id = '5'
         WHERE ADH.activite_adh = '1'
         AND ADH.id_statut <> '9'
         AND TYPE.field_val <> '0'
         ORDER BY DIST.field_val, ADH.cp_adh, ADH.nom_adh, ADH.prenom_adh"

# On execute la requete, et on met en forme chaque ligne de son resultat.
echo "$REQUETE" | mysql -u "$MYSQL_LOGNAME" -p"$MYSQL_PASSWD" "$MYSQL_BDD" | while read ligne ; do
   set $ligne
   chaine=$(echo "$ligne" | tr ' ' '#')
   set $chaine
   adresse=$(echo $6 $7 $8 $9 ${10} ${11} ${12} ${13} ${14} ${15} ${16} ${17} ${18} ${19} ${20} ${21} | tr '#' ' ')
   ville=$(echo $4 | tr '#' ' ')
   echo "$1     $2      $3      $ville  $5      $adresse"
done

Le résultat est une série de lignes de ce genre :

id_adh  nom_adh cp_adh  ville_adh       distributeur    adresse_adh adresse2_adh
526     Ahmed     25000   Besançon        NULL    51, chemin de Papa
500     Balland   25000   Besançon        NULL    42 rue Emile Itaire
535     Breton    25000   Besançon        NULL    8 rue du Bonnet Rouge

On doit donc exécuter le script en redirigeant sa sortie vers un fichier texte.

./monscript.sh > liste_adresses.txt

Étape 2 :

Nous allons utiliser le site Batchgeo.

Le principe est simple : on va copier le contenu du fichier obtenu grâce à notre script, et le coller dans Batchgeo. Ensuite, il ne reste qu’à vérifier que les champs concordent bien, lancer l’exécution, et récupérer le KML de la carte Google obtenue (tout en bas de la page).

Il se peut qu’une ou deux adresse échouent. Mais je n’en ai eu qu’une seule sur environ 200 à traiter. Je n’ai, d’ailleurs, pas trouvé pourquoi puisqu’elle est tout à fait valide et Google Maps la localise sans problème…

Étape 3 :

Nous allons charger le fichier obtenu dans QGIS… Mais pas si vite. Ne sautez pas sur QGIS tout de suite. Si vous chargez le fichier tel que vous l’avez téléchargé depuis Batchgeo, cela fonctionnera mais les informations contenues dans la table d’attributs ne seront pas exploitables. Chaque champ « description » de celle-ci contiendra un bout de code XML absolument illisible tandis que les autres champs seront vides.

On va donc, avant de le charger, le nettoyer un peu pour que le champ « name » contienne l’identifiant de l’adhérent dans la base de données, tandis que « description » contiendra son nom.

(Ne cherchez pas de logique à cela : j’utilise simplement les noms de champs qui peuvent exister dans un fichier KML, dans un ordre qui m’arrange !)

Comme j’ai besoin d’autres champs pour stocker la zone géographique et l’adresse postale (car j’ai envie de l’avoir sous les yeux dans QGIS), je crée des champs personnalisés dans le KML selon la documentation qui figure ici.

Pour ce faire, nous allons taper la suite de commandes suivantes :

# On commence par mettre chaque adherent sur une ligne
# car cela nous simplifiera la vie.
sed -i 's/<Placemark>/\n<Placemark>/g' BatchGeo.kml
# On enleve tout ce qui est avant l'identifiant de l'adherent
# et on le remplace par la balise xml <name>.
sed -i 's@<description>&lt;div&gt;&lt;span class=&quot;l&quot;&gt;id_adh:&lt;/span&gt;&amp;nbsp;@<name>@' BatchGeo.kml
# On enleve ensuite tout ce qui est entre l'identifiant et le nom 
# pour le remplacer par la balise </name> fermante et la balise
# <description> ouvrante.
sed -i 's@&lt;/div&gt;&lt;div&gt;&lt;span class=&quot;l&quot;&gt;nom_adh:&lt;/span&gt;&amp;nbsp;@</name><description>@' 
BatchGeo.kml
# On enlève le bazar inutile apres le nom.
sed -i 's@&lt;/div&gt;@@' BatchGeo.kml
# On va ajouter un champ personnalisé pour la zone geographique.
sed -i 's@&lt;div&gt;&lt;span class=&quot;l&quot;&gt;distributeur:&lt;/span&gt;&amp;nbsp;@</description><ExtendedData><Data name="zone"><value>@' BatchGeo.kml
sed -i 's@&lt;/div&gt;</description>@</value></Data></ExtendedData>@' BatchGeo.kml
sed -i 's@</description><Point>@</description><ExtendedData><Data name="zone"><value>novalue</value></Data></ExtendedData><Point>@' BatchGeo.kml
# Pour terminer, on va egalement mettre l'adresse dans un champ personnalise
# car celui dans lequel Batchgeo le met n'est pas affiche par QGIS.
sed -i -r 's@</ExtendedData><Point><coordinates>(.*)</coordinates></Point><address>(.*)</address>@<address>\2</address></ExtendedData><Point><coordinates>\1</coordinates></Point>@' BatchGeo.kml
sed -i 's@<address>@<Data name="address"><value>@' BatchGeo.kml
sed -i 's@</address>@</value></Data>@' BatchGeo.km

Maintenant, on peut ouvrir QGIS et charger le fichier KML à l’aide du bouton « Ajouter une couche vecteur ».

(Histoire d’avoir un repère, on aura chargé au préalable une carte de la zone sur laquelle on travaille. Par exemple, le réseau routier ou les tuiles d’OpenStreetMap.)

Cette partie est la plus manuelle de tout le processus. Elle consiste à :
- Sauvegarder tout d’abord notre KML en shapefile car QGIS sera plus à l’aise pour travailler dessus.
- Supprimer la couche du KML et ouvrir le shapefile.
- Activer le mode édition de cette nouvelle couche.
- Ouvrir sa table attributaire.
- Supprimer les colonnes inutiles issues du KML.
- Sur la carte, sélectionner avec la souris des adhérents géographiquement proches.
- Dans la table attributaire, choisir de ne montrer que les entités sélectionnées.
- Avec la calculatrice de champs, attribuer un identifiant (nom, numéro…) au groupe géographique, dans un champ créé à cet effet.
- Recommencer pour chaque zone géographique.
- Copier le contenu de la table attributaire dans le presse-papier.

Étape 4 :

On va coller dans un fichier texte le contenu du presse papier, puis se servir de ce fichier pour mettre à jour notre base de données.

Voici quelques lignes du fichier obtenu :

wkt_geom        Name    descriptio      timestamp       zone    address
POINT(6.03260425473480044 47.26369648744299923) 526     Ahmed             novalue 51 Chemin de Papa, 25000 Besançon, France
POINT(6.04156630375489989 47.24470152838200221) 500     Balland           novalue 42 Rue Emile Itaie, 25000 Besançon, France
POINT(6.02956156786449959 47.24672356236000326) 535     Breton            novalue 8 Rue du Bonnet Rouge, 25000 Besançon, France

Il ne reste plus qu’à le lire afin d’exécuter la requête de mise à jour de notre base de données.

cat adherents_regroupes.txt | while read ligne ; do 
set $ligne;
echo "REPLACE INTO galette_dynamic_fields (item_id,field_id,field_form,val_index,field_val) VALUES ('"$3"','7','adh','1','"$5"');"
done > requetes_update_base.sql
mysql -u identifiant -p'motdepasse' nom_base < requetes_update_base.sql

C’est terminé !

Ce contenu a été publié dans Astuces, Informatique. Vous pouvez le mettre en favoris avec ce permalien.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>