+ for table in ('placex', 'location_property_osmline'):
+ has_column = cur.scalar("""SELECT count(*) FROM information_schema.columns
+ WHERE table_name = %s
+ and column_name = 'token_info'""",
+ (table, ))
+ if has_column == 0:
+ cur.execute(pysql.SQL('ALTER TABLE {} ADD COLUMN token_info JSONB')
+ .format(pysql.Identifier(table)))
+ tokenizer = tokenizer_factory.create_tokenizer(config, init_db=False,
+ module_name='legacy')
+
+ tokenizer.migrate_database(config) # type: ignore[attr-defined]
+
+
+@_migration(4, 0, 99, 0)
+def create_tiger_housenumber_index(conn: Connection, **_: Any) -> None:
+ """ 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: Connection, **_: Any) -> None:
+ """ 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: Connection, **_: Any) -> None:
+ """ Add a new column 'step' to the interpolations table.
+
+ Also converts the data into the stricter format which requires that
+ startnumbers comply with the odd/even requirements.
+ """
+ if conn.table_has_column('location_property_osmline', 'step'):
+ return
+
+ 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: Connection, **_: Any) -> None:
+ """ Add a new column 'step' to the tiger data table.
+ """
+ if conn.table_has_column('location_property_tiger', 'step'):
+ return
+
+ 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
+ """)
+
+
+@_migration(4, 0, 99, 4)
+def add_derived_name_column_for_country_names(conn: Connection, **_: Any) -> None:
+ """ Add a new column 'derived_name' which in the future takes the
+ country names as imported from OSM data.
+ """
+ if not conn.table_has_column('country_name', 'derived_name'):
+ with conn.cursor() as cur:
+ cur.execute("ALTER TABLE country_name ADD COLUMN derived_name public.HSTORE")
+
+
+@_migration(4, 0, 99, 5)
+def mark_internal_country_names(conn: Connection, config: Configuration, **_: Any) -> None:
+ """ Names from the country table should be marked as internal to prevent
+ them from being deleted. Only necessary for ICU tokenizer.
+ """
+ import psycopg2.extras # pylint: disable=import-outside-toplevel
+
+ tokenizer = tokenizer_factory.get_tokenizer_for_db(config)
+ with tokenizer.name_analyzer() as analyzer:
+ with conn.cursor() as cur:
+ psycopg2.extras.register_hstore(cur)
+ cur.execute("SELECT country_code, name FROM country_name")
+
+ for country_code, names in cur:
+ if not names:
+ names = {}
+ names['countrycode'] = country_code
+ analyzer.add_country_names(country_code, names)
+
+
+@_migration(4, 1, 99, 0)
+def add_place_deletion_todo_table(conn: Connection, **_: Any) -> None:
+ """ Add helper table for deleting data on updates.
+
+ The table is only necessary when updates are possible, i.e.
+ the database is not in freeze mode.
+ """
+ if conn.table_exists('place'):
+ with conn.cursor() as cur:
+ cur.execute("""CREATE TABLE IF NOT EXISTS place_to_be_deleted (
+ osm_type CHAR(1),
+ osm_id BIGINT,
+ class TEXT,
+ type TEXT,
+ deferred BOOLEAN)""")
+
+
+@_migration(4, 1, 99, 1)
+def split_pending_index(conn: Connection, **_: Any) -> None:
+ """ Reorganise indexes for pending updates.
+ """
+ if conn.table_exists('place'):
+ with conn.cursor() as cur:
+ cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_rank_address_sector
+ ON placex USING BTREE (rank_address, geometry_sector)
+ WHERE indexed_status > 0""")
+ cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_rank_boundaries_sector
+ ON placex USING BTREE (rank_search, geometry_sector)
+ WHERE class = 'boundary' and type = 'administrative'
+ and indexed_status > 0""")
+ cur.execute("DROP INDEX IF EXISTS idx_placex_pendingsector")
+
+
+@_migration(4, 2, 99, 0)
+def enable_forward_dependencies(conn: Connection, **_: Any) -> None:
+ """ Create indexes for updates with forward dependency tracking (long-running).
+ """
+ if conn.table_exists('planet_osm_ways'):
+ with conn.cursor() as cur:
+ cur.execute("""SELECT * FROM pg_indexes
+ WHERE tablename = 'planet_osm_ways'
+ and indexdef LIKE '%nodes%'""")
+ if cur.rowcount == 0:
+ cur.execute("""CREATE OR REPLACE FUNCTION public.planet_osm_index_bucket(bigint[])
+ RETURNS bigint[]
+ LANGUAGE sql IMMUTABLE
+ AS $function$
+ SELECT ARRAY(SELECT DISTINCT unnest($1) >> 5)
+ $function$""")
+ cur.execute("""CREATE INDEX planet_osm_ways_nodes_bucket_idx
+ ON planet_osm_ways
+ USING gin (planet_osm_index_bucket(nodes))
+ WITH (fastupdate=off)""")
+ cur.execute("""CREATE INDEX planet_osm_rels_parts_idx
+ ON planet_osm_rels USING gin (parts)
+ WITH (fastupdate=off)""")
+ cur.execute("ANALYZE planet_osm_ways")
+
+
+@_migration(4, 2, 99, 1)
+def add_improved_geometry_reverse_placenode_index(conn: Connection, **_: Any) -> None:
+ """ Create improved index for reverse lookup of place nodes.
+ """
+ with conn.cursor() as cur:
+ cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPlaceNode
+ ON placex
+ USING gist (ST_Buffer(geometry, reverse_place_diameter(rank_search)))
+ 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'
+ """)
projects / nominatim.git / blobdiff