add postcodes patterns without optional spaces

[nominatim.git] / lib-sql / tokenizer / icu_tokenizer_tables.sql

diff --git a/lib-sql/tokenizer/icu_tokenizer_tables.sql b/lib-sql/tokenizer/icu_tokenizer_tables.sql
index dd3ac4779613d4cc1b69c093f8fbe78bd8587d03..509f6f65d9d6e5dd659c3d95ad2e3220b4209bb5 100644 (file)
--- a/lib-sql/tokenizer/icu_tokenizer_tables.sql
+++ b/lib-sql/tokenizer/icu_tokenizer_tables.sql
@@ -1,8 +1,16 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+

 DROP TABLE IF EXISTS word;
 DROP TABLE IF EXISTS word;

-CREATE TABLE word_icu (
+CREATE TABLE word (

   word_id INTEGER,
   word_token text NOT NULL,
   type text NOT NULL,
   word_id INTEGER,
   word_token text NOT NULL,
   type text NOT NULL,

+  word text,

   info jsonb
 ) {{db.tablespace.search_data}};
 
   info jsonb
 ) {{db.tablespace.search_data}};
 


@@ -10,12 +18,21 @@ CREATE INDEX idx_word_word_token ON word
     USING BTREE (word_token) {{db.tablespace.search_index}};
 -- Used when updating country names from the boundary relation.
 CREATE INDEX idx_word_country_names ON word
     USING BTREE (word_token) {{db.tablespace.search_index}};
 -- Used when updating country names from the boundary relation.
 CREATE INDEX idx_word_country_names ON word

-    USING btree((info->>'cc')) {{db.tablespace.address_index}}
+    USING btree(word) {{db.tablespace.address_index}}

     WHERE type = 'C';
 -- Used when inserting new postcodes on updates.
 CREATE INDEX idx_word_postcodes ON word
     WHERE type = 'C';
 -- Used when inserting new postcodes on updates.
 CREATE INDEX idx_word_postcodes ON word

-    USING btree((info->>'postcode')) {{db.tablespace.address_index}}
-    WHERE type = 'P'
+    USING btree(word) {{db.tablespace.address_index}}
+    WHERE type = 'P';
+-- Used when inserting full words.
+CREATE INDEX idx_word_full_word ON word
+    USING btree(word) {{db.tablespace.address_index}}
+    WHERE type = 'W';
+-- Used when inserting analyzed housenumbers (exclude old-style entries).
+CREATE INDEX idx_word_housenumbers ON word
+    USING btree(word) {{db.tablespace.address_index}}
+    WHERE type = 'H' and word is not null;
+

 GRANT SELECT ON word TO "{{config.DATABASE_WEBUSER}}";
 
 DROP SEQUENCE IF EXISTS seq_word;
 GRANT SELECT ON word TO "{{config.DATABASE_WEBUSER}}";
 
 DROP SEQUENCE IF EXISTS seq_word;