]> git.openstreetmap.org Git - nominatim.git/blob - lib-sql/functions/normalization.sql
Merge pull request #2197 from lonvia/use-jinja-for-sql-preprocessing
[nominatim.git] / 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;