Le portail francophone de la géomatique

meonais · Wed 26 July 2017 09:26

Bonjour,

J'ai fait quelques recherches et essais mais n'ai pas trouvé ou compris la réponse à mon problème

J'ai une couche de points que j'ai "snapé" à une couche de cours d'eau.
Je souhaite vérifier que le snap s'est fait sur le bon cours d'eau en comparant le champ "nom cours d'eau" de la couche de points à celui de la couche de cours d'eau.

Je ne peux pas faire un simple LIKE car la couche de points possède un champ "nom de cours d'eau" mais qui ne provient pas d'une jointure avec la couche cours d'eau que j'ai : donc je me retrouve parfois avec des chaînes de caractères différentes pour appeler un même cours d'eau. Exemple : Rivière le Lez et le Lez de sa confluence à ...

J'ai vu que la recherche plein texte serait peut être le plus efficace mais je n'ai pas trop eu l'énergie de me coller dessus...

Du coup j'ai orienté ma réflexion sur les fonctions et opérateurs de chaîne et j'envisageais ceci :
- exploser chaque champ "nom de cours d'eau" avec par exemple :

Code:

regexp_split_to_array(upper(o.nomentiteh),E'\\s+')

- remplacer tous les mots générique (rivière, fleuve, canal, du, de, la, le...), virgules et espaces par NULL (ou '') avec replace() ou regexp_replace()

- utiliser substring(chaîne from modele) pour faire la comparaison (j'ai fait quelques tests, ça fonctionne à partir du moment où il la chaîne est strictement identique à une partie du modèle ; je ne sais pas comment instaurer % pour étendre les correspondances... )

Code:

((substring(upper((o.nom)) from o.nomentiteh) ~* upper(o.nomentiteh)) OR (substring(upper((o.nomentiteh)) from upper(o.nom)) ~* upper(o.nom)) )

- ou utiliser substring(chaîne from modele for echappement)

Mon problème principal : utilisation des atomes, contraintes, quantificateurs, échappement dans les expressions rationnelles POSIX, notamment pour gérer les [] résultants de regexp_split_to_array et à traiter avec replace() ou regexp_replace ou même substring

L'approche est-elle correcte selon vous ?
Pourriez-vous me donner un exemple qui permette de s'extraire de la présence des [] issus du split_to_array ?

Merci d'avance

Nicolas Ribot · Wed 26 July 2017 10:16

Bonjour,

Vous allez vous embeter en comparant les chaines: entre les accents, les différences de nom et les fautes éventuelles dans les noms.
Vous devriez plutot utiliser les outils de fuzzy match comme fuzzystrmatch, qui donnent des score de similitudes pour deux chaines.

Concernant les '[]', si je comprends bien, vous pouvez extraire les éléments d'un tableau avec unnest();
(select unnest('{A,B}'::text[])

Nicolas

meonais · Thu 27 July 2017 12:31

Bonjour,

Et merci pour votre réponse !

Pour l'outil, je me suis orientée vers pg_trgm plutôt que fuzzystrmatch, j'ai mieux réussi à adapter (comprendre) l'utilisation à mon cas.
J'ai donc ce type de code :

Code:

SELECT id, ocdhydro, similarity(nomentiteh, nom) as simi,nomentiteh, nom
FROM snap_cdo
WHERE nomentiteh % nom
AND  similarity(nomentiteh, nom) > 0.5
GROUP BY id, ocdhydro, nomentiteh, nom
ORDER BY id, ocdhydro ASC ;

Je ne comprends pas bien pourquoi j'ai des enregistrements qui sont bien sélectionnés (note sup à 0.5) et d'autres non (note < 0.5) alors que les correspondances semblent similaires... Voici un exemple de sortie avec > 0.5

Code:

id|ocdhydro|simi|nomentiteh|nom
31|ocdhydro|0.529412|rivière la drôme|La Drôme
32|ocdhydro|0.611111|rivière le roubion|Le Roubion
33|ocdhydro|0.590909|ruisseau les collières|les Collières
34|ocdhydro|0.529412|rivière l'eygues|l'Eygues
35|ocdhydro|0.578947|rivière la bresque|La Bresque
36|ocdhydro|0.666667|rivière le gardon de saint-jean|le Gard et le Gardon de Saint Jean
37|ocdhydro|0.555556|torrent la bévéra|La Bévéra
38|ocdhydro|0.55|le grand anguillon|L'Anguillon
39|ocdhydro|0.611111|rivière la galaure|la galaure
40|ocdhydro|0.5625|rivière la payre|La Payre
41|ocdhydro|0.533333|fleuve le tech|le Tech
42|ocdhydro|0.6|rivière la gervanne|La Gervanne
43|ocdhydro|0.55|ruisseau la cadière|la Cadière
(...)

et maintenant avec <0.5

Code:

id|ocdhydro|simi|nomentiteh|nom
 1| V40-0400 |    0.375 | rivière la vére                                | Vére
 2| V40-0400 |    0.375 | rivière la vére                                | Vére
 3| V50-0400 | 0.333333 | rivière l'ardèche                               | l'ardeche
 4| V50-0400 | 0.333333 | rivière l'ardèche                               | l'ardeche
 5| V50-0400 | 0.333333 | rivière l'ardèche                               | l'ardeche
 6| V34-0400 |   0.4375 | ruisseau l'oron                                 | l'Oron
 7| V50-0400 | 0.333333 | rivière l'ardèche                               | l'ardeche
 8| V50-0400 | 0.333333 | rivière l'ardèche                               | l'ardeche
 9| V---0000 | 0.315789 | fleuve le rhône                                 | le rhone
 10| V3--3002 | 0.315789 | fleuve le rhône                                 | le rhone
 11| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 12| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 13| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 14| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 15| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 16| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 17| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 18| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 19| V52-0400 | 0.384615 | rivière le lez                                  | le lez
 20| V50-0400 | 0.333333 | rivière l'ardèche                             | l'ardeche
 21| V50-0400 | 0.333333 | rivière l'ardèche                             | l'ardeche
 22|          | 0.333333 | rivière l'ardèche                               | l'ardeche
 23| V50-0400 | 0.333333 | rivière l'ardèche                            | l'ardeche
 24| V5310500 | 0.466667 | rivière l'oule                                  | L'Oule
 25| V71-0400 | 0.368421 | rivière le gard                                 | le Gardon
 26| V71-0400 | 0.368421 | rivière le gard                                 | le Gardon
(...)

C'est pourquoi je voudrais arriver à "supprimer" les mots "parasites" dans chacun des champs avant de faire la comparaison (hier j'étais partie sur le regexp_split..() pour ensuite faire un replace() des mots "parasites", d'où ma demande sur []). Cette "suppression" me permettrait de mieux gérer les résultats de similarity(), non ?

Nicolas Ribot · Thu 27 July 2017 13:24

Bienvenu dans le monde de la comparaison de chaines
(moi perso, je n'ai jamais reussi a faire un truc clean lors de la comparaison de chaines)

Vous pouvez eventuellement normaliser un peu les données avant, en supprimant les accents et autres signes, avec unaccent (https://www.postgresql.org/docs/9.6/sta … ccent.html)

Nicolas

tumasgiu · Thu 27 July 2017 13:53

Salut,

je remarque que les noms issus de l'exemple <0.5 ont tous beaucoup de caractères blancs en fin de chaine.
Etant donné que pg_trgm compare le nombre de trigramme communs, il est probable que ceux générés par ces
blancs fassent baisser le score.
Un petit coup de trim pourrait peut être améliorer çà.

meonais · Thu 27 July 2017 14:15

Je vous adore ! : cool

Merci beaucoup, ça aide bien. Je pense que ça suffira pour discriminer les quelques erreurs de snap sur mon jeu de données, avec un autre filtre / jointure sur des champs

Encore merci

Dernière modification par meonais (Mon 31 July 2017 16:56)

JRM · Thu 27 July 2017 14:57

C'est pourquoi je voudrais arriver à "supprimer" les mots "parasites"

Une première passe avec Full Text Search et ses fonctions to_tsvector() et to_tsquery() va te permettre de normaliser tes mots (lexemes) et de réduire les variances en remontant à la racine du terme.

Tu peux utiliser le résultat avec pg_trm, ça permet de réduire les faux positifs et les mots parasites. On peut encore en rajouter une couche avec l'extension unaccent.

meonais · Thu 27 July 2017 14:57

Ha oups...

Il y a du mieux avec unaccent() mais pas encore ça... j'ai tenté trim et regexp_replace mais pas vraiment de succès :
J'ai même l'impression d'avoir de moins bons résultats ? ...

Code:

plagepomi=# SELECT id, ocdhydro, similarity(unaccent(trim(trailing '\s+' from nomentiteh)), unaccent(trim(trailing '\s+' from nom))) as simi,nomentiteh, nom
plagepomi-# FROM snap_cdo
plagepomi-# WHERE nomentiteh % nom
plagepomi-# AND  similarity(unaccent(trim(trailing '\s+' from nomentiteh)), unaccent(trim(trailing '\s+' from nom))) < 0.5
plagepomi-# GROUP BY id, ocdhydro, nomentiteh, nom
plagepomi-# ORDER BY id, ocdhydro ASC ;


id | ocdhydro |   simi   |                   nomentiteh                    |                nom
------------+----------+----------+-------------------------------------------------+------------------------------------
10124   | V40-0400 |    0.375 | riviÞre la vÚore                                | VÚore
10301   | V3530500 | 0.384615 | riviÞre l'ay                                    | L'Ay
22336   | V34-0400 |   0.4375 | ruisseau l'oron                                 | l'Oron
32046   | V52-0400 | 0.384615 | riviÞre le lez                                  | le lez
33548   | V5310500 | 0.466667 | riviÞre l'oule                                  | L'Oule
33873   | V71-0400 | 0.368421 | riviÞre le gard                                 | le Gardon
34885   | V52-0400 | 0.384615 | riviÞre le lez                                  | le lez
35097   | Y25-0400 | 0.461538 | fleuve l'orb                                    | l'Orb
43739   | Y4--0200 | 0.428571 | riviÞre l'arc                                   | l'Arc
44196   | V5440500 |     0.35 | riviÞre l'auzonnet                              | L'Auzon
44292   | V6150500 | 0.333333 | riviÞre la sorgue                               | la sorgue de velleron
44293   | V5440500 |     0.35 | riviÞre l'auzonnet                              | L'Auzon
48817   | V71-0400 | 0.365385 | ruisseau le gardon de saint-martin de lansuscle | le Gard et le Gardon de Saint Jean
48986   | Y3130500 |    0.375 | ruisseau du coulazou                            | le Coulazou
52033   | Y2030500 | 0.428571 | riviÞre la vis                                  | la vis

Je vais voir la combinaison proposée par JRM

Dernière modification par meonais (Thu 27 July 2017 15:11)

JRM · Thu 27 July 2017 15:30

Pour ne pas te prendre la tête aujourd’hui avec des histoire d'immutabilité, tu peux te créer de nouvelles colonnes telles que nomentiteh_trigram que tu remplis avec

Code:

replace(strip(to_tsvector('simple', unaccent(nomentiteh)))::text,'''', '')

puis index avec

Code:

gin (nomentiteh_trigram gin_trgm_ops)

.

Dernière modification par JRM (Thu 27 July 2017 15:31)

meonais · Thu 27 July 2017 18:23

Bonsoir,

Merci JRM pour le bout de code, ça permet d'aller plus vite
Moi qui ne voulait pas trop aller dans le plein texte par manque de temps pour se former... j'y suis jusqu'au cou !

Du coup, j'ai le même type de résultats, même sans les accents et les apostrophes, je dois utiliser un pg_trgm > 0.4, mais c'est parce que je peux visualiser rapidement l'ensemble de mes données...

Donc maintenant que j'y suis jusqu'au cou, j'aimerais arriver à indiquer au dictionnaire 'stop' les mots à ne pas comptabiliser. J'ai trouvé le fichier et rajouté les mots à "supprimer" (riviere, ruisseau, ru, ravin, canal, bief, ...) mais j'ai encore le même type de résultats...

J'ai lancé

Code:

CREATE TEXT SEARCH DICTIONARY public.simple_dict(
TEMPLATE=pg_catalog.simple, 
STOPWORDS=french);

j'ai complété le fichier C:\Program Files\PostgreSQL\9.5\share\tsearch_data\french.stop avec les mots que je ne voulais plus être intégrés dans la recherche, dont "riviere"

J'ai testé sous psql (et sql de pgAdmin) et obtenu :

Code:

plagepomi=# SELECT ts_lexize('french_stem', 'riviere');
 ts_lexize
-----------
 {}
(1 ligne)

Mais quand je lance ma requête pg_trgm, j'ai toujours les mêmes valeurs et donc la même liste d'enregistrements <0.5 alors qu'ils devraient (si j'ai bien tout compris) avoir des "notes" de similarity() plus grande sans les mots "parasites" comme "riviere", non ?

Je vais laisser maturer ce soir mais si vous avez d'autres aiguillages, je veux bien !

Merci beaucoup,

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Wed 26 July 2017 09:26

Expression rationnelle, crochet et replace

Code:

Code:

#2 Wed 26 July 2017 10:16

Re: Expression rationnelle, crochet et replace

#3 Thu 27 July 2017 12:31

Re: Expression rationnelle, crochet et replace

Code:

Code:

Code:

#4 Thu 27 July 2017 13:24

Re: Expression rationnelle, crochet et replace

#5 Thu 27 July 2017 13:53

Re: Expression rationnelle, crochet et replace

#6 Thu 27 July 2017 14:15

Re: Expression rationnelle, crochet et replace

#7 Thu 27 July 2017 14:57

Re: Expression rationnelle, crochet et replace

#8 Thu 27 July 2017 14:57

Re: Expression rationnelle, crochet et replace

Code:

#9 Thu 27 July 2017 15:30

Re: Expression rationnelle, crochet et replace

Code:

Code:

#10 Thu 27 July 2017 18:23

Re: Expression rationnelle, crochet et replace

Code:

Code:

Pied de page des forums