restrict depth of word set calculation

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index aa2a8908a1f24a664681524d8094b4c5d0a570a5..e0d0897f97f4dd19bad10fbd76ed8f01162f7a12 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -5,7 +5,6 @@
 --  score integer
 --);
 
 --  score integer
 --);
 

-

 CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT
   AS $$
 DECLARE


@@ -96,7 +95,7 @@ BEGIN
   SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);

   ELSE
     IF count > get_maxwordfreq() THEN
       return_word_id := NULL;
   ELSE
     IF count > get_maxwordfreq() THEN
       return_word_id := NULL;


@@ -118,7 +117,7 @@ BEGIN
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, 'place', 'house', null, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, 'place', 'house', null, 0);

   END IF;
   RETURN return_word_id;
 END;
   END IF;
   RETURN return_word_id;
 END;


@@ -136,7 +135,7 @@ BEGIN
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, lookup_country_code, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, lookup_country_code, 0);

   END IF;
   RETURN return_word_id;
 END;
   END IF;
   RETURN return_word_id;
 END;


@@ -154,7 +153,7 @@ BEGIN
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0);

   END IF;
   RETURN return_word_id;
 END;
   END IF;
   RETURN return_word_id;
 END;


@@ -172,7 +171,7 @@ BEGIN
   SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, null, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);

   END IF;
   RETURN return_word_id;
 END;
   END IF;
   RETURN return_word_id;
 END;


@@ -204,7 +203,7 @@ BEGIN
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
   SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, op, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0, op);

   END IF;
   RETURN return_word_id;
 END;
   END IF;
   RETURN return_word_id;
 END;


@@ -223,7 +222,7 @@ BEGIN
   SELECT min(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
     return_word_id := nextval('seq_word');
   SELECT min(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
     return_word_id := nextval('seq_word');

-    INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), src_word, null, null, null, 0, null);
+    INSERT INTO word VALUES (return_word_id, lookup_token, src_word, null, null, null, 0);

 --    nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ','');
 --    IF ' '||nospace_lookup_token != lookup_token THEN
 --      INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null);
 --    nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ','');
 --    IF ' '||nospace_lookup_token != lookup_token THEN
 --      INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null);


@@ -346,6 +345,12 @@ BEGIN
       result := result || w;
     END IF;
 
       result := result || w;
     END IF;
 

+    w := getorcreate_word_id(s);
+
+    IF NOT (ARRAY[w] <@ result) THEN
+      result := result || w;
+    END IF;
+

     words := string_to_array(s, ' ');
     IF array_upper(words, 1) IS NOT NULL THEN
       FOR j IN 1..array_upper(words, 1) LOOP
     words := string_to_array(s, ' ');
     IF array_upper(words, 1) IS NOT NULL THEN
       FOR j IN 1..array_upper(words, 1) LOOP


@@ -408,6 +413,12 @@ BEGIN
     result := result || w;
   END IF;
 
     result := result || w;
   END IF;
 

+  w := getorcreate_word_id(s);
+
+  IF NOT (ARRAY[w] <@ result) THEN
+    result := result || w;
+  END IF;
+

   words := string_to_array(s, ' ');
   IF array_upper(words, 1) IS NOT NULL THEN
     FOR j IN 1..array_upper(words, 1) LOOP
   words := string_to_array(s, ' ');
   IF array_upper(words, 1) IS NOT NULL THEN
     FOR j IN 1..array_upper(words, 1) LOOP


@@ -638,7 +649,7 @@ BEGIN
 
 --  RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
 
 
 --  RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
 

-  x := deleteLocationArea(partition, place_id);
+  x := deleteLocationArea(partition, place_id, rank_search);

 
   isarea := false;
   IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
 
   isarea := false;
   IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN


@@ -705,7 +716,7 @@ CREATE OR REPLACE FUNCTION update_location(
 DECLARE
   b BOOLEAN;
 BEGIN
 DECLARE
   b BOOLEAN;
 BEGIN

-  b := deleteLocationArea(partition, place_id);
+  b := deleteLocationArea(partition, place_id, rank_search);

 --  result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
   RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
 END;
 --  result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
   RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
 END;


@@ -783,8 +794,8 @@ CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype
 DECLARE
   
   newpoints INTEGER;
 DECLARE
   
   newpoints INTEGER;

-  waynodes integer[];
-  nodeid INTEGER;
+  waynodes BIGINT[];
+  nodeid BIGINT;

   prevnode RECORD;
   nextnode RECORD;
   startnumber INTEGER;
   prevnode RECORD;
   nextnode RECORD;
   startnumber INTEGER;


@@ -812,10 +823,10 @@ BEGIN
 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 
 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 

-        select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' INTO search_place_id;
+        select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' INTO search_place_id;

         IF search_place_id IS NULL THEN
           -- null record of right type
         IF search_place_id IS NULL THEN
           -- null record of right type

-          select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' limit 1 INTO nextnode;
+          select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode;

           select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
           IF nextnode.geometry IS NULL THEN
             -- we don't have any information about this point, most likely
           select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
           IF nextnode.geometry IS NULL THEN
             -- we don't have any information about this point, most likely


@@ -1144,6 +1155,9 @@ BEGIN
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN
       RETURN NULL;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN
       RETURN NULL;

+    ELSEIF NEW.class = 'mountain_pass' THEN
+        NEW.rank_search := 20;
+        NEW.rank_address := 0;

     END IF;
 
   END IF;
     END IF;
 
   END IF;


@@ -1209,7 +1223,13 @@ BEGIN
     END IF;
     IF diameter > 0 THEN
 --      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
     END IF;
     IF diameter > 0 THEN
 --      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;

-      update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
+      IF NEW.rank_search >= 26 THEN
+        -- roads may cause reparenting for >27 rank places
+        update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+      ELSE
+        -- for all other places the search terms may change as well
+        update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
+      END IF;

     END IF;
 
   END IF;
     END IF;
 
   END IF;


@@ -1289,7 +1309,7 @@ BEGIN
     RETURN NULL;
   END IF;
 
     RETURN NULL;
   END IF;
 

-  IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
+  IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN

     RETURN NEW;
   END IF;
 
     RETURN NEW;
   END IF;
 


@@ -1303,23 +1323,25 @@ BEGIN
     RETURN NEW;
   END IF;
 
     RETURN NEW;
   END IF;
 

+  -- TODO: this test is now redundant?

   IF OLD.indexed_status != 0 THEN
   IF OLD.indexed_status != 0 THEN

-    --DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;

 
     NEW.indexed_date = now();
 
 
     NEW.indexed_date = now();
 

-    IF NEW.class = 'place' AND NEW.type = 'houses' THEN
-      i := create_interpolation(NEW.osm_id, NEW.housenumber);
+    result := deleteSearchName(NEW.partition, NEW.place_id);
+    DELETE FROM place_addressline WHERE place_id = NEW.place_id;
+    DELETE FROM place_boundingbox where place_id = NEW.place_id;
+    result := deleteRoad(NEW.partition, NEW.place_id);
+    result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
+    UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+
+    IF NEW.linked_place_id is not null THEN

       RETURN NEW;
     END IF;
 
       RETURN NEW;
     END IF;
 

-    IF OLD.indexed_status > 0 THEN
-      result := deleteSearchName(NEW.partition, NEW.place_id);
-      DELETE FROM place_addressline WHERE place_id = NEW.place_id;
-      DELETE FROM place_boundingbox where place_id = NEW.place_id;
-      result := deleteRoad(NEW.partition, NEW.place_id);
-      result := deleteLocationArea(NEW.partition, NEW.place_id);
-      UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+    IF NEW.class = 'place' AND NEW.type = 'houses' THEN
+      i := create_interpolation(NEW.osm_id, NEW.housenumber);
+      RETURN NEW;

     END IF;
 
     -- Speed up searches - just use the centroid of the feature
     END IF;
 
     -- Speed up searches - just use the centroid of the feature


@@ -1383,6 +1405,9 @@ BEGIN
 
 --RAISE WARNING 'finding street for %', NEW;
 
 
 --RAISE WARNING 'finding street for %', NEW;
 

+      -- We won't get a better centroid, besides these places are too small to care
+      NEW.centroid := place_centroid;
+

       NEW.parent_place_id := null;
 
       -- to do that we have to find our parent road
       NEW.parent_place_id := null;
 
       -- to do that we have to find our parent road


@@ -1398,7 +1423,7 @@ BEGIN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'node in relation %',relation;
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'node in relation %',relation;

-                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
+                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint 

                   and rank_search = 26 INTO NEW.parent_place_id;
               END IF;
             END LOOP;
                   and rank_search = 26 INTO NEW.parent_place_id;
               END IF;
             END LOOP;


@@ -1427,7 +1452,7 @@ BEGIN
                   FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
                     IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
     --RAISE WARNING 'node in way that is in a relation %',relation;
                   FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
                     IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
     --RAISE WARNING 'node in way that is in a relation %',relation;

-                      SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
+                      SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint 

                         and rank_search = 26 INTO NEW.parent_place_id;
                     END IF;
                   END LOOP;
                         and rank_search = 26 INTO NEW.parent_place_id;
                     END IF;
                   END LOOP;


@@ -1472,7 +1497,7 @@ BEGIN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'way that is in a relation %',relation;
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'way that is in a relation %',relation;

-                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
+                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint

                   and rank_search = 26 INTO NEW.parent_place_id;
               END IF;
             END LOOP;
                   and rank_search = 26 INTO NEW.parent_place_id;
               END IF;
             END LOOP;


@@ -1535,7 +1560,7 @@ BEGIN
           result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
         END IF;
 
           result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
         END IF;
 

-        result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
+        result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);

 
         return NEW;
       END IF;
 
         return NEW;
       END IF;


@@ -1565,7 +1590,7 @@ BEGIN
             -- merge in the label name, re-init word vector
             IF NOT linkedPlacex.name IS NULL THEN
               NEW.name := linkedPlacex.name || NEW.name;
             -- merge in the label name, re-init word vector
             IF NOT linkedPlacex.name IS NULL THEN
               NEW.name := linkedPlacex.name || NEW.name;

-              name_vector := make_keywords(NEW.name);
+              name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));

             END IF;
 
             -- merge in extra tags
             END IF;
 
             -- merge in extra tags


@@ -1573,6 +1598,10 @@ BEGIN
               NEW.extratags := linkedPlacex.extratags || NEW.extratags;
             END IF;
 
               NEW.extratags := linkedPlacex.extratags || NEW.extratags;
             END IF;
 

+            IF NOT NEW.extratags ? linkedPlacex.class THEN
+              NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+            END IF;
+

             -- mark the linked place (excludes from search results)
             UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
             -- mark the linked place (excludes from search results)
             UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 


@@ -1592,7 +1621,6 @@ BEGIN
               IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
                 AND NEW.rank_address = linkedPlacex.rank_address THEN
 
               IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
                 AND NEW.rank_address = linkedPlacex.rank_address THEN
 

-

                 -- If we don't already have one use this as the centre point of the geometry
                 IF NEW.centroid IS NULL THEN
                   NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
                 -- If we don't already have one use this as the centre point of the geometry
                 IF NEW.centroid IS NULL THEN
                   NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));


@@ -1609,6 +1637,10 @@ BEGIN
                   NEW.extratags := linkedPlacex.extratags || NEW.extratags;
                 END IF;
 
                   NEW.extratags := linkedPlacex.extratags || NEW.extratags;
                 END IF;
 

+                IF NOT NEW.extratags ? linkedPlacex.class THEN
+                  NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+                END IF;
+

                 -- mark the linked place (excludes from search results)
                 UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
                 -- mark the linked place (excludes from search results)
                 UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 


@@ -1646,6 +1678,10 @@ BEGIN
           -- merge in extra tags
           NEW.extratags := linkedPlacex.extratags || NEW.extratags;
 
           -- merge in extra tags
           NEW.extratags := linkedPlacex.extratags || NEW.extratags;
 

+          IF NOT NEW.extratags ? linkedPlacex.class THEN
+            NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+          END IF;
+

           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 


@@ -1656,6 +1692,19 @@ BEGIN
 
       IF NEW.centroid IS NOT NULL THEN
         place_centroid := NEW.centroid;
 
       IF NEW.centroid IS NOT NULL THEN
         place_centroid := NEW.centroid;

+        -- Place might have had only a name tag before but has now received translations
+        -- from the linked place. Make sure a name tag for the default language exists in
+        -- this case. 
+        IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+          default_language := get_country_language_code(NEW.calculated_country_code);
+          IF default_language IS NOT NULL THEN
+            IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+              NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
+            ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+              NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
+            END IF;
+          END IF;
+        END IF;

       END IF;
 
       -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
       END IF;
 
       -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance


@@ -1685,8 +1734,15 @@ BEGIN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
           IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
           IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN

+            nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);

             isin_tokens := isin_tokens || address_street_word_id;
           END IF;
             isin_tokens := isin_tokens || address_street_word_id;
           END IF;

+
+          -- merge word into address vector
+          address_street_word_id := get_word_id(make_standard_name(isin[i]));
+          IF address_street_word_id IS NOT NULL THEN
+            nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+          END IF;

         END LOOP;
       END IF;
     END IF;
         END LOOP;
       END IF;
     END IF;


@@ -1696,11 +1752,32 @@ BEGIN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
           IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
           IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN

+            nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);

             isin_tokens := isin_tokens || address_street_word_id;
           END IF;
             isin_tokens := isin_tokens || address_street_word_id;
           END IF;

+
+          -- merge into address vector
+          address_street_word_id := get_word_id(make_standard_name(isin[i]));
+          IF address_street_word_id IS NOT NULL THEN
+            nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+          END IF;

         END LOOP;
       END IF;
     END IF;
         END LOOP;
       END IF;
     END IF;

+
+    -- for the USA we have an additional address table.  Merge in zip codes from there too
+    IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+      FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
+        address_street_word_id := get_name_id(make_standard_name(location.postcode));
+        nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+        isin_tokens := isin_tokens || address_street_word_id;
+
+        -- also merge in the single word version
+        address_street_word_id := get_word_id(make_standard_name(location.postcode));
+        nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+      END LOOP;
+    END IF;
+

 -- RAISE WARNING 'ISIN: %', isin_tokens;
 
     -- Process area matches
 -- RAISE WARNING 'ISIN: %', isin_tokens;
 
     -- Process area matches


@@ -1814,9 +1891,8 @@ BEGIN
         result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
       END IF;
 
         result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
       END IF;
 

-      result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
+      result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);

 
 

---      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.calculated_country_code, name_vector, nameaddress_vector, place_centroid);

     END IF;
 
     -- If we've not managed to pick up a better one - default centroid
     END IF;
 
     -- If we've not managed to pick up a better one - default centroid


@@ -1866,7 +1942,7 @@ BEGIN
   --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
 
   IF OLD.rank_address < 26 THEN
   --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
 
   IF OLD.rank_address < 26 THEN

-    b := deleteLocationArea(OLD.partition, OLD.place_id);
+    b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);

   END IF;
 
   --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
   END IF;
 
   --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;


@@ -2267,11 +2343,11 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-DROP TYPE addressline CASCADE;
+DROP TYPE IF EXISTS addressline CASCADE;

 create type addressline as (
   place_id BIGINT,
   osm_type CHAR(1),
 create type addressline as (
   place_id BIGINT,
   osm_type CHAR(1),

-  osm_id INTEGER,
+  osm_id BIGINT,

   name HSTORE,
   class TEXT,
   type TEXT,
   name HSTORE,
   class TEXT,
   type TEXT,