From f6abb7d75c647013dd83a82361b96c202e615d90 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Thu, 22 Apr 2021 22:51:31 +0200 Subject: 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. --- scripts/build_db.py | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'scripts/build_db.py') 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 -- cgit v1.2.3