Merge remote-tracking branch 'upstream/master'

[nominatim.git] / lib-sql / functions / place_triggers.sql

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index a472d26d898e5b9df70e06014ca55b936e28eb5c..f3b6ab2b4e7f5eff2482eb45792a2f032f62b363 100644 (file)
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -34,6 +34,11 @@ BEGIN
     RETURN null;
   END IF;
 
     RETURN null;
   END IF;
 

+  -- Remove the place from the list of places to be deleted
+  DELETE FROM place_to_be_deleted pdel
+    WHERE pdel.osm_type = NEW.osm_type and pdel.osm_id = NEW.osm_id
+          and pdel.class = NEW.class and pdel.type = NEW.type;
+

   -- Have we already done this place?
   SELECT * INTO existing
     FROM place
   -- Have we already done this place?
   SELECT * INTO existing
     FROM place


@@ -42,8 +47,6 @@ BEGIN
 
   {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
 
 
   {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
 

-  -- Handle a place changing type by removing the old data.
-  -- (This trigger is executed BEFORE INSERT of the NEW tuple.)

   IF existing.osm_type IS NULL THEN
     DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
   END IF;
   IF existing.osm_type IS NULL THEN
     DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
   END IF;


@@ -117,16 +120,6 @@ BEGIN
 
   -- ---- All other place types.
 
 
   -- ---- All other place types.
 

-  -- Patch in additional country names
-  IF NEW.admin_level = 2 and NEW.type = 'administrative' and NEW.address ? 'country'
-  THEN
-    FOR country IN
-      SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country')
-    LOOP
-      NEW.name = country.name || NEW.name;
-    END LOOP;
-  END IF;
-

   -- When an area is changed from large to small: log and discard change
   IF existing.geometry is not null AND ST_IsValid(existing.geometry)
     AND ST_Area(existing.geometry) > 0.02
   -- When an area is changed from large to small: log and discard change
   IF existing.geometry is not null AND ST_IsValid(existing.geometry)
     AND ST_Area(existing.geometry) > 0.02


@@ -156,6 +149,7 @@ BEGIN
         SELECT DISTINCT osm_id, address, geometry FROM place, planet_osm_ways w
         WHERE NEW.geometry && place.geometry
               and place.osm_type = 'W'
         SELECT DISTINCT osm_id, address, geometry FROM place, planet_osm_ways w
         WHERE NEW.geometry && place.geometry
               and place.osm_type = 'W'

+              and place.address ? 'interpolation'

               and exists (SELECT * FROM location_property_osmline
                           WHERE osm_id = place.osm_id
                                 and indexed_status in (0, 2))
               and exists (SELECT * FROM location_property_osmline
                           WHERE osm_id = place.osm_id
                                 and indexed_status in (0, 2))


@@ -196,15 +190,11 @@ BEGIN
     END IF;
     {% endif %}
 
     END IF;
     {% endif %}
 

-    IF existing.osm_type IS NOT NULL THEN
-      -- Pathological case caused by the triggerless copy into place during initial import
-      -- force delete even for large areas, it will be reinserted later
-      UPDATE place SET geometry = ST_SetSRID(ST_Point(0,0), 4326)
-        WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
-              and class = NEW.class and type = NEW.type;
-      DELETE FROM place
-        WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
-              and class = NEW.class and type = NEW.type;
+    IF existingplacex.osm_type is not NULL THEN
+      -- Mark any existing place for delete in the placex table
+      UPDATE placex SET indexed_status = 100
+        WHERE placex.osm_type = NEW.osm_type and placex.osm_id = NEW.osm_id
+              and placex.class = NEW.class and placex.type = NEW.type;

     END IF;
 
     -- Process it as a new insertion
     END IF;
 
     -- Process it as a new insertion


@@ -215,6 +205,27 @@ BEGIN
 
     {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %}
 
 
     {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %}
 

+    IF existing.osm_type is not NULL THEN
+      -- If there is already an entry in place, just update that, if necessary.
+      IF coalesce(existing.name, ''::hstore) != coalesce(NEW.name, ''::hstore)
+         or coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+         or coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore)
+         or coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
+         or existing.geometry::text != NEW.geometry::text
+      THEN
+        UPDATE place
+          SET name = NEW.name,
+              address = NEW.address,
+              extratags = NEW.extratags,
+              admin_level = NEW.admin_level,
+              geometry = NEW.geometry
+          WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
+                and class = NEW.class and type = NEW.type;
+      END IF;
+
+      RETURN NULL;
+    END IF;
+

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


@@ -330,35 +341,25 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
 END;
 $$ LANGUAGE plpgsql;
 

-

 CREATE OR REPLACE FUNCTION place_delete()
   RETURNS TRIGGER
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION place_delete()
   RETURNS TRIGGER
   AS $$
 DECLARE

-  has_rank BOOLEAN;
+  deferred BOOLEAN;

 BEGIN
 BEGIN

-
-  {% if debug %}RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;{% endif %}
-
-  -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
-  IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
-    SELECT bool_or(not (rank_address = 0 or rank_address > 25)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank;
-    IF has_rank THEN
-      insert into import_polygon_delete (osm_type, osm_id, class, type) values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
-      RETURN NULL;
-    END IF;
+  {% if debug %}RAISE WARNING 'Delete for % % %/%', OLD.osm_type, OLD.osm_id, OLD.class, OLD.type;{% endif %}
+
+  deferred := ST_IsValid(OLD.geometry) and ST_Area(OLD.geometry) > 2;
+  IF deferred THEN
+    SELECT bool_or(not (rank_address = 0 or rank_address > 25)) INTO deferred
+      FROM placex
+      WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id
+            and class = OLD.class and type = OLD.type;

   END IF;
 
   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;
-
-  -- interpolations are special
-  IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN
-    UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id)
-  END IF;
+  INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred)
+    VALUES(OLD.osm_type, OLD.osm_id, OLD.class, OLD.type, deferred);

 
 

-  RETURN OLD;
+  RETURN NULL;

 END;
 END;

-$$
-LANGUAGE plpgsql;
-
+$$ LANGUAGE plpgsql;