X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6be2077d922098d65f069557dea55542c15a5c98..800240550bf30a306ca7b23568bab24e3a894920:/lib-sql/functions/placex_triggers.sql?ds=sidebyside

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 9463bb27..50467cf5 100644
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -28,35 +28,38 @@ DECLARE
   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
@@ -117,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
@@ -274,7 +278,7 @@ BEGIN
              OR position(bnd_name in lower(name->'name')) > 0)
         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'
-        AND placex.linked_place_id is null
+        AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id)
         AND placex.rank_search < 26 -- needed to select the right index
         AND placex.type != 'postcode'
         AND ST_Covers(bnd.geometry, placex.geometry)
@@ -290,7 +294,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'
-        AND placex.linked_place_id is null
+        AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id)
         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)
       ORDER BY lower(name->'name') = bnd_name desc
@@ -314,7 +318,7 @@ BEGIN
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'
         AND placex.class = 'place'
-        AND placex.linked_place_id is null
+        AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id)
         AND placex.rank_search < 26 -- needed to select the right index
         AND placex.type != 'postcode'
         AND ST_Covers(bnd.geometry, placex.geometry)
@@ -449,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,
@@ -511,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 *
@@ -754,9 +759,6 @@ BEGIN
   DELETE FROM place_addressline WHERE place_id = NEW.place_id;
   result := deleteRoad(NEW.partition, NEW.place_id);
   result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
-  UPDATE placex set linked_place_id = null, indexed_status = 2
-         where linked_place_id = NEW.place_id;
-  -- update not necessary for osmline, cause linked_place_id does not exist
 
   NEW.extratags := NEW.extratags - 'linked_place'::TEXT;
 
@@ -765,11 +767,11 @@ BEGIN
   linked_place := NEW.linked_place_id;
   NEW.linked_place_id := OLD.linked_place_id;
 
-  IF NEW.linked_place_id is not null THEN
-    NEW.token_info := null;
-    {% if debug %}RAISE WARNING 'place already linked to %', OLD.linked_place_id;{% endif %}
-    RETURN NEW;
-  END IF;
+  -- Remove linkage, if we have computed a different new linkee.
+  UPDATE placex SET linked_place_id = null, indexed_status = 2
+    WHERE linked_place_id = NEW.place_id
+          and (linked_place is null or linked_place_id != linked_place);
+  -- update not necessary for osmline, cause linked_place_id does not exist
 
   -- Postcodes are just here to compute the centroids. They are not searchable
   -- unless they are a boundary=postal_code.
@@ -815,6 +817,16 @@ BEGIN
                             NEW.class, NEW.type, NEW.admin_level,
                             (NEW.extratags->'capital') = 'yes',
                             NEW.address->'postcode');
+
+  -- Short-cut out for linked places. Note that this must happen after the
+  -- address rank has been recomputed. The linking might nullify a shift in
+  -- address rank.
+  IF NEW.linked_place_id is not null THEN
+    NEW.token_info := null;
+    {% if debug %}RAISE WARNING 'place already linked to %', OLD.linked_place_id;{% endif %}
+    RETURN NEW;
+  END IF;
+
   -- We must always increase the address level relative to the admin boundary.
   IF NEW.class = 'boundary' and NEW.type = 'administrative'
      and NEW.osm_type = 'R' and NEW.rank_address > 0
@@ -983,15 +995,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;
@@ -1002,7 +1005,14 @@ BEGIN
   -- Full indexing
   {% if debug %}RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;{% endif %}
   IF linked_place is not null THEN
-    SELECT * INTO location FROM placex WHERE place_id = linked_place;
+    -- Recompute the ranks here as the ones from the linked place might
+    -- have been shifted to accomodate surrounding boundaries.
+    SELECT place_id, osm_id, class, type, extratags,
+           centroid, geometry,
+           (compute_place_rank(country_code, osm_type, class, type, admin_level,
+                              (extratags->'capital') = 'yes', null)).*
+      INTO location
+      FROM placex WHERE place_id = linked_place;
 
     {% if debug %}RAISE WARNING 'Linked %', location;{% endif %}
 
@@ -1013,11 +1023,11 @@ BEGIN
         NEW.centroid := geom;
     END IF;
 
-    {% if debug %}RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;{% endif %}
-    IF location.rank_address > parent_address_level
-       and location.rank_address < 26
+    {% if debug %}RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.address_rank;{% endif %}
+    IF location.address_rank > parent_address_level
+       and location.address_rank < 26
     THEN
-      NEW.rank_address := location.rank_address;
+      NEW.rank_address := location.address_rank;
     END IF;
 
     -- merge in extra tags
@@ -1026,7 +1036,9 @@ BEGIN
                      || coalesce(NEW.extratags, ''::hstore);
 
     -- mark the linked place (excludes from search results)
-    UPDATE placex set linked_place_id = NEW.place_id
+    -- Force reindexing to remove any traces from the search indexes and
+    -- reset the address rank if necessary.
+    UPDATE placex set linked_place_id = NEW.place_id, indexed_status = 2
       WHERE place_id = location.place_id;
     -- ensure that those places are not found anymore
     {% if 'search_name' in db.tables %}
@@ -1106,7 +1118,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 %}