X-Git-Url: https://git.openstreetmap.org/nominatim.git/blobdiff_plain/62b94e838b7c7a67e17de3c749fd5764646b190b..a888f6ff93a7ba81245888ed03f5bf8470788e33:/sql/functions/placex_triggers.sql?ds=sidebyside

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 27fa3643..1e2aac4c 100644
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -494,33 +494,6 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION get_parent_address_level(geom GEOMETRY, in_level SMALLINT)
-  RETURNS SMALLINT
-  AS $$
-DECLARE
-  address_rank SMALLINT;
-BEGIN
-  IF in_level <= 3 or in_level > 15 THEN
-    address_rank := 3;
-  ELSE
-    SELECT rank_address INTO address_rank
-      FROM placex
-      WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
-            and admin_level < in_level
-            and geometry ~ geom and _ST_Covers(geometry, geom)
-      ORDER BY admin_level desc LIMIT 1;
-  END IF;
-
-  IF address_rank is NULL or address_rank <= 3 THEN
-    RETURN 3;
-  END IF;
-
-  RETURN address_rank;
-END;
-$$
-LANGUAGE plpgsql;
-
-
 CREATE OR REPLACE FUNCTION placex_update()
   RETURNS TRIGGER
   AS $$
@@ -610,26 +583,51 @@ BEGIN
      and NEW.osm_type = 'R' and NEW.rank_address > 0
   THEN
     -- First, check that admin boundaries do not overtake each other rank-wise.
-    parent_address_level := get_parent_address_level(NEW.centroid, NEW.admin_level);
-    IF parent_address_level >= NEW.rank_address THEN
-      IF parent_address_level >= 24 THEN
-        NEW.rank_address := 25;
-      ELSE
-        NEW.rank_address := parent_address_level + 2;
-      END IF;
-    END IF;
-    -- Second check that the boundary is not completely contained in a
-    -- place area with a higher address rank
+    parent_address_level := 3;
     FOR location IN
-      SELECT rank_address FROM placex
-      WHERE class = 'place' and rank_address < 24
-            and rank_address > NEW.rank_address
-            and geometry ~ NEW.geometry
-            and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
-      ORDER BY rank_address desc LIMIT 1
+      SELECT rank_address,
+             (CASE WHEN extratags ? 'wikidata' and NEW.extratags ? 'wikidata'
+                        and extratags->'wikidata' = NEW.extratags->'wikidata'
+                   THEN ST_Equals(geometry, NEW.geometry)
+                   ELSE false END) as is_same
+      FROM placex
+      WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
+            and admin_level < NEW.admin_level and admin_level > 3
+            and rank_address > 0
+            and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
+      ORDER BY admin_level desc LIMIT 1
     LOOP
-        NEW.rank_address := location.rank_address + 2;
+      IF location.is_same THEN
+        -- Looks like the same boundary is replicated on multiple admin_levels.
+        -- Usual tagging in Poland. Remove our boundary from addresses.
+        NEW.rank_address := 0;
+      ELSE
+        parent_address_level := location.rank_address;
+        IF location.rank_address >= NEW.rank_address THEN
+          IF location.rank_address >= 24 THEN
+            NEW.rank_address := 25;
+          ELSE
+            NEW.rank_address := location.rank_address + 2;
+          END IF;
+        END IF;
+      END IF;
     END LOOP;
+
+    IF NEW.rank_address > 9 THEN
+        -- Second check that the boundary is not completely contained in a
+        -- place area with a higher address rank
+        FOR location IN
+          SELECT rank_address FROM placex
+          WHERE class = 'place' and rank_address < 24
+                and rank_address > NEW.rank_address
+                and geometry && NEW.geometry
+                and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
+                and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
+          ORDER BY rank_address desc LIMIT 1
+        LOOP
+          NEW.rank_address := location.rank_address + 2;
+        END LOOP;
+    END IF;
   ELSEIF NEW.class = 'place' and NEW.osm_type = 'N'
      and NEW.rank_address between 16 and 23
   THEN
@@ -640,7 +638,7 @@ BEGIN
         SELECT rank_address FROM placex
         WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
               and rank_address = NEW.rank_address
-              and geometry ~ NEW.centroid and _ST_Covers(geometry, NEW.centroid)
+              and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
         LIMIT 1
     LOOP
       NEW.rank_address = NEW.rank_address + 2;
@@ -743,10 +741,10 @@ BEGIN
     IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
        AND NEW.housenumber IS NULL THEN
       FOR location IN
-        -- The additional ~ condition works around the misguided query
+        -- The additional && condition works around the misguided query
         -- planner of postgis 3.0.
         SELECT address from placex where ST_Covers(geometry, NEW.centroid)
-            and geometry ~ NEW.centroid
+            and geometry && NEW.centroid
             and (address ? 'housenumber' or address ? 'street' or address ? 'place')
             and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
             limit 1
@@ -801,7 +799,8 @@ BEGIN
           IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
             result := add_location(NEW.place_id, NEW.country_code, NEW.partition,
                                    name_vector, NEW.rank_search, NEW.rank_address,
-                                   upper(trim(NEW.address->'postcode')), NEW.geometry);
+                                   upper(trim(NEW.address->'postcode')), NEW.geometry,
+                                   NEW.centroid);
             --DEBUG: RAISE WARNING 'Place added to location table';
           END IF;
 
@@ -932,7 +931,7 @@ BEGIN
   IF NEW.name IS NOT NULL THEN
 
     IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
-      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
+      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry, NEW.centroid);
       --DEBUG: RAISE WARNING 'added to location (full)';
     END IF;