X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/bef300305e3c445fe483b806b838ad8ce5b689f6..bd0316b5c3c0e84da952ce63bf7e51c65c6e1433:/lib-sql/tokenizer/legacy_tokenizer.sql diff --git a/lib-sql/tokenizer/legacy_tokenizer.sql b/lib-sql/tokenizer/legacy_tokenizer.sql index 2bbf3ed7..c21d0510 100644 --- a/lib-sql/tokenizer/legacy_tokenizer.sql +++ b/lib-sql/tokenizer/legacy_tokenizer.sql @@ -1,3 +1,10 @@ +-- SPDX-License-Identifier: GPL-2.0-only +-- +-- This file is part of Nominatim. (https://nominatim.org) +-- +-- Copyright (C) 2022 by the Nominatim developer community. +-- For a full list of authors see the git log. + -- Get tokens used for searching the given place. -- -- These are the tokens that will be saved in the search_name table. @@ -34,17 +41,38 @@ AS $$ $$ LANGUAGE SQL IMMUTABLE STRICT; -CREATE OR REPLACE FUNCTION token_addr_street_match_tokens(info JSONB) - RETURNS INTEGER[] +CREATE OR REPLACE FUNCTION token_is_street_address(info JSONB) + RETURNS BOOLEAN +AS $$ + SELECT info->>'street' is not null or info->>'place_search' is null; +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION token_has_addr_street(info JSONB) + RETURNS BOOLEAN +AS $$ + SELECT info->>'street' is not null and info->>'street' != '{}'; +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION token_has_addr_place(info JSONB) + RETURNS BOOLEAN +AS $$ + SELECT info->>'place_match' is not null; +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION token_matches_street(info JSONB, street_tokens INTEGER[]) + RETURNS BOOLEAN AS $$ - SELECT (info->>'street')::INTEGER[] + SELECT (info->>'street')::INTEGER[] && street_tokens $$ LANGUAGE SQL IMMUTABLE STRICT; -CREATE OR REPLACE FUNCTION token_addr_place_match_tokens(info JSONB) - RETURNS INTEGER[] +CREATE OR REPLACE FUNCTION token_matches_place(info JSONB, place_tokens INTEGER[]) + RETURNS BOOLEAN AS $$ - SELECT (info->>'place_match')::INTEGER[] + SELECT (info->>'place_match')::INTEGER[] && place_tokens $$ LANGUAGE SQL IMMUTABLE STRICT; @@ -55,26 +83,31 @@ AS $$ $$ LANGUAGE SQL IMMUTABLE STRICT; -DROP TYPE IF EXISTS token_addresstoken CASCADE; -CREATE TYPE token_addresstoken AS ( - key TEXT, - match_tokens INT[], - search_tokens INT[] -); +CREATE OR REPLACE FUNCTION token_get_address_keys(info JSONB) + RETURNS SETOF TEXT +AS $$ + SELECT * FROM jsonb_object_keys(info->'addr'); +$$ LANGUAGE SQL IMMUTABLE STRICT; + -CREATE OR REPLACE FUNCTION token_get_address_tokens(info JSONB) - RETURNS SETOF token_addresstoken +CREATE OR REPLACE FUNCTION token_get_address_search_tokens(info JSONB, key TEXT) + RETURNS INTEGER[] +AS $$ + SELECT (info->'addr'->key->>0)::INTEGER[]; +$$ LANGUAGE SQL IMMUTABLE STRICT; + + +CREATE OR REPLACE FUNCTION token_matches_address(info JSONB, key TEXT, tokens INTEGER[]) + RETURNS BOOLEAN AS $$ - SELECT key, (value->>1)::int[] as match_tokens, - (value->>0)::int[] as search_tokens - FROM jsonb_each(info->'addr'); + SELECT (info->'addr'->key->>1)::INTEGER[] && tokens; $$ LANGUAGE SQL IMMUTABLE STRICT; -CREATE OR REPLACE FUNCTION token_normalized_postcode(postcode TEXT) +CREATE OR REPLACE FUNCTION token_get_postcode(info JSONB) RETURNS TEXT AS $$ - SELECT CASE WHEN postcode SIMILAR TO '%(,|;)%' THEN NULL ELSE upper(trim(postcode))END; + SELECT info->>'postcode'; $$ LANGUAGE SQL IMMUTABLE STRICT; @@ -202,57 +235,6 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT, - lookup_class text, lookup_type text) - RETURNS INTEGER - AS $$ -DECLARE - lookup_token TEXT; - return_word_id INTEGER; -BEGIN - lookup_token := ' '||trim(lookup_word); - SELECT min(word_id) FROM 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, normalized_word, - lookup_class, lookup_type, null, 0); - END IF; - RETURN return_word_id; -END; -$$ -LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, - normalized_word TEXT, - lookup_class text, - lookup_type text, - op text) - RETURNS INTEGER - AS $$ -DECLARE - lookup_token TEXT; - return_word_id INTEGER; -BEGIN - lookup_token := ' '||trim(lookup_word); - SELECT min(word_id) FROM 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, normalized_word, - lookup_class, lookup_type, null, 0, op); - END IF; - RETURN return_word_id; -END; -$$ -LANGUAGE plpgsql; - - CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT) RETURNS INTEGER AS $$ @@ -276,16 +258,6 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT) - RETURNS INTEGER - AS $$ -DECLARE -BEGIN - RETURN getorcreate_name_id(lookup_word, ''); -END; -$$ -LANGUAGE plpgsql; - -- Normalize a string and lookup its word ids (partial words). CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT) RETURNS INTEGER[] @@ -348,26 +320,21 @@ DECLARE s TEXT; w INTEGER; words TEXT[]; - item RECORD; + value TEXT; j INTEGER; BEGIN result := '{}'::INTEGER[]; - FOR item IN SELECT (each(src)).* LOOP - - s := make_standard_name(item.value); - w := getorcreate_name_id(s, item.value); + FOR value IN SELECT unnest(regexp_split_to_array(svals(src), E'[,;]')) LOOP + -- full name + s := make_standard_name(value); + w := getorcreate_name_id(s, value); IF not(ARRAY[w] <@ result) THEN result := result || w; END IF; - w := getorcreate_word_id(s); - - IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN - result := result || w; - END IF; - + -- partial single-word terms words := string_to_array(s, ' '); IF array_upper(words, 1) IS NOT NULL THEN FOR j IN 1..array_upper(words, 1) LOOP @@ -380,24 +347,23 @@ BEGIN END LOOP; END IF; - words := regexp_split_to_array(item.value, E'[,;()]'); - IF array_upper(words, 1) != 1 THEN - FOR j IN 1..array_upper(words, 1) LOOP - s := make_standard_name(words[j]); - IF s != '' THEN - w := getorcreate_word_id(s); - IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN - result := result || w; - END IF; + -- consider parts before an opening bracket a full word as well + words := regexp_split_to_array(value, E'[(]'); + IF array_upper(words, 1) > 1 THEN + s := make_standard_name(words[1]); + IF s != '' THEN + w := getorcreate_name_id(s, words[1]); + IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN + result := result || w; END IF; - END LOOP; + END IF; END IF; - s := regexp_replace(item.value, '市$', ''); - IF s != item.value THEN + s := regexp_replace(value, '市$', ''); + IF s != value THEN s := make_standard_name(s); IF s != '' THEN - w := getorcreate_name_id(s, item.value); + w := getorcreate_name_id(s, value); IF NOT (ARRAY[w] <@ result) THEN result := result || w; END IF;