change code to work with a 64-bit node size osm2pgsql

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 62829ffe11402a9d54a5019d558305de3dd0abfd..2c81af32437be0a05a09ad3310b9b50aaaa7a1cd 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -302,7 +302,7 @@ BEGIN
   END IF;
   r := a;
   FOR i IN 1..array_upper(b, 1) LOOP  
   END IF;
   r := a;
   FOR i IN 1..array_upper(b, 1) LOOP  

-    IF NOT (ARRAY[b[i]] && r) THEN
+    IF NOT (ARRAY[b[i]] <@ r) THEN

       r := r || b[i];
     END IF;
   END LOOP;
       r := r || b[i];
     END IF;
   END LOOP;


@@ -328,15 +328,18 @@ BEGIN
     s := make_standard_name(item.value);
 
     w := getorcreate_name_id(s, item.value);
     s := make_standard_name(item.value);
 
     w := getorcreate_name_id(s, item.value);

-    result := result | w;
+
+    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
         IF (words[j] != '') THEN
           w = getorcreate_word_id(words[j]);
 
     words := string_to_array(s, ' ');
     IF array_upper(words, 1) IS NOT NULL THEN
       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
-            result := result | w;
+          IF NOT (ARRAY[w] <@ result) THEN
+            result := result || w;

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


@@ -348,8 +351,8 @@ 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
-            result := result | w;
+          IF NOT (ARRAY[w] <@ result) THEN
+            result := result || w;

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


@@ -360,8 +363,8 @@ BEGIN
       s := make_standard_name(s);
       IF s != '' THEN
         w := getorcreate_name_id(s, item.value);
       s := make_standard_name(s);
       IF s != '' THEN
         w := getorcreate_name_id(s, item.value);

-        IF NOT (ARRAY[w] && result) THEN
-          result := result | w;
+        IF NOT (ARRAY[w] <@ result) THEN
+          result := result || w;

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


@@ -388,7 +391,7 @@ BEGIN
   s := make_standard_name(src);
   w := getorcreate_name_id(s);
 
   s := make_standard_name(src);
   w := getorcreate_name_id(s);
 

-  IF NOT (ARRAY[w] && result) THEN
+  IF NOT (ARRAY[w] <@ result) THEN

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


@@ -397,7 +400,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 NOT (ARRAY[w] <@ result) THEN

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


@@ -537,7 +540,7 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION delete_location(OLD_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN

   AS $$
 DECLARE
 BEGIN
   AS $$
 DECLARE
 BEGIN


@@ -549,7 +552,7 @@ $$
 LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION add_location(
 LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION add_location(

-    place_id INTEGER,
+    place_id BIGINT,

     country_code varchar(2),
     partition INTEGER,
     keywords INTEGER[],
     country_code varchar(2),
     partition INTEGER,
     keywords INTEGER[],


@@ -654,7 +657,7 @@ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION update_location(
     partition INTEGER,
 
 CREATE OR REPLACE FUNCTION update_location(
     partition INTEGER,

-    place_id INTEGER,
+    place_id BIGINT,

     place_country_code varchar(2),
     name hstore,
     rank_search INTEGER,
     place_country_code varchar(2),
     name hstore,
     rank_search INTEGER,


@@ -673,7 +676,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id INTEGER, to_add INTEGER[])
+CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id BIGINT, to_add INTEGER[])

   RETURNS BOOLEAN
   AS $$
 DECLARE
   RETURNS BOOLEAN
   AS $$
 DECLARE


@@ -690,9 +693,11 @@ BEGIN
       and search_name.place_id = place_addressline.place_id
   LOOP
     delete from search_name where place_id = childplace.place_id;
       and search_name.place_id = place_addressline.place_id
   LOOP
     delete from search_name where place_id = childplace.place_id;

-    childplace.nameaddress_vector := uniq(sort_asc(childplace.nameaddress_vector + to_add));
-    IF childplace.place_id = parent_place_id THEN
-      childplace.name_vector := uniq(sort_asc(childplace.name_vector + to_add));
+    IF not (ARRAY[to_add] <@ childplace.nameaddress_vector) THEN
+      childplace.nameaddress_vector := childplace.nameaddress_vector || to_add;
+    END IF;
+    IF childplace.place_id = parent_place_id and not (ARRAY[to_add] <@ childplace.name_vector) THEN
+      childplace.name_vector := childplace.name_vector || to_add;

     END IF;
     insert into search_name (place_id, search_rank, address_rank, country_code, name_vector, nameaddress_vector, centroid) 
       values (childplace.place_id, childplace.search_rank, childplace.address_rank, childplace.country_code, 
     END IF;
     insert into search_name (place_id, search_rank, address_rank, country_code, name_vector, nameaddress_vector, centroid) 
       values (childplace.place_id, childplace.search_rank, childplace.address_rank, childplace.country_code, 


@@ -704,7 +709,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id INTEGER, name hstore) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id BIGINT, name hstore) RETURNS BOOLEAN

   AS $$
 DECLARE
   newkeywords INTEGER[];
   AS $$
 DECLARE
   newkeywords INTEGER[];


@@ -737,7 +742,7 @@ $$
 LANGUAGE plpgsql;
 
 
 LANGUAGE plpgsql;
 
 

-CREATE OR REPLACE FUNCTION create_interpolation(wayid INTEGER, interpolationtype TEXT) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER

   AS $$
 DECLARE
   
   AS $$
 DECLARE
   


@@ -753,7 +758,7 @@ DECLARE
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;

-  search_place_id INTEGER;
+  search_place_id BIGINT;

   defpostalcode TEXT;
 
   havefirstpoint BOOLEAN;
   defpostalcode TEXT;
 
   havefirstpoint BOOLEAN;


@@ -788,7 +793,7 @@ BEGIN
           linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
           endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
 
           linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
           endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
 

-          IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 THEN
+          IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 AND @(startnumber - endnumber) < 1000 THEN

 
 --RAISE WARNING 'interpolation end % % ',nextnode.place_id,endnumber;
 
 
 --RAISE WARNING 'interpolation end % % ',nextnode.place_id,endnumber;
 


@@ -870,6 +875,7 @@ DECLARE
   country_code VARCHAR(2);
   default_language VARCHAR(10);
   diameter FLOAT;
   country_code VARCHAR(2);
   default_language VARCHAR(10);
   diameter FLOAT;

+  classtable TEXT;

 BEGIN
 --  RAISE WARNING '%',NEW.osm_id;
 
 BEGIN
 --  RAISE WARNING '%',NEW.osm_id;
 


@@ -903,18 +909,23 @@ BEGIN
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 

-  NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  IF NEW.rank_search >= 4 THEN
+    NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  ELSE
+    NEW.country_code := NULL;
+  END IF;
+

   NEW.partition := get_partition(NEW.geometry, NEW.country_code);
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)
   NEW.partition := get_partition(NEW.geometry, NEW.country_code);
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)

-  IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+  IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN

     default_language := get_country_language_code(NEW.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'));
       ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
     default_language := get_country_language_code(NEW.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'));
       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 || ('name' => (NEW.name -> ('name:'||default_language)));

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


@@ -934,7 +945,55 @@ BEGIN
     NEW.rank_address := NEW.rank_search;
 
     -- By doing in postgres we have the country available to us - currently only used for postcode
     NEW.rank_address := NEW.rank_search;
 
     -- By doing in postgres we have the country available to us - currently only used for postcode

-    IF NEW.class = 'place' THEN
+    IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+
+        IF NEW.postcode IS NULL THEN
+            -- most likely just a part of a multipolygon postcode boundary, throw it away
+            RETURN NULL;
+        END IF;
+
+        NEW.name := 'ref'=>NEW.postcode;
+
+        IF NEW.country_code = 'gb' THEN
+
+          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
+            NEW.rank_search := 25;
+            NEW.rank_address := 5;
+          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
+            NEW.rank_search := 23;
+            NEW.rank_address := 5;
+          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
+            NEW.rank_search := 21;
+            NEW.rank_address := 5;
+          END IF;
+
+        ELSEIF NEW.country_code = 'de' THEN
+
+          IF NEW.postcode ~ '^([0-9]{5})$' THEN
+            NEW.rank_search := 21;
+            NEW.rank_address := 11;
+          END IF;
+
+        ELSE
+          -- Guess at the postcode format and coverage (!)
+          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
+            NEW.rank_search := 21;
+            NEW.rank_address := 11;
+          ELSE
+            -- Does it look splitable into and area and local code?
+            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
+
+            IF postcode IS NOT NULL THEN
+              NEW.rank_search := 25;
+              NEW.rank_address := 11;
+            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
+              NEW.rank_search := 21;
+              NEW.rank_address := 11;
+            END IF;
+          END IF;
+        END IF;
+
+    ELSEIF NEW.class = 'place' THEN

       IF NEW.type in ('continent') THEN
         NEW.rank_search := 2;
         NEW.rank_address := NEW.rank_search;
       IF NEW.type in ('continent') THEN
         NEW.rank_search := 2;
         NEW.rank_address := NEW.rank_search;


@@ -948,8 +1007,8 @@ BEGIN
         NEW.rank_search := 8;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('region') THEN
         NEW.rank_search := 8;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('region') THEN

-        NEW.rank_search := 10;
-        NEW.rank_address := NEW.rank_search;
+        NEW.rank_search := 18; -- dropped from previous value of 10
+        NEW.rank_address := 0; -- So badly miss-used that better to just drop it!

       ELSEIF NEW.type in ('county') THEN
         NEW.rank_search := 12;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('county') THEN
         NEW.rank_search := 12;
         NEW.rank_address := NEW.rank_search;


@@ -980,55 +1039,16 @@ BEGIN
       ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;

-      ELSEIF NEW.type in ('farm','locality','islet') THEN
+      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN

         NEW.rank_search := 20;
         NEW.rank_address := 0;
         NEW.rank_search := 20;
         NEW.rank_address := 0;

+        -- Irish townlands, tagged as place=locality and locality=townland
+        IF (NEW.extratags -> 'locality') = 'townland' THEN
+          NEW.rank_address := 20;
+        END IF;

       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
         NEW.rank_search := 22;
         NEW.rank_address := 22;
       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
         NEW.rank_search := 22;
         NEW.rank_address := 22;

-      ELSEIF NEW.type in ('postcode') THEN
-
-        NEW.name := 'ref'=>NEW.postcode;
-
-        IF NEW.country_code = 'gb' THEN
-
-          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
-            NEW.rank_search := 25;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
-            NEW.rank_search := 23;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 5;
-          END IF;
-
-        ELSEIF NEW.country_code = 'de' THEN
-
-          IF NEW.postcode ~ '^([0-9]{5})$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          END IF;
-
-        ELSE
-          -- Guess at the postcode format and coverage (!)
-          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          ELSE
-            -- Does it look splitable into and area and local code?
-            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
-
-            IF postcode IS NOT NULL THEN
-              NEW.rank_search := 25;
-              NEW.rank_address := 11;
-            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
-              NEW.rank_search := 21;
-              NEW.rank_address := 11;
-            END IF;
-          END IF;
-        END IF;
-

       ELSEIF NEW.type in ('airport','street') THEN
         NEW.rank_search := 26;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('airport','street') THEN
         NEW.rank_search := 26;
         NEW.rank_address := NEW.rank_search;


@@ -1109,11 +1129,12 @@ BEGIN
     IF st_area(NEW.geometry) < 1 THEN
       -- mark items within the geometry for re-indexing
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
     IF st_area(NEW.geometry) < 1 THEN
       -- mark items within the geometry for re-indexing
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;

--- work around bug in postgis
+
+      -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)

       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point';
+       AND rank_search > NEW.rank_search 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_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point';
+       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null);

     END IF;
   ELSE
     -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
     END IF;
   ELSE
     -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(


@@ -1138,11 +1159,22 @@ 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);
+      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;
 

+   -- add to tables for special search
+   -- Note: won't work on initial import because the classtype tables
+   -- do not yet exist. It won't hurt either.
+  classtable := 'place_classtype_' || NEW.class || '_' || NEW.type;
+  SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable INTO result;
+  IF result THEN
+    EXECUTE 'INSERT INTO ' || classtable::regclass || ' (place_id, centroid) VALUES ($1,$2)' 
+    USING NEW.place_id, ST_Centroid(NEW.geometry);
+  END IF;
+
+

 --  IF NEW.rank_search < 26 THEN
 --    RAISE WARNING 'placex insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 --  END IF;
 --  IF NEW.rank_search < 26 THEN
 --    RAISE WARNING 'placex insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 --  END IF;


@@ -1171,12 +1203,15 @@ DECLARE
   location RECORD;
   way RECORD;
   relation RECORD;
   location RECORD;
   way RECORD;
   relation RECORD;

+  relation_members TEXT[];
+  relMember RECORD;
+  linkedplacex RECORD;

   search_diameter FLOAT;
   search_prevdiameter FLOAT;
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;
   search_diameter FLOAT;
   search_prevdiameter FLOAT;
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;

-  parent_place_id_rank INTEGER;
+  parent_place_id_rank BIGINT;

   
   isin TEXT[];
   isin_tokens INT[];
   
   isin TEXT[];
   isin_tokens INT[];


@@ -1186,6 +1221,7 @@ DECLARE
 
   tagpairid INTEGER;
 
 
   tagpairid INTEGER;
 

+  default_language TEXT;

   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
 
   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
 


@@ -1200,9 +1236,9 @@ BEGIN
     RETURN NEW;
   END IF;
 
     RETURN NEW;
   END IF;
 

-  -- defered delete
+  -- deferred delete

   IF OLD.indexed_status = 100 THEN
   IF OLD.indexed_status = 100 THEN

-    delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
+    delete from placex where place_id = OLD.place_id;

     RETURN NULL;
   END IF;
 
     RETURN NULL;
   END IF;
 


@@ -1215,13 +1251,22 @@ BEGIN
       RETURN NEW;
     END IF;
 
       RETURN NEW;
     END IF;
 

-    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.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+    IF OLD.indexed_status > 1 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;
+    END IF;
+
+    -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
+    IF NEW.rank_search >= 4 THEN
+      SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
+      NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+    ELSE
+      NEW.country_code := NULL;
+    END IF;

     NEW.partition := get_partition(NEW.geometry, NEW.country_code);
     NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
     NEW.partition := get_partition(NEW.geometry, NEW.country_code);
     NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 


@@ -1234,6 +1279,20 @@ BEGIN
     -- Speed up searches - just use the centroid of the feature
     -- cheaper but less acurate
     place_centroid := ST_Centroid(NEW.geometry);
     -- Speed up searches - just use the centroid of the feature
     -- cheaper but less acurate
     place_centroid := ST_Centroid(NEW.geometry);

+    NEW.centroid := null;
+
+    -- Thought this wasn't needed but when we add new languages to the country_name table
+    -- we need to update the existing names
+    IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+      default_language := get_country_language_code(NEW.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'));
+        ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+          NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
+        END IF;
+      END IF;
+    END IF;

 
     -- Initialise the name vector using our name
     name_vector := make_keywords(NEW.name);
 
     -- Initialise the name vector using our name
     name_vector := make_keywords(NEW.name);


@@ -1264,7 +1323,7 @@ BEGIN
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 
         -- Is this node part of a relation?
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 
         -- Is this node part of a relation?

-        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
+        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id]

         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN


@@ -1280,7 +1339,7 @@ BEGIN
 
 --RAISE WARNING 'x1';
         -- Is this node part of a way?
 
 --RAISE WARNING 'x1';
         -- Is this node part of a way?

-        FOR way IN select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] LOOP
+        FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP

 --RAISE WARNING '%', way;
         FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
         LOOP
 --RAISE WARNING '%', way;
         FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
         LOOP


@@ -1292,19 +1351,21 @@ BEGIN
           END IF;
 
           -- Is the WAY part of a relation
           END IF;
 
           -- Is the WAY part of a relation

-          FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
-          LOOP
-            -- At the moment we only process one type of relation - associatedStreet
-            IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
-              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 
-                    and rank_search = 26 INTO NEW.parent_place_id;
+          IF NEW.parent_place_id IS NULL THEN
+              FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
+              LOOP
+                -- At the moment we only process one type of relation - associatedStreet
+                IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
+                  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 
+                        and rank_search = 26 INTO NEW.parent_place_id;
+                    END IF;
+                  END LOOP;

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

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

           
           -- If the way contains an explicit name of a street copy it
           IF NEW.street IS NULL AND location.street IS NOT NULL THEN
           
           -- If the way contains an explicit name of a street copy it
           IF NEW.street IS NULL AND location.street IS NOT NULL THEN


@@ -1336,7 +1397,7 @@ BEGIN
 
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN
         -- Is this way part of a relation?
 
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN
         -- Is this way part of a relation?

-        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
+        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.osm_id]

         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN


@@ -1377,13 +1438,13 @@ BEGIN
       IF NEW.parent_place_id IS NOT NULL THEN
 
         -- Add the street to the address as zero distance to force to front of list
       IF NEW.parent_place_id IS NOT NULL THEN
 
         -- Add the street to the address as zero distance to force to front of list

-        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);
+--        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);

         address_havelevel[26] := true;
 
         -- Import address details from parent, reclculating distance in process
         address_havelevel[26] := true;
 
         -- Import address details from parent, reclculating distance in process

-        INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
-          from place_addressline as x join placex on (address_place_id = placex.place_id)
-          where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;
+--        INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
+--          from place_addressline as x join placex on (address_place_id = placex.place_id)
+--          where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;

 
         -- Get the details of the parent road
         select * from search_name where place_id = NEW.parent_place_id INTO location;
 
         -- Get the details of the parent road
         select * from search_name where place_id = NEW.parent_place_id INTO location;


@@ -1405,7 +1466,7 @@ BEGIN
           result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
         END IF;
 
           result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
         END IF;
 

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

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


@@ -1414,6 +1475,95 @@ BEGIN
 
 -- RAISE WARNING '  INDEXING: %',NEW;
 
 
 -- RAISE WARNING '  INDEXING: %',NEW;
 

+    IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
+
+      -- see if we have any special relation members
+      select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
+
+      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)) 
+          and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+        -- 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 extra tags
+        NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+        -- 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;
+
+      IF NEW.centroid IS NULL THEN
+
+        FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+
+          select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
+            and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+          IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search 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);
+
+            -- merge in extra tags
+            NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+            -- mark the linked place (excludes from search results)
+            UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+          END IF;
+
+        END LOOP;
+
+      END IF;
+
+      -- not found one yet? how about doing a name search
+      IF NEW.centroid IS NULL THEN
+        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.place_id != NEW.place_id
+          AND osm_type = 'N'
+          AND st_contains(NEW.geometry, placex.geometry)
+        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;
+
+          -- merge in the name, re-init word vector
+          NEW.name := linkedPlacex.name || NEW.name;
+          name_vector := make_keywords(NEW.name);
+
+          -- merge in extra tags
+          NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+          -- 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 IF;
+
+      IF NEW.centroid IS NOT NULL THEN
+        place_centroid := NEW.centroid;
+      END IF;
+
+    END IF;
+

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


@@ -1424,31 +1574,29 @@ BEGIN
       IF array_upper(isin, 1) IS NOT NULL THEN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
       IF array_upper(isin, 1) IS NOT NULL 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 THEN
-            isin_tokens := isin_tokens + address_street_word_id;
+          IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+            isin_tokens := isin_tokens || address_street_word_id;

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

-      isin_tokens := uniq(sort(isin_tokens));

     END IF;
     IF NEW.postcode IS NOT NULL THEN
       isin := regexp_split_to_array(NEW.postcode, E'[;,]');
       IF array_upper(isin, 1) IS NOT NULL THEN
         FOR i IN 1..array_upper(isin, 1) LOOP
           address_street_word_id := get_name_id(make_standard_name(isin[i]));
     END IF;
     IF NEW.postcode IS NOT NULL THEN
       isin := regexp_split_to_array(NEW.postcode, E'[;,]');
       IF array_upper(isin, 1) IS NOT NULL 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 THEN
-            isin_tokens := isin_tokens + address_street_word_id;
+          IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+            isin_tokens := isin_tokens || address_street_word_id;

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

-      isin_tokens := uniq(sort(isin_tokens));

     END IF;
 --RAISE WARNING 'ISIN: %', isin_tokens;
 
     -- Process area matches
     location_rank_search := 100;
     location_distance := 0;
     END IF;
 --RAISE WARNING 'ISIN: %', isin_tokens;
 
     -- Process area matches
     location_rank_search := 100;
     location_distance := 0;

---RAISE WARNING '  getNearFeatures(%,%,%,%)',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+--RAISE WARNING '  getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;

     FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
 
 --RAISE WARNING '  AREA: %',location;
     FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
 
 --RAISE WARNING '  AREA: %',location;


@@ -1458,13 +1606,15 @@ 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.distance < location_distance OR NOT location.isguess THEN

 
         -- Add it to the list of search terms
         nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); 
         address_havelevel[location.rank_address] := true;
 
 
         -- Add it to the list of search terms
         nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); 
         address_havelevel[location.rank_address] := true;
 

+--RAISE WARNING '  Terms: (%) %',location, nameaddress_vector;
+

         IF location.rank_address > parent_place_id_rank THEN
           NEW.parent_place_id = location.place_id;
           parent_place_id_rank = location.rank_address;
         IF location.rank_address > parent_place_id_rank THEN
           NEW.parent_place_id = location.place_id;
           parent_place_id_rank = location.rank_address;


@@ -1533,11 +1683,16 @@ BEGIN
         result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
       END IF;
 
         result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
       END IF;
 

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

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

+    -- If we've not managed to pick up a better one - default centroid
+    IF NEW.centroid IS NULL THEN
+      NEW.centroid := place_centroid;
+    END IF;
+

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


@@ -1549,8 +1704,12 @@ CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER
   AS $$
 DECLARE
   b BOOLEAN;
   AS $$
 DECLARE
   b BOOLEAN;

+  classtable TEXT;

 BEGIN
 
 BEGIN
 

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

   IF OLD.rank_address < 30 THEN
 
     -- mark everything linked to this place for re-indexing
   IF OLD.rank_address < 30 THEN
 
     -- mark everything linked to this place for re-indexing


@@ -1575,6 +1734,13 @@ BEGIN
 
   DELETE FROM place_addressline where place_id = OLD.place_id;
 
 
   DELETE FROM place_addressline where place_id = OLD.place_id;
 

+  -- remove from tables for special search
+  classtable := 'place_classtype_' || OLD.class || '_' || OLD.type;
+  SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable INTO b;
+  IF b THEN
+    EXECUTE 'DELETE FROM ' || classtable::regclass || ' WHERE place_id = $1' USING OLD.place_id;
+  END IF;
+

   RETURN OLD;
 
 END;
   RETURN OLD;
 
 END;


@@ -1584,11 +1750,17 @@ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
   AS $$
 DECLARE

-  placeid INTEGER;
+  placeid BIGINT;

 BEGIN
 
 --  RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
 
 BEGIN
 
 --  RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
 

+  -- deleting large polygons can have a massive effect ont he system - require manual intervention to let them through
+  IF st_area(OLD.geometry) > 2 THEN
+    insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
+    RETURN NULL;
+  END IF;
+

   -- mark for delete
   UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
 
   -- mark for delete
   UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
 


@@ -1606,7 +1778,7 @@ DECLARE
   existing RECORD;
   existingplacex RECORD;
   existinggeometry GEOMETRY;
   existing RECORD;
   existingplacex RECORD;
   existinggeometry GEOMETRY;

-  existingplace_id INTEGER;
+  existingplace_id BIGINT;

   result BOOLEAN;
   partition INTEGER;
 BEGIN
   result BOOLEAN;
   partition INTEGER;
 BEGIN


@@ -1645,11 +1817,11 @@ BEGIN
   select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
 
   -- Handle a place changing type by removing the old data
   select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
 
   -- Handle a place changing type by removing the old data

-  -- My generated 'place' types are causing havok because they overlap with real tags
+  -- My generated 'place' types are causing havok because they overlap with real keys

   -- TODO: move them to their own special purpose key/class to avoid collisions
   -- TODO: move them to their own special purpose key/class to avoid collisions

---  IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
---    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
---  END IF;
+  IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
+    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
+  END IF;

 
 --  RAISE WARNING 'Existing: %',existing.place_id;
 
 
 --  RAISE WARNING 'Existing: %',existing.place_id;
 


@@ -1665,6 +1837,7 @@ BEGIN
   END IF;
 
   DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
   END IF;
 
   DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;

+  DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;

 
   -- To paraphrase, if there isn't an existing item, OR if the admin level has changed, OR if it is a major change in geometry
   IF existing.osm_type IS NULL 
 
   -- To paraphrase, if there isn't an existing item, OR if the admin level has changed, OR if it is a major change in geometry
   IF existing.osm_type IS NULL 


@@ -1754,12 +1927,12 @@ BEGIN
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
           AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
           AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))

-          AND rank_search > existingplacex.rank_search;
+          AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);

 
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
           AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
 
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
           AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))

-          AND rank_search > existingplacex.rank_search;
+          AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);

 
     END IF;
 
 
     END IF;
 


@@ -1779,7 +1952,8 @@ BEGIN
 
       IF st_area(NEW.geometry) < 0.5 THEN
         UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id 
 
       IF st_area(NEW.geometry) < 0.5 THEN
         UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id 

-          and placex.place_id = place_addressline.place_id and indexed_status = 0;
+          and placex.place_id = place_addressline.place_id and indexed_status = 0
+          and (rank_search < 28 or name is not null);

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


@@ -1887,7 +2061,9 @@ BEGIN
     FOR location IN 
       select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
     LOOP
     FOR location IN 
       select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
     LOOP

-      searchnodes := searchnodes | location.nodes;
+      IF not (ARRAY[location.nodes] <@ searchnodes) THEN
+        searchnodes := searchnodes || location.nodes;
+      END IF;

     END LOOP;
   END LOOP;
 
     END LOOP;
   END LOOP;
 


@@ -1896,7 +2072,7 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id INTEGER) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT

   AS $$
 DECLARE
   result TEXT[];
   AS $$
 DECLARE
   result TEXT[];


@@ -1921,7 +2097,7 @@ BEGIN
     IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
       FOR j IN 1..array_upper(search, 1) LOOP
         FOR k IN 1..array_upper(location.name, 1) LOOP
     IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
       FOR j IN 1..array_upper(search, 1) LOOP
         FOR k IN 1..array_upper(location.name, 1) LOOP

-          IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
+          IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result @> ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN

             result[(100 - location.rank_address)] := trim(location.name[k].value);
             found := location.rank_address;
           END IF;
             result[(100 - location.rank_address)] := trim(location.name[k].value);
             found := location.rank_address;
           END IF;


@@ -1935,7 +2111,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id INTEGER, languagepref TEXT[]) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT

   AS $$
 DECLARE
   result TEXT[];
   AS $$
 DECLARE
   result TEXT[];


@@ -1962,7 +2138,7 @@ LANGUAGE plpgsql;
 
 DROP TYPE addressline CASCADE;
 create type addressline as (
 
 DROP TYPE addressline CASCADE;
 create type addressline as (

-  place_id INTEGER,
+  place_id BIGINT,

   osm_type CHAR(1),
   osm_id INTEGER,
   name HSTORE,
   osm_type CHAR(1),
   osm_id INTEGER,
   name HSTORE,


@@ -1975,10 +2151,10 @@ create type addressline as (
   distance FLOAT
 );
 
   distance FLOAT
 );
 

-CREATE OR REPLACE FUNCTION get_addressdata(in_place_id INTEGER) RETURNS setof addressline 
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline 

   AS $$
 DECLARE
   AS $$
 DECLARE

-  for_place_id INTEGER;
+  for_place_id BIGINT;

   result TEXT[];
   search TEXT[];
   found INTEGER;
   result TEXT[];
   search TEXT[];
   found INTEGER;


@@ -2021,6 +2197,39 @@ BEGIN
 
   found := 1000;
   hadcountry := false;
 
   found := 1000;
   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,
+      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, country_code
+      from placex
+      where place_id = for_place_id 
+  LOOP
+--RAISE WARNING '%',location;
+    IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
+      searchcountrycode := location.country_code;
+    END IF;
+    IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
+      location.isaddress := FALSE;
+    END IF;
+    IF location.rank_address = 4 AND location.isaddress THEN
+      hadcountry := true;
+    END IF;
+    IF location.rank_address < 4 AND NOT hadcountry THEN
+      select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
+      IF countryname IS NOT NULL THEN
+        countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
+        RETURN NEXT countrylocation;
+      END IF;
+    END IF;
+    countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class, 
+                           location.type, location.admin_level, location.fromarea, location.isaddress, location.rank_address, 
+                           location.distance)::addressline;
+    RETURN NEXT countrylocation;
+    found := location.rank_address;
+  END LOOP;
+

   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,
   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,


@@ -2029,7 +2238,8 @@ BEGIN
       distance,country_code
       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 
       distance,country_code
       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 

-      and ((cached_rank_address > 0 AND cached_rank_address < searchrankaddress) OR address_place_id = for_place_id)
+      and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
+      and address_place_id != for_place_id

       and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
       order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
   LOOP
       and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
       order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
   LOOP


@@ -2092,7 +2302,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox

   AS $$
 DECLARE
   result place_boundingbox;
   AS $$
 DECLARE
   result place_boundingbox;


@@ -2129,7 +2339,7 @@ $$
 LANGUAGE plpgsql;
 
 -- don't do the operation if it would be slow
 LANGUAGE plpgsql;
 
 -- don't do the operation if it would be slow

-CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox

   AS $$
 DECLARE
   result place_boundingbox;
   AS $$
 DECLARE
   result place_boundingbox;


@@ -2168,7 +2378,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN

   AS $$
 DECLARE
   result place_boundingbox;
   AS $$
 DECLARE
   result place_boundingbox;


@@ -2319,13 +2529,6 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE AGGREGATE array_agg(INT[])
-(
-    sfunc = array_cat,
-    stype = INT[],
-    initcond = '{}'
-);
-

 CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, 
   in_endnumber INTEGER, interpolationtype TEXT, 
   in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
 CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, 
   in_endnumber INTEGER, interpolationtype TEXT, 
   in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER


@@ -2340,8 +2543,8 @@ DECLARE
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;

-  partition INTEGER;
-  parent_place_id INTEGER;
+  out_partition INTEGER;
+  out_parent_place_id BIGINT;

   location RECORD;
   address_street_word_id INTEGER;  
 
   location RECORD;
   address_street_word_id INTEGER;  
 


@@ -2378,32 +2581,32 @@ BEGIN
   END IF;
 
   place_centroid := ST_Centroid(linegeo);
   END IF;
 
   place_centroid := ST_Centroid(linegeo);

-  partition := get_partition(place_centroid, 'us');
-  parent_place_id := null;
+  out_partition := get_partition(place_centroid, 'us');
+  out_parent_place_id := null;

 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN

-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
-      parent_place_id := location.place_id;
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+      out_parent_place_id := location.place_id;

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

-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+      out_parent_place_id := location.place_id;

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

-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+      out_parent_place_id := location.place_id;

     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)

-    values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+    values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,

       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;
       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;


@@ -2420,36 +2623,82 @@ DECLARE
 
   newpoints INTEGER;
   place_centroid GEOMETRY;
 
   newpoints INTEGER;
   place_centroid GEOMETRY;

-  partition INTEGER;
-  parent_place_id INTEGER;
+  out_partition INTEGER;
+  out_parent_place_id BIGINT;

   location RECORD;
   address_street_word_id INTEGER;  
   location RECORD;
   address_street_word_id INTEGER;  

+  out_postcode TEXT;

 
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
 
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);

-  partition := get_partition(place_centroid, in_countrycode);
-  parent_place_id := null;
+  out_partition := get_partition(place_centroid, in_countrycode);
+  out_parent_place_id := null;

 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN

-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
-      parent_place_id := location.place_id;
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+      out_parent_place_id := location.place_id;

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

-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+      out_parent_place_id := location.place_id;

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

+  out_postcode := in_postcode;
+  IF out_postcode IS NULL THEN
+    SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
+  END IF;
+  IF out_postcode IS NULL THEN
+    out_postcode := getNearestPostcode(out_partition, place_centroid);
+  END IF;
+

   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)

-    values (nextval('seq_place'), partition, parent_place_id, in_housenumber, in_postcode, place_centroid);
+    values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);

   newpoints := newpoints + 1;
 
   RETURN newpoints;
 END;
 $$
 LANGUAGE plpgsql;
   newpoints := newpoints + 1;
 
   RETURN newpoints;
 END;
 $$
 LANGUAGE plpgsql;

+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
+  AS $$
+DECLARE
+  result TEXT[];
+  i INTEGER;
+BEGIN
+
+  FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+    IF members[i+1] = member THEN
+      result := result || members[i];
+    END IF;
+  END LOOP;
+
+  return result;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
+  AS $$
+DECLARE
+  i INTEGER;
+BEGIN
+
+  FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+    IF members[i+1] = ANY(memberLabels) THEN
+      RETURN NEXT members[i];
+    END IF;
+  END LOOP;
+
+  RETURN;
+END;
+$$
+LANGUAGE plpgsql;
+
+