From 457982e1d2f4de0b4c387c2c3ac7f96488e9eca6 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Fri, 18 Jun 2021 00:28:10 +0200 Subject: [PATCH] update postcode in place if it already exists --- lib-sql/functions/place_triggers.sql | 13 ++++++++ test/bdd/db/update/postcode.feature | 48 ++++++++++++++++++++++++++++ 2 files changed, 61 insertions(+) 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 -- 2.45.1