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.

2 files changed, 13 insertions, 6 deletions

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)