From: Sarah Hoffmann <lonvia@denofr.de>
Date: Fri, 18 Jun 2021 08:58:41 +0000 (+0200)
Subject: make sure old data gets deleted on place type change
X-Git-Tag: v4.0.0~61
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/e7b4fc70e786d6914ffad2bc424535b40c1db675?hp=-c

make sure old data gets deleted on place type change

When changing from some other place type to place=postcode
make sure that the old place type entry in the place table
is deleted.
---

e7b4fc70e786d6914ffad2bc424535b40c1db675
diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index 15389215..014c8cd7 100644
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -92,6 +92,13 @@ BEGIN
     -- Get the existing place_id
     select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
 
+    -- Handle a place changing type by removing the old data
+    -- My generated 'place' types are causing havok because they overlap with real keys
+    -- TODO: move them to their own special purpose key/class to avoid collisions
+    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;
+
     -- Pure postcodes are never queried from placex so we don't add them.
     -- location_postcodes is filled from the place table directly.
     IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
@@ -113,13 +120,6 @@ BEGIN
       RETURN NEW;
     END IF;
 
-    -- Handle a place changing type by removing the old data
-    -- My generated 'place' types are causing havok because they overlap with real keys
-    -- TODO: move them to their own special purpose key/class to avoid collisions
-    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 debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
     {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
 
diff --git a/test/bdd/db/update/postcode.feature b/test/bdd/db/update/postcode.feature
index e25e2867..94550ffd 100644
--- a/test/bdd/db/update/postcode.feature
+++ b/test/bdd/db/update/postcode.feature
@@ -84,6 +84,9 @@ Feature: Update of postcode
         Then placex contains
            | object | addr+housenumber | geometry |
            | N34    | 1                | country:de|
+        And place contains exactly
+           | object | class | type  |
+           | N34    | place | house |
         When updating postcodes
         Then location_postcode contains exactly
            | country | postcode | geometry |
@@ -104,3 +107,13 @@ Feature: Update of postcode
            | osm | class | type     | addr+postcode |  geometry |
            | N34 | place | postcode | 01982         | country:de |
         Then placex has no entry for N34
+        And place contains exactly
+           | object | class | type     |
+           | N34    | place | postcode |
+        When updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry |
+           | de      | 01982    | country:de |
+        And word contains
+           | word  | class | type |
+           | 01982 | place | postcode |