From 4d9fa77bef599353d3809541cb7e6adb8ec12525 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sun, 24 May 2020 19:52:46 +0200 Subject: Database: add a same_source_target_crs column to supersession table This is in preparation for EPSG 9.8.11 import that supersedes a number of grid transformation for US transformations, but the superseded and replacement transformations don't operate on the same (source_crs, target_crs), which is a bit weird. So in that situation, ignores the supersession. --- scripts/build_db.py | 11 +++++++++-- scripts/build_db_from_esri.py | 8 ++++---- 2 files changed, 13 insertions(+), 6 deletions(-) (limited to 'scripts') diff --git a/scripts/build_db.py b/scripts/build_db.py index 5897a2d8..dbe40c08 100755 --- a/scripts/build_db.py +++ b/scripts/build_db.py @@ -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..01ecf3cd 100755 --- a/scripts/build_db_from_esri.py +++ b/scripts/build_db_from_esri.py @@ -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) -- cgit v1.2.3 From c2654aaf0fc5192b141a5a34f60c226ff45b375c Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sun, 24 May 2020 20:50:40 +0200 Subject: Database: update ESRI content to ArcMap 10.8.1 --- scripts/build_db_from_esri.py | 41 +++++++++++++++++++++++++++++++++++++---- 1 file changed, 37 insertions(+), 4 deletions(-) (limited to 'scripts') diff --git a/scripts/build_db_from_esri.py b/scripts/build_db_from_esri.py index 01ecf3cd..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)) @@ -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. -- cgit v1.2.3 From bf50f76e1d1e45d65b79229c060fe66df8ef289c Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sun, 24 May 2020 21:35:13 +0200 Subject: Database: import scope and remarks for conversion (fixes #2237) --- scripts/build_db.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'scripts') diff --git a/scripts/build_db.py b/scripts/build_db.py index dbe40c08..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], -- cgit v1.2.3