Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index b1280caca85f3d38a7fc95f2f6f3f0e1f6450029..5b17f6d034a927e17460d6d9e4f692addb21bd08 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


@@ -83,18 +82,24 @@ END;
 $$
 LANGUAGE 'plpgsql' IMMUTABLE;
 
 $$
 LANGUAGE 'plpgsql' IMMUTABLE;
 

+-- returns NULL if the word is too common

 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT) 
   RETURNS INTEGER
   AS $$
 DECLARE
   lookup_token TEXT;
   return_word_id INTEGER;
 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT) 
   RETURNS INTEGER
   AS $$
 DECLARE
   lookup_token TEXT;
   return_word_id INTEGER;

+  count INTEGER;

 BEGIN
   lookup_token := trim(lookup_word);
 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;
+  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);
   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);

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

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


@@ -290,6 +295,34 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

+CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
+  AS $$
+DECLARE
+  s TEXT;
+  w INTEGER;
+  words TEXT[];
+  item RECORD;
+  j INTEGER;
+BEGIN
+  FOR item IN SELECT (each(src)).* LOOP
+
+    s := make_standard_name(item.value);
+    w := getorcreate_country(s, lookup_country_code);
+
+    words := regexp_split_to_array(item.value, E'[,;()]');
+    IF array_upper(words, 1) != 1 THEN
+      FOR j IN 1..array_upper(words, 1) LOOP
+        s := make_standard_name(words[j]);
+        IF s != '' THEN
+          w := getorcreate_country(s, lookup_country_code);
+        END IF;
+      END LOOP;
+    END IF;
+  END LOOP;
+END;
+$$
+LANGUAGE plpgsql;
+

 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[]
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[]
   AS $$
 DECLARE


@@ -317,7 +350,7 @@ BEGIN
       FOR j IN 1..array_upper(words, 1) LOOP
         IF (words[j] != '') THEN
           w = getorcreate_word_id(words[j]);
       FOR j IN 1..array_upper(words, 1) LOOP
         IF (words[j] != '') THEN
           w = getorcreate_word_id(words[j]);

-          IF NOT (ARRAY[w] <@ result) THEN
+          IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

             result := result || w;
           END IF;
         END IF;
             result := result || w;
           END IF;
         END IF;


@@ -330,7 +363,7 @@ BEGIN
         s := make_standard_name(words[j]);
         IF s != '' THEN
           w := getorcreate_word_id(s);
         s := make_standard_name(words[j]);
         IF s != '' THEN
           w := getorcreate_word_id(s);

-          IF NOT (ARRAY[w] <@ result) THEN
+          IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

             result := result || w;
           END IF;
         END IF;
             result := result || w;
           END IF;
         END IF;


@@ -379,7 +412,7 @@ BEGIN
     FOR j IN 1..array_upper(words, 1) LOOP
       IF (words[j] != '') THEN
         w = getorcreate_word_id(words[j]);
     FOR j IN 1..array_upper(words, 1) LOOP
       IF (words[j] != '') THEN
         w = getorcreate_word_id(words[j]);

-        IF NOT (ARRAY[w] <@ result) THEN
+        IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

           result := result || w;
         END IF;
       END IF;
           result := result || w;
         END IF;
       END IF;


@@ -392,7 +425,7 @@ BEGIN
       s := make_standard_name(words[j]);
       IF s != '' THEN
         w := getorcreate_word_id(s);
       s := make_standard_name(words[j]);
       IF s != '' THEN
         w := getorcreate_word_id(s);

-        IF NOT (ARRAY[w] <@ result) THEN
+        IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

           result := result || w;
         END IF;
       END IF;
           result := result || w;
         END IF;
       END IF;


@@ -537,6 +570,20 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

+CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) RETURNS TEXT[]
+  AS $$
+DECLARE
+  nearcountry RECORD;
+BEGIN
+  FOR nearcountry IN select country_default_language_codes from country_name where country_code = search_country_code limit 1
+  LOOP
+    RETURN lower(nearcountry.country_default_language_codes);
+  END LOOP;
+  RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+

 CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
   AS $$
 DECLARE


@@ -590,7 +637,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


@@ -657,7 +704,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;


@@ -916,9 +963,9 @@ BEGIN
     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
     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 || (('name:'||default_language) => (NEW.name -> 'name'));
+        NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));

       ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
       ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN

-        NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
+        NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));

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


@@ -945,7 +992,7 @@ BEGIN
             RETURN NULL;
         END IF;
 
             RETURN NULL;
         END IF;
 

-        NEW.name := 'ref'=>NEW.postcode;
+        NEW.name := hstore('ref', NEW.postcode);

 
         IF NEW.calculated_country_code = 'gb' THEN
 
 
         IF NEW.calculated_country_code = 'gb' THEN
 


@@ -1068,6 +1115,9 @@ BEGIN
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
       NEW.rank_address := NEW.rank_search;

+    ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
+      NEW.rank_search := 18;
+      NEW.rank_address := 0;

     -- any feature more than 5 square miles is probably worth indexing
     ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
       NEW.rank_search := 22;
     -- any feature more than 5 square miles is probably worth indexing
     ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
       NEW.rank_search := 22;


@@ -1093,9 +1143,6 @@ 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 = 'natural' and NEW.type in ('peak','volcano') THEN
-      NEW.rank_search := 18;
-      NEW.rank_address := 0;

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


@@ -1161,7 +1208,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;


@@ -1234,7 +1287,14 @@ DECLARE
   result BOOLEAN;
 BEGIN
 
   result BOOLEAN;
 BEGIN
 

-  IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
+  -- deferred delete
+  IF OLD.indexed_status = 100 THEN
+    --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
+    delete from placex where place_id = OLD.place_id;
+    RETURN NULL;
+  END IF;
+
+  IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN

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


@@ -1248,30 +1308,25 @@ BEGIN
     RETURN NEW;
   END IF;
 
     RETURN NEW;
   END IF;
 

-  -- deferred delete
-  IF OLD.indexed_status = 100 THEN
-    --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
-    delete from placex where place_id = OLD.place_id;
-    RETURN NULL;
-  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


@@ -1301,9 +1356,9 @@ BEGIN
       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
       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 || (('name:'||default_language) => (NEW.name -> 'name'));
+          NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));

         ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
         ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN

-          NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
+          NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));

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


@@ -1335,6 +1390,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


@@ -1478,6 +1536,7 @@ BEGIN
 
         -- Merge address from parent
         nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
 
         -- Merge address from parent
         nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);

+        nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);

 --return NEW;
         -- Performance, it would be more acurate to do all the rest of the import process but it takes too long
         -- Just be happy with inheriting from parent road only
 --return NEW;
         -- Performance, it would be more acurate to do all the rest of the import process but it takes too long
         -- Just be happy with inheriting from parent road only


@@ -1502,62 +1561,76 @@ BEGIN
       select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
 
 -- RAISE WARNING 'get_osm_rel_members, label';
       select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
 
 -- RAISE WARNING 'get_osm_rel_members, label';

-      FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+      IF relation_members IS NOT NULL THEN
+        FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP

 
 

-        select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
-          and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+          FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
+            and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP

 
 

-        -- 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));
-        END IF;
+            -- 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));
+            END IF;

 
 

-        -- merge in the label name, re-init word vector
-        NEW.name := linkedPlacex.name || NEW.name;
-        name_vector := make_keywords(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);
+            END IF;

 
 

-        -- merge in extra tags
-        NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+            -- merge in extra tags
+            IF NOT linkedPlacex.extratags IS NULL THEN
+              NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+            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;

 
 

-      END LOOP;
+          END LOOP;

 
 

-      IF NEW.centroid IS NULL THEN
+        END LOOP;

 
 

-        FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+        IF NEW.centroid IS NULL THEN

 
 

-          select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
-            and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+          FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP

 
 

-          -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
-          -- But that can be fixed by explicitly setting the label in the data
-          IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
-            AND NEW.rank_search = linkedPlacex.rank_search THEN
+            FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
+              and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP

 
 

+              -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+              -- But that can be fixed by explicitly setting the label in the data
+              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));
-            END IF;

 
 

-            -- merge in the name, re-init word vector
-            NEW.name := linkedPlacex.name || NEW.name;
-            name_vector := make_keywords(NEW.name);
+                -- 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));
+                END IF;

 
 

-            -- merge in extra tags
-            NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+                -- merge in the name, re-init word vector
+                IF NOT linkedPlacex.name IS NULL THEN
+                  NEW.name := linkedPlacex.name || NEW.name;
+                  name_vector := make_keywords(NEW.name);
+                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;
+                -- merge in extra tags
+                IF NOT linkedPlacex.extratags IS NULL THEN
+                  NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+                END IF;

 
 

-            -- keep a note of the node id in case we need it for wikipedia in a bit
-            linked_node_id := linkedPlacex.osm_id;
-          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;

 
 

-        END LOOP;
+                -- keep a note of the node id in case we need it for wikipedia in a bit
+                linked_node_id := linkedPlacex.osm_id;
+              END IF;

 
 

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

       END IF;
 
       -- not found one yet? how about doing a name search
       END IF;
 
       -- not found one yet? how about doing a name search


@@ -1565,7 +1638,7 @@ BEGIN
 
         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
 
         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')

-          AND placex.rank_search = NEW.rank_search
+          AND placex.rank_address = NEW.rank_address

           AND placex.place_id != NEW.place_id
           AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
           AND st_covers(NEW.geometry, placex.geometry)
           AND placex.place_id != NEW.place_id
           AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
           AND st_covers(NEW.geometry, placex.geometry)


@@ -1606,6 +1679,11 @@ BEGIN
 
     END IF;
 
 
     END IF;
 

+    -- make sure all names are in the word table
+    IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+      perform create_country(NEW.name, lower(NEW.country_code));
+    END IF;
+

     NEW.parent_place_id = 0;
     parent_place_id_rank = 0;
 
     NEW.parent_place_id = 0;
     parent_place_id_rank = 0;
 


@@ -1633,6 +1711,16 @@ BEGIN
         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;
+      END LOOP;
+    END IF;
+

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


@@ -1660,7 +1748,9 @@ BEGIN
 
         -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
         -- Add it to the list of search terms
 
         -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
         -- Add it to the list of search terms

-        nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+        IF location.rank_search > 4 THEN
+            nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+        END IF;

         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
 
         IF location_isaddress THEN
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
 
         IF location_isaddress THEN


@@ -1685,21 +1775,25 @@ BEGIN
     IF array_upper(isin_tokens, 1) IS NOT NULL THEN
       FOR i IN 1..array_upper(isin_tokens, 1) LOOP
 --RAISE WARNING '  getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
     IF array_upper(isin_tokens, 1) IS NOT NULL THEN
       FOR i IN 1..array_upper(isin_tokens, 1) LOOP
 --RAISE WARNING '  getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];

+        IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN

 
 

-        FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+          FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP

 
 

---RAISE WARNING '  ISIN: %',location;
+  --RAISE WARNING '  ISIN: %',location;

 
 

-          nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
-          INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
-          address_havelevel[location.rank_address] := true;
+            IF location.rank_search > 4 THEN
+                nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+                INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
+                address_havelevel[location.rank_address] := true;

 
 

-          IF location.rank_address > parent_place_id_rank THEN
-            NEW.parent_place_id = location.place_id;
-            parent_place_id_rank = location.rank_address;
-          END IF;
+                IF location.rank_address > parent_place_id_rank THEN
+                  NEW.parent_place_id = location.place_id;
+                  parent_place_id_rank = location.rank_address;
+                END IF;
+            END IF;
+          END LOOP;

 
 

-        END LOOP;
+        END IF;

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


@@ -1717,7 +1811,7 @@ BEGIN
           location_distance := location.distance * 1.5;
         END IF;
 
           location_distance := location.distance * 1.5;
         END IF;
 

-        IF location.distance < location_distance THEN
+        IF location.rank_search > 4 AND location.distance < location_distance THEN

 
           -- Add it to the list of search terms
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
 
           -- Add it to the list of search terms
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);


@@ -1765,9 +1859,9 @@ DECLARE
 BEGIN
   -- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
 
 BEGIN
   -- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
 

-  update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+  update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;

   --DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
   --DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;

-  update placex set indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+  update placex set linked_place_id = null where linked_place_id = OLD.place_id;

   --DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
 
   IF OLD.rank_address < 30 THEN
   --DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
 
   IF OLD.rank_address < 30 THEN


@@ -1792,7 +1886,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;


@@ -2098,7 +2192,9 @@ BEGIN
     END IF;
   END LOOP;
 
     END IF;
   END LOOP;
 

-  RETURN null;
+  -- anything will do as a fallback - just take the first name type thing there is
+  search := avals(name);
+  RETURN search[1];

 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;


@@ -2254,7 +2350,7 @@ BEGIN
   hadcountry := false;
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
   hadcountry := false;
   FOR location IN 
     select placex.place_id, osm_type, osm_id,

-      CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+      CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,

       class, type, admin_level, true as fromarea, true as isaddress,
       CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       0 as distance, calculated_country_code
       class, type, admin_level, true as fromarea, true as isaddress,
       CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       0 as distance, calculated_country_code


@@ -2287,7 +2383,7 @@ BEGIN
 
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
 
   FOR location IN 
     select placex.place_id, osm_type, osm_id,

-      CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+      CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,

       class, type, admin_level, fromarea, isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,calculated_country_code
       class, type, admin_level, fromarea, isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,calculated_country_code


@@ -2332,7 +2428,7 @@ BEGIN
   END IF;
 
   IF searchcountrycode IS NOT NULL THEN
   END IF;
 
   IF searchcountrycode IS NOT NULL THEN

-    location := ROW(null, null, null, 'ref'=>searchcountrycode, 'place', 'country_code', null, true, false, 4, 0)::addressline;
+    location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline;

     RETURN NEXT location;
   END IF;
 
     RETURN NEXT location;
   END IF;
 


@@ -2343,12 +2439,12 @@ BEGIN
   END IF;
 
   IF searchhousenumber IS NOT NULL THEN
   END IF;
 
   IF searchhousenumber IS NOT NULL THEN

-    location := ROW(in_place_id, null, null, 'ref'=>searchhousenumber, 'place', 'house_number', null, true, true, 28, 0)::addressline;
+    location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline;

     RETURN NEXT location;
   END IF;
 
   IF searchpostcode IS NOT NULL THEN
     RETURN NEXT location;
   END IF;
 
   IF searchpostcode IS NOT NULL THEN

-    location := ROW(null, null, null, 'ref'=>searchpostcode, 'place', 'postcode', null, true, true, 5, 0)::addressline;
+    location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline;

     RETURN NEXT location;
   END IF;
 
     RETURN NEXT location;
   END IF;
 


@@ -2844,3 +2940,70 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql;
 END;
 $$
 LANGUAGE plpgsql;

+
+
+CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN
+  AS $$
+DECLARE
+    osmid BIGINT;
+    osmtype character(1);
+    pclass text;
+    ptype text;
+BEGIN
+  SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
+  DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+  DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+  -- force delete from place/placex by making it a very small geometry
+  UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+  DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+
+  RETURN TRUE;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT) RETURNS BOOLEAN
+  AS $$
+DECLARE
+  placegeom GEOMETRY;
+  geom GEOMETRY;
+  diameter FLOAT;
+  rank INTEGER;
+BEGIN
+  SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
+  IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
+    IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
+      FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
+        update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
+        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null);
+        update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
+        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null);
+      END LOOP;
+    ELSE
+        diameter := 0;
+        IF rank = 11 THEN
+          diameter := 0.05;
+        ELSEIF rank < 18 THEN
+          diameter := 0.1;
+        ELSEIF rank < 20 THEN
+          diameter := 0.05;
+        ELSEIF rank = 21 THEN
+          diameter := 0.001;
+        ELSEIF rank < 24 THEN
+          diameter := 0.02;
+        ELSEIF rank < 26 THEN
+          diameter := 0.002; -- 100 to 200 meters
+        ELSEIF rank < 28 THEN
+          diameter := 0.001; -- 50 to 100 meters
+        END IF;
+        IF diameter > 0 THEN
+          update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
+        END IF;
+    END IF;
+    RETURN TRUE;
+  END IF;
+
+  RETURN FALSE;
+END;
+$$
+LANGUAGE plpgsql;