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