Merge pull request #1960 from lonvia/fix-postcodes-duplicated-by-normalization

[nominatim.git] / sql / functions / normalization.sql

diff --git a/sql/functions/normalization.sql b/sql/functions/normalization.sql
index 53a81588e09e9f2f79c57c92b969cd165294f333..08087172c578f72186620b1b1fe859773bec2617 100644 (file)
--- a/sql/functions/normalization.sql
+++ b/sql/functions/normalization.sql
@@ -81,7 +81,8 @@ BEGIN
   lookup_word := upper(trim(postcode));
   lookup_token := ' ' || make_standard_name(lookup_word);
   SELECT min(word_id) FROM word
-    WHERE word_token = lookup_token and class='place' and type='postcode'
+    WHERE word_token = lookup_token and word = lookup_word
+          and class='place' and type='postcode'
     INTO return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
@@ -201,56 +202,46 @@ END;
 $$
 LANGUAGE plpgsql;
 
-
-CREATE OR REPLACE FUNCTION get_word_id(lookup_word TEXT)
-  RETURNS INTEGER
-  AS $$
-DECLARE
-  lookup_token TEXT;
-  return_word_id INTEGER;
-BEGIN
-  lookup_token := trim(lookup_word);
-  SELECT min(word_id) FROM word
-    WHERE word_token = lookup_token and class is null and type is null
-    INTO return_word_id;
-  RETURN return_word_id;
-END;
-$$
-LANGUAGE plpgsql STABLE;
-
-
-CREATE OR REPLACE FUNCTION get_name_id(lookup_word TEXT)
-  RETURNS INTEGER
+-- Normalize a string and lookup its word ids (partial words).
+CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
+  RETURNS INTEGER[]
   AS $$
 DECLARE
   lookup_token TEXT;
-  return_word_id INTEGER;
+  id INTEGER;
+  return_word_id INTEGER[];
 BEGIN
-  lookup_token := ' '||trim(lookup_word);
-  SELECT min(word_id) FROM word
+  lookup_token := make_standard_name(lookup_word);
+  SELECT array_agg(word_id) FROM word
     WHERE word_token = lookup_token and class is null and type is null
     INTO return_word_id;
+  IF return_word_id IS NULL THEN
+    id := nextval('seq_word');
+    INSERT INTO word VALUES (id, lookup_token, null, null, null, null, 0);
+    return_word_id = ARRAY[id];
+  END IF;
   RETURN return_word_id;
 END;
 $$
-LANGUAGE plpgsql STABLE;
+LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT)
+-- Normalize a string and look up its name ids (full words).
+CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT)
   RETURNS INTEGER[]
   AS $$
 DECLARE
   lookup_token TEXT;
   return_word_ids INTEGER[];
 BEGIN
-  lookup_token := ' '||trim(lookup_word);
+  lookup_token := ' '|| make_standard_name(lookup_word);
   SELECT array_agg(word_id) FROM word
     WHERE word_token = lookup_token and class is null and type is null
     INTO return_word_ids;
   RETURN return_word_ids;
 END;
 $$
-LANGUAGE plpgsql STABLE;
+LANGUAGE plpgsql STABLE STRICT;
 
 
 CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2))