timezone trouble

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 3029d01588ab6a8f696b095f6232ed129f7ac191..1411f300461b91707e4e82d52faa7a4849dd140e 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -793,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;
 


@@ -875,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;
 


@@ -1107,7 +1108,7 @@ BEGIN
 --    RETURN NULL;
 --  END IF;
 
 --    RETURN NULL;
 --  END IF;
 

-  RETURN NEW;  -- The following is not needed until doing diff updates, and slows the main index process down
+--  RETURN NEW;  -- The following is not needed until doing diff updates, and slows the main index process down

 
   IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
     -- Performance: We just can't handle re-indexing for country level changes
 
   IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
     -- Performance: We just can't handle re-indexing for country level changes


@@ -1116,9 +1117,9 @@ BEGIN
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 -- work around bug in postgis
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 -- work around bug in postgis
       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 :(


@@ -1143,11 +1144,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;


@@ -1205,9 +1217,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;
 


@@ -1220,11 +1232,13 @@ 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);
+    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);
+    END IF;

 
     -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
     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;
 
     -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
     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;


@@ -1558,6 +1572,7 @@ CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER
   AS $$
 DECLARE
   b BOOLEAN;
   AS $$
 DECLARE
   b BOOLEAN;

+  classtable TEXT;

 BEGIN
 
   IF OLD.rank_address < 30 THEN
 BEGIN
 
   IF OLD.rank_address < 30 THEN


@@ -1584,6 +1599,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;


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


@@ -1795,7 +1817,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;


@@ -2371,13 +2394,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