update country_names from OSM data

Update names in the coutry_names table on the fly from incomming
OSM country data. Adding a small sanity check that the country
must be an OSM relation and within the area where we expect the
country to be.

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 87f15e8c59954f7ab97ec31594f1f8525692a10d..6965fe14dab23b1ad3ac55984d4a2ff62f848254 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -919,6 +919,18 @@ BEGIN
   THEN
     PERFORM create_country(NEW.name, lower(NEW.country_code));
     --DEBUG: RAISE WARNING 'Country names updated';
   THEN
     PERFORM create_country(NEW.name, lower(NEW.country_code));
     --DEBUG: RAISE WARNING 'Country names updated';

+
+    -- Also update the list of country names. Adding an additional sanity
+    -- check here: make sure the country does overlap with the area where
+    -- we expect it to be as per static country grid.
+    FOR location IN
+      SELECT country_code FROM country_osm_grid
+       WHERE ST_Covers(geometry, NEW.centroid) and country_code = NEW.country_code
+       LIMIT 1
+    LOOP
+      --DEBUG: RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;
+      UPDATE country_name SET name = name || NEW.name WHERE country_code = NEW.country_code;
+    END LOOP;

   END IF;
 
   -- For linear features we need the full geometry for determining the address
   END IF;
 
   -- For linear features we need the full geometry for determining the address

diff --git a/test/bdd/db/import/country.feature b/test/bdd/db/import/country.feature
new file mode 100644 (file)
index 0000000..21f95e1
--- /dev/null
+++ b/test/bdd/db/import/country.feature
@@ -0,0 +1,47 @@
+@DB
+Feature: Country handling
+    Tests for import and use of country information
+
+    Scenario: Country names from OSM country relations are added
+        Given the places
+            | osm  | class    | type           | admin | name+name:xy | country | geometry |
+            | R1   | boundary | administrative | 2     | Loudou       | de      | (9 52, 9 53, 10 52, 9 52) |
+        Given the places
+            | osm  | class    | type          | name  | geometry   |
+            | N1   | place    | town          | Wenig | country:de |
+        When importing
+        When searching for "Wenig, Loudou"
+        Then results contain
+            | osm_type | osm_id | name |
+            | N        | 1      | Wenig, Deutschland |
+        When searching for "Wenig"
+            | accept-language |
+            | xy,en |
+        Then results contain
+            | osm_type | osm_id | name |
+            | N        | 1      | Wenig, Loudou |
+    Scenario: OSM country relations outside expected boundaries are ignored
+        Given the places
+            | osm  | class    | type           | admin | name+name:xy | country | geometry |
+            | R1   | boundary | administrative | 2     | Loudou       | de      | poly-area:0.1 |
+        Given the places
+            | osm  | class    | type          | name  | geometry   |
+            | N1   | place    | town          | Wenig | country:de |
+        When importing
+        When searching for "Wenig"
+            | accept-language |
+            | xy,en |
+        Then results contain
+            | osm_type | osm_id | name |
+            | N        | 1      | Wenig, Germany |
+    Scenario: Pre-defined country names are used
+        Given the places
+            | osm  | class    | type          | name  | geometry   |
+            | N1   | place    | town          | Ingb  | country:ch |
+        When importing
+        And searching for "Ingb"
+            | accept-language |
+            | en,de |
+        Then results contain
+            | osm_type | osm_id | name |
+            | N        | 1      | Ingb, Switzerland |