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, normalized_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 = normalized_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, normalized_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                                                        normalized_word TEXT,
 147                                                        lookup_class text,
 148                                                        lookup_type text,
 149                                                        op text)
 150   RETURNS INTEGER
 151   AS $$
 152 DECLARE
 153   lookup_token TEXT;
 154   return_word_id INTEGER;
 155 BEGIN
 156   lookup_token := ' '||trim(lookup_word);
 157   SELECT min(word_id) FROM word
 158   WHERE word_token = lookup_token and word = normalized_word
 159         and class = lookup_class and type = lookup_type and operator = op
 160   INTO return_word_id;
 161   IF return_word_id IS NULL THEN
 162     return_word_id := nextval('seq_word');
 163     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 164                              lookup_class, lookup_type, null, 0, op);
 165   END IF;
 166   RETURN return_word_id;
 167 END;
 168 $$
 169 LANGUAGE plpgsql;
 170
 171
 172 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT)
 173   RETURNS INTEGER
 174   AS $$
 175 DECLARE
 176   lookup_token TEXT;
 177   nospace_lookup_token TEXT;
 178   return_word_id INTEGER;
 179 BEGIN
 180   lookup_token := ' '||trim(lookup_word);
 181   SELECT min(word_id) FROM word
 182   WHERE word_token = lookup_token and class is null and type is null
 183   INTO return_word_id;
 184   IF return_word_id IS NULL THEN
 185     return_word_id := nextval('seq_word');
 186     INSERT INTO word VALUES (return_word_id, lookup_token, src_word,
 187                              null, null, null, 0);
 188   END IF;
 189   RETURN return_word_id;
 190 END;
 191 $$
 192 LANGUAGE plpgsql;
 193
 194
 195 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT)
 196   RETURNS INTEGER
 197   AS $$
 198 DECLARE
 199 BEGIN
 200   RETURN getorcreate_name_id(lookup_word, '');
 201 END;
 202 $$
 203 LANGUAGE plpgsql;
 204
 205 -- Normalize a string and lookup its word ids (partial words).
 206 CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
 207   RETURNS INTEGER[]
 208   AS $$
 209 DECLARE
 210   lookup_token TEXT;
 211   id INTEGER;
 212   return_word_id INTEGER[];
 213 BEGIN
 214   lookup_token := make_standard_name(lookup_word);
 215   SELECT array_agg(word_id) FROM word
 216     WHERE word_token = lookup_token and class is null and type is null
 217     INTO return_word_id;
 218   IF return_word_id IS NULL THEN
 219     id := nextval('seq_word');
 220     INSERT INTO word VALUES (id, lookup_token, null, null, null, null, 0);
 221     return_word_id = ARRAY[id];
 222   END IF;
 223   RETURN return_word_id;
 224 END;
 225 $$
 226 LANGUAGE plpgsql;
 227
 228
 229 -- Normalize a string and look up its name ids (full words).
 230 CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
 231   RETURNS INTEGER[]
 232   AS $$
 233 DECLARE
 234   lookup_token TEXT;
 235   return_word_ids INTEGER[];
 236 BEGIN
 237   lookup_token := ' '|| make_standard_name(lookup_word);
 238   SELECT array_agg(word_id) FROM word
 239     WHERE word_token = lookup_token and class is null and type is null
 240     INTO return_word_ids;
 241   RETURN return_word_ids;
 242 END;
 243 $$
 244 LANGUAGE plpgsql STABLE STRICT;
 245
 246
 247 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))
 248   RETURNS VOID
 249   AS $$
 250 DECLARE
 251   s TEXT;
 252   w INTEGER;
 253   words TEXT[];
 254   item RECORD;
 255   j INTEGER;
 256 BEGIN
 257   FOR item IN SELECT (each(src)).* LOOP
 258
 259     s := make_standard_name(item.value);
 260     w := getorcreate_country(s, country_code);
 261
 262     words := regexp_split_to_array(item.value, E'[,;()]');
 263     IF array_upper(words, 1) != 1 THEN
 264       FOR j IN 1..array_upper(words, 1) LOOP
 265         s := make_standard_name(words[j]);
 266         IF s != '' THEN
 267           w := getorcreate_country(s, country_code);
 268         END IF;
 269       END LOOP;
 270     END IF;
 271   END LOOP;
 272 END;
 273 $$
 274 LANGUAGE plpgsql;
 275
 276
 277 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
 278   RETURNS INTEGER[]
 279   AS $$
 280 DECLARE
 281   result INTEGER[];
 282   s TEXT;
 283   w INTEGER;
 284   words TEXT[];
 285   item RECORD;
 286   j INTEGER;
 287 BEGIN
 288   result := '{}'::INTEGER[];
 289
 290   FOR item IN SELECT (each(src)).* LOOP
 291
 292     s := make_standard_name(item.value);
 293     w := getorcreate_name_id(s, item.value);
 294
 295     IF not(ARRAY[w] <@ result) THEN
 296       result := result || w;
 297     END IF;
 298
 299     w := getorcreate_word_id(s);
 300
 301     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 302       result := result || w;
 303     END IF;
 304
 305     words := string_to_array(s, ' ');
 306     IF array_upper(words, 1) IS NOT NULL THEN
 307       FOR j IN 1..array_upper(words, 1) LOOP
 308         IF (words[j] != '') THEN
 309           w = getorcreate_word_id(words[j]);
 310           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 311             result := result || w;
 312           END IF;
 313         END IF;
 314       END LOOP;
 315     END IF;
 316
 317     words := regexp_split_to_array(item.value, E'[,;()]');
 318     IF array_upper(words, 1) != 1 THEN
 319       FOR j IN 1..array_upper(words, 1) LOOP
 320         s := make_standard_name(words[j]);
 321         IF s != '' THEN
 322           w := getorcreate_word_id(s);
 323           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 324             result := result || w;
 325           END IF;
 326         END IF;
 327       END LOOP;
 328     END IF;
 329
 330     s := regexp_replace(item.value, '市$', '');
 331     IF s != item.value THEN
 332       s := make_standard_name(s);
 333       IF s != '' THEN
 334         w := getorcreate_name_id(s, item.value);
 335         IF NOT (ARRAY[w] <@ result) THEN
 336           result := result || w;
 337         END IF;
 338       END IF;
 339     END IF;
 340
 341   END LOOP;
 342
 343   RETURN result;
 344 END;
 345 $$
 346 LANGUAGE plpgsql;
 347
 348
 349 CREATE OR REPLACE FUNCTION make_keywords(src TEXT)
 350   RETURNS INTEGER[]
 351   AS $$
 352 DECLARE
 353   result INTEGER[];
 354   s TEXT;
 355   w INTEGER;
 356   words TEXT[];
 357   i INTEGER;
 358   j INTEGER;
 359 BEGIN
 360   result := '{}'::INTEGER[];
 361
 362   s := make_standard_name(src);
 363   w := getorcreate_name_id(s, src);
 364
 365   IF NOT (ARRAY[w] <@ result) THEN
 366     result := result || w;
 367   END IF;
 368
 369   w := getorcreate_word_id(s);
 370
 371   IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 372     result := result || w;
 373   END IF;
 374
 375   words := string_to_array(s, ' ');
 376   IF array_upper(words, 1) IS NOT NULL THEN
 377     FOR j IN 1..array_upper(words, 1) LOOP
 378       IF (words[j] != '') THEN
 379         w = getorcreate_word_id(words[j]);
 380         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 381           result := result || w;
 382         END IF;
 383       END IF;
 384     END LOOP;
 385   END IF;
 386
 387   words := regexp_split_to_array(src, E'[,;()]');
 388   IF array_upper(words, 1) != 1 THEN
 389     FOR j IN 1..array_upper(words, 1) LOOP
 390       s := make_standard_name(words[j]);
 391       IF s != '' THEN
 392         w := getorcreate_word_id(s);
 393         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 394           result := result || w;
 395         END IF;
 396       END IF;
 397     END LOOP;
 398   END IF;
 399
 400   s := regexp_replace(src, '市$', '');
 401   IF s != src THEN
 402     s := make_standard_name(s);
 403     IF s != '' THEN
 404       w := getorcreate_name_id(s, src);
 405       IF NOT (ARRAY[w] <@ result) THEN
 406         result := result || w;
 407       END IF;
 408     END IF;
 409   END IF;
 410
 411   RETURN result;
 412 END;
 413 $$
 414 LANGUAGE plpgsql;
 415
 416
 417 CREATE OR REPLACE FUNCTION create_poi_search_terms(obj_place_id BIGINT,
 418                                                    in_partition SMALLINT,
 419                                                    parent_place_id BIGINT,
 420                                                    address HSTORE,
 421                                                    country TEXT,
 422                                                    housenumber TEXT,
 423                                                    initial_name_vector INTEGER[],
 424                                                    geometry GEOMETRY,
 425                                                    OUT name_vector INTEGER[],
 426                                                    OUT nameaddress_vector INTEGER[])
 427   AS $$
 428 DECLARE
 429   parent_name_vector INTEGER[];
 430   parent_address_vector INTEGER[];
 431   addr_place_ids INTEGER[];
 432
 433   addr_item RECORD;
 434   parent_address_place_ids BIGINT[];
 435   filtered_address HSTORE;
 436 BEGIN
 437   nameaddress_vector := '{}'::INTEGER[];
 438
 439   SELECT s.name_vector, s.nameaddress_vector
 440     INTO parent_name_vector, parent_address_vector
 441     FROM search_name s
 442     WHERE s.place_id = parent_place_id;
 443
 444   -- Find all address tags that don't appear in the parent search names.
 445   SELECT hstore(array_agg(ARRAY[k, v])) INTO filtered_address
 446     FROM (SELECT skeys(address) as k, svals(address) as v) a
 447    WHERE not addr_ids_from_name(v) && parent_address_vector
 448          AND k not in ('country', 'street', 'place', 'postcode',
 449                        'housenumber', 'streetnumber', 'conscriptionnumber');
 450
 451   -- Compute all search terms from the addr: tags.
 452   IF filtered_address IS NOT NULL THEN
 453     FOR addr_item IN
 454       SELECT * FROM
 455         get_places_for_addr_tags(in_partition, geometry, filtered_address, country)
 456     LOOP
 457         IF addr_item.place_id is null THEN
 458             nameaddress_vector := array_merge(nameaddress_vector,
 459                                               addr_item.keywords);
 460             CONTINUE;
 461         END IF;
 462
 463         IF parent_address_place_ids is null THEN
 464             SELECT array_agg(parent_place_id) INTO parent_address_place_ids
 465               FROM place_addressline
 466              WHERE place_id = parent_place_id;
 467         END IF;
 468
 469         IF not parent_address_place_ids @> ARRAY[addr_item.place_id] THEN
 470             nameaddress_vector := array_merge(nameaddress_vector,
 471                                               addr_item.keywords);
 472
 473             INSERT INTO place_addressline (place_id, address_place_id, fromarea,
 474                                            isaddress, distance, cached_rank_address)
 475             VALUES (obj_place_id, addr_item.place_id, not addr_item.isguess,
 476                     true, addr_item.distance, addr_item.rank_address);
 477         END IF;
 478     END LOOP;
 479   END IF;
 480
 481
 482   -- If the POI is named, simply mix in all address terms and be done.
 483   IF array_length(initial_name_vector, 1) is not NULL THEN
 484     -- Cheating here by not recomputing all terms but simply using the ones
 485     -- from the parent object.
 486     name_vector := initial_name_vector;
 487     nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector);
 488     nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
 489
 490     IF not address ? 'street' and address ? 'place' THEN
 491       -- make sure addr:place terms are always searchable
 492       nameaddress_vector := array_merge(nameaddress_vector,
 493                                         addr_ids_from_name(address->'place'));
 494     END IF;
 495
 496     RETURN;
 497   END IF;
 498
 499   ----- unnamed POIS
 500
 501   IF (array_length(nameaddress_vector, 1) is null
 502       and (address ? 'street'or not address ? 'place'))
 503      or housenumber is null
 504   THEN
 505     RETURN;
 506   END IF;
 507
 508   -- Check if the parent covers all address terms.
 509   -- If not, create a search name entry with the house number as the name.
 510   -- This is unusual for the search_name table but prevents that the place
 511   -- is returned when we only search for the street/place.
 512
 513   IF not nameaddress_vector <@ parent_address_vector THEN
 514     name_vector := ARRAY[getorcreate_name_id(housenumber)];
 515   END IF;
 516
 517   IF not address ? 'street' and address ? 'place' THEN
 518     addr_place_ids := addr_ids_from_name(address->'place');
 519     IF not addr_place_ids <@ parent_name_vector THEN
 520       -- addr:place tag exists without a corresponding place. Mix in addr:place
 521       -- in the address and drop the name from the parent. This would only be
 522       -- the street name of the nearest street.
 523       nameaddress_vector := array_merge(nameaddress_vector, addr_place_ids);
 524       name_vector := ARRAY[getorcreate_name_id(housenumber)];
 525     END IF;
 526   ELSE
 527     nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector);
 528   END IF;
 529
 530   -- The address vector always gets merged in.
 531   nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
 532
 533 END;
 534 $$
 535 LANGUAGE plpgsql;