X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/a73711f3cd5e4256ca7808dcca8f6fdb59fa716f..98432395c31f0ae46962907e2c19c4eebc12c8bb:/nominatim/tools/migration.py diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py index c8011eeb..dc6dfeca 100644 --- a/nominatim/tools/migration.py +++ b/nominatim/tools/migration.py @@ -1,3 +1,9 @@ +# 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. """ Functions for database migration to newer software versions. """ @@ -26,7 +32,7 @@ def migrate(config, paths): if db_version_str is not None: parts = db_version_str.split('.') - db_version = tuple([int(x) for x in parts[:2] + parts[2].split('-')]) + db_version = tuple(int(x) for x in parts[:2] + parts[2].split('-')) if db_version == NOMINATIM_VERSION: LOG.warning("Database already at latest version (%s)", db_version_str) @@ -44,6 +50,7 @@ def migrate(config, paths): '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(version)) kwargs = dict(conn=conn, config=config, paths=paths) func(**kwargs) + conn.commit() has_run_migration = True if has_run_migration: @@ -95,6 +102,7 @@ def _migration(major, minor, patch=0, dbpatch=0): """ def decorator(func): _MIGRATION_FUNCTIONS.append(((major, minor, patch, dbpatch), func)) + return func return decorator @@ -129,6 +137,9 @@ def change_housenumber_transliteration(conn, **_): The database schema switched from saving raw housenumbers in placex.housenumber to saving transliterated ones. + + Note: the function create_housenumber_id() has been dropped in later + versions. """ with conn.cursor() as cur: cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT) @@ -138,7 +149,8 @@ def change_housenumber_transliteration(conn, **_): BEGIN SELECT array_to_string(array_agg(trans), ';') INTO normtext - FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word) + 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; @@ -181,9 +193,81 @@ def install_legacy_tokenizer(conn, config, **_): WHERE table_name = %s and column_name = 'token_info'""", (table, )) - if has_column == 0: - cur.execute('ALTER TABLE {} ADD COLUMN token_info JSONB'.format(table)) + if has_column == 0: + cur.execute('ALTER TABLE {} ADD COLUMN token_info JSONB'.format(table)) tokenizer = tokenizer_factory.create_tokenizer(config, init_db=False, module_name='legacy') tokenizer.migrate_database(config) + + +@_migration(4, 0, 99, 0) +def create_tiger_housenumber_index(conn, **_): + """ Create idx_location_property_tiger_parent_place_id with included + house number. + + The inclusion is needed for efficient lookup of housenumbers in + full address searches. + """ + if conn.server_version_tuple() >= (11, 0, 0): + with conn.cursor() as cur: + cur.execute(""" CREATE INDEX IF NOT EXISTS + idx_location_property_tiger_housenumber_migrated + ON location_property_tiger + USING btree(parent_place_id) + INCLUDE (startnumber, endnumber) """) + + +@_migration(4, 0, 99, 1) +def create_interpolation_index_on_place(conn, **_): + """ Create idx_place_interpolations for lookup of interpolation lines + on updates. + """ + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_place_interpolations + ON place USING gist(geometry) + WHERE osm_type = 'W' and address ? 'interpolation'""") + + +@_migration(4, 0, 99, 2) +def add_step_column_for_interpolation(conn, **_): + """ Add a new column 'step' to the interpolations table. + + Also convers the data into the stricter format which requires that + startnumbers comply with the odd/even requirements. + """ + with conn.cursor() as cur: + # Mark invalid all interpolations with no intermediate numbers. + cur.execute("""UPDATE location_property_osmline SET startnumber = null + WHERE endnumber - startnumber <= 1 """) + # Align the start numbers where odd/even does not match. + cur.execute("""UPDATE location_property_osmline + SET startnumber = startnumber + 1, + linegeo = ST_LineSubString(linegeo, + 1.0 / (endnumber - startnumber)::float, + 1) + WHERE (interpolationtype = 'odd' and startnumber % 2 = 0) + or (interpolationtype = 'even' and startnumber % 2 = 1) + """) + # Mark invalid odd/even interpolations with no intermediate numbers. + cur.execute("""UPDATE location_property_osmline SET startnumber = null + WHERE interpolationtype in ('odd', 'even') + and endnumber - startnumber = 2""") + # Finally add the new column and populate it. + cur.execute("ALTER TABLE location_property_osmline ADD COLUMN step SMALLINT") + cur.execute("""UPDATE location_property_osmline + SET step = CASE WHEN interpolationtype = 'all' + THEN 1 ELSE 2 END + """) + + +@_migration(4, 0, 99, 3) +def add_step_column_for_tiger(conn, **_): + """ Add a new column 'step' to the tiger data table. + """ + with conn.cursor() as cur: + cur.execute("ALTER TABLE location_property_tiger ADD COLUMN step SMALLINT") + cur.execute("""UPDATE location_property_tiger + SET step = CASE WHEN interpolationtype = 'all' + THEN 1 ELSE 2 END + """)