From 6cbef84cad0d08221d4a386cb5c3db19447ddede Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 1 Apr 2021 18:52:06 +0200 Subject: [PATCH] use new transliteration in initial housenumber word computation The new create_housenumber_id() function splits housenumber lists correctly. Otherwise there is no difference. --- lib-sql/functions/normalization.sql | 7 +++++-- lib-sql/functions/placex_triggers.sql | 2 +- lib-sql/words.sql | 2 +- 3 files changed, 7 insertions(+), 4 deletions(-) diff --git a/lib-sql/functions/normalization.sql b/lib-sql/functions/normalization.sql index aca793c5..f283f916 100644 --- a/lib-sql/functions/normalization.sql +++ b/lib-sql/functions/normalization.sql @@ -47,7 +47,10 @@ END; $$ LANGUAGE plpgsql; - +-- Create housenumber tokens from an OSM addr:housenumber. +-- The housnumber is split at comma and semicolon as necessary. +-- The function returns the normalized form of the housenumber suitable +-- for comparison. CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT) RETURNS TEXT AS $$ @@ -56,7 +59,7 @@ DECLARE BEGIN SELECT array_to_string(array_agg(trans), ';') INTO normtext - FROM (SELECT transliteration(lookup_word) as trans, getorcreate_housenumber_id(lookup_word) + FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word) FROM (SELECT make_standard_name(h) as lookup_word FROM regexp_split_to_table(housenumber, '[,;]') h) x) y; diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index b47758df..6998224e 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -667,7 +667,7 @@ BEGIN IF NEW.address is not NULL THEN IF NEW.address ? 'conscriptionnumber' THEN IF NEW.address ? 'streetnumber' THEN - NEW.housenumber := (NEW.address->'conscriptionnumber') || ';' || (NEW.address->'streetnumber'); + NEW.housenumber := (NEW.address->'conscriptionnumber') || '/' || (NEW.address->'streetnumber'); ELSE NEW.housenumber := NEW.address->'conscriptionnumber'; END IF; diff --git a/lib-sql/words.sql b/lib-sql/words.sql index ac379221..8be17814 100644 --- a/lib-sql/words.sql +++ b/lib-sql/words.sql @@ -5,7 +5,7 @@ CREATE TABLE word_frequencies AS GROUP BY id); select count(getorcreate_postcode_id(v)) from (select distinct address->'postcode' as v from place where address ? 'postcode') as w where v is not null; -select count(getorcreate_housenumber_id(make_standard_name(v))) from (select distinct address->'housenumber' as v from place where address ? 'housenumber') as w; +select count(create_housenumber_id(v)) from (select distinct address->'housenumber' as v from place where address ? 'housenumber') as w; -- copy the word frequencies update word set search_name_count = count from word_frequencies wf where wf.id = word.word_id; -- 2.39.5