X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/38bd08d25fffca597a9c712224cd8c7b3833339b..36cf0eb922d4c03e73a3a3a5505e46d5cee061b9:/lib-sql/functions/place_triggers.sql?ds=sidebyside

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index 4e316990..ca6ba690 100644
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -13,8 +13,8 @@ DECLARE
   country RECORD;
   existing RECORD;
   existingplacex RECORD;
-  existingline RECORD;
-  result BOOLEAN;
+  existingline BIGINT[];
+  interpol RECORD;
 BEGIN
   {% if debug %}
     RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
@@ -34,6 +34,11 @@ BEGIN
     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;
+
   -- 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 %}
 
-  -- 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;
@@ -57,19 +60,7 @@ BEGIN
   IF NEW.class='place' and NEW.type='houses'
      and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString'
   THEN
-    -- Get the existing entry from the interpolation table.
-    SELECT * INTO existingline
-      FROM location_property_osmline WHERE osm_id = NEW.osm_id;
-
-    -- Update the interpolation table:
-    --   delete all old interpolation lines with same osm_id
-    --   and insert the new one(s) (they can be split up, if they have > 2 nodes)
-    IF existingline.osm_id IS NOT NULL THEN
-      DELETE FROM location_property_osmline where osm_id = NEW.osm_id;
-    END IF;
-
-    INSERT INTO location_property_osmline (osm_id, address, linegeo)
-      VALUES (NEW.osm_id, NEW.address, NEW.geometry);
+    PERFORM reinsert_interpolation(NEW.osm_id, NEW.address, NEW.geometry);
 
     -- Now invalidate all address nodes on the line.
     -- They get their parent from the interpolation.
@@ -129,16 +120,6 @@ BEGIN
 
   -- ---- 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
@@ -156,6 +137,29 @@ BEGIN
     RETURN null;
   END IF;
 
+  -- If an address node is part of a interpolation line and changes or is
+  -- newly inserted (happens when the node already existed but now gets address
+  -- information), then mark the interpolation line for reparenting.
+  -- (Already here, because interpolation lines are reindexed before nodes,
+  --  so in the second call it would be too late.)
+  IF NEW.osm_type='N'
+     and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+  THEN
+      FOR interpol IN
+        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 w.id = place.osm_id and NEW.osm_id = any (w.nodes)
+      LOOP
+        PERFORM reinsert_interpolation(interpol.osm_id, interpol.address,
+                                       interpol.geometry);
+      END LOOP;
+  END IF;
+
   -- Get the existing placex entry.
   SELECT * INTO existingplacex
     FROM placex
@@ -186,15 +190,11 @@ BEGIN
     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 = 'boundary' and placex.type = 'administrative';
     END IF;
 
     -- Process it as a new insertion
@@ -205,6 +205,27 @@ BEGIN
 
     {% 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;
 
@@ -278,16 +299,6 @@ BEGIN
           geometry = NEW.geometry
       WHERE place_id = existingplacex.place_id;
 
-    -- If an address node which is part of a interpolation line changes
-    -- mark this line for reparenting.
-    -- (Already here, because interpolation lines are reindexed before nodes,
-    --  so in the second call it would be too late.)
-    IF NEW.osm_type='N'
-       and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
-    THEN
-        result:= osmline_reinsert(NEW.osm_id, NEW.geometry);
-    END IF;
-
     -- Invalidate linked places: they potentially get a new name and addresses.
     IF existingplacex.linked_place_id is not NULL THEN
       UPDATE placex x
@@ -330,35 +341,67 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
-
 CREATE OR REPLACE FUNCTION place_delete()
   RETURNS TRIGGER
   AS $$
 DECLARE
-  has_rank BOOLEAN;
+  deferred BOOLEAN;
 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;
 
-  -- 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;
+  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);
 
-  -- 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;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
 
-  RETURN OLD;
+CREATE OR REPLACE FUNCTION flush_deleted_places()
+  RETURNS INTEGER
+  AS $$
+BEGIN
+  -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
+  INSERT INTO import_polygon_delete (osm_type, osm_id, class, type)
+    SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred;
+
+  -- delete from place table
+  ALTER TABLE place DISABLE TRIGGER place_before_delete;
+  DELETE FROM place USING place_to_be_deleted
+    WHERE place.osm_type = place_to_be_deleted.osm_type
+          and place.osm_id = place_to_be_deleted.osm_id
+          and place.class = place_to_be_deleted.class
+          and place.type = place_to_be_deleted.type
+          and not deferred;
+  ALTER TABLE place ENABLE TRIGGER place_before_delete;
+
+  -- Mark for delete in the placex table
+  UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
+    WHERE placex.osm_type = place_to_be_deleted.osm_type
+          and placex.osm_id = place_to_be_deleted.osm_id
+          and placex.class = place_to_be_deleted.class
+          and placex.type = place_to_be_deleted.type
+          and not deferred;
+
+   -- Mark for delete in interpolations
+   UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted
+    WHERE place_to_be_deleted.osm_type = 'W'
+          and place_to_be_deleted.class = 'place'
+          and place_to_be_deleted.type = 'houses'
+          and location_property_osmline.osm_id = place_to_be_deleted.osm_id
+          and not deferred;
+
+   -- Clear todo list.
+   TRUNCATE TABLE place_to_be_deleted;
+
+   RETURN NULL;
 END;
-$$
-LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql;