add migration for new interpolation table layout

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index 284bd3166f8648cf18b0554c9368dc0368579ccf..c24b09cbf1303949c935eceb40f70be147da77c1 100644 (file)
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -227,3 +227,35 @@ def create_interpolation_index_on_place(conn, **_):
         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 which will.
+
+        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
+                    """)

diff --git a/nominatim/version.py b/nominatim/version.py
index b666c8e76a43bf6d5eb6c365212cd84ef651019c..88bb881f491a491f8e34030666b9cd6c5059481c 100644 (file)
--- a/nominatim/version.py
+++ b/nominatim/version.py
@@ -24,7 +24,7 @@ Version information for Nominatim.
 # patch level when cherry-picking the commit with the migration.
 #
 # Released versions always have a database patch level of 0.
-NOMINATIM_VERSION = (4, 0, 99, 2)
+NOMINATIM_VERSION = (4, 0, 99, 3)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 5)
 POSTGIS_REQUIRED_VERSION = (2, 2)