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 getorcreate_housenumber_id(lookup_word TEXT)
  52   RETURNS INTEGER
  53   AS $$
  54 DECLARE
  55   lookup_token TEXT;
  56   return_word_id INTEGER;
  57 BEGIN
  58   lookup_token := ' ' || trim(lookup_word);
  59   SELECT min(word_id) FROM word
  60     WHERE word_token = lookup_token and class='place' and type='house'
  61     INTO return_word_id;
  62   IF return_word_id IS NULL THEN
  63     return_word_id := nextval('seq_word');
  64     INSERT INTO word VALUES (return_word_id, lookup_token, null,
  65                              'place', 'house', null, 0);
  66   END IF;
  67   RETURN return_word_id;
  68 END;
  69 $$
  70 LANGUAGE plpgsql;
  71
  72
  73 CREATE OR REPLACE FUNCTION getorcreate_postcode_id(postcode TEXT)
  74   RETURNS INTEGER
  75   AS $$
  76 DECLARE
  77   lookup_token TEXT;
  78   lookup_word TEXT;
  79   return_word_id INTEGER;
  80 BEGIN
  81   lookup_word := upper(trim(postcode));
  82   lookup_token := ' ' || make_standard_name(lookup_word);
  83   SELECT min(word_id) FROM word
  84     WHERE word_token = lookup_token and word = lookup_word
  85           and class='place' and type='postcode'
  86     INTO return_word_id;
  87   IF return_word_id IS NULL THEN
  88     return_word_id := nextval('seq_word');
  89     INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word,
  90                              'place', 'postcode', null, 0);
  91   END IF;
  92   RETURN return_word_id;
  93 END;
  94 $$
  95 LANGUAGE plpgsql;
  96
  97
  98 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT,
  99                                                lookup_country_code varchar(2))
 100   RETURNS INTEGER
 101   AS $$
 102 DECLARE
 103   lookup_token TEXT;
 104   return_word_id INTEGER;
 105 BEGIN
 106   lookup_token := ' '||trim(lookup_word);
 107   SELECT min(word_id) FROM word
 108     WHERE word_token = lookup_token and country_code=lookup_country_code
 109     INTO return_word_id;
 110   IF return_word_id IS NULL THEN
 111     return_word_id := nextval('seq_word');
 112     INSERT INTO word VALUES (return_word_id, lookup_token, null,
 113                              null, null, lookup_country_code, 0);
 114   END IF;
 115   RETURN return_word_id;
 116 END;
 117 $$
 118 LANGUAGE plpgsql;
 119
 120
 121 CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT,
 122                                                lookup_class text, lookup_type text)
 123   RETURNS INTEGER
 124   AS $$
 125 DECLARE
 126   lookup_token TEXT;
 127   return_word_id INTEGER;
 128 BEGIN
 129   lookup_token := ' '||trim(lookup_word);
 130   SELECT min(word_id) FROM word
 131   WHERE word_token = lookup_token and word = lookup_word
 132         and class = lookup_class and type = lookup_type
 133   INTO return_word_id;
 134   IF return_word_id IS NULL THEN
 135     return_word_id := nextval('seq_word');
 136     INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word,
 137                              lookup_class, lookup_type, null, 0);
 138   END IF;
 139   RETURN return_word_id;
 140 END;
 141 $$
 142 LANGUAGE plpgsql;
 143
 144
 145 CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT,
 146                                                        lookup_class text,
 147                                                        lookup_type text,
 148                                                        op text)
 149   RETURNS INTEGER
 150   AS $$
 151 DECLARE
 152   lookup_token TEXT;
 153   return_word_id INTEGER;
 154 BEGIN
 155   lookup_token := ' '||trim(lookup_word);
 156   SELECT min(word_id) FROM word
 157   WHERE word_token = lookup_token and word = lookup_word
 158         and class = lookup_class and type = lookup_type and operator = op
 159   INTO return_word_id;
 160   IF return_word_id IS NULL THEN
 161     return_word_id := nextval('seq_word');
 162     INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word,
 163                              lookup_class, lookup_type, null, 0, op);
 164   END IF;
 165   RETURN return_word_id;
 166 END;
 167 $$
 168 LANGUAGE plpgsql;
 169
 170
 171 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT)
 172   RETURNS INTEGER
 173   AS $$
 174 DECLARE
 175   lookup_token TEXT;
 176   nospace_lookup_token TEXT;
 177   return_word_id INTEGER;
 178 BEGIN
 179   lookup_token := ' '||trim(lookup_word);
 180   SELECT min(word_id) FROM word
 181   WHERE word_token = lookup_token and class is null and type is null
 182   INTO return_word_id;
 183   IF return_word_id IS NULL THEN
 184     return_word_id := nextval('seq_word');
 185     INSERT INTO word VALUES (return_word_id, lookup_token, src_word,
 186                              null, null, null, 0);
 187   END IF;
 188   RETURN return_word_id;
 189 END;
 190 $$
 191 LANGUAGE plpgsql;
 192
 193
 194 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT)
 195   RETURNS INTEGER
 196   AS $$
 197 DECLARE
 198 BEGIN
 199   RETURN getorcreate_name_id(lookup_word, '');
 200 END;
 201 $$
 202 LANGUAGE plpgsql;
 203
 204 -- Normalize a string and lookup its word ids (partial words).
 205 CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
 206   RETURNS INTEGER[]
 207   AS $$
 208 DECLARE
 209   words TEXT[];
 210   id INTEGER;
 211   return_word_id INTEGER[];
 212   word_ids INTEGER[];
 213   j INTEGER;
 214 BEGIN
 215   words := string_to_array(make_standard_name(lookup_word), ' ');
 216   IF array_upper(words, 1) IS NOT NULL THEN
 217     FOR j IN 1..array_upper(words, 1) LOOP
 218       IF (words[j] != '') THEN
 219         SELECT array_agg(word_id) INTO word_ids
 220           FROM word
 221          WHERE word_token = words[j] and class is null and type is null;
 222
 223         IF word_ids IS NULL THEN
 224           id := nextval('seq_word');
 225           INSERT INTO word VALUES (id, words[j], null, null, null, null, 0);
 226           return_word_id := return_word_id || id;
 227         ELSE
 228           return_word_id := array_merge(return_word_id, word_ids);
 229         END IF;
 230       END IF;
 231     END LOOP;
 232   END IF;
 233
 234   RETURN return_word_id;
 235 END;
 236 $$
 237 LANGUAGE plpgsql;
 238
 239
 240 -- Normalize a string and look up its name ids (full words).
 241 CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
 242   RETURNS INTEGER[]
 243   AS $$
 244 DECLARE
 245   lookup_token TEXT;
 246   return_word_ids INTEGER[];
 247 BEGIN
 248   lookup_token := ' '|| make_standard_name(lookup_word);
 249   SELECT array_agg(word_id) FROM word
 250     WHERE word_token = lookup_token and class is null and type is null
 251     INTO return_word_ids;
 252   RETURN return_word_ids;
 253 END;
 254 $$
 255 LANGUAGE plpgsql STABLE STRICT;
 256
 257
 258 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))
 259   RETURNS VOID
 260   AS $$
 261 DECLARE
 262   s TEXT;
 263   w INTEGER;
 264   words TEXT[];
 265   item RECORD;
 266   j INTEGER;
 267 BEGIN
 268   FOR item IN SELECT (each(src)).* LOOP
 269
 270     s := make_standard_name(item.value);
 271     w := getorcreate_country(s, country_code);
 272
 273     words := regexp_split_to_array(item.value, E'[,;()]');
 274     IF array_upper(words, 1) != 1 THEN
 275       FOR j IN 1..array_upper(words, 1) LOOP
 276         s := make_standard_name(words[j]);
 277         IF s != '' THEN
 278           w := getorcreate_country(s, country_code);
 279         END IF;
 280       END LOOP;
 281     END IF;
 282   END LOOP;
 283 END;
 284 $$
 285 LANGUAGE plpgsql;
 286
 287
 288 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
 289   RETURNS INTEGER[]
 290   AS $$
 291 DECLARE
 292   result INTEGER[];
 293   s TEXT;
 294   w INTEGER;
 295   words TEXT[];
 296   item RECORD;
 297   j INTEGER;
 298 BEGIN
 299   result := '{}'::INTEGER[];
 300
 301   FOR item IN SELECT (each(src)).* LOOP
 302
 303     s := make_standard_name(item.value);
 304     w := getorcreate_name_id(s, item.value);
 305
 306     IF not(ARRAY[w] <@ result) THEN
 307       result := result || w;
 308     END IF;
 309
 310     w := getorcreate_word_id(s);
 311
 312     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 313       result := result || w;
 314     END IF;
 315
 316     words := string_to_array(s, ' ');
 317     IF array_upper(words, 1) IS NOT NULL THEN
 318       FOR j IN 1..array_upper(words, 1) LOOP
 319         IF (words[j] != '') THEN
 320           w = getorcreate_word_id(words[j]);
 321           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 322             result := result || w;
 323           END IF;
 324         END IF;
 325       END LOOP;
 326     END IF;
 327
 328     words := regexp_split_to_array(item.value, E'[,;()]');
 329     IF array_upper(words, 1) != 1 THEN
 330       FOR j IN 1..array_upper(words, 1) LOOP
 331         s := make_standard_name(words[j]);
 332         IF s != '' THEN
 333           w := getorcreate_word_id(s);
 334           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 335             result := result || w;
 336           END IF;
 337         END IF;
 338       END LOOP;
 339     END IF;
 340
 341     s := regexp_replace(item.value, '市$', '');
 342     IF s != item.value THEN
 343       s := make_standard_name(s);
 344       IF s != '' THEN
 345         w := getorcreate_name_id(s, item.value);
 346         IF NOT (ARRAY[w] <@ result) THEN
 347           result := result || w;
 348         END IF;
 349       END IF;
 350     END IF;
 351
 352   END LOOP;
 353
 354   RETURN result;
 355 END;
 356 $$
 357 LANGUAGE plpgsql;
 358
 359
 360 CREATE OR REPLACE FUNCTION make_keywords(src TEXT)
 361   RETURNS INTEGER[]
 362   AS $$
 363 DECLARE
 364   result INTEGER[];
 365   s TEXT;
 366   w INTEGER;
 367   words TEXT[];
 368   i INTEGER;
 369   j INTEGER;
 370 BEGIN
 371   result := '{}'::INTEGER[];
 372
 373   s := make_standard_name(src);
 374   w := getorcreate_name_id(s, src);
 375
 376   IF NOT (ARRAY[w] <@ result) THEN
 377     result := result || w;
 378   END IF;
 379
 380   w := getorcreate_word_id(s);
 381
 382   IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 383     result := result || w;
 384   END IF;
 385
 386   words := string_to_array(s, ' ');
 387   IF array_upper(words, 1) IS NOT NULL THEN
 388     FOR j IN 1..array_upper(words, 1) LOOP
 389       IF (words[j] != '') THEN
 390         w = getorcreate_word_id(words[j]);
 391         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 392           result := result || w;
 393         END IF;
 394       END IF;
 395     END LOOP;
 396   END IF;
 397
 398   words := regexp_split_to_array(src, E'[,;()]');
 399   IF array_upper(words, 1) != 1 THEN
 400     FOR j IN 1..array_upper(words, 1) LOOP
 401       s := make_standard_name(words[j]);
 402       IF s != '' THEN
 403         w := getorcreate_word_id(s);
 404         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 405           result := result || w;
 406         END IF;
 407       END IF;
 408     END LOOP;
 409   END IF;
 410
 411   s := regexp_replace(src, '市$', '');
 412   IF s != src THEN
 413     s := make_standard_name(s);
 414     IF s != '' THEN
 415       w := getorcreate_name_id(s, src);
 416       IF NOT (ARRAY[w] <@ result) THEN
 417         result := result || w;
 418       END IF;
 419     END IF;
 420   END IF;
 421
 422   RETURN result;
 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;