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