update country_names from OSM data

[nominatim.git] / sql / functions / placex_triggers.sql

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 6fd9e2258a4f67c91e372a00fd98509f8dfe6343..6965fe14dab23b1ad3ac55984d4a2ff62f848254 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -97,7 +97,7 @@ BEGIN
         -- Instead simply use the containing area with the largest rank.
         FOR location IN
           SELECT place_id FROM placex
         -- Instead simply use the containing area with the largest rank.
         FOR location IN
           SELECT place_id FROM placex

-            WHERE bbox @ geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
+            WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))

                   AND rank_address between 5 and 25
             ORDER BY rank_address desc
         LOOP
                   AND rank_address between 5 and 25
             ORDER BY rank_address desc
         LOOP


@@ -112,7 +112,7 @@ BEGIN
         -- contains the bbox, only use addressable features
         FOR location IN
           SELECT place_id FROM placex
         -- contains the bbox, only use addressable features
         FOR location IN
           SELECT place_id FROM placex

-            WHERE bbox @ geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
+            WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))

                   AND rank_address between 5 and 25
             ORDER BY rank_address desc
         LOOP
                   AND rank_address between 5 and 25
             ORDER BY rank_address desc
         LOOP


@@ -183,6 +183,7 @@ BEGIN
       WHERE make_standard_name(name->'name') = bnd_name
         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'
       WHERE make_standard_name(name->'name') = bnd_name
         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'

+        AND placex.linked_place_id is null

         AND placex.rank_search < 26 -- needed to select the right index
         AND _st_covers(bnd.geometry, placex.geometry)
     LOOP
         AND placex.rank_search < 26 -- needed to select the right index
         AND _st_covers(bnd.geometry, placex.geometry)
     LOOP


@@ -197,6 +198,7 @@ BEGIN
       WHERE placex.class = 'place' AND placex.osm_type = 'N'
         AND placex.extratags ? 'wikidata' -- needed to select right index
         AND placex.extratags->'wikidata' = bnd.extratags->'wikidata'
       WHERE placex.class = 'place' AND placex.osm_type = 'N'
         AND placex.extratags ? 'wikidata' -- needed to select right index
         AND placex.extratags->'wikidata' = bnd.extratags->'wikidata'

+        AND placex.linked_place_id is null

         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)
       ORDER BY make_standard_name(name->'name') = bnd_name desc
         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)
       ORDER BY make_standard_name(name->'name') = bnd_name desc


@@ -219,6 +221,7 @@ BEGIN
                                                          false, placex.postcode)).address_rank)
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'
                                                          false, placex.postcode)).address_rank)
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'

+        AND placex.linked_place_id is null

         AND placex.rank_search < 26 -- needed to select the right index
         AND _st_covers(bnd.geometry, placex.geometry)
     LOOP
         AND placex.rank_search < 26 -- needed to select the right index
         AND _st_covers(bnd.geometry, placex.geometry)
     LOOP


@@ -508,7 +511,7 @@ DECLARE
   location RECORD;
   relation_members TEXT[];
 
   location RECORD;
   relation_members TEXT[];
 

-  centroid GEOMETRY;
+  geom GEOMETRY;

   parent_address_level SMALLINT;
   place_address_level SMALLINT;
 
   parent_address_level SMALLINT;
   place_address_level SMALLINT;
 


@@ -849,9 +852,9 @@ BEGIN
 
     -- Use the linked point as the centre point of the geometry,
     -- but only if it is within the area of the boundary.
 
     -- Use the linked point as the centre point of the geometry,
     -- but only if it is within the area of the boundary.

-    centroid := coalesce(location.centroid, ST_Centroid(location.geometry));
-    IF centroid is not NULL AND ST_Within(centroid, NEW.geometry) THEN
-        NEW.centroid := centroid;
+    geom := coalesce(location.centroid, ST_Centroid(location.geometry));
+    IF geom is not NULL AND ST_Within(geom, NEW.geometry) THEN
+        NEW.centroid := geom;

     END IF;
 
     --DEBUG: RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;
     END IF;
 
     --DEBUG: RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;


@@ -916,10 +919,37 @@ 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
+  -- because they may go through several administrative entities. Otherwise use
+  -- the centroid for performance reasons.
+  IF ST_GeometryType(NEW.geometry) in ('ST_LineString', 'ST_MultiLineString') THEN
+    geom := NEW.geometry;
+  ELSE
+    geom := NEW.centroid;

   END IF;
 
   IF NEW.rank_address = 0 THEN
     max_rank := geometry_to_rank(NEW.rank_search, NEW.geometry, NEW.country_code);
   END IF;
 
   IF NEW.rank_address = 0 THEN
     max_rank := geometry_to_rank(NEW.rank_search, NEW.geometry, NEW.country_code);

+    -- Rank 0 features may also span multiple administrative areas (e.g. lakes)
+    -- so use the geometry here too. Just make sure the areas don't become too
+    -- large.
+    IF NEW.class = 'natural' or max_rank > 10 THEN
+      geom := NEW.geometry;
+    END IF;

   ELSEIF NEW.rank_address > 25 THEN
     max_rank := 25;
   ELSE
   ELSEIF NEW.rank_address > 25 THEN
     max_rank := 25;
   ELSE


@@ -927,11 +957,7 @@ BEGIN
   END IF;
 
   SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition, max_rank,
   END IF;
 
   SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition, max_rank,

-                                    NEW.address,
-                                    CASE WHEN (NEW.rank_address = 0 or
-                                               NEW.rank_search between 26 and 29)
-                                         THEN NEW.geometry ELSE NEW.centroid END,
-                                    NEW.country_code)
+                                    NEW.address, geom, NEW.country_code)

     INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
 
   --DEBUG: RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;
     INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
 
   --DEBUG: RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;