From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 17 Jun 2021 22:28:10 +0000 (+0200)
Subject: update postcode in place if it already exists
X-Git-Tag: v4.0.0~62
X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/457982e1d2f4de0b4c387c2c3ac7f96488e9eca6?ds=sidebyside

update postcode in place if it already exists
---

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index dd0f1662..15389215 100644
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -97,6 +97,19 @@ BEGIN
     IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
       -- Remove old placex entry.
       DELETE FROM placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+
+      IF existing.osm_type IS NOT NULL THEN
+        IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+           OR existing.geometry::text != NEW.geometry::text
+        THEN
+
+          update place set address = NEW.address, 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;
 
diff --git a/test/bdd/db/update/postcode.feature b/test/bdd/db/update/postcode.feature
index fbac9341..e25e2867 100644
--- a/test/bdd/db/update/postcode.feature
+++ b/test/bdd/db/update/postcode.feature
@@ -56,3 +56,51 @@ Feature: Update of postcode
            | word  | class | type |
            | 01982 | place | postcode |
 
+     Scenario: Updating a postcode is reflected in postcode table
+        Given the places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        When importing
+        And updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 20453         | country:de |
+        And updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry |
+           | de      | 20453    | country:de |
+        And word contains
+           | word  | class | type |
+           | 20453 | place | postcode |
+
+     Scenario: When changing from a postcode type, the entry appears in placex
+        When importing
+        And updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        Then placex has no entry for N34
+        When updating places
+           | osm | class | type  | addr+postcode | housenr |  geometry |
+           | N34 | place | house | 20453         | 1       | country:de |
+        Then placex contains
+           | object | addr+housenumber | geometry |
+           | N34    | 1                | country:de|
+        When updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry |
+           | de      | 20453    | country:de |
+        And word contains
+           | word  | class | type |
+           | 20453 | place | postcode |
+
+     Scenario: When changing to a postcode type, the entry disappears from placex
+        When importing
+        And updating places
+           | osm | class | type  | addr+postcode | housenr |  geometry |
+           | N34 | place | house | 20453         | 1       | country:de |
+        Then placex contains
+           | object | addr+housenumber | geometry |
+           | N34    | 1                | country:de|
+        When updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        Then placex has no entry for N34