Update to EPSG 9.6.2 (#1462)

Update to EPSG 9.6.2

5 files changed, 168 insertions, 9 deletions

diff --git a/NEWS b/NEWS
index 8368b22a..3c82f8dc 100644
--- a/NEWS
+++ b/NEWS
@@ -14,7 +14,7 @@
 
  o Update internal version of googletest to v1.8.1 (#1361)
 
- o Database update: EPSG v9.6.1, IGNF v3.0.3, ESRI 10.7.0
+ o Database update: EPSG v9.6.2 (#1462), IGNF v3.0.3, ESRI 10.7.0
    and add operation_version column (#1368)
 
  o Add proj_normalize_for_visualization() that attempts to apply axis
diff --git a/data/sql/grid_transformation.sql b/data/sql/grid_transformation.sql
index 87279545..dd75c490 100644
--- a/data/sql/grid_transformation.sql
+++ b/data/sql/grid_transformation.sql
@@ -256,8 +256,8 @@ INSERT INTO "grid_transformation" VALUES('EPSG','8364','S-JTSK [JTSK03] to S-JTS
 INSERT INTO "grid_transformation" VALUES('EPSG','8369','BD72 to ETRS89 (3)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4313','EPSG','4258','EPSG','1347',0.01,'EPSG','8656','Latitude and longitude difference file','bd72lb72_etrs89lb08.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Bel 0.01m',0);
 INSERT INTO "grid_transformation" VALUES('EPSG','8371','RGF93 to NGF IGN69 height (2)',NULL,NULL,'EPSG','1073','Geographic3D to GravityRelatedHeight (IGN2009)','EPSG','4965','EPSG','5720','EPSG','1326',0.02,'EPSG','8666','Geoid (height correction) model file','RAF09.mnt',NULL,NULL,NULL,NULL,NULL,NULL,'IGN Fra 09',0);
 INSERT INTO "grid_transformation" VALUES('EPSG','8372','RGF93 to IGN78 Corsica height (2)',NULL,NULL,'EPSG','1073','Geographic3D to GravityRelatedHeight (IGN2009)','EPSG','4965','EPSG','5721','EPSG','1327',0.05,'EPSG','8666','Geoid (height correction) model file','RAC09.mnt',NULL,NULL,NULL,NULL,NULL,NULL,'IGN Fra Cor 09',0);
-INSERT INTO "grid_transformation" VALUES('EPSG','8444','GDA94 to GDA2020 (4)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','4169',0.05,'EPSG','8656','Latitude and longitude difference file','XMAS_C_V1.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cxr CI Conf',0);
-INSERT INTO "grid_transformation" VALUES('EPSG','8445','GDA94 to GDA2020 (5)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','1069',0.05,'EPSG','8656','Latitude and longitude difference file','COCOS_C_V1.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cki Conf',0);
+INSERT INTO "grid_transformation" VALUES('EPSG','8444','GDA94 to GDA2020 (4)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','4169',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_christmas_island.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cxr Conf',0);
+INSERT INTO "grid_transformation" VALUES('EPSG','8445','GDA94 to GDA2020 (5)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','1069',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_cocos_island.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cck Conf',0);
 INSERT INTO "grid_transformation" VALUES('EPSG','8446','GDA94 to GDA2020 (3)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','2575',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Aus NTv2 Conf',0);
 INSERT INTO "grid_transformation" VALUES('EPSG','8447','GDA94 to GDA2020 (2)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','2575',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_and_distortion.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Aus Conf and Dist',0);
 INSERT INTO "grid_transformation" VALUES('EPSG','8451','GDA2020 to AHD height (1)',NULL,NULL,'EPSG','1048','Geographic3D to GravityRelatedHeight (Ausgeoid v2)','EPSG','7843','EPSG','5711','EPSG','4493',0.03,'EPSG','8666','Geoid (height correction) model file','AUSGeoid2020_windows_binary.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'GA-Aus 2020',0);
diff --git a/data/sql/helmert_transformation.sql b/data/sql/helmert_transformation.sql
index a2aaeda1..86f759e9 100644
--- a/data/sql/helmert_transformation.sql
+++ b/data/sql/helmert_transformation.sql
@@ -1089,8 +1089,10 @@ INSERT INTO "helmert_transformation" VALUES('EPSG','8892','LKS94 to ETRS89 (1)',
 INSERT INTO "helmert_transformation" VALUES('EPSG','8893','SRB_ETRS89 to ETRS89 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','8685','EPSG','4258','EPSG','3534',0.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IOGP-Sbr',0);
 INSERT INTO "helmert_transformation" VALUES('EPSG','8894','SRB_ETRS89 to WGS 84 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','8685','EPSG','4326','EPSG','3534',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IOGP-Srb',0);
 INSERT INTO "helmert_transformation" VALUES('EPSG','8895','CHTRF95 to ETRS89 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4151','EPSG','4258','EPSG','1286',0.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IOGP-Che',0);
-INSERT INTO "helmert_transformation" VALUES('EPSG','8913','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','8906','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0);
-INSERT INTO "helmert_transformation" VALUES('EPSG','8914','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0);
+INSERT INTO "helmert_transformation" VALUES('EPSG','8913','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','8906','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',1);
+INSERT INTO "helmert_transformation" VALUES('EPSG','8914','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',1);
+INSERT INTO "helmert_transformation" VALUES('EPSG','8968','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5365','EPSG','8907','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0);
+INSERT INTO "helmert_transformation" VALUES('EPSG','8969','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5365','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0);
 INSERT INTO "helmert_transformation" VALUES('EPSG','10085','Trinidad 1903 to WGS 84 (2)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4302','EPSG','4326','EPSG','1339',3.0,-61.0,285.2,471.6,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'EOG-Tto Trin',0);
 INSERT INTO "helmert_transformation" VALUES('EPSG','10086','JAD69 to WGS 72 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4242','EPSG','4322','EPSG','3342',15.0,48.0,208.0,382.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SD-Jam',0);
 INSERT INTO "helmert_transformation" VALUES('EPSG','10089','Aratu to WGS 84 (5)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4208','EPSG','4326','EPSG','2962',7.0,-163.466,317.396,-147.538,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'EXM-Bra Santos',0);
diff --git a/data/sql/metadata.sql b/data/sql/metadata.sql
index 2d0151de..39250290 100644
--- a/data/sql/metadata.sql
+++ b/data/sql/metadata.sql
@@ -1,2 +1,2 @@
-INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v9.6.1');
-INSERT INTO "metadata" VALUES('EPSG.DATE', '2019-03-25');
+INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v9.6.2');
+INSERT INTO "metadata" VALUES('EPSG.DATE', '2019-05-08');
diff --git a/scripts/build_db.py b/scripts/build_db.py
index d10db663..26839669 100755
--- a/scripts/build_db.py
+++ b/scripts/build_db.py
@@ -134,7 +134,7 @@ def fill_conversion(proj_db_cursor):
     trigger_sql = """
 CREATE TRIGGER conversion_method_check_insert_trigger
 BEFORE INSERT ON conversion
-FOR EACH ROW BEGIN
+BEGIN
 """
 
     proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, area_of_use_code, coord_op_method_code, coord_op_method_name, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'conversion' AND coord_op_name NOT LIKE '%to DMSH'")
@@ -598,8 +598,165 @@ proj_db_cursor = proj_db_conn.cursor()
 proj_db_cursor.execute('PRAGMA foreign_keys = 1;')
 
 ingest_sqlite_dump(proj_db_cursor, os.path.join(sql_dir_name, 'proj_db_table_defs.sql'))
+
+# A bit messy, but to avoid churn in our existing .sql files, we temporarily
+# recreate the original conversion and helmert_transformation tables
+# instead of the view in the true database.
+
+proj_db_cursor.execute("""DROP VIEW conversion;""")
+proj_db_cursor.execute("""DROP TABLE conversion_table;""")
+proj_db_cursor.execute("""CREATE TABLE conversion(
+    auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
+    code TEXT NOT NULL CHECK (length(code) >= 1),
+    name TEXT NOT NULL CHECK (length(name) >= 2),
+
+    description TEXT,
+    scope TEXT,
+
+    area_of_use_auth_name TEXT NOT NULL,
+    area_of_use_code TEXT NOT NULL,
+
+    method_auth_name TEXT CHECK (method_auth_name IS NULL OR length(method_auth_name) >= 1),
+    method_code TEXT CHECK (method_code IS NULL OR length(method_code) >= 1),
+    method_name TEXT,
+
+    param1_auth_name TEXT,
+    param1_code TEXT,
+    param1_name TEXT,
+    param1_value FLOAT,
+    param1_uom_auth_name TEXT,
+    param1_uom_code TEXT,
+
+    param2_auth_name TEXT,
+    param2_code TEXT,
+    param2_name TEXT,
+    param2_value FLOAT,
+    param2_uom_auth_name TEXT,
+    param2_uom_code TEXT,
+
+    param3_auth_name TEXT,
+    param3_code TEXT,
+    param3_name TEXT,
+    param3_value FLOAT,
+    param3_uom_auth_name TEXT,
+    param3_uom_code TEXT,
+
+    param4_auth_name TEXT,
+    param4_code TEXT,
+    param4_name TEXT,
+    param4_value FLOAT,
+    param4_uom_auth_name TEXT,
+    param4_uom_code TEXT,
+
+    param5_auth_name TEXT,
+    param5_code TEXT,
+    param5_name TEXT,
+    param5_value FLOAT,
+    param5_uom_auth_name TEXT,
+    param5_uom_code TEXT,
+
+    param6_auth_name TEXT,
+    param6_code TEXT,
+    param6_name TEXT,
+    param6_value FLOAT,
+    param6_uom_auth_name TEXT,
+    param6_uom_code TEXT,
+
+    param7_auth_name TEXT,
+    param7_code TEXT,
+    param7_name TEXT,
+    param7_value FLOAT,
+    param7_uom_auth_name TEXT,
+    param7_uom_code TEXT,
+
+    deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
+
+    CONSTRAINT pk_conversion PRIMARY KEY (auth_name, code)
+);""")
+
+proj_db_cursor.execute("""DROP VIEW helmert_transformation;""")
+proj_db_cursor.execute("""DROP TABLE helmert_transformation_table;""")
+proj_db_cursor.execute("""CREATE TABLE helmert_transformation(
+    auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
+    code TEXT NOT NULL CHECK (length(code) >= 1),
+    name TEXT NOT NULL CHECK (length(name) >= 2),
+
+    description TEXT,
+    scope TEXT,
+
+    method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1),
+    method_code TEXT NOT NULL CHECK (length(method_code) >= 1),
+    method_name NOT NULL CHECK (length(method_name) >= 2),
+
+    source_crs_auth_name TEXT NOT NULL,
+    source_crs_code TEXT NOT NULL,
+    target_crs_auth_name TEXT NOT NULL,
+    target_crs_code TEXT NOT NULL,
+
+    area_of_use_auth_name TEXT NOT NULL,
+    area_of_use_code TEXT NOT NULL,
+
+    accuracy FLOAT CHECK (accuracy >= 0),
+
+    tx FLOAT NOT NULL,
+    ty FLOAT NOT NULL,
+    tz FLOAT NOT NULL,
+    translation_uom_auth_name TEXT NOT NULL,
+    translation_uom_code TEXT NOT NULL,
+    rx FLOAT,
+    ry FLOAT,
+    rz FLOAT,
+    rotation_uom_auth_name TEXT,
+    rotation_uom_code TEXT,
+    scale_difference FLOAT,
+    scale_difference_uom_auth_name TEXT,
+    scale_difference_uom_code TEXT,
+    rate_tx FLOAT,
+    rate_ty FLOAT,
+    rate_tz FLOAT,
+    rate_translation_uom_auth_name TEXT,
+    rate_translation_uom_code TEXT,
+    rate_rx FLOAT,
+    rate_ry FLOAT,
+    rate_rz FLOAT,
+    rate_rotation_uom_auth_name TEXT,
+    rate_rotation_uom_code TEXT,
+    rate_scale_difference FLOAT,
+    rate_scale_difference_uom_auth_name TEXT,
+    rate_scale_difference_uom_code TEXT,
+    epoch FLOAT,
+    epoch_uom_auth_name TEXT,
+    epoch_uom_code TEXT,
+    px FLOAT, -- Pivot / evaluation point for Molodensky-Badekas
+    py FLOAT,
+    pz FLOAT,
+    pivot_uom_auth_name TEXT,
+    pivot_uom_code TEXT,
+
+    operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here
+
+    deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
+
+    CONSTRAINT pk_helmert_transformation PRIMARY KEY (auth_name, code)
+);""")
+
+proj_db_cursor.execute("SELECT name, sql FROM sqlite_master WHERE type = 'table' AND name = 'projected_crs'")
+for (name, sql) in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("DROP TABLE " + name)
+    proj_db_cursor.execute(sql.replace('conversion_table', 'conversion'))
+
+proj_db_cursor.execute("SELECT name, sql FROM sqlite_master WHERE type = 'view'")
+for (name, sql) in proj_db_cursor.fetchall():
+    if 'conversion_table' in sql:
+        proj_db_cursor.execute("DROP VIEW " + name)
+        proj_db_cursor.execute(sql.replace('conversion_table', 'conversion'))
+    elif 'helmert_transformation_table' in sql:
+        proj_db_cursor.execute("DROP VIEW " + name)
+        proj_db_cursor.execute(sql.replace('helmert_transformation_table', 'helmert_transformation'))
+
 proj_db_cursor.execute("ATTACH DATABASE '%s' AS epsg;" % epsg_tmp_db_filename)
 
+
 fill_unit_of_measure(proj_db_cursor)
 fill_ellipsoid(proj_db_cursor)
 fill_area(proj_db_cursor)
@@ -647,7 +804,7 @@ for line in proj_db_conn.iterdump():
             f = open(os.path.join(sql_dir_name, table_name) + '.sql', 'wb')
             f.write("--- This file has been generated by scripts/build_db.py. DO NOT EDIT !\n\n".encode('UTF-8'))
             files[table_name] = f
-        f.write((line + '\n').encode('UTF-8'))
+        f.write((line + '\n').replace('BEFORE INSERT ON conversion', 'INSTEAD OF INSERT ON conversion').encode('UTF-8'))
 #f = files['coordinate_operation']
 #for row in non_imported_operations:
 #    f.write(("--- Non imported: " + str(row) + '\n').encode('UTF-8'))