Database: update to EPSG v10.019

Non-trivial updates:
- some vertical CRS are now encoded as DerivedVerticalCRS. e.g EPSG:8228
  "NAVD88 height (ft)", with base EPSG:5703 "NAVD88 height". As we don't
  have support in our PROJ db model for DerivedVerticalCRS, modify the
  import script to 'resolve' the derivation up to the original datum.
- Method EPSG:1069 'Change of Vertical Unit' is no longer used. It is
  replaced by a generic-purpose EPSG:1104 method that doesn't take any
  conversion factor. And generic conversions EPSG:7812 and EPSG:7813 are
  now used in concatenated operations, which require code changes as
  well.

1 files changed, 23 insertions, 0 deletions

diff --git a/scripts/build_db.py b/scripts/build_db.py
index afecf3e8..7f17443c 100755
--- a/scripts/build_db.py
+++ b/scripts/build_db.py
@@ -47,6 +47,12 @@ def ingest_sqlite_dump(cursor, filename):
             line = line.decode('utf-8') #python3
         else:
             line = str(line) # python2
+
+        # Historically this script was developped with code columns using TEXT
+        # so keep it that way to minimized changes in it, and in the diff of
+        # generated .sql files
+        line = line.replace('INTEGER_OR_TEXT', 'TEXT')
+
         sql += line
         if sqlite3.complete_statement(sql):
             sql = sql.strip()
@@ -277,6 +283,23 @@ def fill_vertical_crs(proj_db_cursor):
     #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('vertical') AND datum_code IS NOT NULL", (EPSG_AUTHORITY,))
     proj_db_cursor.execute("INSERT INTO vertical_crs SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, ?, coord_sys_code, ?, datum_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('vertical') AND datum_code IS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
 
+    proj_db_cursor.execute("SELECT * FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('vertical') AND datum_code IS NULL AND projection_conv_code NOT IN (7812, 7813)")
+    res = proj_db_cursor.fetchall()
+    for row in res:
+        assert False, row
+
+    proj_db_cursor.execute("SELECT * FROM epsg.epsg_coordinatereferencesystem crs1 WHERE crs1.coord_ref_sys_kind IN ('vertical') AND crs1.datum_code IS NULL AND NOT EXISTS (SELECT 1 FROM epsg.epsg_coordinatereferencesystem crs2 WHERE crs2.coord_ref_sys_code = crs1.base_crs_code AND crs2.coord_ref_sys_kind IN ('vertical'))")
+    res = proj_db_cursor.fetchall()
+    for row in res:
+        assert False, row
+
+    # Insert vertical_crs that are based on another one, such as EPSG:8228 that is based on EPSG:5703
+    proj_db_cursor.execute("INSERT INTO vertical_crs SELECT ?, crs1.coord_ref_sys_code, crs1.coord_ref_sys_name, NULL, ?, crs1.coord_sys_code, ?, crs2.datum_code, crs1.deprecated FROM epsg.epsg_coordinatereferencesystem crs1 JOIN epsg.epsg_coordinatereferencesystem crs2 ON crs1.base_crs_code = crs2.coord_ref_sys_code WHERE crs1.coord_ref_sys_kind IN ('vertical') AND crs1.datum_code IS NULL AND crs2.datum_code iS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+
+    # Extra punishment for EPSG:8051 that is based on EPSG:5715 which is based on EPSG:5714
+    proj_db_cursor.execute("INSERT INTO vertical_crs SELECT ?, crs1.coord_ref_sys_code, crs1.coord_ref_sys_name, NULL, ?, crs1.coord_sys_code, ?, crs3.datum_code, crs1.deprecated FROM epsg.epsg_coordinatereferencesystem crs1 JOIN epsg.epsg_coordinatereferencesystem crs2 ON crs1.base_crs_code = crs2.coord_ref_sys_code JOIN epsg.epsg_coordinatereferencesystem crs3 ON crs2.base_crs_code = crs3.coord_ref_sys_code WHERE crs1.coord_ref_sys_kind IN ('vertical') AND crs1.datum_code IS NULL AND crs2.datum_code IS NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+
+
 def fill_conversion(proj_db_cursor):
 
     # TODO? current we deal with point motion operation as transformation in grid_transformation table