reindex postcodes that loose their parents

When the parent place of a postcode is deleted, it needs to
be reindexed to get a new parent. Otherwise displaying of
results is broken.

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 681c302d575f60c0c22fdcdea01c0d8e20618591..a40923b04a703d164e96950709fdcd0bc7cecd0c 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -1399,6 +1399,8 @@ BEGIN
 
   {% if debug %}RAISE WARNING 'placex_delete:12 % %',OLD.osm_type,OLD.osm_id;{% endif %}
 
+  UPDATE location_postcode SET indexed_status = 2 WHERE parent_place_id = OLD.place_id;
+
   RETURN OLD;
 
 END;

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index 9c31f55699c06e58c9242c8c722e7d42ab430dd1..8c176fdf8199b55503a9775f5cf8e78e973ce969 100644 (file)
--- a/lib-sql/indices.sql
+++ b/lib-sql/indices.sql
@@ -81,6 +81,9 @@ CREATE INDEX IF NOT EXISTS idx_postcode_postcode
     type TEXT,
     deferred BOOLEAN
    );
+---
+  CREATE INDEX IF NOT EXISTS idx_location_postcode_parent_place_id
+    ON location_postcode USING BTREE (parent_place_id) {{db.tablespace.address_index}};
 {% endif %}
 
 -- Indices only needed for search.

diff --git a/nominatim/clicmd/index.py b/nominatim/clicmd/index.py
index 16b5311c79f5764c2f9cde048b5635e0a1619e03..86a504e71f15b56e53d9886cabcde14fee849cb5 100644 (file)
--- a/nominatim/clicmd/index.py
+++ b/nominatim/clicmd/index.py
@@ -56,6 +56,7 @@ class UpdateIndex:
             indexer.index_boundaries(args.minrank, args.maxrank)
         if not args.boundaries_only:
             indexer.index_by_rank(args.minrank, args.maxrank)
+            indexer.index_postcodes()
 
         if not args.no_boundaries and not args.boundaries_only \
            and args.minrank == 0 and args.maxrank == 30:

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index e864ce5254e7840bee0059c98d8288beacff7af8..02b7d19a0033213971c0326c6372aad4155ff0b0 100644 (file)
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -384,7 +384,7 @@ def add_improved_geometry_reverse_placenode_index(conn: Connection, **_: Any) ->
                     """)
 
 @_migration(4, 4, 99, 0)
-def create_postcode_ara_lookup_index(conn: Connection, **_: Any) -> None:
+def create_postcode_area_lookup_index(conn: Connection, **_: Any) -> None:
     """ Create index needed for looking up postcode areas from postocde points.
     """
     with conn.cursor() as cur:
@@ -392,3 +392,14 @@ def create_postcode_ara_lookup_index(conn: Connection, **_: Any) -> None:
                        ON placex USING BTREE (country_code, postcode)
                        WHERE osm_type = 'R' AND class = 'boundary' AND type = 'postal_code'
                     """)
+
+
+@_migration(4, 4, 99, 1)
+def create_postcode_parent_index(conn: Connection, **_: Any) -> None:
+    """ Create index needed for updating postcodes when a parent changes.
+    """
+    if conn.table_exists('planet_osm_ways'):
+        with conn.cursor() as cur:
+            cur.execute("""CREATE INDEX IF NOT EXISTS
+                             idx_location_postcode_parent_place_id
+                             ON location_postcode USING BTREE (parent_place_id)""")

diff --git a/nominatim/version.py b/nominatim/version.py
index 76da1dbd97694b09ebd374849f0cd25ac5756a33..9ef22a8792426a18abea2c5cab5c83047051ad84 100644 (file)
--- a/nominatim/version.py
+++ b/nominatim/version.py
@@ -34,7 +34,7 @@ class NominatimVersion(NamedTuple):
         return f"{self.major}.{self.minor}.{self.patch_level}-{self.db_patch_level}"
 
 
-NOMINATIM_VERSION = NominatimVersion(4, 4, 99, 0)
+NOMINATIM_VERSION = NominatimVersion(4, 4, 99, 1)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 6)
 POSTGIS_REQUIRED_VERSION = (2, 2)

diff --git a/test/bdd/db/update/postcode.feature b/test/bdd/db/update/postcode.feature
index c6696ddae8d86090e991cb545d43f4c145cad96a..393181012cd876259438538b7c809b7433390795 100644 (file)
--- a/test/bdd/db/update/postcode.feature
+++ b/test/bdd/db/update/postcode.feature
@@ -102,3 +102,25 @@ Feature: Update of postcode
            | country | postcode | geometry |
            | de      | 01982    | country:de |
         And there are word tokens for postcodes 01982
+
+    Scenario: When a parent is deleted, the postcode gets a new parent
+        Given the grid with origin DE
+           | 1 |   | 3 | 4 |
+           |   | 9 |   |   |
+           | 2 |   | 5 | 6 |
+        Given the places
+           | osm | class    | type           | name  | admin | geometry    |
+           | R1  | boundary | administrative | Big   | 6     | (1,4,6,2,1) |
+           | R2  | boundary | administrative | Small | 6     | (1,3,5,2,1) |
+        Given the named places
+           | osm | class | type     | addr+postcode | geometry |
+           | N9  | place | postcode | 12345         | 9        |
+        When importing
+        And updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry | parent_place_id |
+           | de      | 12345    | 9        | R2              |
+        When marking for delete R2
+        Then location_postcode contains exactly
+           | country | postcode | geometry | parent_place_id |
+           | de      | 12345    | 9        | R1              |

diff --git a/test/python/cli/test_cli.py b/test/python/cli/test_cli.py
index 998bccb434c418eeaa076995f851a0cbaeab8ba9..12a58d075b9933244e1b7bd33f757d1b568be9bf 100644 (file)
--- a/test/python/cli/test_cli.py
+++ b/test/python/cli/test_cli.py
@@ -129,11 +129,13 @@ class TestCliWithDb:
         table_factory('import_status', 'indexed bool')
         bnd_mock = mock_func_factory(nominatim.indexer.indexer.Indexer, 'index_boundaries')
         rank_mock = mock_func_factory(nominatim.indexer.indexer.Indexer, 'index_by_rank')
+        postcode_mock = mock_func_factory(nominatim.indexer.indexer.Indexer, 'index_postcodes')
 
         assert self.call_nominatim('index', *params) == 0
 
         assert bnd_mock.called == do_bnds
         assert rank_mock.called == do_ranks
+        assert postcode_mock.called == do_ranks
 
 
     def test_special_phrases_wiki_command(self, mock_func_factory):