look for postcode areas when finding something in the postcode table

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index b802a660e7c31446c5a62483707de1e4f771228f..9c31f55699c06e58c9242c8c722e7d42ab430dd1 100644 (file)
--- 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;
 ---
   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
 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 be883953276ccf74eed7497d0ece6caf9a275498..6631c7cb522ceef2f02fa9173660e4dbfa043058 100644 (file)
--- 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)):
 
         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)
             assert result
             result.accuracy = row.accuracy
             results.append(result)

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index ffeb4958f409ce285e7fa8f91ba2d342ffe2cfe9..e864ce5254e7840bee0059c98d8288beacff7af8 100644 (file)
--- 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'
                     """)
                        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 88112cf6c67701bcc915bdb7c8d8c97cc5846706..76da1dbd97694b09ebd374849f0cd25ac5756a33 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}"
 
 
         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)
 
 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 78a26a90f5dfd723a2d3511e23a03a0293023536..e0a622d1c074bee218183f7792ec40de71e664c6 100644 (file)
--- 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 |
 
            | 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 |