<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class="">Hi Sarah and Dmitry,</div><div class=""><br class=""></div><div class="">thanks for your responses! I will definitely investigate into the libpostal project later on as well as some of the geocoders Dmitry suggested.</div><div class=""><br class=""></div><div class="">But right now I’m doing some tests with pg_trgm. And Sarah, I cannot confirm so far your comment</div><div class=""><br class=""></div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"><div class="">"Trigrams only work with misspellings of a letter or two, they fail</div><div class="">completely when trying to match up abbreviations.“</div></blockquote><div class=""><br class=""></div><div class="">To me the opposite seems true, as you can see in the following examples. Let’s take this address, as I want to look for it and the way OSM has it stored and spelled.</div><div class=""><br class=""></div><div class=""><span class="Apple-tab-span" style="white-space: pre;"> </span>(asked address)<span class="Apple-tab-span" style="white-space: pre;"> </span>(OSM address)</div><div class=""><div class="">—street: <span class="Apple-tab-span" style="white-space: pre;"> </span>Верещагина ул<span class="Apple-tab-span" style="white-space: pre;"> </span>улица Верещагина</div><div class="">—town:<span class="Apple-tab-span" style="white-space: pre;"> </span>Ханская ст-ца<span class="Apple-tab-span" style="white-space: pre;"> </span>Ханская </div><div class="">—city:<span class="Apple-tab-span" style="white-space: pre;"> </span>Майкоп г<span class="Apple-tab-span" style="white-space: pre;"> </span>городской округ Майкоп </div><div class="">—region:<span class="Apple-tab-span" style="white-space: pre;"> </span>Адыгея Респ<span class="Apple-tab-span" style="white-space: pre;"> </span>Адыгея </div></div><div class=""><br class=""></div><div class="">The Nominatim standard query is basically this (for the street):</div><div class=""><br class=""></div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"><div class=""><div class="">select word_id, word_token, word</div></div><div class=""><div class="">from word</div></div><div class=""><div class="">where word_token = make_standard_name('Ханская ст-ца')</div></div></blockquote><div class=""><br class=""></div><div class="">…and does not return anything.</div><div class=""><br class=""></div><div class="">Now I enabled the extension (CREATE EXTENSION pg_trgm;) and created an index (CREATE INDEX word_token_trgm_idx ON word USING GIST (word_token gist_trgm_ops);) and modified the select slightly:</div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"><div class=""><div class=""><br class=""></div><div class="">select word_id, word_token, word, gettokenstring(transliteration(‚Верещагина ул')) as asked, </div><div class=""><span class="Apple-tab-span" style="white-space: pre;"> </span>similarity(word_token, gettokenstring(transliteration('Верещагина ул'))) as sml</div><div class="">from word</div><div class="">where word_token % make_standard_name('Верещагина ул')</div><div class="">order by sml desc</div><div class="">limit 20</div></div></blockquote><div class=""><br class=""></div><div class="">…and this is the result (I hope the formatting gets through…):</div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"><div class=""><br class=""></div><div class=""><div class="">"word_id"<span class="Apple-tab-span" style="white-space:pre"> </span>"word_token"<span class="Apple-tab-span" style="white-space:pre"> </span>"word"<span class="Apple-tab-span" style="white-space:pre"> </span>"asked"<span class="Apple-tab-span" style="white-space:pre"> </span>"sml"</div><div class="">19098<span class="Apple-tab-span" style="white-space:pre"> </span>" ul virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>"улица Верещагина"<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>1.0</div><div class="">19099<span class="Apple-tab-span" style="white-space:pre"> </span>"ul virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>1.0</div><div class="">19100<span class="Apple-tab-span" style="white-space:pre"> </span>„virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>0.833333</div><div class="">1525904<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>"Верещагина"<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.833333</div><div class="">115343<span class="Apple-tab-span" style="white-space:pre"> </span>"ul virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.8</div><div class="">115342<span class="Apple-tab-span" style="white-space:pre"> </span>" ul virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span>"улица Верещагино"<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.8</div><div class="">568775<span class="Apple-tab-span" style="white-space:pre"> </span>„ n virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>"На Верещагина"<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.75</div><div class="">568776<span class="Apple-tab-span" style="white-space:pre"> </span>"n virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.75</div><div class="">1256480<span class="Apple-tab-span" style="white-space:pre"> </span>" pl virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>"площадь Верещагина"<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.714286</div><div class="">1256481<span class="Apple-tab-span" style="white-space:pre"> </span>"pl virishchaghina"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.714286</div><div class="">351652<span class="Apple-tab-span" style="white-space:pre"> </span>„ virishchaghin"<span class="Apple-tab-span" style="white-space:pre"> </span>"Верещагин"<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.684211</div><div class="">351653<span class="Apple-tab-span" style="white-space:pre"> </span>"virishchaghin"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.684211</div><div class="">217731<span class="Apple-tab-span" style="white-space:pre"> </span>„ virishchaghinskaia ul"<span class="Apple-tab-span" style="white-space:pre"> </span>"Верещагинская улица"" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.666667</div><div class="">217732<span class="Apple-tab-span" style="white-space:pre"> </span>"virishchaghinskaia ul"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.666667</div><div class="">115344<span class="Apple-tab-span" style="white-space:pre"> </span>"virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.65</div><div class="">824366<span class="Apple-tab-span" style="white-space:pre"> </span>„ v v virishchaghin"<span class="Apple-tab-span" style="white-space:pre"> </span>"В.В.Верещагин"<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.65</div><div class="">824367<span class="Apple-tab-span" style="white-space:pre"> </span>"v v virishchaghin"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.65</div><div class="">855756<span class="Apple-tab-span" style="white-space:pre"> </span>„ virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span>"Верещагино"<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.65</div><div class="">721916<span class="Apple-tab-span" style="white-space:pre"> </span>„ur virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span>""<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>" virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.636364</div><div class="">721915<span class="Apple-tab-span" style="white-space:pre"> </span>„ ur virishchaghino"<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>"ур. Верещагино“<span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-tab-span" style="white-space:pre"> </span>„ virishchaghina ul "<span class="Apple-tab-span" style="white-space:pre"> </span>0.636364</div><div class=""><br class=""></div></div></blockquote><div class="">So the first two answers with a matching of 1 (=100%) are exactly the street I asked for!</div><div class=""><br class=""></div><div class="">The same happens with the town („Ханская ст-ца“ <-> „Ханская“) and with the region („Адыгея Респ“ <-> „Адыгея“). Of course the similarity is not alway 1, but this doesn’t matter, as long as the best match is still my address. And furthermore it tells me how certain the answer is, so I can deal with the information.</div><div class=""><br class=""></div><div class="">What Sarah mentions might apply to the city („Майкоп г“ <-> „городской округ Майкоп“), where the real answer only appears as 23. result with a matching of 40%, after the „best“ (but wrong) match of 70%.</div><div class=""><br class=""></div><div class="">Maybe libpostal could help here, or the OSM data are wrong or the name I asked for. Anyway this would be acceptable because of the huge difference in spelling. It could even be healed with a clever combination of region, city, town and street.</div><div class=""><br class=""></div><div class="">So, in conclusion, to me pg_trgm looks really promising! And the query doesn’t change a lot. Sure, Nominatim would have to deal with the similarity in the response, but this doesn’t seem a huge thing, is it?</div><div class=""><br class=""></div><div class="">Kind regards,</div><div class=""><br class=""></div><div class="">Tom</div><div class=""><br class=""></div></body></html>