X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/17cb59efbd5ebec4ee44cf56999e443a7993b050..7666d484091219d5c79d6732e3cd1857b4cf6eb8:/lib-sql/functions/normalization.sql diff --git a/lib-sql/functions/normalization.sql b/lib-sql/functions/normalization.sql index 0300dab4..f283f916 100644 --- a/lib-sql/functions/normalization.sql +++ b/lib-sql/functions/normalization.sql @@ -47,6 +47,25 @@ 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 $$ +DECLARE + normtext TEXT; +BEGIN + SELECT array_to_string(array_agg(trans), ';') + INTO normtext + 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; + + return normtext; +END; +$$ LANGUAGE plpgsql STABLE STRICT; CREATE OR REPLACE FUNCTION getorcreate_housenumber_id(lookup_word TEXT) RETURNS INTEGER @@ -118,7 +137,7 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, +CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT, lookup_class text, lookup_type text) RETURNS INTEGER AS $$ @@ -128,12 +147,12 @@ DECLARE BEGIN lookup_token := ' '||trim(lookup_word); SELECT min(word_id) FROM word - WHERE word_token = lookup_token and word = lookup_word + WHERE word_token = lookup_token and word = normalized_word and class = lookup_class and type = lookup_type INTO return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word, + INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0); END IF; RETURN return_word_id; @@ -143,6 +162,7 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, + normalized_word TEXT, lookup_class text, lookup_type text, op text) @@ -154,12 +174,12 @@ DECLARE BEGIN lookup_token := ' '||trim(lookup_word); SELECT min(word_id) FROM word - WHERE word_token = lookup_token and word = lookup_word + WHERE word_token = lookup_token and word = normalized_word and class = lookup_class and type = lookup_type and operator = op INTO return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word, + INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0, op); END IF; RETURN return_word_id;