Discussion :

[boubil]

Bonjour

Je dispose d'une table des contacts avec plus de 16 000 lignes.

Je dispose par ailleurs d'une table des codes postaux / ville (source Insee).

Je souhaiterai trouver un script qui me permette de vérifier / corriger l'orthographe des ville de ma table des contacts

Merci d'avance

[CinePhil]

Il est assez aisé de trouver les villes des contacts qui n'existent pas dans les villes de référence. Un truc du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT c.id, c.ville
FROM contact c
WHERE NOT EXISTS
(
	SELECT 1
	FROM ville v
	WHERE v.nom = c.ville
)

Si cette requête donne trop de résultats pour les traiter un par un à la main, tu peux rechercher les différents cas existants et les traiter en masse.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT DISTINCT ville AS ville_fausse
FROM contact c
WHERE NOT EXISTS
(
	SELECT 1
	FROM ville v
	WHERE v.nom = c.ville
)

Et là il faudra bien traiter les différentes villes fausses quasiment une par une avec des requêtes UPDATE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UPDATE contact
SET ville = 'Saint-Étienne'
WHERE ville IN ('St Etienne', 'Saint Etienne', 'Saint-Etienne')

Si tu es sûr de la validité des codes postaux de la table contacts, tu peux aussi UPDATEr en masse la table en te basant sur les codes postaux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UPDATE contact c
INNER JOIN ville v ON v.code_postal = c.code_postal
SET c.ville = v.nom

Et s'il y a des codes postaux de la table contact qui n'existent pas dans le référentiel, ben reprendre la première méthode pour vérifier les villes.

Ceci dit, il vaudrait quand même mieux ensuite intégrer cette référence INSEE dans le schéma de la BDD et mettre une clé étrangère dans la table des contacts, ce qui aura des répercussions côté applicatif.

Bon courage !

[boubil]

Merci CinéPhil

En fait, j'ai pas bien exprimé ce que je voulais.
Dans la table des contacts les nom de villes et les CP sont issus d'une saisie manuelle.
On se retrouve par exemple avec
PARIS et PARISS
Ou LA FRETTE SUR SEINE et LA FRETTE SUR SEINNE,...
Ou j'ai aussi toutes les variantes avec sous sans LE / LA / ...
ou les villes avec sous sans les tirets,...
et les villes avec Saint / ST
A coté de ça certains code postaux correspondent a plusieurs ville (c'est bcp le cas pour les villages, dès fois il peut y avoir 30 villages sur un même code postal).

Je sais qu'il existes de outils de normalisation d'adresse mais comme c'est pour une asso on ne peut pas se payer ca. Alors je voudrais au minimum faire une passe sur les villes car en lecture directe j'ai repéré pas mal de petites erreurs, mais sur 16 000 contact je ne peux pas les traiter manuellement.

J'imagine qu'il y a des algorithmes existant pour faire ca qui sont capable de traiter 95% des cas.

J'imaginais commencer par ajouter une colonne pour y mettre le nom de ville sans espaces / tiret / apostrophe
remplacer sous les ste / st par saint / sainte / ...
[....]

puis faire des scores d'identité entre mes deux tables.

J'imagine que je ne suis pas la première à traiter cette problématique. Donc j'esperais trouver un algorithme tout fait

[SQLpro]

Si vous êtiez sous MS SQL Server, ce serait simple : FuzzyLookup !
http://www.bimonkey.com/2009/06/the-...ransformation/

Dommage !

A +

[boubil]

SQL_PRo, tu as bien fait d'en parler, ca m'a donné une piste de recherche à partir du mot fuzzy en particulier en utilisant la fonction soundex de mysql ou match against (http://dev.mysql.com/doc/refman/5.0/...xt-search.html)

Et j'ai trouvé aussi une discussion intéressante sur les recherches phonétiques.

J'ai aussi trouvé un script de soundex php adapté au français.
http://www.octopuce.fr/Soundex-en-Fr...tion-de-racine

Enfin, j'ai trouvé une proc SQL qui permet de calculer la distance entre deux mots (nb de transformations pour passer de l'un à l'autre

Levenshtein distance : http://www.artfulsoftware.com/infotree/queries.php#552

Et aussi : http://dannykopping.com/blog/fuzzy-t...l-jaro-winkler

Je vais essayer de mettre tout ca bout à bout et proposer quelque chose

[SQLpro]

Si vous vouliez ce genre d'info, pas la peine d'aller si loin.. J'ai publié mes travaux sur ce site il y a longtemps, et ils ont été repris pour certains notamment pour le recherche des malfaiteurs des listes tracfin de la PP...
À me lire sur les "soundex" : http://sqlpro.developpez.com/cours/soundex/
À me lire sur les fonctions de rapprochement de motifs : http://sqlpro.developpez.com/cours/s...aisons-motifs/

A +

[boubil]

Merci, c'est suer intéressant et très clair !

Avec ca je vais pouvoir traiter ma base sans problème. Je pense que je vais mettre aussi en place ta version evoluée du soudex ou du phonex pour les recherches de doublons sur la base de contact (à la saisie).

Merci infiniment de partager tes connaissances ainsi !