X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/dbaf215feacb6ca82ec8c1bf3821ba5ebf218778..b8d41cf38cb9abead17f53fc42c28b65e0ae62b7:/lib-sql/functions/placex_triggers.sql

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 3755a11f..f8580d7f 100644
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -26,40 +26,47 @@ CREATE OR REPLACE FUNCTION placex_indexing_prepare(p placex)
 DECLARE
   location RECORD;
   result prepare_update_info;
+  extra_names HSTORE;
 BEGIN
+  IF not p.address ? '_inherited' THEN
+    result.address := p.address;
+  END IF;
+
   -- For POI nodes, check if the address should be derived from a surrounding
   -- building.
-  IF p.rank_search < 30 OR p.osm_type != 'N' THEN
-    result.address := p.address;
-  ELSEIF p.address is null THEN
-    -- The additional && condition works around the misguided query
-    -- planner of postgis 3.0.
-    SELECT placex.address || hstore('_inherited', '') INTO result.address
-      FROM placex
-     WHERE ST_Covers(geometry, p.centroid)
-           and geometry && p.centroid
-           and placex.address is not null
-           and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
-           and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
-     LIMIT 1;
-  ELSE
-    result.address := p.address;
-    -- See if we can inherit addtional address tags from an interpolation.
-    -- These will become permanent.
-    FOR location IN
-      SELECT (address - 'interpolation'::text - 'housenumber'::text) as address
-        FROM place, planet_osm_ways w
-        WHERE place.osm_type = 'W' and place.address ? 'interpolation'
-              and place.geometry && p.geometry
-              and place.osm_id = w.id
-              and p.osm_id = any(w.nodes)
-    LOOP
-      result.address := location.address || result.address;
-    END LOOP;
+  IF p.rank_search = 30 AND p.osm_type = 'N' THEN
+    IF p.address is null THEN
+        -- The additional && condition works around the misguided query
+        -- planner of postgis 3.0.
+        SELECT placex.address || hstore('_inherited', '') INTO result.address
+          FROM placex
+         WHERE ST_Covers(geometry, p.centroid)
+               and geometry && p.centroid
+               and placex.address is not null
+               and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
+               and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+         LIMIT 1;
+    ELSE
+      -- See if we can inherit addtional address tags from an interpolation.
+      -- These will become permanent.
+      FOR location IN
+        SELECT (address - 'interpolation'::text - 'housenumber'::text) as address
+          FROM place, planet_osm_ways w
+          WHERE place.osm_type = 'W' and place.address ? 'interpolation'
+                and place.geometry && p.geometry
+                and place.osm_id = w.id
+                and p.osm_id = any(w.nodes)
+      LOOP
+        result.address := location.address || result.address;
+      END LOOP;
+    END IF;
   END IF;
 
+  -- remove internal and derived names
   result.address := result.address - '_unlisted_place'::TEXT;
-  result.name := p.name;
+  SELECT hstore(array_agg(key), array_agg(value)) INTO result.name
+    FROM each(p.name) WHERE key not like '\_%';
+
   result.class := p.class;
   result.type := p.type;
   result.country_code := p.country_code;
@@ -72,8 +79,20 @@ BEGIN
   IF location.place_id is not NULL THEN
     result.linked_place_id := location.place_id;
 
-    IF NOT location.name IS NULL THEN
-      result.name := location.name || result.name;
+    IF location.name is not NULL THEN
+      {% if debug %}RAISE WARNING 'Names original: %, location: %', result.name, location.name;{% endif %}
+      -- Add all names from the place nodes that deviate from the name
+      -- in the relation with the prefix '_place_'. Deviation means that
+      -- either the value is different or a given key is missing completely
+      SELECT hstore(array_agg('_place_' || key), array_agg(value)) INTO extra_names
+        FROM each(location.name - result.name);
+      {% if debug %}RAISE WARNING 'Extra names: %', extra_names;{% endif %}
+
+      IF extra_names is not null THEN
+          result.name := result.name || extra_names;
+      END IF;
+
+      {% if debug %}RAISE WARNING 'Final names: %', result.name;{% endif %}
     END IF;
   END IF;
 
@@ -101,7 +120,8 @@ BEGIN
       IF location.members[i+1] = 'street' THEN
         FOR parent IN
           SELECT place_id from placex
-           WHERE osm_type = 'W' and osm_id = substring(location.members[i],2)::bigint
+           WHERE osm_type = upper(substring(location.members[i], 1, 1))::char(1)
+                 and osm_id = substring(location.members[i], 2)::bigint
                  and name is not null
                  and rank_search between 26 and 27
         LOOP
@@ -342,9 +362,10 @@ BEGIN
     WHERE s.place_id = parent_place_id;
 
   FOR addr_item IN
-    SELECT (get_addr_tag_rank(key, country)).*, key,
+    SELECT ranks.*, key,
            token_get_address_search_tokens(token_info, key) as search_tokens
-      FROM token_get_address_keys(token_info) as key
+      FROM token_get_address_keys(token_info) as key,
+           LATERAL get_addr_tag_rank(key, country) as ranks
       WHERE not token_get_address_search_tokens(token_info, key) <@ parent_address_vector
   LOOP
     addr_place := get_address_place(in_partition, geometry,
@@ -432,6 +453,7 @@ CREATE OR REPLACE FUNCTION insert_addresslines(obj_place_id BIGINT,
                                                maxrank SMALLINT,
                                                token_info JSONB,
                                                geometry GEOMETRY,
+                                               centroid GEOMETRY,
                                                country TEXT,
                                                OUT parent_place_id BIGINT,
                                                OUT postcode TEXT,
@@ -456,10 +478,12 @@ BEGIN
   address_havelevel := array_fill(false, ARRAY[maxrank]);
 
   FOR location IN
-    SELECT (get_address_place(partition, geometry, from_rank, to_rank,
-                              extent, token_info, key)).*, key
-      FROM (SELECT (get_addr_tag_rank(key, country)).*, key
-              FROM token_get_address_keys(token_info) as key) x
+    SELECT apl.*, key
+      FROM (SELECT extra.*, key
+              FROM token_get_address_keys(token_info) as key,
+                   LATERAL get_addr_tag_rank(key, country) as extra) x,
+           LATERAL get_address_place(partition, geometry, from_rank, to_rank,
+                              extent, token_info, key) as apl
       ORDER BY rank_address, distance, isguess desc
   LOOP
     IF location.place_id is null THEN
@@ -492,7 +516,7 @@ BEGIN
   END LOOP;
 
   FOR location IN
-    SELECT * FROM getNearFeatures(partition, geometry, maxrank)
+    SELECT * FROM getNearFeatures(partition, geometry, centroid, maxrank)
     WHERE not addr_place_ids @> ARRAY[place_id]
     ORDER BY rank_address, isguess asc,
              distance *
@@ -964,15 +988,6 @@ BEGIN
       {% endif %}
 
       NEW.token_info := token_strip_info(NEW.token_info);
-      -- If the address was inherited from a surrounding building,
-      -- do not add it permanently to the table.
-      IF NEW.address ? '_inherited' THEN
-        IF NEW.address ? '_unlisted_place' THEN
-          NEW.address := hstore('_unlisted_place', NEW.address->'_unlisted_place');
-        ELSE
-          NEW.address := null;
-        END IF;
-      END IF;
 
       RETURN NEW;
     END IF;
@@ -1044,17 +1059,23 @@ BEGIN
      AND NEW.class = 'boundary' AND NEW.type = 'administrative'
      AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R'
   THEN
-    -- 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
-    --  {% if debug %}RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;{% endif %}
-    --  UPDATE country_name SET name = name || NEW.name WHERE country_code = NEW.country_code;
-    -- END LOOP;
+    -- Update the list of country names.
+    -- Only take the name from the largest area for the given country code
+    -- in the hope that this is the authoritive one.
+    -- Also replace any old names so that all mapping mistakes can
+    -- be fixed through regular OSM updates.
+    FOR location IN
+      SELECT osm_id FROM placex
+       WHERE rank_search = 4 and osm_type = 'R'
+             and country_code = NEW.country_code
+       ORDER BY ST_Area(geometry) desc
+       LIMIT 1
+    LOOP
+      IF location.osm_id = NEW.osm_id THEN
+        {% if debug %}RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;{% endif %}
+        UPDATE country_name SET derived_name = NEW.name WHERE country_code = NEW.country_code;
+      END IF;
+    END LOOP;
   END IF;
 
   -- For linear features we need the full geometry for determining the address
@@ -1081,7 +1102,8 @@ BEGIN
   END IF;
 
   SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition, max_rank,
-                                    NEW.token_info, geom, NEW.country_code)
+                                    NEW.token_info, geom, NEW.centroid,
+                                    NEW.country_code)
     INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
 
   {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %}