add explicit bbox contains check

Now that the containment check uses ST_Relate, we need to add
a separate bbox contains check to ensure that Postgis does the
efficient check first. Note that we still cannot get rid of the
overlap(&&) check because then Postgis will use the wrong indexes.

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 9bc3469b10d31e420f31292f07dcf96a7d974762..696a7c343349e19b842a5e0f65ef45b8d34d7d77 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -618,18 +618,21 @@ BEGIN
         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
-    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
-    LOOP
-        NEW.rank_address := location.rank_address + 2;
-    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