Merge pull request #2105 from lonvia/fix-use-of-records

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

diff --git a/sql/functions/normalization.sql b/sql/functions/normalization.sql
index 71b14ee5109aa198f510993247073df754cafdc5..8bb4915bbf8612ab858c98345409bbef57ff6eb0 100644 (file)
--- a/sql/functions/normalization.sql
+++ b/sql/functions/normalization.sql
@@ -207,19 +207,31 @@ CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT)
   RETURNS INTEGER[]
   AS $$
 DECLARE
   RETURNS INTEGER[]
   AS $$
 DECLARE

-  lookup_token TEXT;
+  words TEXT[];

   id INTEGER;
   return_word_id INTEGER[];
   id INTEGER;
   return_word_id INTEGER[];

+  word_ids INTEGER[];
+  j INTEGER;

 BEGIN
 BEGIN

-  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];
+  words := string_to_array(make_standard_name(lookup_word), ' ');
+  IF array_upper(words, 1) IS NOT NULL THEN
+    FOR j IN 1..array_upper(words, 1) LOOP
+      IF (words[j] != '') THEN
+        SELECT array_agg(word_id) INTO word_ids
+          FROM word
+         WHERE word_token = words[j] and class is null and type is null;
+
+        IF word_ids IS NULL THEN
+          id := nextval('seq_word');
+          INSERT INTO word VALUES (id, words[j], null, null, null, null, 0);
+          return_word_id := return_word_id || id;
+        ELSE
+          return_word_id := array_merge(return_word_id, word_ids);
+        END IF;
+      END IF;
+    END LOOP;

   END IF;
   END IF;

+

   RETURN return_word_id;
 END;
 $$
   RETURN return_word_id;
 END;
 $$


@@ -414,10 +426,14 @@ $$
 LANGUAGE plpgsql;
 
 
 LANGUAGE plpgsql;
 
 

-CREATE OR REPLACE FUNCTION create_poi_search_terms(parent_place_id BIGINT,
+CREATE OR REPLACE FUNCTION create_poi_search_terms(obj_place_id BIGINT,
+                                                   in_partition SMALLINT,
+                                                   parent_place_id BIGINT,

                                                    address HSTORE,
                                                    address HSTORE,

+                                                   country TEXT,

                                                    housenumber TEXT,
                                                    initial_name_vector INTEGER[],
                                                    housenumber TEXT,
                                                    initial_name_vector INTEGER[],

+                                                   geometry GEOMETRY,

                                                    OUT name_vector INTEGER[],
                                                    OUT nameaddress_vector INTEGER[])
   AS $$
                                                    OUT name_vector INTEGER[],
                                                    OUT nameaddress_vector INTEGER[])
   AS $$


@@ -427,82 +443,82 @@ DECLARE
   addr_place_ids INTEGER[];
 
   addr_item RECORD;
   addr_place_ids INTEGER[];
 
   addr_item RECORD;

+  parent_address_place_ids BIGINT[];
+  filtered_address HSTORE;

 BEGIN
 BEGIN

-  -- Compute all search terms from the addr: tags.

   nameaddress_vector := '{}'::INTEGER[];
 
   nameaddress_vector := '{}'::INTEGER[];
 

-  IF address IS NOT NULL THEN
-    FOR addr_item IN SELECT * FROM each(address)
-    LOOP
-      IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province',
-                           'district', 'region', 'county', 'municipality',
-                           'hamlet', 'village', 'subdistrict', 'town',
-                           'neighbourhood', 'quarter', 'parish')
-      THEN
-          nameaddress_vector := array_merge(nameaddress_vector,
-                                            addr_ids_from_name(addr_item.value));
-      END IF;
-    END LOOP;
-  END IF;
-
-  -- If the POI is named, simply mix in all address terms and be done.
-  IF array_length(initial_name_vector, 1) is not NULL THEN
-    -- Cheating here by not recomputing all terms but simply using the ones
-    -- from the parent object.
-    SELECT array_merge(s.name_vector, s.nameaddress_vector)
-      INTO parent_address_vector
-      FROM search_name s
-      WHERE s.place_id = parent_place_id;
+  SELECT s.name_vector, s.nameaddress_vector
+    INTO parent_name_vector, parent_address_vector
+    FROM search_name s
+    WHERE s.place_id = parent_place_id;

 
 

-    name_vector := initial_name_vector;
-    nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
+  -- Find all address tags that don't appear in the parent search names.
+  SELECT hstore(array_agg(ARRAY[k, v])) INTO filtered_address
+    FROM (SELECT skeys(address) as k, svals(address) as v) a
+   WHERE not addr_ids_from_name(v) && parent_address_vector
+         AND k not in ('country', 'street', 'place', 'postcode',
+                       'housenumber', 'streetnumber', 'conscriptionnumber');

 
 

-    IF not address ? 'street' and address ? 'place' THEN
-      -- make sure addr:place terms are always searchable
-      nameaddress_vector := array_merge(nameaddress_vector,
-                                        addr_ids_from_name(address->'place'));
-    END IF;
+  -- Compute all search terms from the addr: tags.
+  IF filtered_address IS NOT NULL THEN
+    FOR addr_item IN
+      SELECT * FROM
+        get_places_for_addr_tags(in_partition, geometry, filtered_address, country)
+    LOOP
+        IF addr_item.place_id is null THEN
+            nameaddress_vector := array_merge(nameaddress_vector,
+                                              addr_item.keywords);
+            CONTINUE;
+        END IF;

 
 

-    RETURN;
-  END IF;
+        IF parent_address_place_ids is null THEN
+            SELECT array_agg(parent_place_id) INTO parent_address_place_ids
+              FROM place_addressline
+             WHERE place_id = parent_place_id;
+        END IF;

 
 

-  ----- unnamed POIS
+        IF not parent_address_place_ids @> ARRAY[addr_item.place_id] THEN
+            nameaddress_vector := array_merge(nameaddress_vector,
+                                              addr_item.keywords);

 
 

-  IF (array_length(nameaddress_vector, 1) is null
-      and (address ? 'street'or not address ? 'place'))
-     or housenumber is null
-  THEN
-    RETURN;
+            INSERT INTO place_addressline (place_id, address_place_id, fromarea,
+                                           isaddress, distance, cached_rank_address)
+            VALUES (obj_place_id, addr_item.place_id, not addr_item.isguess,
+                    true, addr_item.distance, addr_item.rank_address);
+        END IF;
+    END LOOP;

   END IF;
 
   END IF;
 

-  SELECT s.name_vector, s.nameaddress_vector
-    INTO parent_name_vector, parent_address_vector
-    FROM search_name s
-    WHERE s.place_id = parent_place_id;
+  name_vector := initial_name_vector;

 
   -- Check if the parent covers all address terms.
   -- If not, create a search name entry with the house number as the name.
   -- This is unusual for the search_name table but prevents that the place
   -- is returned when we only search for the street/place.
 
 
   -- Check if the parent covers all address terms.
   -- If not, create a search name entry with the house number as the name.
   -- This is unusual for the search_name table but prevents that the place
   -- is returned when we only search for the street/place.
 

-  IF not nameaddress_vector <@ parent_address_vector THEN
-    name_vector := ARRAY[getorcreate_name_id(housenumber)];
+  IF housenumber is not null and not nameaddress_vector <@ parent_address_vector THEN
+    name_vector := array_merge(name_vector,
+                               ARRAY[getorcreate_housenumber_id(make_standard_name(housenumber))]);

   END IF;
 
   IF not address ? 'street' and address ? 'place' THEN
     addr_place_ids := addr_ids_from_name(address->'place');
     IF not addr_place_ids <@ parent_name_vector THEN
   END IF;
 
   IF not address ? 'street' and address ? 'place' THEN
     addr_place_ids := addr_ids_from_name(address->'place');
     IF not addr_place_ids <@ parent_name_vector THEN

-      -- addr:place tag exists without a corresponding place. Mix in addr:place
-      -- in the address and drop the name from the parent. This would only be
-      -- the street name of the nearest street.
+      -- make sure addr:place terms are always searchable

       nameaddress_vector := array_merge(nameaddress_vector, addr_place_ids);
       nameaddress_vector := array_merge(nameaddress_vector, addr_place_ids);

-      name_vector := ARRAY[getorcreate_name_id(housenumber)];
+      -- If there is a housenumber, also add the place name as a name,
+      -- so we can search it by the usual housenumber+place algorithms.
+      IF housenumber is not null THEN
+        name_vector := array_merge(name_vector,
+                                   ARRAY[getorcreate_name_id(make_standard_name(address->'place'))]);
+      END IF;

     END IF;
     END IF;

-  ELSE
-    nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector);

   END IF;
 
   END IF;
 

-  -- The address vector always gets merged in.
+  -- Cheating here by not recomputing all terms but simply using the ones
+  -- from the parent object.
+  nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector);

   nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
 
 END;
   nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector);
 
 END;