]> git.openstreetmap.org Git - nominatim.git/blob - sql/functions/normalization.sql
move SQL functions for normalisation in separate file
[nominatim.git] / 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 create_country(src HSTORE, country_code varchar(2))
257   RETURNS VOID
258   AS $$
259 DECLARE
260   s TEXT;
261   w INTEGER;
262   words TEXT[];
263   item RECORD;
264   j INTEGER;
265 BEGIN
266   FOR item IN SELECT (each(src)).* LOOP
267
268     s := make_standard_name(item.value);
269     w := getorcreate_country(s, country_code);
270
271     words := regexp_split_to_array(item.value, E'[,;()]');
272     IF array_upper(words, 1) != 1 THEN
273       FOR j IN 1..array_upper(words, 1) LOOP
274         s := make_standard_name(words[j]);
275         IF s != '' THEN
276           w := getorcreate_country(s, country_code);
277         END IF;
278       END LOOP;
279     END IF;
280   END LOOP;
281 END;
282 $$
283 LANGUAGE plpgsql;
284
285
286 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE)
287   RETURNS INTEGER[]
288   AS $$
289 DECLARE
290   result INTEGER[];
291   s TEXT;
292   w INTEGER;
293   words TEXT[];
294   item RECORD;
295   j INTEGER;
296 BEGIN
297   result := '{}'::INTEGER[];
298
299   FOR item IN SELECT (each(src)).* LOOP
300
301     s := make_standard_name(item.value);
302     w := getorcreate_name_id(s, item.value);
303
304     IF not(ARRAY[w] <@ result) THEN
305       result := result || w;
306     END IF;
307
308     w := getorcreate_word_id(s);
309
310     IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
311       result := result || w;
312     END IF;
313
314     words := string_to_array(s, ' ');
315     IF array_upper(words, 1) IS NOT NULL THEN
316       FOR j IN 1..array_upper(words, 1) LOOP
317         IF (words[j] != '') THEN
318           w = getorcreate_word_id(words[j]);
319           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
320             result := result || w;
321           END IF;
322         END IF;
323       END LOOP;
324     END IF;
325
326     words := regexp_split_to_array(item.value, E'[,;()]');
327     IF array_upper(words, 1) != 1 THEN
328       FOR j IN 1..array_upper(words, 1) LOOP
329         s := make_standard_name(words[j]);
330         IF s != '' THEN
331           w := getorcreate_word_id(s);
332           IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
333             result := result || w;
334           END IF;
335         END IF;
336       END LOOP;
337     END IF;
338
339     s := regexp_replace(item.value, '市$', '');
340     IF s != item.value THEN
341       s := make_standard_name(s);
342       IF s != '' THEN
343         w := getorcreate_name_id(s, item.value);
344         IF NOT (ARRAY[w] <@ result) THEN
345           result := result || w;
346         END IF;
347       END IF;
348     END IF;
349
350   END LOOP;
351
352   RETURN result;
353 END;
354 $$
355 LANGUAGE plpgsql;
356
357
358 CREATE OR REPLACE FUNCTION make_keywords(src TEXT)
359   RETURNS INTEGER[]
360   AS $$
361 DECLARE
362   result INTEGER[];
363   s TEXT;
364   w INTEGER;
365   words TEXT[];
366   i INTEGER;
367   j INTEGER;
368 BEGIN
369   result := '{}'::INTEGER[];
370
371   s := make_standard_name(src);
372   w := getorcreate_name_id(s, src);
373
374   IF NOT (ARRAY[w] <@ result) THEN
375     result := result || w;
376   END IF;
377
378   w := getorcreate_word_id(s);
379
380   IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
381     result := result || w;
382   END IF;
383
384   words := string_to_array(s, ' ');
385   IF array_upper(words, 1) IS NOT NULL THEN
386     FOR j IN 1..array_upper(words, 1) LOOP
387       IF (words[j] != '') THEN
388         w = getorcreate_word_id(words[j]);
389         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
390           result := result || w;
391         END IF;
392       END IF;
393     END LOOP;
394   END IF;
395
396   words := regexp_split_to_array(src, E'[,;()]');
397   IF array_upper(words, 1) != 1 THEN
398     FOR j IN 1..array_upper(words, 1) LOOP
399       s := make_standard_name(words[j]);
400       IF s != '' THEN
401         w := getorcreate_word_id(s);
402         IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
403           result := result || w;
404         END IF;
405       END IF;
406     END LOOP;
407   END IF;
408
409   s := regexp_replace(src, '市$', '');
410   IF s != src THEN
411     s := make_standard_name(s);
412     IF s != '' THEN
413       w := getorcreate_name_id(s, src);
414       IF NOT (ARRAY[w] <@ result) THEN
415         result := result || w;
416       END IF;
417     END IF;
418   END IF;
419
420   RETURN result;
421 END;
422 $$
423 LANGUAGE plpgsql;