diff options
Diffstat (limited to 'scripts/build_db.py')
| -rwxr-xr-x | scripts/build_db.py | 214 |
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() |