add migration for transliterated housenumbers

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index 756a1d3a903326a8540dcbee9ee3b545bb11d843..b5f0b80e7786328955c910e7b58ffa6b7190c71c 100644 (file)
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -130,3 +130,29 @@ def add_nominatim_property_table(conn, config, **_):
                                value TEXT);
                            GRANT SELECT ON TABLE nominatim_properties TO "{}";
                         """.format(config.DATABASE_WEBUSER))
+
+@_migration(3, 6, 0, 0)
+def change_housenumber_transliteration(conn, **_):
+    """ Transliterate housenumbers.
+
+        The database schema switched from saving raw housenumbers in
+        placex.housenumber to saving transliterated ones.
+    """
+    with conn.cursor() as cur:
+        cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)
+                       RETURNS TEXT AS $$
+                       DECLARE
+                         normtext TEXT;
+                       BEGIN
+                         SELECT array_to_string(array_agg(trans), ';')
+                           INTO normtext
+                           FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word)
+                                 FROM (SELECT make_standard_name(h) as lookup_word
+                                       FROM regexp_split_to_table(housenumber, '[,;]') h) x) y;
+                         return normtext;
+                       END;
+                       $$ LANGUAGE plpgsql STABLE STRICT;""")
+        cur.execute("DELETE FROM word WHERE class = 'place' and type = 'house'")
+        cur.execute("""UPDATE placex
+                       SET housenumber = create_housenumber_id(housenumber)
+                       WHERE housenumber is not null""")

diff --git a/nominatim/version.py b/nominatim/version.py
index e7f31a12b5ab4dcebcfed07e9a32effdb1e716c4..352e6e55d79c39e455db9d003cd863f2bc337ad9 100644 (file)
--- a/nominatim/version.py
+++ b/nominatim/version.py
@@ -10,7 +10,7 @@ Version information for Nominatim.
 # and must always be increased when there is a change to the database or code
 # that requires a migration.
 # Released versions always have a database patch level of 0.
-NOMINATIM_VERSION = (3, 6, 0, 0)
+NOMINATIM_VERSION = (3, 6, 0, 1)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 3)
 POSTGIS_REQUIRED_VERSION = (2, 2)