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 class='place' and type='postcode'
  85     INTO return_word_id;
  86   IF return_word_id IS NULL THEN
  87     return_word_id := nextval('seq_word');
  88     INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word,
  89                              'place', 'postcode', null, 0);
  90   END IF;
  91   RETURN return_word_id;
  92 END;
  93 $$
  94 LANGUAGE plpgsql;
  95
  96
  97 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT,
  98                                                lookup_country_code varchar(2))
  99   RETURNS INTEGER
 100   AS $$
 101 DECLARE
 102   lookup_token TEXT;
 103   return_word_id INTEGER;
 104 BEGIN
 105   lookup_token := ' '||trim(lookup_word);
 106   SELECT min(word_id) FROM word
 107     WHERE word_token = lookup_token and country_code=lookup_country_code
 108     INTO return_word_id;
 109   IF return_word_id IS NULL THEN
 110     return_word_id := nextval('seq_word');
 111     INSERT INTO word VALUES (return_word_id, lookup_token, null,
 112                              null, null, lookup_country_code, 0);
 113   END IF;
 114   RETURN return_word_id;
 115 END;
 116 $$
 117 LANGUAGE plpgsql;
 118
 119
 120 CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT,
 121                                                lookup_class text, lookup_type text)
 122   RETURNS INTEGER
 123   AS $$
 124 DECLARE
 125   lookup_token TEXT;
 126   return_word_id INTEGER;
 127 BEGIN
 128   lookup_token := ' '||trim(lookup_word);
 129   SELECT min(word_id) FROM word
 130   WHERE word_token = lookup_token and word = normalized_word
 131         and class = lookup_class and type = lookup_type
 132   INTO return_word_id;
 133   IF return_word_id IS NULL THEN
 134     return_word_id := nextval('seq_word');
 135     INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word,
 136                              lookup_class, lookup_type, null, 0);
 137   END IF;
 138   RETURN return_word_id;
 139 END;
 140 $$
 141 LANGUAGE plpgsql;
 142
 143
 144 CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT,
 145                                                        normalized_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 = normalized_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, normalized_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
 205 CREATE OR REPLACE FUNCTION get_word_id(lookup_word TEXT)
 206   RETURNS INTEGER
 207   AS $$
 208 DECLARE
 209   lookup_token TEXT;
 210   return_word_id INTEGER;
 211 BEGIN
 212   lookup_token := trim(lookup_word);
 213   SELECT min(word_id) FROM word
 214     WHERE word_token = lookup_token and class is null and type is null
 215     INTO return_word_id;
 216   RETURN return_word_id;
 217 END;
 218 $$
 219 LANGUAGE plpgsql STABLE;
 220
 221
 222 CREATE OR REPLACE FUNCTION get_name_id(lookup_word TEXT)
 223   RETURNS INTEGER
 224   AS $$
 225 DECLARE
 226   lookup_token TEXT;
 227   return_word_id INTEGER;
 228 BEGIN
 229   lookup_token := ' '||trim(lookup_word);
 230   SELECT min(word_id) FROM word
 231     WHERE word_token = lookup_token and class is null and type is null
 232     INTO return_word_id;
 233   RETURN return_word_id;
 234 END;
 235 $$
 236 LANGUAGE plpgsql STABLE;
 237
 238
 239 CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT)
 240   RETURNS INTEGER[]
 241   AS $$
 242 DECLARE
 243   lookup_token TEXT;
 244   return_word_ids INTEGER[];
 245 BEGIN
 246   lookup_token := ' '||trim(lookup_word);
 247   SELECT array_agg(word_id) FROM word
 248     WHERE word_token = lookup_token and class is null and type is null
 249     INTO return_word_ids;
 250   RETURN return_word_ids;
 251 END;
 252 $$
 253 LANGUAGE plpgsql STABLE;
 254
 255
 256 CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
 257   RETURNS INTEGER[]
 258   AS $$
 259 DECLARE
 260   lookup_token TEXT;
 261   return_word_ids INTEGER[];
 262 BEGIN
 263   lookup_token := ' '|| make_standard_name(lookup_word);
 264   SELECT array_agg(word_id) FROM word
 265     WHERE word_token = lookup_token and class is null and type is null
 266     INTO return_word_ids;
 267   RETURN return_word_ids;
 268 END;
 269 $$
 270 LANGUAGE plpgsql STABLE STRICT;
 271
 272
 273 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))
 274   RETURNS VOID
 275   AS $$
 276 DECLARE
 277   s TEXT;
 278   w INTEGER;
 279   words TEXT[];
 280   item RECORD;
 281   j INTEGER;
 282 BEGIN
 283   FOR item IN SELECT (each(src)).* LOOP
 284
 285     s := make_standard_name(item.value);
 286     w := getorcreate_country(s, country_code);
 287
 288     words := regexp_split_to_array(item.value, E'[,;()]');
 289     IF array_upper(words, 1) != 1 THEN
 290       FOR j IN 1..array_upper(words, 1) LOOP
 291         s := make_standard_name(words[j]);
 292         IF s != '' THEN
 293           w := getorcreate_country(s, country_code);
 294         END IF;
 295       END LOOP;
 296     END IF;
 297   END LOOP;
 298 END;
 299 $$
 300 LANGUAGE plpgsql;
 301
 302
 303 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
 304   RETURNS INTEGER[]
 305   AS $$
 306 DECLARE
 307   result INTEGER[];
 308   s TEXT;
 309   w INTEGER;
 310   words TEXT[];
 311   item RECORD;
 312   j INTEGER;
 313 BEGIN
 314   result := '{}'::INTEGER[];
 315
 316   FOR item IN SELECT (each(src)).* LOOP
 317
 318     s := make_standard_name(item.value);
 319     w := getorcreate_name_id(s, item.value);
 320
 321     IF not(ARRAY[w] <@ result) THEN
 322       result := result || w;
 323     END IF;
 324
 325     w := getorcreate_word_id(s);
 326
 327     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 328       result := result || w;
 329     END IF;
 330
 331     words := string_to_array(s, ' ');
 332     IF array_upper(words, 1) IS NOT NULL THEN
 333       FOR j IN 1..array_upper(words, 1) LOOP
 334         IF (words[j] != '') THEN
 335           w = getorcreate_word_id(words[j]);
 336           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 337             result := result || w;
 338           END IF;
 339         END IF;
 340       END LOOP;
 341     END IF;
 342
 343     words := regexp_split_to_array(item.value, E'[,;()]');
 344     IF array_upper(words, 1) != 1 THEN
 345       FOR j IN 1..array_upper(words, 1) LOOP
 346         s := make_standard_name(words[j]);
 347         IF s != '' THEN
 348           w := getorcreate_word_id(s);
 349           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 350             result := result || w;
 351           END IF;
 352         END IF;
 353       END LOOP;
 354     END IF;
 355
 356     s := regexp_replace(item.value, '市$', '');
 357     IF s != item.value THEN
 358       s := make_standard_name(s);
 359       IF s != '' THEN
 360         w := getorcreate_name_id(s, item.value);
 361         IF NOT (ARRAY[w] <@ result) THEN
 362           result := result || w;
 363         END IF;
 364       END IF;
 365     END IF;
 366
 367   END LOOP;
 368
 369   RETURN result;
 370 END;
 371 $$
 372 LANGUAGE plpgsql;
 373
 374
 375 CREATE OR REPLACE FUNCTION make_keywords(src TEXT)
 376   RETURNS INTEGER[]
 377   AS $$
 378 DECLARE
 379   result INTEGER[];
 380   s TEXT;
 381   w INTEGER;
 382   words TEXT[];
 383   i INTEGER;
 384   j INTEGER;
 385 BEGIN
 386   result := '{}'::INTEGER[];
 387
 388   s := make_standard_name(src);
 389   w := getorcreate_name_id(s, src);
 390
 391   IF NOT (ARRAY[w] <@ result) THEN
 392     result := result || w;
 393   END IF;
 394
 395   w := getorcreate_word_id(s);
 396
 397   IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 398     result := result || w;
 399   END IF;
 400
 401   words := string_to_array(s, ' ');
 402   IF array_upper(words, 1) IS NOT NULL THEN
 403     FOR j IN 1..array_upper(words, 1) LOOP
 404       IF (words[j] != '') THEN
 405         w = getorcreate_word_id(words[j]);
 406         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 407           result := result || w;
 408         END IF;
 409       END IF;
 410     END LOOP;
 411   END IF;
 412
 413   words := regexp_split_to_array(src, E'[,;()]');
 414   IF array_upper(words, 1) != 1 THEN
 415     FOR j IN 1..array_upper(words, 1) LOOP
 416       s := make_standard_name(words[j]);
 417       IF s != '' THEN
 418         w := getorcreate_word_id(s);
 419         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
 420           result := result || w;
 421         END IF;
 422       END IF;
 423     END LOOP;
 424   END IF;
 425
 426   s := regexp_replace(src, '市$', '');
 427   IF s != src THEN
 428     s := make_standard_name(s);
 429     IF s != '' THEN
 430       w := getorcreate_name_id(s, src);
 431       IF NOT (ARRAY[w] <@ result) THEN
 432         result := result || w;
 433       END IF;
 434     END IF;
 435   END IF;
 436
 437   RETURN result;
 438 END;
 439 $$
 440 LANGUAGE plpgsql;