lib-sql/tokenizer/legacy_tokenizer.sql

   1 -- Get tokens used for searching the given place.
   2 --
   3 -- These are the tokens that will be saved in the search_name table.
   4 CREATE OR REPLACE FUNCTION token_get_name_search_tokens(info JSONB)
   5   RETURNS INTEGER[]
   6 AS $$
   7   SELECT (info->>'names')::INTEGER[]
   8 $$ LANGUAGE SQL IMMUTABLE STRICT;
   9
  10
  11 -- Get tokens for matching the place name against others.
  12 --
  13 -- This should usually be restricted to full name tokens.
  14 CREATE OR REPLACE FUNCTION token_get_name_match_tokens(info JSONB)
  15   RETURNS INTEGER[]
  16 AS $$
  17   SELECT (info->>'names')::INTEGER[]
  18 $$ LANGUAGE SQL IMMUTABLE STRICT;
  19
  20
  21 -- Return the housenumber tokens applicable for the place.
  22 CREATE OR REPLACE FUNCTION token_get_housenumber_search_tokens(info JSONB)
  23   RETURNS INTEGER[]
  24 AS $$
  25   SELECT (info->>'hnr_tokens')::INTEGER[]
  26 $$ LANGUAGE SQL IMMUTABLE STRICT;
  27
  28
  29 -- Return the housenumber in the form that it can be matched during search.
  30 CREATE OR REPLACE FUNCTION token_normalized_housenumber(info JSONB)
  31   RETURNS TEXT
  32 AS $$
  33   SELECT info->>'hnr';
  34 $$ LANGUAGE SQL IMMUTABLE STRICT;
  35
  36
  37 CREATE OR REPLACE FUNCTION token_addr_street_match_tokens(info JSONB)
  38   RETURNS INTEGER[]
  39 AS $$
  40   SELECT (info->>'street')::INTEGER[]
  41 $$ LANGUAGE SQL IMMUTABLE STRICT;
  42
  43
  44 CREATE OR REPLACE FUNCTION token_addr_place_match_tokens(info JSONB)
  45   RETURNS INTEGER[]
  46 AS $$
  47   SELECT (info->>'place_match')::INTEGER[]
  48 $$ LANGUAGE SQL IMMUTABLE STRICT;
  49
  50
  51 CREATE OR REPLACE FUNCTION token_addr_place_search_tokens(info JSONB)
  52   RETURNS INTEGER[]
  53 AS $$
  54   SELECT (info->>'place_search')::INTEGER[]
  55 $$ LANGUAGE SQL IMMUTABLE STRICT;
  56
  57
  58 DROP TYPE IF EXISTS token_addresstoken CASCADE;
  59 CREATE TYPE token_addresstoken AS (
  60   key TEXT,
  61   match_tokens INT[],
  62   search_tokens INT[]
  63 );
  64
  65 CREATE OR REPLACE FUNCTION token_get_address_tokens(info JSONB)
  66   RETURNS SETOF token_addresstoken
  67 AS $$
  68   SELECT key, (value->>1)::int[] as match_tokens,
  69          (value->>0)::int[] as search_tokens
  70   FROM jsonb_each(info->'addr');
  71 $$ LANGUAGE SQL IMMUTABLE STRICT;
  72
  73
  74 CREATE OR REPLACE FUNCTION token_normalized_postcode(postcode TEXT)
  75   RETURNS TEXT
  76 AS $$
  77   SELECT CASE WHEN postcode SIMILAR TO '%(,|;)%' THEN NULL ELSE upper(trim(postcode))END;
  78 $$ LANGUAGE SQL IMMUTABLE STRICT;
  79
  80
  81 -- Return token info that should be saved permanently in the database.
  82 CREATE OR REPLACE FUNCTION token_strip_info(info JSONB)
  83   RETURNS JSONB
  84 AS $$
  85   SELECT NULL::JSONB;
  86 $$ LANGUAGE SQL IMMUTABLE STRICT;
  87
  88 --------------- private functions ----------------------------------------------
  89
  90 -- Functions for term normalisation and access to the 'word' table.
  91
  92 CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text
  93   AS '{{ modulepath }}/nominatim.so', 'transliteration'
  94 LANGUAGE c IMMUTABLE STRICT;
  95
  96
  97 CREATE OR REPLACE FUNCTION gettokenstring(text) RETURNS text
  98   AS '{{ modulepath }}/nominatim.so', 'gettokenstring'
  99 LANGUAGE c IMMUTABLE STRICT;
 100
 101
 102 CREATE OR REPLACE FUNCTION make_standard_name(name TEXT) RETURNS TEXT
 103   AS $$
 104 DECLARE
 105   o TEXT;
 106 BEGIN
 107   o := public.gettokenstring(public.transliteration(name));
 108   RETURN trim(substr(o,1,length(o)));
 109 END;
 110 $$
 111 LANGUAGE plpgsql IMMUTABLE;
 112
 113 -- returns NULL if the word is too common
 114 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT)
 115   RETURNS INTEGER
 116   AS $$
 117 DECLARE
 118   lookup_token TEXT;
 119   return_word_id INTEGER;
 120   count INTEGER;
 121 BEGIN
 122   lookup_token := trim(lookup_word);
 123   SELECT min(word_id), max(search_name_count) FROM word
 124     WHERE word_token = lookup_token and class is null and type is null
 125     INTO return_word_id, count;
 126   IF return_word_id IS NULL THEN
 127     return_word_id := nextval('seq_word');
 128     INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);
 129   ELSE
 130     IF count > {{ max_word_freq }} THEN
 131       return_word_id := NULL;
 132     END IF;
 133   END IF;
 134   RETURN return_word_id;
 135 END;
 136 $$
 137 LANGUAGE plpgsql;
 138
 139
 140 -- Create housenumber tokens from an OSM addr:housenumber.
 141 -- The housnumber is split at comma and semicolon as necessary.
 142 -- The function returns the normalized form of the housenumber suitable
 143 -- for comparison.
 144 CREATE OR REPLACE FUNCTION create_housenumbers(housenumbers TEXT[],
 145                                                OUT tokens TEXT,
 146                                                OUT normtext TEXT)
 147   AS $$
 148 BEGIN
 149   SELECT array_to_string(array_agg(trans), ';'), array_agg(tid)::TEXT
 150     INTO normtext, tokens
 151     FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word) as tid
 152           FROM (SELECT make_standard_name(h) as lookup_word
 153                 FROM unnest(housenumbers) h) x) y;
 154 END;
 155 $$ LANGUAGE plpgsql STABLE STRICT;
 156
 157
 158 CREATE OR REPLACE FUNCTION getorcreate_housenumber_id(lookup_word TEXT)
 159   RETURNS INTEGER
 160   AS $$
 161 DECLARE
 162   lookup_token TEXT;
 163   return_word_id INTEGER;
 164 BEGIN
 165   lookup_token := ' ' || trim(lookup_word);
 166   SELECT min(word_id) FROM word
 167     WHERE word_token = lookup_token and class='place' and type='house'
 168     INTO return_word_id;
 169   IF return_word_id IS NULL THEN
 170     return_word_id := nextval('seq_word');
 171     INSERT INTO word VALUES (return_word_id, lookup_token, null,
 172                              'place', 'house', null, 0);
 173   END IF;
 174   RETURN return_word_id;
 175 END;
 176 $$
 177 LANGUAGE plpgsql;
 178
 179
 180 CREATE OR REPLACE FUNCTION create_postcode_id(postcode TEXT)
 181   RETURNS BOOLEAN
 182   AS $$
 183 DECLARE
 184   r RECORD;
 185   lookup_token TEXT;
 186   return_word_id INTEGER;
 187 BEGIN
 188   lookup_token := ' ' || make_standard_name(postcode);
 189   FOR r IN
 190     SELECT word_id FROM word
 191     WHERE word_token = lookup_token and word = postcode
 192           and class='place' and type='postcode'
 193   LOOP
 194     RETURN false;
 195   END LOOP;
 196
 197   INSERT INTO word VALUES (nextval('seq_word'), lookup_token, postcode,
 198                            'place', 'postcode', null, 0);
 199   RETURN true;
 200 END;
 201 $$
 202 LANGUAGE plpgsql;
 203
 204
 205 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT,
 206                                                lookup_country_code varchar(2))
 207   RETURNS INTEGER
 208   AS $$
 209 DECLARE
 210   lookup_token TEXT;
 211   return_word_id INTEGER;
 212 BEGIN
 213   lookup_token := ' '||trim(lookup_word);
 214   SELECT min(word_id) FROM word
 215     WHERE word_token = lookup_token and country_code=lookup_country_code
 216     INTO return_word_id;
 217   IF return_word_id IS NULL THEN
 218     return_word_id := nextval('seq_word');
 219     INSERT INTO word VALUES (return_word_id, lookup_token, null,
 220                              null, null, lookup_country_code, 0);
 221   END IF;
 222   RETURN return_word_id;
 223 END;
 224 $$
 225 LANGUAGE plpgsql;
 226
 227
 228 CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT,
 229                                                lookup_class text, lookup_type text)
 230   RETURNS INTEGER
 231   AS $$
 232 DECLARE
 233   lookup_token TEXT;
 234   return_word_id INTEGER;
 235 BEGIN
 236   lookup_token := ' '||trim(lookup_word);
 237   SELECT min(word_id) FROM word
 238   WHERE word_token = lookup_token and word = normalized_word
 239         and class = lookup_class and type = lookup_type
 240   INTO return_word_id;
 241   IF return_word_id IS NULL THEN
 242     return_word_id := nextval('seq_word');
 243     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 244                              lookup_class, lookup_type, null, 0);
 245   END IF;
 246   RETURN return_word_id;
 247 END;
 248 $$
 249 LANGUAGE plpgsql;
 250
 251
 252 CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT,
 253                                                        normalized_word TEXT,
 254                                                        lookup_class text,
 255                                                        lookup_type text,
 256                                                        op text)
 257   RETURNS INTEGER
 258   AS $$
 259 DECLARE
 260   lookup_token TEXT;
 261   return_word_id INTEGER;
 262 BEGIN
 263   lookup_token := ' '||trim(lookup_word);
 264   SELECT min(word_id) FROM word
 265   WHERE word_token = lookup_token and word = normalized_word
 266         and class = lookup_class and type = lookup_type and operator = op
 267   INTO return_word_id;
 268   IF return_word_id IS NULL THEN
 269     return_word_id := nextval('seq_word');
 270     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 271                              lookup_class, lookup_type, null, 0, op);
 272   END IF;
 273   RETURN return_word_id;
 274 END;
 275 $$
 276 LANGUAGE plpgsql;
 277
 278
 279 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT)
 280   RETURNS INTEGER
 281   AS $$
 282 DECLARE
 283   lookup_token TEXT;
 284   nospace_lookup_token TEXT;
 285   return_word_id INTEGER;
 286 BEGIN
 287   lookup_token := ' '||trim(lookup_word);
 288   SELECT min(word_id) FROM word
 289   WHERE word_token = lookup_token and class is null and type is null
 290   INTO return_word_id;
 291   IF return_word_id IS NULL THEN
 292     return_word_id := nextval('seq_word');
 293     INSERT INTO word VALUES (return_word_id, lookup_token, src_word,
 294                              null, null, null, 0);
 295   END IF;
 296   RETURN return_word_id;
 297 END;
 298 $$
 299 LANGUAGE plpgsql;
 300
 301
 302 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT)
 303   RETURNS INTEGER
 304   AS $$
 305 DECLARE
 306 BEGIN
 307   RETURN getorcreate_name_id(lookup_word, '');
 308 END;
 309 $$
 310 LANGUAGE plpgsql;
 311
 312 -- Normalize a string and lookup its word ids (partial words).
 313 CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
 314   RETURNS INTEGER[]
 315   AS $$
 316 DECLARE
 317   words TEXT[];
 318   id INTEGER;
 319   return_word_id INTEGER[];
 320   word_ids INTEGER[];
 321   j INTEGER;
 322 BEGIN
 323   words := string_to_array(make_standard_name(lookup_word), ' ');
 324   IF array_upper(words, 1) IS NOT NULL THEN
 325     FOR j IN 1..array_upper(words, 1) LOOP
 326       IF (words[j] != '') THEN
 327         SELECT array_agg(word_id) INTO word_ids
 328           FROM word
 329          WHERE word_token = words[j] and class is null and type is null;
 330
 331         IF word_ids IS NULL THEN
 332           id := nextval('seq_word');
 333           INSERT INTO word VALUES (id, words[j], null, null, null, null, 0);
 334           return_word_id := return_word_id || id;
 335         ELSE
 336           return_word_id := array_merge(return_word_id, word_ids);
 337         END IF;
 338       END IF;
 339     END LOOP;
 340   END IF;
 341
 342   RETURN return_word_id;
 343 END;
 344 $$
 345 LANGUAGE plpgsql;
 346
 347
 348 -- Normalize a string and look up its name ids (full words).
 349 CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
 350   RETURNS INTEGER[]
 351   AS $$
 352 DECLARE
 353   lookup_token TEXT;
 354   return_word_ids INTEGER[];
 355 BEGIN
 356   lookup_token := ' '|| make_standard_name(lookup_word);
 357   SELECT array_agg(word_id) FROM word
 358     WHERE word_token = lookup_token and class is null and type is null
 359     INTO return_word_ids;
 360   RETURN return_word_ids;
 361 END;
 362 $$
 363 LANGUAGE plpgsql STABLE STRICT;
 364
 365
 366 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))
 367   RETURNS VOID
 368   AS $$
 369 DECLARE
 370   s TEXT;
 371   w INTEGER;
 372   words TEXT[];
 373   item RECORD;
 374   j INTEGER;
 375 BEGIN
 376   FOR item IN SELECT (each(src)).* LOOP
 377
 378     s := make_standard_name(item.value);
 379     w := getorcreate_country(s, country_code);
 380
 381     words := regexp_split_to_array(item.value, E'[,;()]');
 382     IF array_upper(words, 1) != 1 THEN
 383       FOR j IN 1..array_upper(words, 1) LOOP
 384         s := make_standard_name(words[j]);
 385         IF s != '' THEN
 386           w := getorcreate_country(s, country_code);
 387         END IF;
 388       END LOOP;
 389     END IF;
 390   END LOOP;
 391 END;
 392 $$
 393 LANGUAGE plpgsql;
 394
 395
 396 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
 397   RETURNS INTEGER[]
 398   AS $$
 399 DECLARE
 400   result INTEGER[];
 401   s TEXT;
 402   w INTEGER;
 403   words TEXT[];
 404   item RECORD;
 405   j INTEGER;
 406 BEGIN
 407   result := '{}'::INTEGER[];
 408
 409   FOR item IN SELECT (each(src)).* LOOP
 410
 411     s := make_standard_name(item.value);
 412     w := getorcreate_name_id(s, item.value);
 413
 414     IF not(ARRAY[w] <@ result) THEN
 415       result := result || w;
 416     END IF;
 417
 418     w := getorcreate_word_id(s);
 419
 420     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 421       result := result || w;
 422     END IF;
 423
 424     words := string_to_array(s, ' ');
 425     IF array_upper(words, 1) IS NOT NULL THEN
 426       FOR j IN 1..array_upper(words, 1) LOOP
 427         IF (words[j] != '') THEN
 428           w = getorcreate_word_id(words[j]);
 429           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 430             result := result || w;
 431           END IF;
 432         END IF;
 433       END LOOP;
 434     END IF;
 435
 436     words := regexp_split_to_array(item.value, E'[,;()]');
 437     IF array_upper(words, 1) != 1 THEN
 438       FOR j IN 1..array_upper(words, 1) LOOP
 439         s := make_standard_name(words[j]);
 440         IF s != '' THEN
 441           w := getorcreate_word_id(s);
 442           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 443             result := result || w;
 444           END IF;
 445         END IF;
 446       END LOOP;
 447     END IF;
 448
 449     s := regexp_replace(item.value, '市$', '');
 450     IF s != item.value THEN
 451       s := make_standard_name(s);
 452       IF s != '' THEN
 453         w := getorcreate_name_id(s, item.value);
 454         IF NOT (ARRAY[w] <@ result) THEN
 455           result := result || w;
 456         END IF;
 457       END IF;
 458     END IF;
 459
 460   END LOOP;
 461
 462   RETURN result;
 463 END;
 464 $$
 465 LANGUAGE plpgsql;
 466
 467
 468 CREATE OR REPLACE FUNCTION precompute_words(src TEXT)
 469   RETURNS INTEGER
 470   AS $$
 471 DECLARE
 472   s TEXT;
 473   w INTEGER;
 474   words TEXT[];
 475   i INTEGER;
 476   j INTEGER;
 477 BEGIN
 478   s := make_standard_name(src);
 479   w := getorcreate_name_id(s, src);
 480
 481   w := getorcreate_word_id(s);
 482
 483   words := string_to_array(s, ' ');
 484   IF array_upper(words, 1) IS NOT NULL THEN
 485     FOR j IN 1..array_upper(words, 1) LOOP
 486       IF (words[j] != '') THEN
 487         w := getorcreate_word_id(words[j]);
 488       END IF;
 489     END LOOP;
 490   END IF;
 491
 492   words := regexp_split_to_array(src, E'[,;()]');
 493   IF array_upper(words, 1) != 1 THEN
 494     FOR j IN 1..array_upper(words, 1) LOOP
 495       s := make_standard_name(words[j]);
 496       IF s != '' THEN
 497         w := getorcreate_word_id(s);
 498       END IF;
 499     END LOOP;
 500   END IF;
 501
 502   s := regexp_replace(src, '市$', '');
 503   IF s != src THEN
 504     s := make_standard_name(s);
 505     IF s != '' THEN
 506       w := getorcreate_name_id(s, src);
 507     END IF;
 508   END IF;
 509
 510   RETURN 1;
 511 END;
 512 $$
 513 LANGUAGE plpgsql;