add indexes for lookup of addressable areas

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

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 70071b2f592f0c6a38f1a19a02bb0f7ddd95b8f1..6c23fd6799a569c5d8ac4d6728fe30abfef3b781 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -874,8 +874,9 @@ BEGIN
           FROM placex,
                LATERAL compute_place_rank(country_code, 'A', class, type,
                                           admin_level, False, null) prank
-          WHERE class = 'place' and rank_address < 24
+          WHERE class = 'place' and rank_address between 1 and 23
                 and prank.address_rank >= NEW.rank_address
+                and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
                 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
@@ -896,6 +897,8 @@ BEGIN
                LATERAL compute_place_rank(country_code, 'A', class, type,
                                           admin_level, False, null) prank
           WHERE prank.address_rank < 24
+                and rank_address between 1 and 25 -- select right index
+                and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
                 and prank.address_rank >= NEW.rank_address
                 and geometry && NEW.geometry
                 and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
@@ -916,7 +919,10 @@ BEGIN
              LATERAL compute_place_rank(country_code, 'A', class, type,
                                         admin_level, False, null) prank
         WHERE osm_type = 'R'
-              and prank.address_rank = NEW.rank_address
+              and rank_address between 1 and 25 -- select right index
+              and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
+              and ((class = 'place' and prank.address_rank = NEW.rank_address)
+                   or (class = 'boundary' and rank_address = NEW.rank_address))
               and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
         LIMIT 1
     LOOP
@@ -1101,6 +1107,15 @@ BEGIN
     END IF;
   END IF;
 
+  {% if not disable_diff_updates %}
+  IF OLD.rank_address != NEW.rank_address THEN
+    -- After a rank shift all addresses containing us must be updated.
+    UPDATE placex p SET indexed_status = 2 FROM place_addressline pa
+      WHERE pa.address_place_id = NEW.place_id and p.place_id = pa.place_id
+            and p.indexed_status = 0 and p.rank_address between 4 and 25;
+  END IF;
+  {% endif %}
+
   IF NEW.admin_level = 2
      AND NEW.class = 'boundary' AND NEW.type = 'administrative'
      AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R'