Merge pull request #2238 from rouault/update_to_epsg_9_8_11

Update to EPSG 9.8.11, ESRI 10.8.1 and import scope and remarks for conversion

2 files changed, 53 insertions, 13 deletions

diff --git a/scripts/build_db.py b/scripts/build_db.py
index 5897a2d8..df730319 100755
--- a/scripts/build_db.py
+++ b/scripts/build_db.py
@@ -142,8 +142,8 @@ BEGIN
     # don't refer to particular CRS, and instances pointing to CRS names
     # The later are imported in the other_transformation table since we recover
     # the source/target CRS names from the transformation name.
-    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' AND (coord_op_method_code NOT IN (1068, 1069) OR coord_op_code IN (7812,7813))")
-    for (code, name, area_of_use_code, method_code, method_name, deprecated) in proj_db_cursor.fetchall():
+    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, coord_op_scope, epsg_coordoperation.remarks 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' AND (coord_op_method_code NOT IN (1068, 1069) OR coord_op_code IN (7812,7813))")
+    for (code, name, area_of_use_code, method_code, method_name, deprecated, scope, remarks) in proj_db_cursor.fetchall():
         expected_order = 1
         max_n_params = 7
         param_auth_name = [None for i in range(max_n_params)]
@@ -190,7 +190,7 @@ BEGIN
             trigger_sql += ");\n"
 
         arg = (EPSG_AUTHORITY, code, name,
-               None, None, # description + scope
+               remarks, scope,
                EPSG_AUTHORITY, area_of_use_code,
                EPSG_AUTHORITY, method_code, method_name,
                param_auth_name[0], param_code[0], param_name[0],
@@ -619,9 +619,16 @@ def fill_supersession(proj_db_cursor):
         assert superseded_table_name, row
         assert replacement_table_name, row
         if superseded_table_name == 'grid_transformation' and replacement_table_name == 'grid_transformation' and src_name.startswith('NAD27 to NAD83'):
-            print('Skipping supersession of %d (%s) by %d (%s)' % (code, src_name, superseded_by, dst_name))
+            print('Skipping supersession of %d (%s) by %d (%s) because of exception specific to NAD27 to NAD83' % (code, src_name, superseded_by, dst_name))
             continue
-        proj_db_cursor.execute("INSERT INTO supersession VALUES (?,'EPSG',?,?,'EPSG',?,'EPSG')", (superseded_table_name, code, replacement_table_name, superseded_by))
+
+        proj_db_cursor.execute("SELECT source_crs_code, target_crs_code FROM epsg_coordoperation WHERE coord_op_code = ?", (code,))
+        source_crs_code_superseded, target_crs_code_superseded = proj_db_cursor.fetchone()
+
+        proj_db_cursor.execute("SELECT source_crs_code, target_crs_code FROM epsg_coordoperation WHERE coord_op_code = ?", (superseded_by,))
+        source_crs_code_replacement, target_crs_code_replacement = proj_db_cursor.fetchone()
+        same_source_target_crs = (source_crs_code_superseded, target_crs_code_superseded) == (source_crs_code_replacement, target_crs_code_replacement)
+        proj_db_cursor.execute("INSERT INTO supersession VALUES (?,'EPSG',?,?,'EPSG',?,'EPSG',?)", (superseded_table_name, code, replacement_table_name, superseded_by, same_source_target_crs))
 
 def fill_deprecation(proj_db_cursor):
     proj_db_cursor.execute("SELECT object_code, replaced_by FROM epsg.epsg_deprecation WHERE object_table_name = 'epsg_coordinatereferencesystem' AND object_code != replaced_by")
diff --git a/scripts/build_db_from_esri.py b/scripts/build_db_from_esri.py
index de70b638..482c968b 100755
--- a/scripts/build_db_from_esri.py
+++ b/scripts/build_db_from_esri.py
@@ -51,10 +51,10 @@ cursor = conn.cursor()
 all_sql = []
 
 # TODO: update this !
-version = 'ArcMap 10.8.0'
+version = 'ArcMap 10.8.1'
 all_sql.append(
     """INSERT INTO "metadata" VALUES('ESRI.VERSION', '%s');""" % (version))
-date = '2019-12-01'
+date = '2020-05-24'
 all_sql.append(
     """INSERT INTO "metadata" VALUES('ESRI.DATE', '%s');""" % (date))
 
@@ -638,7 +638,7 @@ def import_geogcs():
                     src_row = cursor.fetchone()
                     assert src_row, (code, latestWkid)
 
-                    sql = """INSERT INTO "supersession" VALUES('geodetic_crs','ESRI','%s','geodetic_crs','EPSG','%s','ESRI');""" % (
+                    sql = """INSERT INTO "supersession" VALUES('geodetic_crs','ESRI','%s','geodetic_crs','EPSG','%s','ESRI',1);""" % (
                         code, latestWkid)
                     all_sql.append(sql)
 
@@ -1017,7 +1017,7 @@ def import_projcs():
             latestWkid = mapDeprecatedToNonDeprecated[deprecated]
 
             if latestWkid in wkid_set:
-                sql = """INSERT INTO "supersession" VALUES('projected_crs','ESRI','%s','projected_crs','ESRI','%s','ESRI');""" % (
+                sql = """INSERT INTO "supersession" VALUES('projected_crs','ESRI','%s','projected_crs','ESRI','%s','ESRI',1);""" % (
                     code, latestWkid)
                 all_sql.append(sql)
             else:
@@ -1025,7 +1025,7 @@ def import_projcs():
                     "SELECT name FROM projected_crs WHERE auth_name = 'EPSG' AND code = ?", (latestWkid,))
                 src_row = cursor.fetchone()
                 assert src_row, row
-                sql = """INSERT INTO "supersession" VALUES('projected_crs','ESRI','%s','projected_crs','EPSG','%s','ESRI');""" % (
+                sql = """INSERT INTO "supersession" VALUES('projected_crs','ESRI','%s','projected_crs','EPSG','%s','ESRI',1);""" % (
                     code, latestWkid)
                 all_sql.append(sql)
 
@@ -1251,7 +1251,7 @@ def import_vertcs():
                     src_row = cursor.fetchone()
                     assert src_row
 
-                    sql = """INSERT INTO "supersession" VALUES('vertical_crs','ESRI','%s','vertical_crs','EPSG','%s','ESRI');""" % (
+                    sql = """INSERT INTO "supersession" VALUES('vertical_crs','ESRI','%s','vertical_crs','EPSG','%s','ESRI',1);""" % (
                         code, latestWkid)
                     all_sql.append(sql)
 
@@ -1476,13 +1476,16 @@ def import_geogtran():
                 is_geocon = 'METHOD["GEOCON"]' in wkt
                 is_harn = 'METHOD["HARN"]' in wkt
                 is_molodensky_badekas = 'METHOD["Molodensky_Badekas"]' in wkt
-                assert is_cf or is_pv or is_geocentric_translation or is_molodensky_badekas or is_nadcon or is_geog2d_offset or is_ntv2 or is_geocon or is_null or is_harn or is_unitchange, (
-                    row)
+                is_Time_Based_Helmert_Position_Vector = 'METHOD["Time_Based_Helmert_Position_Vector"]' in wkt
+                is_Time_Based_Helmert_Coordinate_Frame = 'METHOD["Time_Based_Helmert_Coordinate_Frame"]' in wkt
+                assert is_cf or is_pv or is_geocentric_translation or is_molodensky_badekas or is_nadcon or is_geog2d_offset or is_ntv2 or is_geocon or is_null or is_harn or is_unitchange or is_Time_Based_Helmert_Position_Vector or is_Time_Based_Helmert_Coordinate_Frame, row
 
                 area_auth_name, area_code = find_area(
                     row[idx_areaname], row[idx_slat], row[idx_nlat], row[idx_llon], row[idx_rlon])
 
                 accuracy = row[idx_accuracy]
+                if float(accuracy) == 999:
+                    accuracy = 'NULL'
 
                 if is_cf or is_pv:
                     x = get_parameter(wkt, 'X_Axis_Translation')
@@ -1540,6 +1543,36 @@ def import_geogtran():
                         wkid, esri_name, method_code, method_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code, accuracy, x, y, z, deprecated)
                     all_sql.append(sql)
 
+                elif is_Time_Based_Helmert_Position_Vector or is_Time_Based_Helmert_Coordinate_Frame:
+
+                    x = get_parameter(wkt, 'X_Axis_Translation')
+                    y = get_parameter(wkt, 'Y_Axis_Translation')
+                    z = get_parameter(wkt, 'Z_Axis_Translation')
+                    rx = get_parameter(wkt, 'X_Axis_Rotation')  # in arc second
+                    ry = get_parameter(wkt, 'Y_Axis_Rotation')
+                    rz = get_parameter(wkt, 'Z_Axis_Rotation')
+                    s = get_parameter(wkt, 'Scale_Difference')  # in ppm
+                    rate_x = get_parameter(wkt, 'X_Axis_Translation_Rate')
+                    rate_y = get_parameter(wkt, 'Y_Axis_Translation_Rate')
+                    rate_z = get_parameter(wkt, 'Z_Axis_Translation_Rate')
+                    rate_rx = get_parameter(wkt, 'X_Axis_Rotation_Rate')  # in arc second / year
+                    rate_ry = get_parameter(wkt, 'Y_Axis_Rotation_Rate')
+                    rate_rz = get_parameter(wkt, 'Z_Axis_Rotation_Rate')
+                    rate_s = get_parameter(wkt, 'Scale_Difference_Rate')  # in ppm / year
+                    reference_time = get_parameter(wkt, 'Reference_Time')
+                    assert wkt.count('PARAMETER[') == 15
+
+                    if is_Time_Based_Helmert_Coordinate_Frame:
+                        method_code = '1057'
+                        method_name = 'Time-dependent Coordinate Frame rotation (geog2D)'
+                    else:
+                        method_code = '1054'
+                        method_name = 'Time-dependent Position Vector tfm (geog2D)'
+
+                    sql = """INSERT INTO "helmert_transformation" VALUES('ESRI','%s','%s',NULL,NULL,'EPSG','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s,%s,%s,'EPSG','9001',%s,%s,%s,'EPSG','9104',%s,'EPSG','9202',%s,%s,%s,'EPSG','1042',%s,%s,%s,'EPSG','1043',%s,'EPSG','1041',%s,'EPSG','1029',NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % (
+                        wkid, esri_name, method_code, method_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code, accuracy, x, y, z, rx, ry, rz, s, rate_x, rate_y, rate_z, rate_rx, rate_ry, rate_rz, rate_s, reference_time, deprecated)
+                    all_sql.append(sql)
+
                 elif is_geog2d_offset:
 
                     # The only occurence is quite boring: from NTF(Paris) to NTF.