postcode/zipcode improvements, finish work on handling extratags

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 84dd62d586f402c232765416bee81a26c94893f9..cdc56d700492f156fd1f38351e9c513be827981a 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -575,10 +575,12 @@ DECLARE
   x BOOLEAN;
 BEGIN
 
   x BOOLEAN;
 BEGIN
 

-  IF rank_search > 26 THEN
-    RAISE EXCEPTION 'Adding location with rank > 26 (% rank %)', place_id, rank_search;
+  IF rank_search > 25 THEN
+    RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;

   END IF;
 
   END IF;
 

+  RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
+

   x := deleteLocationArea(partition, place_id);
 
   isarea := false;
   x := deleteLocationArea(partition, place_id);
 
   isarea := false;


@@ -611,20 +613,24 @@ BEGIN
   ELSEIF rank_search < 26 THEN
 
     diameter := 0.02;
   ELSEIF rank_search < 26 THEN
 
     diameter := 0.02;

-    IF rank_search = 14 THEN
+    IF rank_address = 0 THEN
+      diameter := 0.02;
+    ELSEIF rank_search <= 14 THEN
+      diameter := 1.2;
+    ELSEIF rank_search <= 15 THEN

       diameter := 1;
       diameter := 1;

-    ELSEIF rank_search = 15 THEN
+    ELSEIF rank_search <= 16 THEN

       diameter := 0.5;
       diameter := 0.5;

-    ELSEIF rank_search = 16 THEN
-      diameter := 0.15;
-    ELSEIF rank_search = 17 THEN
+    ELSEIF rank_search <= 17 THEN
+      diameter := 0.2;
+    ELSEIF rank_search <= 21 THEN

       diameter := 0.05;
       diameter := 0.05;

-    ELSEIF rank_search = 21 THEN
-      diameter := 0.01;

     ELSEIF rank_search = 25 THEN
       diameter := 0.005;
     END IF;
 
     ELSEIF rank_search = 25 THEN
       diameter := 0.005;
     END IF;
 

+    RAISE WARNING 'adding % diameter %', place_id, diameter;
+

     secgeo := ST_Buffer(geometry, diameter);
     x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
 
     secgeo := ST_Buffer(geometry, diameter);
     x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
 


@@ -746,6 +752,7 @@ DECLARE
   housenum INTEGER;
   linegeo GEOMETRY;
   search_place_id INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;
   search_place_id INTEGER;

+  defpostalcode TEXT;

 
   havefirstpoint BOOLEAN;
   linestr TEXT;
 
   havefirstpoint BOOLEAN;
   linestr TEXT;


@@ -753,6 +760,7 @@ BEGIN
   newpoints := 0;
   IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN
 
   newpoints := 0;
   IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN
 

+    select postcode from placex where osm_type = 'W' and osm_id = wayid INTO defpostalcode;

     select nodes from planet_osm_ways where id = wayid INTO waynodes;
 --RAISE WARNING 'interpolation % % %',wayid,interpolationtype,waynodes;
     IF array_upper(waynodes, 1) IS NOT NULL THEN
     select nodes from planet_osm_ways where id = wayid INTO waynodes;
 --RAISE WARNING 'interpolation % % %',wayid,interpolationtype,waynodes;
     IF array_upper(waynodes, 1) IS NOT NULL THEN


@@ -819,9 +827,9 @@ BEGIN
               FOR housenum IN startnumber..endnumber BY stepsize LOOP
                 -- this should really copy postcodes but it puts a huge burdon on the system for no big benefit
                 -- ideally postcodes should move up to the way
               FOR housenum IN startnumber..endnumber BY stepsize LOOP
                 -- this should really copy postcodes but it puts a huge burdon on the system for no big benefit
                 -- ideally postcodes should move up to the way

-                insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin, 
+                insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode,

                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)

-                  values ('N',prevnode.osm_id, prevnode.class, prevnode.type, prevnode.admin_level, housenum, prevnode.street, prevnode.isin, 
+                  values ('N',prevnode.osm_id, prevnode.class, prevnode.type, prevnode.admin_level, housenum, prevnode.street, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),

                   prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
                   prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;


@@ -890,12 +898,12 @@ 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 := get_country_code(NEW.geometry, NEW.country_code);
+  NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));

   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 THEN
+  IF NEW.name is not null AND array_upper(%#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
     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


@@ -947,11 +955,11 @@ BEGIN
         NEW.rank_search := 17;
         NEW.rank_address := 0;
       ELSEIF NEW.type in ('town') THEN
         NEW.rank_search := 17;
         NEW.rank_address := 0;
       ELSEIF NEW.type in ('town') THEN

-        NEW.rank_search := 17;
-        NEW.rank_address := NEW.rank_search;
-      ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN

         NEW.rank_search := 18;
         NEW.rank_search := 18;

-        NEW.rank_address := 17;
+        NEW.rank_address := 16;
+      ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
+        NEW.rank_search := 19;
+        NEW.rank_address := 16;

       ELSEIF NEW.type in ('airport') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
         NEW.rank_search := 18;
         NEW.rank_address := 17;
       ELSEIF NEW.type in ('airport') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
         NEW.rank_search := 18;
         NEW.rank_address := 17;


@@ -1031,6 +1039,9 @@ BEGIN
       END IF;
 
     ELSEIF NEW.class = 'boundary' THEN
       END IF;
 
     ELSEIF NEW.class = 'boundary' THEN

+      IF ST_GeometryType(NEW.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') THEN
+        return NULL;
+      END IF;

       NEW.rank_search := NEW.admin_level * 2;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := NEW.admin_level * 2;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN


@@ -1075,6 +1086,10 @@ BEGIN
     NEW.rank_address := 30;
   END IF;
 
     NEW.rank_address := 30;
   END IF;
 

+  IF (NEW.extratags -> 'capital') = 'yes' THEN
+    NEW.rank_search := NEW.rank_search -1;
+  END IF;
+

 -- Block import below rank 22
 --  IF NEW.rank_search > 22 THEN
 --    RETURN NULL;
 -- Block import below rank 22
 --  IF NEW.rank_search > 22 THEN
 --    RETURN NULL;


@@ -1154,7 +1169,8 @@ DECLARE
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;

-  parent_place_id_count INTEGER;
+  parent_place_id_rank INTEGER;
+  

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


@@ -1246,7 +1262,7 @@ BEGIN
 --RAISE WARNING 'x1';
         -- Is this node part of a way?
         FOR location IN select * from placex where osm_type = 'W' 
 --RAISE WARNING 'x1';
         -- Is this node part of a way?
         FOR location IN select * from placex where osm_type = 'W' 

-          and osm_id in (select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer])
+          and osm_id in (select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] limit 10)

         LOOP
 --RAISE WARNING '%', location;
           -- Way IS a road then we are on it - that must be our road
         LOOP
 --RAISE WARNING '%', location;
           -- Way IS a road then we are on it - that must be our road


@@ -1318,10 +1334,8 @@ BEGIN
 
       IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
        address_street_word_id := get_name_id(make_standard_name(NEW.street));
 
       IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
        address_street_word_id := get_name_id(make_standard_name(NEW.street));

---RAISE WARNING 'street: % %', NEW.street, address_street_word_id;

         IF address_street_word_id IS NOT NULL THEN
           FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP
         IF address_street_word_id IS NOT NULL THEN
           FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP

---RAISE WARNING 'streetname found nearby %',location;

             NEW.parent_place_id := location.place_id;
           END LOOP;
         END IF;
             NEW.parent_place_id := location.place_id;
           END LOOP;
         END IF;


@@ -1330,12 +1344,6 @@ BEGIN
 --RAISE WARNING 'x4';
 
       -- Still nothing, just use the nearest road
 --RAISE WARNING 'x4';
 
       -- Still nothing, just use the nearest road

---      IF NEW.parent_place_id IS NULL THEN
---        FOR location IN SELECT place_id FROM getNearRoads(NEW.partition, place_centroid) LOOP
---          NEW.parent_place_id := location.place_id;
---        END LOOP;
---      END IF;
-

       search_diameter := 0.00005;
       WHILE NEW.parent_place_id IS NULL AND search_diameter < 0.1 LOOP
         FOR location IN SELECT place_id FROM placex
       search_diameter := 0.00005;
       WHILE NEW.parent_place_id IS NULL AND search_diameter < 0.1 LOOP
         FOR location IN SELECT place_id FROM placex


@@ -1347,6 +1355,7 @@ BEGIN
         search_diameter := search_diameter * 2;
       END LOOP;
 
         search_diameter := search_diameter * 2;
       END LOOP;
 

+--return NEW;

 --RAISE WARNING 'x6 %',NEW.parent_place_id;
 
       -- If we didn't find any road fallback to standard method
 --RAISE WARNING 'x6 %',NEW.parent_place_id;
 
       -- If we didn't find any road fallback to standard method


@@ -1373,20 +1382,25 @@ 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);

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

+
+        IF NEW.rank_search <= 25 THEN
+          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, place_centroid);

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

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

---RAISE WARNING '  INDEXING: %',NEW;
+RAISE WARNING '  INDEXING: %',NEW;
+
+    NEW.parent_place_id = 0;
+    parent_place_id_rank = 0;

 
     -- convert isin to array of tokenids
     isin_tokens := '{}'::int[];
 
     -- convert isin to array of tokenids
     isin_tokens := '{}'::int[];


@@ -1402,11 +1416,42 @@ BEGIN
       END IF;
       isin_tokens := uniq(sort(isin_tokens));
     END IF;
       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]));
+          IF address_street_word_id IS NOT NULL THEN
+            isin_tokens := isin_tokens + address_street_word_id;
+          END IF;
+        END LOOP;
+      END IF;
+      isin_tokens := uniq(sort(isin_tokens));
+    END IF;
+
+    -- try using the isin value to find parent places
+    IF array_upper(isin_tokens, 1) IS NOT NULL THEN
+      FOR i IN 1..array_upper(isin_tokens, 1) LOOP
+--RAISE WARNING '  ISIN: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+
+        FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+          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;
+
+        END LOOP;
+
+      END LOOP;
+    END IF;

 
     -- Process area matches
     location_rank_search := 100;
     location_distance := 0;
 
     -- Process area matches
     location_rank_search := 100;
     location_distance := 0;

---RAISE WARNING '%', NEW.partition;

     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;


@@ -1423,27 +1468,44 @@ BEGIN
         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;
 
         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;
 

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

-    -- try using the isin value to find parent places
-    IF array_upper(isin_tokens, 1) IS NOT NULL THEN
-      FOR i IN 1..array_upper(isin_tokens, 1) LOOP
+    -- for long ways we should add search terms for the entire length
+    IF st_length(NEW.geometry) > 0.05 THEN

 
 

-        FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+      location_rank_search := 100;
+      location_distance := 0;
+
+      FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
+
+        IF location.rank_search < location_rank_search THEN
+          location_rank_search := location.rank_search;
+          location_distance := location.distance * 1.5;
+        END IF;
+
+        IF location.distance < location_distance THEN
+
+          -- Add it to the list of search terms

           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
           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;
-        END LOOP;
+          INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); 
+
+        END IF;

 
       END LOOP;
 
       END LOOP;

+

     END IF;
 
     -- if we have a name add this to the name search table
     IF NEW.name IS NOT NULL THEN
 
     END IF;
 
     -- if we have a name add this to the name search table
     IF NEW.name IS NOT NULL THEN
 

-      IF NEW.rank_search <= 26 THEN
+      IF NEW.rank_search <= 25 THEN

         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;
 


@@ -1465,15 +1527,25 @@ DECLARE
   b BOOLEAN;
 BEGIN
 
   b BOOLEAN;
 BEGIN
 

-  -- mark everything linked to this place for re-indexing
-  UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id 
-    and placex.place_id = place_addressline.place_id and indexed_status = 0;
+  IF OLD.rank_address < 30 THEN
+
+    -- mark everything linked to this place for re-indexing
+    UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id 
+      and placex.place_id = place_addressline.place_id and indexed_status = 0;
+
+    DELETE FROM place_addressline where address_place_id = OLD.place_id;
+
+  END IF;
+
+  IF OLD.rank_address < 26 THEN
+    b := deleteLocationArea(OLD.partition, OLD.place_id);
+  END IF;
+
+  IF OLD.name is not null THEN
+    b := deleteSearchName(OLD.partition, OLD.place_id);
+  END IF;

 
 

-  -- do the actual delete
-  b := deleteLocationArea(OLD.partition, OLD.place_id);
-  b := deleteSearchName(OLD.partition, OLD.place_id);

   DELETE FROM place_addressline where place_id = OLD.place_id;
   DELETE FROM place_addressline where place_id = OLD.place_id;

-  DELETE FROM place_addressline where address_place_id = OLD.place_id;

 
   RETURN OLD;
 
 
   RETURN OLD;
 


@@ -1840,13 +1912,14 @@ DECLARE
   searchcountrycode varchar(2);
   searchhousenumber TEXT;
   searchrankaddress INTEGER;
   searchcountrycode varchar(2);
   searchhousenumber TEXT;
   searchrankaddress INTEGER;

+  searchpostcode TEXT;

 BEGIN
 
   found := 1000;
   search := languagepref;
   result := '{}';
 
 BEGIN
 
   found := 1000;
   search := languagepref;
   result := '{}';
 

-  select country_code,housenumber,rank_address from placex where place_id = for_place_id into searchcountrycode,searchhousenumber,searchrankaddress;
+  select country_code,housenumber,rank_address,postcode from placex where place_id = for_place_id into searchcountrycode,searchhousenumber,searchrankaddress,searchpostcode;

 
   FOR location IN 
     select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,
 
   FOR location IN 
     select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,


@@ -1875,6 +1948,10 @@ BEGIN
     result[(100 - 28)] := searchhousenumber;
   END IF;
 
     result[(100 - 28)] := searchhousenumber;
   END IF;
 

+  IF searchpostcode IS NOT NULL THEN
+    result[(100 - 5)] := searchpostcode;
+  END IF;
+

   -- No country polygon - add it from the country_code
   IF found > 4 THEN
     select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code) 
   -- No country polygon - add it from the country_code
   IF found > 4 THEN
     select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code) 


@@ -2041,6 +2118,7 @@ BEGIN
       where placex.place_id = search_place_id 
         and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
         and place.class = placex.class and place.type = placex.type;
       where placex.place_id = search_place_id 
         and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
         and place.class = placex.class and place.type = placex.type;

+  update placex set indexed_status = 1 where place_id = search_place_id;

   update placex set indexed_status = 0 where place_id = search_place_id;
   return true;
 END;
   update placex set indexed_status = 0 where place_id = search_place_id;
   return true;
 END;


@@ -2060,12 +2138,12 @@ BEGIN
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,

-      parent_place_id = null,
-      indexed_status = 2      
+      parent_place_id = null

       from place
       where placex.place_id = search_place_id 
         and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
         and place.class = placex.class and place.type = placex.type;
       from place
       where placex.place_id = search_place_id 
         and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
         and place.class = placex.class and place.type = placex.type;

+  update placex set indexed_status = 2 where place_id = search_place_id;

   update placex set indexed_status = 0 where place_id = search_place_id;
   return true;
 END;
   update placex set indexed_status = 0 where place_id = search_place_id;
   return true;
 END;


@@ -2205,4 +2283,74 @@ CREATE AGGREGATE array_agg(INT[])
     initcond = '{}'
 );
 
     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
+  AS $$
+DECLARE
+  
+  startnumber INTEGER;
+  endnumber INTEGER;
+  stepsize INTEGER;
+  housenum INTEGER;
+  newpoints INTEGER;
+  numberrange INTEGER;
+  rangestartnumber INTEGER;
+  place_centroid GEOMETRY;
+  partition INTEGER;
+  parent_place_id INTEGER;
+  location RECORD;
+  address_street_word_id INTEGER;  
+
+BEGIN
+
+  IF in_endnumber > in_startnumber THEN
+    startnumber = in_startnumber;
+    endnumber = in_endnumber;
+  ELSE
+    startnumber = in_endnumber;
+    endnumber = in_startnumber;
+  END IF;
+
+  numberrange := endnumber - startnumber;
+  rangestartnumber := startnumber;
+
+  IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
+    startnumber := startnumber + 1;
+    stepsize := 2;
+  ELSE
+    IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
+      stepsize := 2;
+    ELSE -- everything else assumed to be 'all'
+      stepsize := 1;
+    END IF;
+  END IF;

 
 

+  -- Filter out really broken tiger data
+  IF numberrange > 0 AND (numberrange::float/stepsize::float > 500) AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN
+    RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin,ST_length(linegeo)/(numberrange::float/stepsize::float);    
+    RETURN 0;
+  END IF;
+
+  place_centroid := ST_Centroid(linegeo);
+  partition := get_partition(place_centroid, 'us');
+  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
+    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+      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)
+    values (nextval('seq_place'), 2, parent_place_id, housenum, in_postcode,
+      ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
+    newpoints := newpoints + 1;
+  END LOOP;
+
+  RETURN newpoints;
+END;
+$$
+LANGUAGE plpgsql;