From dc7cfd1708da6fceb268d2efaaf92ef513a20234 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Mon, 11 Mar 2024 14:48:24 +0100
Subject: [PATCH] look for postcode areas when finding something in the
 postcode table

---
 lib-sql/indices.sql                 |  5 +++++
 nominatim/api/search/db_searches.py | 16 +++++++++++++++-
 nominatim/tools/migration.py        | 10 ++++++++++
 nominatim/version.py                |  2 +-
 test/bdd/db/query/postcodes.feature | 16 ++++++++++++++++
 5 files changed, 47 insertions(+), 2 deletions(-)

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index b802a660..9c31f556 100644
--- a/lib-sql/indices.sql
+++ b/lib-sql/indices.sql
@@ -21,6 +21,11 @@ CREATE INDEX IF NOT EXISTS idx_placex_parent_place_id
   ON placex USING BTREE (parent_place_id) {{db.tablespace.search_index}}
   WHERE parent_place_id IS NOT NULL;
 ---
+-- Used to find postcode areas after a search in location_postcode.
+CREATE INDEX IF NOT EXISTS idx_placex_postcode_areas
+  ON placex USING BTREE (country_code, postcode) {{db.tablespace.search_index}}
+  WHERE osm_type = 'R' AND class = 'boundary' AND type = 'postal_code';
+---
 CREATE INDEX IF NOT EXISTS idx_placex_geometry ON placex
   USING GIST (geometry) {{db.tablespace.search_index}};
 -- Index is needed during import but can be dropped as soon as a full
diff --git a/nominatim/api/search/db_searches.py b/nominatim/api/search/db_searches.py
index be883953..6631c7cb 100644
--- a/nominatim/api/search/db_searches.py
+++ b/nominatim/api/search/db_searches.py
@@ -602,7 +602,21 @@ class PostcodeSearch(AbstractSearch):
 
         results = nres.SearchResults()
         for row in await conn.execute(sql, _details_to_bind_params(details)):
-            result = nres.create_from_postcode_row(row, nres.SearchResult)
+            p = conn.t.placex
+            placex_sql = _select_placex(p).add_columns(p.c.importance)\
+                             .where(sa.text("""class = 'boundary'
+                                               AND type = 'postal_code'
+                                               AND osm_type = 'R'"""))\
+                             .where(p.c.country_code == row.country_code)\
+                             .where(p.c.postcode == row.postcode)\
+                             .where(_exclude_places(p))\
+                             .limit(1)
+            for prow in await conn.execute(placex_sql, _details_to_bind_params(details)):
+                result = nres.create_from_placex_row(prow, nres.SearchResult)
+                break
+            else:
+                result = nres.create_from_postcode_row(row, nres.SearchResult)
+
             assert result
             result.accuracy = row.accuracy
             results.append(result)
diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index ffeb4958..e864ce52 100644
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -382,3 +382,13 @@ def add_improved_geometry_reverse_placenode_index(conn: Connection, **_: Any) ->
                        WHERE rank_address between 4 and 25 AND type != 'postcode'
                          AND name is not null AND linked_place_id is null AND osm_type = 'N'
                     """)
+
+@_migration(4, 4, 99, 0)
+def create_postcode_ara_lookup_index(conn: Connection, **_: Any) -> None:
+    """ Create index needed for looking up postcode areas from postocde points.
+    """
+    with conn.cursor() as cur:
+        cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_postcode_areas
+                       ON placex USING BTREE (country_code, postcode)
+                       WHERE osm_type = 'R' AND class = 'boundary' AND type = 'postal_code'
+                    """)
diff --git a/nominatim/version.py b/nominatim/version.py
index 88112cf6..76da1dbd 100644
--- 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, 0, 0)
+NOMINATIM_VERSION = NominatimVersion(4, 4, 99, 0)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 6)
 POSTGIS_REQUIRED_VERSION = (2, 2)
diff --git a/test/bdd/db/query/postcodes.feature b/test/bdd/db/query/postcodes.feature
index 78a26a90..e0a622d1 100644
--- a/test/bdd/db/query/postcodes.feature
+++ b/test/bdd/db/query/postcodes.feature
@@ -95,3 +95,19 @@ Feature: Querying fo postcode variants
            | type     | display_name |
            | postcode | E4 7EA, United Kingdom |
 
+
+    Scenario: Postcode areas are preferred over postcode points
+        Given the grid with origin DE
+            | 1 | 2 |
+            | 4 | 3 |
+        Given the places
+            | osm | class    | type        | postcode | geometry    |
+            | R23 | boundary | postal_code | 12345    | (1,2,3,4,1) |
+        When importing
+        Then location_postcode contains exactly
+          | country | postcode |
+          | de      | 12345    |
+        When sending search query "12345, de"
+        Then results contain
+          | osm |
+          | R23 |
-- 
2.45.1