lib-sql/functions/normalization.sql

   1 -- Functions for term normalisation and access to the 'word' table.
   2
   3 CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text
   4   AS '{{ modulepath }}/nominatim.so', 'transliteration'
   5 LANGUAGE c IMMUTABLE STRICT;
   6
   7
   8 CREATE OR REPLACE FUNCTION gettokenstring(text) RETURNS text
   9   AS '{{ modulepath }}/nominatim.so', 'gettokenstring'
  10 LANGUAGE c IMMUTABLE STRICT;
  11
  12
  13 CREATE OR REPLACE FUNCTION make_standard_name(name TEXT) RETURNS TEXT
  14   AS $$
  15 DECLARE
  16   o TEXT;
  17 BEGIN
  18   o := public.gettokenstring(public.transliteration(name));
  19   RETURN trim(substr(o,1,length(o)));
  20 END;
  21 $$
  22 LANGUAGE plpgsql IMMUTABLE;
  23
  24 -- returns NULL if the word is too common
  25 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT)
  26   RETURNS INTEGER
  27   AS $$
  28 DECLARE
  29   lookup_token TEXT;
  30   return_word_id INTEGER;
  31   count INTEGER;
  32 BEGIN
  33   lookup_token := trim(lookup_word);
  34   SELECT min(word_id), max(search_name_count) FROM word
  35     WHERE word_token = lookup_token and class is null and type is null
  36     INTO return_word_id, count;
  37   IF return_word_id IS NULL THEN
  38     return_word_id := nextval('seq_word');
  39     INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);
  40   ELSE
  41     IF count > get_maxwordfreq() THEN
  42       return_word_id := NULL;
  43     END IF;
  44   END IF;
  45   RETURN return_word_id;
  46 END;
  47 $$
  48 LANGUAGE plpgsql;
  49
  50
  51 CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)
  52   RETURNS TEXT
  53   AS $$
  54 DECLARE
  55   normtext TEXT;
  56 BEGIN
  57   SELECT array_to_string(array_agg(trans), ';')
  58     INTO normtext
  59     FROM (SELECT transliteration(lookup_word) as trans, getorcreate_housenumber_id(lookup_word)
  60           FROM (SELECT make_standard_name(h) as lookup_word
  61                 FROM regexp_split_to_table(housenumber, '[,;]') h) x) y;
  62
  63   return normtext;
  64 END;
  65 $$ LANGUAGE plpgsql STABLE STRICT;
  66
  67 CREATE OR REPLACE FUNCTION getorcreate_housenumber_id(lookup_word TEXT)
  68   RETURNS INTEGER
  69   AS $$
  70 DECLARE
  71   lookup_token TEXT;
  72   return_word_id INTEGER;
  73 BEGIN
  74   lookup_token := ' ' || trim(lookup_word);
  75   SELECT min(word_id) FROM word
  76     WHERE word_token = lookup_token and class='place' and type='house'
  77     INTO return_word_id;
  78   IF return_word_id IS NULL THEN
  79     return_word_id := nextval('seq_word');
  80     INSERT INTO word VALUES (return_word_id, lookup_token, null,
  81                              'place', 'house', null, 0);
  82   END IF;
  83   RETURN return_word_id;
  84 END;
  85 $$
  86 LANGUAGE plpgsql;
  87
  88
  89 CREATE OR REPLACE FUNCTION getorcreate_postcode_id(postcode TEXT)
  90   RETURNS INTEGER
  91   AS $$
  92 DECLARE
  93   lookup_token TEXT;
  94   lookup_word TEXT;
  95   return_word_id INTEGER;
  96 BEGIN
  97   lookup_word := upper(trim(postcode));
  98   lookup_token := ' ' || make_standard_name(lookup_word);
  99   SELECT min(word_id) FROM word
 100     WHERE word_token = lookup_token and word = lookup_word
 101           and class='place' and type='postcode'
 102     INTO return_word_id;
 103   IF return_word_id IS NULL THEN
 104     return_word_id := nextval('seq_word');
 105     INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word,
 106                              'place', 'postcode', null, 0);
 107   END IF;
 108   RETURN return_word_id;
 109 END;
 110 $$
 111 LANGUAGE plpgsql;
 112
 113
 114 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT,
 115                                                lookup_country_code varchar(2))
 116   RETURNS INTEGER
 117   AS $$
 118 DECLARE
 119   lookup_token TEXT;
 120   return_word_id INTEGER;
 121 BEGIN
 122   lookup_token := ' '||trim(lookup_word);
 123   SELECT min(word_id) FROM word
 124     WHERE word_token = lookup_token and country_code=lookup_country_code
 125     INTO return_word_id;
 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,
 129                              null, null, lookup_country_code, 0);
 130   END IF;
 131   RETURN return_word_id;
 132 END;
 133 $$
 134 LANGUAGE plpgsql;
 135
 136
 137 CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT,
 138                                                lookup_class text, lookup_type text)
 139   RETURNS INTEGER
 140   AS $$
 141 DECLARE
 142   lookup_token TEXT;
 143   return_word_id INTEGER;
 144 BEGIN
 145   lookup_token := ' '||trim(lookup_word);
 146   SELECT min(word_id) FROM word
 147   WHERE word_token = lookup_token and word = normalized_word
 148         and class = lookup_class and type = lookup_type
 149   INTO return_word_id;
 150   IF return_word_id IS NULL THEN
 151     return_word_id := nextval('seq_word');
 152     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 153                              lookup_class, lookup_type, null, 0);
 154   END IF;
 155   RETURN return_word_id;
 156 END;
 157 $$
 158 LANGUAGE plpgsql;
 159
 160
 161 CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT,
 162                                                        normalized_word TEXT,
 163                                                        lookup_class text,
 164                                                        lookup_type text,
 165                                                        op text)
 166   RETURNS INTEGER
 167   AS $$
 168 DECLARE
 169   lookup_token TEXT;
 170   return_word_id INTEGER;
 171 BEGIN
 172   lookup_token := ' '||trim(lookup_word);
 173   SELECT min(word_id) FROM word
 174   WHERE word_token = lookup_token and word = normalized_word
 175         and class = lookup_class and type = lookup_type and operator = op
 176   INTO return_word_id;
 177   IF return_word_id IS NULL THEN
 178     return_word_id := nextval('seq_word');
 179     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 180                              lookup_class, lookup_type, null, 0, op);
 181   END IF;
 182   RETURN return_word_id;
 183 END;
 184 $$
 185 LANGUAGE plpgsql;
 186
 187
 188 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT)
 189   RETURNS INTEGER
 190   AS $$
 191 DECLARE
 192   lookup_token TEXT;
 193   nospace_lookup_token TEXT;
 194   return_word_id INTEGER;
 195 BEGIN
 196   lookup_token := ' '||trim(lookup_word);
 197   SELECT min(word_id) FROM word
 198   WHERE word_token = lookup_token and class is null and type is null
 199   INTO return_word_id;
 200   IF return_word_id IS NULL THEN
 201     return_word_id := nextval('seq_word');
 202     INSERT INTO word VALUES (return_word_id, lookup_token, src_word,
 203                              null, null, null, 0);
 204   END IF;
 205   RETURN return_word_id;
 206 END;
 207 $$
 208 LANGUAGE plpgsql;
 209
 210
 211 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT)
 212   RETURNS INTEGER
 213   AS $$
 214 DECLARE
 215 BEGIN
 216   RETURN getorcreate_name_id(lookup_word, '');
 217 END;
 218 $$
 219 LANGUAGE plpgsql;
 220
 221 -- Normalize a string and lookup its word ids (partial words).
 222 CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
 223   RETURNS INTEGER[]
 224   AS $$
 225 DECLARE
 226   words TEXT[];
 227   id INTEGER;
 228   return_word_id INTEGER[];
 229   word_ids INTEGER[];
 230   j INTEGER;
 231 BEGIN
 232   words := string_to_array(make_standard_name(lookup_word), ' ');
 233   IF array_upper(words, 1) IS NOT NULL THEN
 234     FOR j IN 1..array_upper(words, 1) LOOP
 235       IF (words[j] != '') THEN
 236         SELECT array_agg(word_id) INTO word_ids
 237           FROM word
 238          WHERE word_token = words[j] and class is null and type is null;
 239
 240         IF word_ids IS NULL THEN
 241           id := nextval('seq_word');
 242           INSERT INTO word VALUES (id, words[j], null, null, null, null, 0);
 243           return_word_id := return_word_id || id;
 244         ELSE
 245           return_word_id := array_merge(return_word_id, word_ids);
 246         END IF;
 247       END IF;
 248     END LOOP;
 249   END IF;
 250
 251   RETURN return_word_id;
 252 END;
 253 $$
 254 LANGUAGE plpgsql;
 255
 256
 257 -- Normalize a string and look up its name ids (full words).
 258 CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
 259   RETURNS INTEGER[]
 260   AS $$
 261 DECLARE
 262   lookup_token TEXT;
 263   return_word_ids INTEGER[];
 264 BEGIN
 265   lookup_token := ' '|| make_standard_name(lookup_word);
 266   SELECT array_agg(word_id) FROM word
 267     WHERE word_token = lookup_token and class is null and type is null
 268     INTO return_word_ids;
 269   RETURN return_word_ids;
 270 END;
 271 $$
 272 LANGUAGE plpgsql STABLE STRICT;
 273
 274
 275 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))
 276   RETURNS VOID
 277   AS $$
 278 DECLARE
 279   s TEXT;
 280   w INTEGER;
 281   words TEXT[];
 282   item RECORD;
 283   j INTEGER;
 284 BEGIN
 285   FOR item IN SELECT (each(src)).* LOOP
 286
 287     s := make_standard_name(item.value);
 288     w := getorcreate_country(s, country_code);
 289
 290     words := regexp_split_to_array(item.value, E'[,;()]');
 291     IF array_upper(words, 1) != 1 THEN
 292       FOR j IN 1..array_upper(words, 1) LOOP
 293         s := make_standard_name(words[j]);
 294         IF s != '' THEN
 295           w := getorcreate_country(s, country_code);
 296         END IF;
 297       END LOOP;
 298     END IF;
 299   END LOOP;
 300 END;
 301 $$
 302 LANGUAGE plpgsql;
 303
 304
 305 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
 306   RETURNS INTEGER[]
 307   AS $$
 308 DECLARE
 309   result INTEGER[];
 310   s TEXT;
 311   w INTEGER;
 312   words TEXT[];
 313   item RECORD;
 314   j INTEGER;
 315 BEGIN
 316   result := '{}'::INTEGER[];
 317
 318   FOR item IN SELECT (each(src)).* LOOP
 319
 320     s := make_standard_name(item.value);
 321     w := getorcreate_name_id(s, item.value);
 322
 323     IF not(ARRAY[w] <@ result) THEN
 324       result := result || w;
 325     END IF;
 326
 327     w := getorcreate_word_id(s);
 328
 329     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 330       result := result || w;
 331     END IF;
 332
 333     words := string_to_array(s, ' ');
 334     IF array_upper(words, 1) IS NOT NULL THEN
 335       FOR j IN 1..array_upper(words, 1) LOOP
 336         IF (words[j] != '') THEN
 337           w = getorcreate_word_id(words[j]);
 338           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 339             result := result || w;
 340           END IF;
 341         END IF;
 342       END LOOP;
 343     END IF;
 344
 345     words := regexp_split_to_array(item.value, E'[,;()]');
 346     IF array_upper(words, 1) != 1 THEN
 347       FOR j IN 1..array_upper(words, 1) LOOP
 348         s := make_standard_name(words[j]);
 349         IF s != '' THEN
 350           w := getorcreate_word_id(s);
 351           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 352             result := result || w;
 353           END IF;
 354         END IF;
 355       END LOOP;
 356     END IF;
 357
 358     s := regexp_replace(item.value, '市$', '');
 359     IF s != item.value THEN
 360       s := make_standard_name(s);
 361       IF s != '' THEN
 362         w := getorcreate_name_id(s, item.value);
 363         IF NOT (ARRAY[w] <@ result) THEN
 364           result := result || w;
 365         END IF;
 366       END IF;
 367     END IF;
 368
 369   END LOOP;
 370
 371   RETURN result;
 372 END;
 373 $$
 374 LANGUAGE plpgsql;
 375
 376
 377 CREATE OR REPLACE FUNCTION make_keywords(src TEXT)
 378   RETURNS INTEGER[]
 379   AS $$
 380 DECLARE
 381   result INTEGER[];
 382   s TEXT;
 383   w INTEGER;
 384   words TEXT[];
 385   i INTEGER;
 386   j INTEGER;
 387 BEGIN
 388   result := '{}'::INTEGER[];
 389
 390   s := make_standard_name(src);
 391   w := getorcreate_name_id(s, src);
 392
 393   IF NOT (ARRAY[w] <@ result) THEN
 394     result := result || w;
 395   END IF;
 396
 397   w := getorcreate_word_id(s);
 398
 399   IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 400     result := result || w;
 401   END IF;
 402
 403   words := string_to_array(s, ' ');
 404   IF array_upper(words, 1) IS NOT NULL THEN
 405     FOR j IN 1..array_upper(words, 1) LOOP
 406       IF (words[j] != '') THEN
 407         w = getorcreate_word_id(words[j]);
 408         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 409           result := result || w;
 410         END IF;
 411       END IF;
 412     END LOOP;
 413   END IF;
 414
 415   words := regexp_split_to_array(src, E'[,;()]');
 416   IF array_upper(words, 1) != 1 THEN
 417     FOR j IN 1..array_upper(words, 1) LOOP
 418       s := make_standard_name(words[j]);
 419       IF s != '' THEN
 420         w := getorcreate_word_id(s);
 421         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 422           result := result || w;
 423         END IF;
 424       END IF;
 425     END LOOP;
 426   END IF;
 427
 428   s := regexp_replace(src, '市$', '');
 429   IF s != src THEN
 430     s := make_standard_name(s);
 431     IF s != '' THEN
 432       w := getorcreate_name_id(s, src);
 433       IF NOT (ARRAY[w] <@ result) THEN
 434         result := result || w;
 435       END IF;
 436     END IF;
 437   END IF;
 438
 439   RETURN result;
 440 END;
 441 $$
 442 LANGUAGE plpgsql;
 443
 444
 445 CREATE OR REPLACE FUNCTION create_poi_search_terms(obj_place_id BIGINT,
 446                                                    in_partition SMALLINT,
 447                                                    parent_place_id BIGINT,
 448                                                    address HSTORE,
 449                                                    country TEXT,
 450                                                    housenumber TEXT,
 451                                                    initial_name_vector INTEGER[],
 452                                                    geometry GEOMETRY,
 453                                                    OUT name_vector INTEGER[],
 454                                                    OUT nameaddress_vector INTEGER[])
 455   AS $$
 456 DECLARE
 457   parent_name_vector INTEGER[];
 458   parent_address_vector INTEGER[];
 459   addr_place_ids INTEGER[];
 460
 461   addr_item RECORD;
 462   parent_address_place_ids BIGINT[];
 463   filtered_address HSTORE;
 464 BEGIN
 465   nameaddress_vector := '{}'::INTEGER[];
 466
 467   SELECT s.name_vector, s.nameaddress_vector
 468     INTO parent_name_vector, parent_address_vector
 469     FROM search_name s
 470     WHERE s.place_id = parent_place_id;
 471
 472   -- Find all address tags that don't appear in the parent search names.
 473   SELECT hstore(array_agg(ARRAY[k, v])) INTO filtered_address
 474     FROM (SELECT skeys(address) as k, svals(address) as v) a
 475    WHERE not addr_ids_from_name(v) && parent_address_vector
 476          AND k not in ('country', 'street', 'place', 'postcode',
 477                        'housenumber', 'streetnumber', 'conscriptionnumber');
 478
 479   -- Compute all search terms from the addr: tags.
 480   IF filtered_address IS NOT NULL THEN
 481     FOR addr_item IN
 482       SELECT * FROM
 483         get_places_for_addr_tags(in_partition, geometry, filtered_address, country)
 484     LOOP
 485         IF addr_item.place_id is null THEN
 486             nameaddress_vector := array_merge(nameaddress_vector,
 487                                               addr_item.keywords);
 488             CONTINUE;
 489         END IF;
 490
 491         IF parent_address_place_ids is null THEN
 492             SELECT array_agg(parent_place_id) INTO parent_address_place_ids
 493               FROM place_addressline
 494              WHERE place_id = parent_place_id;
 495         END IF;
 496
 497         IF not parent_address_place_ids @> ARRAY[addr_item.place_id] THEN
 498             nameaddress_vector := array_merge(nameaddress_vector,
 499                                               addr_item.keywords);
 500
 501             INSERT INTO place_addressline (place_id, address_place_id, fromarea,
 502                                            isaddress, distance, cached_rank_address)
 503             VALUES (obj_place_id, addr_item.place_id, not addr_item.isguess,
 504                     true, addr_item.distance, addr_item.rank_address);
 505         END IF;
 506     END LOOP;
 507   END IF;
 508
 509   name_vector := initial_name_vector;
 510
 511   -- Check if the parent covers all address terms.
 512   -- If not, create a search name entry with the house number as the name.
 513   -- This is unusual for the search_name table but prevents that the place
 514   -- is returned when we only search for the street/place.
 515
 516   IF housenumber is not null and not nameaddress_vector <@ parent_address_vector THEN
 517     name_vector := array_merge(name_vector,
 518                                ARRAY[getorcreate_housenumber_id(make_standard_name(housenumber))]);
 519   END IF;
 520
 521   IF not address ? 'street' and address ? 'place' THEN
 522     addr_place_ids := addr_ids_from_name(address->'place');
 523     IF not addr_place_ids <@ parent_name_vector THEN
 524       -- make sure addr:place terms are always searchable
 525       nameaddress_vector := array_merge(nameaddress_vector, addr_place_ids);
 526       -- If there is a housenumber, also add the place name as a name,
 527       -- so we can search it by the usual housenumber+place algorithms.
 528       IF housenumber is not null THEN
 529         name_vector := array_merge(name_vector,
 530                                    ARRAY[getorcreate_name_id(make_standard_name(address->'place'))]);
 531       END IF;
 532     END IF;
 533   END IF;
 534
 535   -- Cheating here by not recomputing all terms but simply using the ones
 536   -- from the parent object.
 537   nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector);
 538   nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
 539
 540 END;
 541 $$
 542 LANGUAGE plpgsql;