Database: "minimal" update to EPSG v10.003

Content mostly unchanged since v9.9

This update is "minimal" in that it mostly reflects the removal of the 'area'
table, replaced now by 'extent', 'scope' and 'usage'

Other new aspects of EPSG v10 are left aside.

1 files changed, 157 insertions, 57 deletions

diff --git a/scripts/build_db.py b/scripts/build_db.py
index 97d68f34..0c8f97be 100755
--- a/scripts/build_db.py
+++ b/scripts/build_db.py
@@ -30,17 +30,31 @@
 
 import os
 import sqlite3
+import sys
 
 EPSG_AUTHORITY = 'EPSG'
 
 def ingest_sqlite_dump(cursor, filename):
     sql = ''
-    for line in open(filename, 'rt').readlines():
+    f = open(filename, 'rb')
+    # Skip UTF-8 BOM
+    if f.read(3) != b'\xEF\xBB\xBF':
+        f.seek(0, os.SEEK_SET)
+    for line in f.readlines():
+        line = line.replace(b'\r\n', '\n')
+        if sys.version_info >= (3, 0, 0):
+            line = line.decode('utf-8') #python3
+        else:
+            line = str(line) # python2
         sql += line
         if sqlite3.complete_statement(sql):
             sql = sql.strip()
             if sql != 'COMMIT;':
-                cursor.execute(sql)
+                try:
+                    cursor.execute(sql)
+                except:
+                    print(sql)
+                    raise
             sql = ''
 
 
@@ -76,9 +90,40 @@ def fill_ellipsoid(proj_db_cursor):
         "INSERT INTO ellipsoid SELECT ?, ellipsoid_code, ellipsoid_name, NULL, 'PROJ', 'EARTH', semi_major_axis, ?, uom_code, inv_flattening, semi_minor_axis, deprecated FROM epsg.epsg_ellipsoid", (EPSG_AUTHORITY, EPSG_AUTHORITY))
 
 
-def fill_area(proj_db_cursor):
+def fill_extent(proj_db_cursor):
+    proj_db_cursor.execute(
+        "INSERT INTO extent SELECT ?, extent_code, extent_name, extent_description, bbox_south_bound_lat, bbox_north_bound_lat, bbox_west_bound_lon, bbox_east_bound_lon, deprecated FROM epsg.epsg_extent", (EPSG_AUTHORITY,))
+
+
+def fill_scope(proj_db_cursor):
     proj_db_cursor.execute(
-        "INSERT INTO area SELECT ?, area_code, area_name, area_of_use, area_south_bound_lat, area_north_bound_lat, area_west_bound_lon, area_east_bound_lon, deprecated FROM epsg.epsg_area", (EPSG_AUTHORITY,))
+        "INSERT INTO scope SELECT ?, scope_code, scope, deprecated FROM epsg.epsg_scope", (EPSG_AUTHORITY,))
+
+
+def fill_usage(proj_db_cursor):
+    proj_db_cursor.execute(
+        "SELECT usage_code, object_table_name, object_code, extent_code, scope_code FROM epsg.epsg_usage")
+    res = proj_db_cursor.fetchall()
+    for (usage_code, object_table_name, object_code, extent_code, scope_code) in res:
+        if object_table_name == 'epsg_coordinatereferencesystem':
+            proj_db_cursor.execute('SELECT table_name FROM crs_view WHERE auth_name = ? AND code = ?', (EPSG_AUTHORITY, object_code))
+            proj_table_name = proj_db_cursor.fetchone()
+            if proj_table_name is None:
+                continue
+        elif object_table_name == 'epsg_coordoperation':
+            proj_db_cursor.execute("SELECT table_name FROM coordinate_operation_view WHERE auth_name = ? AND code = ? UNION ALL SELECT 'conversion' FROM conversion WHERE auth_name = ? AND code = ?", (EPSG_AUTHORITY, object_code, EPSG_AUTHORITY, object_code))
+            proj_table_name = proj_db_cursor.fetchone()
+            if proj_table_name is None:
+                continue
+        elif object_table_name == 'epsg_datum':
+            proj_db_cursor.execute("SELECT 'geodetic_datum' FROM geodetic_datum WHERE auth_name = ? AND code = ? UNION ALL SELECT 'vertical_datum' FROM vertical_datum WHERE auth_name = ? AND code = ?", (EPSG_AUTHORITY, object_code, EPSG_AUTHORITY, object_code))
+            proj_table_name = proj_db_cursor.fetchone()
+            if proj_table_name is None:
+                continue
+
+        proj_table_name = proj_table_name[0]
+        proj_db_cursor.execute(
+            "INSERT INTO usage VALUES (?,?,?,?,?,?,?,?,?)", (EPSG_AUTHORITY, usage_code, proj_table_name, EPSG_AUTHORITY, object_code, EPSG_AUTHORITY, extent_code, EPSG_AUTHORITY, scope_code))
 
 
 def fill_prime_meridian(proj_db_cursor):
@@ -86,20 +131,74 @@ def fill_prime_meridian(proj_db_cursor):
         "INSERT INTO prime_meridian SELECT ?, prime_meridian_code, prime_meridian_name, greenwich_longitude, ?, uom_code, deprecated FROM epsg.epsg_primemeridian", (EPSG_AUTHORITY, EPSG_AUTHORITY))
 
 
+def compute_publication_date(datum_code, datum_name, frame_reference_epoch, publication_date):
+    if frame_reference_epoch is not None:
+        epoch = float(frame_reference_epoch)
+        fractional = epoch - int(epoch)
+        if fractional == 0:
+            publication_date = '%04d-01-01' % int(epoch)
+        elif abs(fractional - 0.4) < 1e-6:
+            publication_date = '%04d-05-01' % int(epoch)
+        elif abs(fractional - 0.5) < 1e-6:
+            publication_date = '%04d-07-01' % int(epoch)
+        else:
+            assert False, (datum_code, datum_name, frame_reference_epoch, fractional)
+    elif publication_date != '':
+        if len(publication_date) == 4:
+            publication_date += '-01-01'
+        elif len(publication_date) == 7:
+            publication_date += '-01'
+        else:
+            assert len(publication_date) == 10, (datum_code, datum_name, publication_date)
+    else:
+        publication_date = None
+    return publication_date
+
+
 def fill_geodetic_datum(proj_db_cursor):
     proj_db_cursor.execute(
-        "SELECT DISTINCT * FROM epsg.epsg_datum WHERE datum_type NOT IN ('geodetic', 'vertical', 'engineering')")
+        "SELECT DISTINCT * FROM epsg.epsg_datum WHERE datum_type NOT IN ('geodetic', 'dynamic geodetic', 'ensemble', 'vertical', 'engineering')")
     res = proj_db_cursor.fetchall()
     if res:
         raise Exception('Found unexpected datum_type in epsg_datum: %s' % str(res))
 
-    proj_db_cursor.execute(
-        "INSERT INTO geodetic_datum SELECT ?, datum_code, datum_name, NULL, NULL, ?, ellipsoid_code, ?, prime_meridian_code, ?, area_of_use_code, CASE WHEN realization_epoch = '' THEN NULL ELSE realization_epoch END, deprecated FROM epsg.epsg_datum WHERE datum_type = 'geodetic'", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+    proj_db_cursor.execute("SELECT datum_code, datum_name, ellipsoid_code, prime_meridian_code, publication_date, frame_reference_epoch, deprecated FROM epsg.epsg_datum WHERE datum_type IN ('geodetic', 'dynamic geodetic')")
+    res = proj_db_cursor.fetchall()
+    for (datum_code, datum_name, ellipsoid_code, prime_meridian_code, publication_date, frame_reference_epoch, deprecated) in res:
+        publication_date = compute_publication_date(datum_code, datum_name, frame_reference_epoch, publication_date)
+        proj_db_cursor.execute(
+        "INSERT INTO geodetic_datum VALUES (?, ?, ?, NULL, ?, ?, ?, ?, ?, ?)", (EPSG_AUTHORITY, datum_code, datum_name, EPSG_AUTHORITY, ellipsoid_code, EPSG_AUTHORITY, prime_meridian_code, publication_date, deprecated))
 
 
 def fill_vertical_datum(proj_db_cursor):
-    proj_db_cursor.execute(
-        "INSERT INTO vertical_datum SELECT ?, datum_code, datum_name, NULL, NULL, ?, area_of_use_code, CASE WHEN realization_epoch = '' THEN NULL ELSE realization_epoch END, deprecated FROM epsg.epsg_datum WHERE datum_type = 'vertical'", (EPSG_AUTHORITY,EPSG_AUTHORITY))
+
+    proj_db_cursor.execute("SELECT datum_code, datum_name, publication_date, frame_reference_epoch, deprecated FROM epsg.epsg_datum WHERE datum_type IN ('vertical')")
+    res = proj_db_cursor.fetchall()
+    for (datum_code, datum_name, publication_date, frame_reference_epoch, deprecated) in res:
+        publication_date = compute_publication_date(datum_code, datum_name, frame_reference_epoch, publication_date)
+        proj_db_cursor.execute(
+        "INSERT INTO vertical_datum VALUES (?, ?, ?, NULL, ?, ?)", (EPSG_AUTHORITY, datum_code, datum_name,publication_date, deprecated))
+
+
+def fill_datumensemble(proj_db_cursor):
+
+    proj_db_cursor.execute("SELECT datum_code, datum_name, deprecated FROM epsg.epsg_datum WHERE datum_type = 'ensemble'")
+    rows = proj_db_cursor.fetchall()
+    for (datum_code, datum_name, deprecated) in rows:
+        proj_db_cursor.execute("SELECT DISTINCT datum_type, ellipsoid_code, prime_meridian_code FROM epsg.epsg_datum WHERE datum_code IN (SELECT datum_code FROM epsg.epsg_datumensemblemember WHERE datum_ensemble_code = ?)", (datum_code,))
+        subrows = proj_db_cursor.fetchall()
+        assert len(subrows) == 1, datum_code
+        datum_type = subrows[0][0]
+        if datum_type == 'vertical':
+            proj_db_cursor.execute("INSERT INTO vertical_datum (auth_name, code, name, description, publication_date, deprecated) VALUES (?, ?, ?, ?, ?, ?)", (EPSG_AUTHORITY, datum_code, datum_name, None, None, deprecated))
+        else:
+            assert datum_type in ('dynamic geodetic', 'geodetic'), datum_code
+            ellipsoid_code = subrows[0][1]
+            prime_meridian_code = subrows[0][2]
+            assert ellipsoid_code, datum_code
+            assert prime_meridian_code, datum_code
+            proj_db_cursor.execute(
+            "INSERT INTO geodetic_datum (auth_name, code, name, description, ellipsoid_auth_name, ellipsoid_code, prime_meridian_auth_name, prime_meridian_code, publication_date , deprecated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (EPSG_AUTHORITY, datum_code, datum_name, None, EPSG_AUTHORITY, ellipsoid_code, EPSG_AUTHORITY, prime_meridian_code, None, deprecated))
 
 
 def fill_coordinate_system(proj_db_cursor):
@@ -108,28 +207,36 @@ def fill_coordinate_system(proj_db_cursor):
 
 
 def fill_axis(proj_db_cursor):
-    proj_db_cursor.execute("INSERT INTO axis SELECT ?, coord_axis_code, coord_axis_name, coord_axis_abbreviation, coord_axis_orientation, ?, coord_sys_code, coord_axis_order, ?, uom_code FROM epsg.epsg_coordinateaxis ca LEFT JOIN epsg.epsg_coordinateaxisname can ON ca.coord_axis_name_code = can.coord_axis_name_code", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+    proj_db_cursor.execute("INSERT INTO axis SELECT ?, coord_axis_code, coord_axis_name, coord_axis_abbreviation, coord_axis_orientation, ?, coord_sys_code, coord_axis_order, CASE WHEN uom_code IS NULL THEN NULL ELSE ? END, uom_code FROM epsg.epsg_coordinateaxis ca LEFT JOIN epsg.epsg_coordinateaxisname can ON ca.coord_axis_name_code = can.coord_axis_name_code", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
 
 
 def fill_geodetic_crs(proj_db_cursor):
+    # TODO?: address 'derived'
     proj_db_cursor.execute(
-        "SELECT DISTINCT * FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind NOT IN ('projected', 'geographic 2D', 'geographic 3D', 'geocentric', 'vertical', 'compound', 'engineering')")
+        "SELECT DISTINCT * FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind NOT IN ('projected', 'geographic 2D', 'geographic 3D', 'geocentric', 'vertical', 'compound', 'engineering', 'derived')")
     res = proj_db_cursor.fetchall()
     if res:
         raise Exception('Found unexpected coord_ref_sys_kind in epsg_coordinatereferencesystem: %s' % str(res))
 
     #proj_db_cursor.execute(
     #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric') AND datum_code IS NOT NULL", (EPSG_AUTHORITY,))
-    proj_db_cursor.execute("INSERT INTO geodetic_crs SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, coord_ref_sys_kind, ?, coord_sys_code, ?, datum_code, ?, area_of_use_code, NULL, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric') AND datum_code IS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+    proj_db_cursor.execute("INSERT INTO geodetic_crs SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, coord_ref_sys_kind, ?, coord_sys_code, ?, datum_code, NULL, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric') AND datum_code IS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
 
 
 def fill_vertical_crs(proj_db_cursor):
     #proj_db_cursor.execute(
     #    "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, NULL, ?, coord_sys_code, ?, datum_code, ?, area_of_use_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, 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))
 
 def fill_conversion(proj_db_cursor):
 
+    # TODO? current we deal with point motion operation as transformation in grid_transformation table
+    proj_db_cursor.execute(
+        "SELECT DISTINCT * FROM epsg.epsg_coordoperation WHERE coord_op_type NOT IN ('conversion', 'transformation', 'concatenated operation', 'point motion operation')")
+    res = proj_db_cursor.fetchall()
+    if res:
+        raise Exception('Found unexpected coord_op_type in epsg_coordoperation: %s' % str(res))
+
     already_mapped_methods = set()
     trigger_sql = """
 CREATE TRIGGER conversion_method_check_insert_trigger
@@ -142,8 +249,10 @@ 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, 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():
+    # Method EPSG:9666 'P6 I=J+90 seismic bin grid coordinate operation' requires more than 7 parameters. Not supported by PROJ for now
+    # Idem for EPSG:1049 'P6 I=J-90 seismic bin grid coordinate operation'
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, epsg_coordoperation.deprecated, 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, 9666, 1049) OR coord_op_code IN (7812,7813))")
+    for (code, name, method_code, method_name, deprecated, remarks) in proj_db_cursor.fetchall():
         expected_order = 1
         max_n_params = 7
         param_auth_name = [None for i in range(max_n_params)]
@@ -159,7 +268,7 @@ BEGIN
             # Modified Krovak and Krovak North Oriented: keep only the 7 first parameters
             if order == max_n_params + 1 and method_code in (1042, 1043):
                 break
-            assert order <= max_n_params
+            assert order <= max_n_params, (method_code, method_name, order)
             assert order == expected_order
             param_auth_name[order - 1] = EPSG_AUTHORITY
             param_code[order - 1] = parameter_code
@@ -190,8 +299,7 @@ BEGIN
             trigger_sql += ");\n"
 
         arg = (EPSG_AUTHORITY, code, name,
-               remarks, scope,
-               EPSG_AUTHORITY, area_of_use_code,
+               remarks,
                EPSG_AUTHORITY, method_code, method_name,
                param_auth_name[0], param_code[0], param_name[0],
                param_value[0], param_uom_auth_name[0], param_uom_code[0],
@@ -211,7 +319,7 @@ BEGIN
 
         #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'conversion')", (EPSG_AUTHORITY, code))
         proj_db_cursor.execute('INSERT INTO conversion VALUES (' +
-            '?,?,?, ?,?, ?,?, ?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
+            '?,?,?, ?, ?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
             '?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?)', arg)
 
     trigger_sql += "END;";
@@ -222,31 +330,31 @@ BEGIN
 def fill_projected_crs(proj_db_cursor):
     #proj_db_cursor.execute(
     #    "INSERT INTO crs SELECT 'EPSG', coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')")
-    #proj_db_cursor.execute("INSERT INTO projected_crs SELECT 'EPSG', coord_ref_sys_code, coord_ref_sys_name, 'EPSG', coord_sys_code, 'EPSG', source_geogcrs_code, 'EPSG', projection_conv_code, 'EPSG', area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')")
-    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, ?, coord_sys_code, ?, source_geogcrs_code, ?, projection_conv_code, ?, area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+    #proj_db_cursor.execute("INSERT INTO projected_crs SELECT 'EPSG', coord_ref_sys_code, coord_ref_sys_name, 'EPSG', coord_sys_code, 'EPSG', base_crs_code, 'EPSG', projection_conv_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')")
+    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, ?, coord_sys_code, ?, base_crs_code, ?, projection_conv_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
     for row in proj_db_cursor.fetchall():
-        (auth_name, code, name, description, scope, coordinate_system_auth_name, coordinate_system_code, geodetic_crs_auth_name, geodetic_crs_code, conversion_auth_name, conversion_code, area_of_use_auth_name, area_of_use_code, deprecated) = row
+        (auth_name, code, name, description, coordinate_system_auth_name, coordinate_system_code, geodetic_crs_auth_name, geodetic_crs_code, conversion_auth_name, conversion_code, deprecated) = row
         proj_db_cursor.execute("SELECT 1 FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_code = ? AND coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric')", (geodetic_crs_code,))
         if proj_db_cursor.fetchone():
             #proj_db_cursor.execute("INSERT INTO crs VALUES (?, ?, 'projected')", (EPSG_AUTHORITY, code))
-            proj_db_cursor.execute("INSERT INTO projected_crs VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)", row)
+            proj_db_cursor.execute("INSERT INTO projected_crs VALUES (?,?,?,?,?,?,?,?,?,?,NULL,?)", row)
 
 def fill_compound_crs(proj_db_cursor):
     #proj_db_cursor.execute(
     #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('compound')", (EPSG_AUTHORITY,))
 
-    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, ?, cmpd_horizcrs_code, ?, cmpd_vertcrs_code, ?, area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('compound') AND cmpd_horizcrs_code NOT IN (SELECT coord_ref_sys_code FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind = 'engineering')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
-    for auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, ?, cmpd_horizcrs_code, ?, cmpd_vertcrs_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('compound') AND cmpd_horizcrs_code NOT IN (SELECT coord_ref_sys_code FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind = 'engineering')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
+    for auth_name, code, name, description, horiz_auth_name, horiz_code, vert_auth_name, vert_code, deprecated in proj_db_cursor.fetchall():
         try:
-            proj_db_cursor.execute("INSERT INTO compound_crs VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated))
+            proj_db_cursor.execute("INSERT INTO compound_crs VALUES (?,?,?,?,?,?,?,?,?)", (auth_name, code, name, description, horiz_auth_name, horiz_code, vert_auth_name, vert_code, deprecated))
         except sqlite3.IntegrityError as e:
             print(e)
-            print(auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated)
+            print(auth_name, code, name, description, horiz_auth_name, horiz_code, vert_auth_name, vert_code, deprecated)
             raise
 
 def fill_helmert_transformation(proj_db_cursor):
-    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, 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 = 'transformation' AND coord_op_method_code IN (1031, 1032, 1033, 1034, 1035, 1037, 1038, 1039, 1053, 1054, 1055, 1056, 1057, 1058, 1061, 1062, 1063, 1065, 1066, 9603, 9606, 9607, 9636) ")
-    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, deprecated, scope, remarks) in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, epsg_coordoperation.deprecated, epsg_coordoperation.remarks FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'transformation' AND coord_op_method_code IN (1031, 1032, 1033, 1034, 1035, 1037, 1038, 1039, 1053, 1054, 1055, 1056, 1057, 1058, 1061, 1062, 1063, 1065, 1066, 9603, 9606, 9607, 9636) ")
+    for (code, name, method_code, method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, deprecated, remarks) in proj_db_cursor.fetchall():
         expected_order = 1
         max_n_params = 15
         param_auth_name = [None for i in range(max_n_params)]
@@ -325,11 +433,10 @@ def fill_helmert_transformation(proj_db_cursor):
             assert param_uom_code[10] == param_uom_code[12]
 
         arg = (EPSG_AUTHORITY, code, name,
-               remarks, scope,
+               remarks,
                EPSG_AUTHORITY, method_code, method_name,
                EPSG_AUTHORITY, source_crs_code,
                EPSG_AUTHORITY, target_crs_code,
-               EPSG_AUTHORITY, area_of_use_code,
                coord_op_accuracy,
                param_value[0], param_value[1], param_value[2], EPSG_AUTHORITY, param_uom_code[0],
                param_value[3], param_value[4], param_value[5], EPSG_AUTHORITY if param_uom_code[3] else None, param_uom_code[3],
@@ -345,11 +452,11 @@ def fill_helmert_transformation(proj_db_cursor):
 
         #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'helmert_transformation')", (EPSG_AUTHORITY, code))
         proj_db_cursor.execute('INSERT INTO helmert_transformation VALUES (' +
-            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?, ?,?)', arg)
+            '?,?,?, ?, ?,?,?, ?,?, ?,?, ?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?, ?,?)', arg)
 
 def fill_grid_transformation(proj_db_cursor):
-    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, 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 = 'transformation' AND (coord_op_method_name LIKE 'Geographic3D to%' OR coord_op_method_name LIKE 'Geog3D to%' OR coord_op_method_name LIKE 'Point motion by grid%' OR coord_op_method_name LIKE 'Vertical Offset by Grid Interpolation%' OR coord_op_method_name IN ('NADCON', 'NADCON5 (2D)', 'NTv1', 'NTv2', 'VERTCON', 'Geocentric translation by Grid Interpolation (IGN)'))")
-    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, deprecated, scope, remarks) in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, epsg_coordoperation.deprecated, epsg_coordoperation.remarks FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type IN ('transformation', 'point motion operation') AND (coord_op_method_name LIKE 'Geographic3D to%' OR coord_op_method_name LIKE 'Geog3D to%' OR coord_op_method_name LIKE 'Point motion by grid%' OR coord_op_method_name LIKE 'Vertical Offset by Grid Interpolation%' OR coord_op_method_name IN ('NADCON', 'NADCON5 (2D)', 'NTv1', 'NTv2', 'VERTCON', 'Geocentric translation by Grid Interpolation (IGN)'))")
+    for (code, name, method_code, method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, deprecated, remarks) in proj_db_cursor.fetchall():
         expected_order = 1
         max_n_params = 3 if method_name == 'Geocentric translation by Grid Interpolation (IGN)' else 2
         param_auth_name = [None for i in range(max_n_params)]
@@ -416,11 +523,10 @@ def fill_grid_transformation(proj_db_cursor):
 
 
         arg = (EPSG_AUTHORITY, code, name,
-               remarks, scope,
+               remarks,
                EPSG_AUTHORITY, method_code, method_name,
                EPSG_AUTHORITY, source_crs_code,
                EPSG_AUTHORITY, target_crs_code,
-               EPSG_AUTHORITY, area_of_use_code,
                coord_op_accuracy,
                EPSG_AUTHORITY, param_code[0], param_name[0], param_value[0],
                grid2_param_auth_name, grid2_param_code, grid2_param_name, grid2_value,
@@ -431,7 +537,7 @@ def fill_grid_transformation(proj_db_cursor):
 
         #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'grid_transformation')", (EPSG_AUTHORITY, code))
         proj_db_cursor.execute('INSERT INTO grid_transformation VALUES (' +
-            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?, ?,?,?,?, ?,?, ?,?)', arg)
+            '?,?,?, ?, ?,?,?, ?,?, ?,?, ?, ?,?,?,?, ?,?,?,?, ?,?, ?,?)', arg)
 
 def fill_other_transformation(proj_db_cursor):
     # 9601: Longitude rotation
@@ -442,8 +548,8 @@ def fill_other_transformation(proj_db_cursor):
     # 9660: Geographic3D offsets
     # 1068: Height Depth Reversal
     # 1069: Change of Vertical Unit
-    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, 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_method_code IN (9601, 9616, 9618, 9619, 9624, 9660, 1068, 1069)")
-    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, deprecated, scope, remarks) in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, epsg_coordoperation.deprecated, epsg_coordoperation.remarks FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_method_code IN (9601, 9616, 9618, 9619, 9624, 9660, 1068, 1069)")
+    for (code, name, method_code, method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, deprecated, remarks) in proj_db_cursor.fetchall():
 
         # 1068 and 1069 are Height Depth Reversal and Change of Vertical Unit
         # In EPSG, there is one generic instance of those as 7812 and 7813 that
@@ -487,11 +593,10 @@ def fill_other_transformation(proj_db_cursor):
             expected_order += 1
 
         arg = (EPSG_AUTHORITY, code, name,
-               remarks, scope,
+               remarks, 
                EPSG_AUTHORITY, method_code, method_name,
                EPSG_AUTHORITY, source_crs_code,
                EPSG_AUTHORITY, target_crs_code,
-               EPSG_AUTHORITY, area_of_use_code,
                coord_op_accuracy,
                param_auth_name[0], param_code[0], param_name[0],
                param_value[0], param_uom_auth_name[0], param_uom_code[0],
@@ -513,12 +618,12 @@ def fill_other_transformation(proj_db_cursor):
         #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'other_transformation')", (EPSG_AUTHORITY, code))
         #print(arg)
         proj_db_cursor.execute('INSERT INTO other_transformation VALUES (' +
-            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
+            '?,?,?, ?, ?,?,?, ?,?, ?,?,  ?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
             '?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?)', arg)
 
 def fill_concatenated_operation(proj_db_cursor):
-    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, 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 = 'concatenated operation'")
-    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, coord_tfm_version, deprecated, scope, remarks) in proj_db_cursor.fetchall():
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, coord_op_accuracy, coord_tfm_version, epsg_coordoperation.deprecated, epsg_coordoperation.remarks FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'concatenated operation'")
+    for (code, name, method_code, method_name, source_crs_code, target_crs_code,  coord_op_accuracy, coord_tfm_version, deprecated, remarks) in proj_db_cursor.fetchall():
         expected_order = 1
         steps_code = []
 
@@ -542,10 +647,9 @@ def fill_concatenated_operation(proj_db_cursor):
         if all_steps_exist:
 
             arg = (EPSG_AUTHORITY, code, name,
-                remarks, scope,
+                remarks,
                 EPSG_AUTHORITY, source_crs_code,
                 EPSG_AUTHORITY, target_crs_code,
-                EPSG_AUTHORITY, area_of_use_code,
                 coord_op_accuracy,
                 coord_tfm_version,
                 deprecated
@@ -553,7 +657,7 @@ def fill_concatenated_operation(proj_db_cursor):
 
             #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'concatenated_operation')", (EPSG_AUTHORITY, code))
             proj_db_cursor.execute('INSERT INTO concatenated_operation VALUES (' +
-            '?,?,?, ?,?, ?,?, ?,?, ?,?, ?, ?,?)', arg)
+            '?,?,?, ?, ?,?, ?,?, ?, ?,?)', arg)
 
             for i in range(len(steps_code)):
                 proj_db_cursor.execute('INSERT INTO concatenated_operation_step VALUES (?,?,?,?,?)', (EPSG_AUTHORITY, code, i+1, EPSG_AUTHORITY,steps_code[i]))
@@ -651,7 +755,7 @@ def fill_deprecation(proj_db_cursor):
         proj_db_cursor.execute("INSERT INTO deprecation VALUES (?,'EPSG',?,'EPSG',?,'EPSG')", (deprecated_table_name, code, replaced_by))
 
 def report_non_imported_operations(proj_db_cursor):
-    proj_db_cursor.execute("SELECT coord_op_code, coord_op_type, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_code NOT IN (SELECT code FROM coordinate_operation_with_conversion_view)")
+    proj_db_cursor.execute("SELECT coord_op_code, coord_op_type, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_code NOT IN (SELECT code FROM coordinate_operation_with_conversion_view) AND NOT (coord_op_method_name = 'France geocentric interpolation' AND epsg_coordoperation.deprecated = 1)")
     rows = []
     first = True
     for row in proj_db_cursor.fetchall():
@@ -668,6 +772,7 @@ script_dir_name = os.path.dirname(os.path.realpath(__file__))
 sql_dir_name = os.path.join(os.path.dirname(script_dir_name), 'data', 'sql')
 
 proj_db_filename = ':memory:'
+#proj_db_filename = 'tmp_proj.db'
 if os.path.exists(proj_db_filename):
     os.unlink(proj_db_filename)
 proj_db_conn = sqlite3.connect(proj_db_filename)
@@ -688,10 +793,6 @@ proj_db_cursor.execute("""CREATE TABLE conversion(
     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),
@@ -759,7 +860,6 @@ proj_db_cursor.execute("""CREATE TABLE helmert_transformation(
     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),
@@ -770,9 +870,6 @@ proj_db_cursor.execute("""CREATE TABLE helmert_transformation(
     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,
@@ -837,10 +934,12 @@ 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)
+fill_extent(proj_db_cursor)
+fill_scope(proj_db_cursor)
 fill_prime_meridian(proj_db_cursor)
 fill_geodetic_datum(proj_db_cursor)
 fill_vertical_datum(proj_db_cursor)
+fill_datumensemble(proj_db_cursor)
 fill_coordinate_system(proj_db_cursor)
 fill_axis(proj_db_cursor)
 fill_geodetic_crs(proj_db_cursor)
@@ -855,6 +954,7 @@ fill_concatenated_operation(proj_db_cursor)
 fill_alias(proj_db_cursor)
 fill_supersession(proj_db_cursor)
 fill_deprecation(proj_db_cursor)
+fill_usage(proj_db_cursor)
 non_imported_operations = report_non_imported_operations(proj_db_cursor)
 
 proj_db_cursor.close()