make rank_search consistent

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 84dd62d586f402c232765416bee81a26c94893f9..90c0c4f490c955a9ba8ea1b42e708cf9dd181f50 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -575,8 +575,8 @@ DECLARE
   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;
 
   x := deleteLocationArea(partition, place_id);
@@ -1318,10 +1318,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));
---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
---RAISE WARNING 'streetname found nearby %',location;
             NEW.parent_place_id := location.place_id;
           END LOOP;
         END IF;
@@ -1330,12 +1328,6 @@ BEGIN
 --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
@@ -1443,7 +1435,7 @@ BEGIN
     -- 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;
 
@@ -1465,15 +1457,25 @@ DECLARE
   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 address_place_id = OLD.place_id;
 
   RETURN OLD;
 
@@ -2205,4 +2207,60 @@ CREATE AGGREGATE array_agg(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
+  AS $$
+DECLARE
+  
+  startnumber INTEGER;
+  endnumber INTEGER;
+  stepsize INTEGER;
+  housenum INTEGER;
+  newpoints INTEGER;
+  numberrange INTEGER;
+  rangestartnumber 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;
+
+  newpoints := 0;
+  FOR housenum IN startnumber..endnumber BY stepsize LOOP
+    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)
+    values ('T', nextval('seq_tigger_house'), 'place', 'house', null, housenum, in_street, in_isin, in_postcode,
+      'us', null, 30, 30, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
+    newpoints := newpoints + 1;
+  END LOOP;
+
+  RETURN newpoints;
+END;
+$$
+LANGUAGE plpgsql;