diff options
| author | Even Rouault <even.rouault@spatialys.com> | 2020-09-24 22:41:59 +0200 |
|---|---|---|
| committer | Even Rouault <even.rouault@spatialys.com> | 2020-10-06 23:48:52 +0200 |
| commit | a9b6f39494e6dab0ea02af9d82e7b3d570f5422f (patch) | |
| tree | 8fc2fba9511877d81a2270238e40f7fa19e03ba0 /scripts | |
| parent | 7cec30b85ece4bca206f27642ee9aeb2807f5aba (diff) | |
| download | PROJ-a9b6f39494e6dab0ea02af9d82e7b3d570f5422f.tar.gz PROJ-a9b6f39494e6dab0ea02af9d82e7b3d570f5422f.zip | |
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.
Diffstat (limited to 'scripts')
| -rwxr-xr-x | scripts/build_db.py | 214 | ||||
| -rwxr-xr-x | scripts/build_db_create_ignf_from_xml.py | 181 | ||||
| -rwxr-xr-x | scripts/build_db_from_esri.py | 193 |
3 files changed, 401 insertions, 187 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() diff --git a/scripts/build_db_create_ignf_from_xml.py b/scripts/build_db_create_ignf_from_xml.py index 63e22770..ec818dc2 100755 --- a/scripts/build_db_create_ignf_from_xml.py +++ b/scripts/build_db_create_ignf_from_xml.py @@ -144,8 +144,7 @@ def ingest_datums(root, all_sql, mapEllpsId, mapPmId): ellpsCode = extract_id_from_href(node.find('usesEllipsoid').attrib['href']) assert ellpsCode in mapEllpsId - # We sheat by using EPSG:1262 = World for area of use - sql = """INSERT INTO "geodetic_datum" VALUES('IGNF','%s','%s',NULL,NULL,'%s','%s','%s','%s','EPSG','1262',NULL,0);""" % (id, names[0], mapEllpsId[ellpsCode][0], mapEllpsId[ellpsCode][1], mapPmId[pmCode][0], mapPmId[pmCode][1]) + sql = """INSERT INTO "geodetic_datum" VALUES('IGNF','%s','%s',NULL,'%s','%s','%s','%s',NULL,0);""" % (id, names[0], mapEllpsId[ellpsCode][0], mapEllpsId[ellpsCode][1], mapPmId[pmCode][0], mapPmId[pmCode][1]) all_sql.append(sql) mapDatumId[id] = ('IGNF', id) @@ -155,7 +154,7 @@ def ingest_datums(root, all_sql, mapEllpsId, mapPmId): id = node.attrib['id'] names = [_name.text for _name in node.iter('name')] - sql = """INSERT INTO "vertical_datum" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262',NULL,0);"""% (id, names[0]) + sql = """INSERT INTO "vertical_datum" VALUES('IGNF','%s','%s',NULL,NULL,0);"""% (id, names[0]) all_sql.append(sql) mapVerticalDatumId[id] = ('IGNF', id) @@ -169,31 +168,41 @@ mapEllpsId = ingest_ellipsoids(root, all_sql) mapPmId = ingest_prime_meridians(root, all_sql) mapDatumId, mapVerticalDatumId, invalidDatumId = ingest_datums(root, all_sql, mapEllpsId, mapPmId) -areaOfUseMap = {} +extentMap = {} -def get_area_of_use(domainOfValidity): +def get_extent_auth_name_code(domainOfValidity): extent = domainOfValidity.find('EX_Extent') desc = extent.find('description').find('CharacterString').text if desc is None: return 'EPSG', '1262' - if desc in areaOfUseMap: - return areaOfUseMap[desc] + if desc in extentMap: + return extentMap[desc] geographicElement = extent.find('geographicElement') if geographicElement is None: print('No geographicElement for area of use ' + desc) return 'EPSG', '1262' - code = str(len(areaOfUseMap) + 1) - areaOfUseMap[desc] = ['IGNF', code ] + code = str(len(extentMap) + 1) + extentMap[desc] = ['IGNF', code ] EX_GeographicBoundingBox = geographicElement.find('EX_GeographicBoundingBox') south = EX_GeographicBoundingBox.find('southBoundLatitude').find('Decimal').text west = EX_GeographicBoundingBox.find('westBoundLongitude').find('Decimal').text north = EX_GeographicBoundingBox.find('northBoundLatitude').find('Decimal').text east = EX_GeographicBoundingBox.find('eastBoundLongitude').find('Decimal').text - all_sql.append("""INSERT INTO "area" VALUES('IGNF','%s','%s','%s',%s,%s,%s,%s,0);""" % (code, escape_literal(desc), escape_literal(desc), south, north, west, east)) - return areaOfUseMap[desc] + all_sql.append("""INSERT INTO "extent" VALUES('IGNF','%s','%s','%s',%s,%s,%s,%s,0);""" % (code, escape_literal(desc), escape_literal(desc), south, north, west, east)) + return extentMap[desc] + +scopeMap = {} +def get_scope_auth_name_code(scope): + if scope in scopeMap: + return scopeMap[scope] + + code = str(len(scopeMap)+1) + scopeMap[scope] = ['IGNF', code] + all_sql.append("""INSERT INTO scope VALUES('IGNF','%s','%s',0);""" % (code, scope)) + return scopeMap[scope] mapCrsId = {} mapGeocentricId = {} @@ -221,12 +230,16 @@ for node in root.iterfind('.//GeocentricCRS'): continue assert datumCode in mapDatumId, (id, name, datumCode) - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope = node.find('scope').text + scope_auth_and_code = get_scope_auth_name_code(scope) #sql = """INSERT INTO "crs" VALUES('IGNF','%s','geocentric');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,NULL,'geocentric','EPSG','6500','%s','%s','%s','%s',NULL,0);""" % (id, name, mapDatumId[datumCode][0], mapDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,'geocentric','EPSG','6500','%s','%s',NULL,0);""" % (id, name, mapDatumId[datumCode][0], mapDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','geodetic_crs','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) mapCrsId[id] = ('IGNF', id) @@ -242,10 +255,12 @@ for node in root.iterfind('.//GeocentricCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','geocentric'); -- alias of %s""" % (alias, id) #all_sql.append(sql) - sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,NULL,'geocentric','EPSG','6500','%s','%s','%s','%s',NULL,0);""" % (alias, name, mapDatumId[datumCode][0], mapDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,'geocentric','EPSG','6500','%s','%s',NULL,0);""" % (alias, name, mapDatumId[datumCode][0], mapDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','geodetic_crs','IGNF','%s','%s','%s','%s','%s');""" % (alias, alias, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) - key = str((mapDatumId[datumCode], area_of_use)) + key = str((mapDatumId[datumCode], extent_auth_and_code)) assert key not in mapDatumAndAreaToGeocentricId, (id, name) mapDatumAndAreaToGeocentricId[key] = ('IGNF', id) mapGeocentricIdToDatumAndArea[id] = key @@ -266,7 +281,9 @@ for node in root.iterfind('.//GeographicCRS'): continue assert datumCode in mapDatumId, (id, name, datumCode) - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope = node.find('scope').text + scope_auth_and_code = get_scope_auth_name_code(scope) csCode = None type = 'geographic 2D' @@ -281,7 +298,9 @@ for node in root.iterfind('.//GeographicCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','%s');""" % (id, type) #all_sql.append(sql) - sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,NULL,'%s','EPSG','%s','%s','%s','%s','%s',NULL,0);""" % (id, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','%s','%s','%s',NULL,0);""" % (id, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','geodetic_crs','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) if id == 'WGS84G': @@ -295,12 +314,14 @@ for node in root.iterfind('.//GeographicCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','%s'); -- alias of %s""" % (alias, type, id) #all_sql.append(sql) - sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,NULL,'%s','EPSG','%s','%s','%s','%s','%s',NULL,0);""" % (alias, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','%s','%s','%s',NULL,0);""" % (alias, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','geodetic_crs','IGNF','%s','%s','%s','%s','%s');""" % (alias, alias, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) mapCrsId[id] = ('IGNF', id) mapGeographicId[id] = ('IGNF', id) - key = str((mapDatumId[datumCode], area_of_use)) + key = str((mapDatumId[datumCode], extent_auth_and_code)) if key in mapDatumAndAreaToGeographicId: #print('Adding ' + id + ' to ' + str(mapDatumAndAreaToGeographicId[key])) mapDatumAndAreaToGeographicId[key].append(id) @@ -318,12 +339,14 @@ for node in root.iterfind('.//GeographicCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','%s');""" % (id, type) #all_sql.append(sql) - sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,NULL,'%s','EPSG','%s','%s','%s','%s','%s',NULL,0);""" % (id, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "geodetic_crs" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','%s','%s','%s',NULL,0);""" % (id, name, type, csCode, mapDatumId[datumCode][0], mapDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','geodetic_crs','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) mapCrsId[id] = ('IGNF', id) mapGeographicId[id] = ('IGNF', id) - key = str((mapDatumId[datumCode], area_of_use)) + key = str((mapDatumId[datumCode], extent_auth_and_code)) if key in mapDatumAndAreaToGeographicId: #print('Adding ' + id + ' to ' + str(mapDatumAndAreaToGeographicId[key])) mapDatumAndAreaToGeographicId[key].append(id) @@ -351,9 +374,13 @@ for node in root.iterfind('.//VerticalCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','vertical');""" % (id_modified) #all_sql.append(sql) - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope = node.find('scope').text + scope_auth_and_code = get_scope_auth_name_code(scope) - sql = """INSERT INTO "vertical_crs" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','6499','%s','%s','%s','%s',0);""" % (id_modified, name, mapVerticalDatumId[datumCode][0], mapVerticalDatumId[datumCode][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "vertical_crs" VALUES('IGNF','%s','%s',NULL,'EPSG','6499','%s','%s',0);""" % (id_modified, name, mapVerticalDatumId[datumCode][0], mapVerticalDatumId[datumCode][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','vertical_crs','IGNF','%s','%s','%s','%s','%s');""" % (id_modified, id_modified, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) if len(names) >= 2 and names[1].startswith('http://registre.ign.fr/ign/IGNF/crs/IGNF/'): @@ -487,9 +514,9 @@ for node in root.iterfind('.//Transformation'): continue operation_version = node.find('operationVersion').text - scope = node.find('scope').text - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope_auth_and_code = get_scope_auth_name_code(node.find('scope').text) usesMethod = extract_id_from_href(node.find('usesMethod').attrib['href']) if usesMethod in ('Geographic3DtoGravityRelatedHeight_IGN'): @@ -520,7 +547,9 @@ for node in root.iterfind('.//Transformation'): name_components = name.split(' vers ') name_inverted = name_components[1] + ' vers ' + name_components[0] - sql = """INSERT INTO "grid_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','9664','Geographic3D to GravityRelatedHeight (IGN1997)','%s','%s','%s','%s','%s','%s',NULL,'EPSG','8666','Geoid (height correction) model file','%s',NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (id, name_inverted, scope, mapCrsId[targetCRS][0], mapCrsId[targetCRS][1], mapCrsId[sourceCRS][0], mapCrsId[sourceCRS][1], area_of_use[0], area_of_use[1], filename, operation_version) + sql = """INSERT INTO "grid_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG','9664','Geographic3D to GravityRelatedHeight (IGN1997)','%s','%s','%s','%s',NULL,'EPSG','8666','Geoid (height correction) model file','%s',NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (id, name_inverted, mapCrsId[targetCRS][0], mapCrsId[targetCRS][1], mapCrsId[sourceCRS][0], mapCrsId[sourceCRS][1], filename, operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','grid_transformation','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) continue @@ -547,7 +576,9 @@ for node in root.iterfind('.//Transformation'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','other_transformation');""" % custom_id #all_sql.append(sql) - sql = """INSERT INTO "other_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','9601','Longitude rotation','%s','%s','%s','%s','%s','%s',0.0,'EPSG','8602','Longitude offset',2.5969213,'EPSG','9105',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);"""% (custom_id, name, scope, src[0], src[1], target[0], target[1], area_of_use[0], area_of_use[1], operation_version) + sql = """INSERT INTO "other_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG','9601','Longitude rotation','%s','%s','%s','%s',0.0,'EPSG','8602','Longitude offset',2.5969213,'EPSG','9105',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);"""% (custom_id, name, src[0], src[1], target[0], target[1], operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','other_transformation','IGNF','%s','%s','%s','%s','%s');""" % (custom_id, custom_id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) continue @@ -575,7 +606,9 @@ for node in root.iterfind('.//Transformation'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','grid_transformation');""" % (custom_id) #all_sql.append(sql) - sql = """INSERT INTO "grid_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','9615','NTv2','%s','%s','%s','%s','%s','%s',NULL,'EPSG','8656','Latitude and longitude difference file','ntf_r93.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (custom_id, name, scope, src[0], src[1], target[0], target[1], area_of_use[0], area_of_use[1], operation_version) + sql = """INSERT INTO "grid_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG','9615','NTv2','%s','%s','%s','%s',NULL,'EPSG','8656','Latitude and longitude difference file','ntf_r93.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (custom_id, name, src[0], src[1], target[0], target[1], operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','grid_transformation','IGNF','%s','%s','%s','%s','%s');""" % (custom_id, custom_id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) continue @@ -591,7 +624,9 @@ for node in root.iterfind('.//Transformation'): uom = paramValue.find('value').attrib['uom'] assert uom == 'm' - sql = """INSERT INTO "other_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG','9616','Vertical Offset','%s','%s','%s','%s','%s','%s',NULL,'EPSG','8603','Vertical Offset',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);"""% (id, name, scope, mapCrsId[sourceCRS][0], mapCrsId[sourceCRS][1], mapCrsId[targetCRS][0], mapCrsId[targetCRS][1], area_of_use[0], area_of_use[1], value, operation_version) + sql = """INSERT INTO "other_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG','9616','Vertical Offset','%s','%s','%s','%s',NULL,'EPSG','8603','Vertical Offset',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);"""% (id, name, mapCrsId[sourceCRS][0], mapCrsId[sourceCRS][1], mapCrsId[targetCRS][0], mapCrsId[targetCRS][1], value, operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','other_transformation','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) continue @@ -660,7 +695,9 @@ for node in root.iterfind('.//Transformation'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','helmert_transformation');""" % (custom_id) #all_sql.append(sql) - sql = """INSERT INTO "helmert_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG',%s,%s,'%s','%s','%s','%s','%s','%s',NULL,%s,%s,%s,'EPSG','9001',%s,%s,%s,%s,%s,%s,%s, %s,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (custom_id, name, scope, method_code, method_name, src[0], src[1], target[0], target[1], area_of_use[0], area_of_use[1], x, y, z, rx, ry, rz, r_uom_auth_name, r_uom_code, s, s_uom_auth_name, s_uom_code, operation_version) + sql = """INSERT INTO "helmert_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG',%s,%s,'%s','%s','%s','%s',NULL,%s,%s,%s,'EPSG','9001',%s,%s,%s,%s,%s, %s,%s,%s,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (custom_id, name, method_code, method_name, src[0], src[1], target[0], target[1], x, y, z, rx, ry, rz, r_uom_auth_name, r_uom_code, s, s_uom_auth_name, s_uom_code, operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','helmert_transformation','IGNF','%s','%s','%s','%s','%s');""" % (custom_id, custom_id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) @@ -682,7 +719,9 @@ for node in root.iterfind('.//Transformation'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','helmert_transformation');""" % (id_geog) #all_sql.append(sql) - sql = """INSERT INTO "helmert_transformation" VALUES('IGNF','%s','%s',NULL,'%s','EPSG',%s,%s,'%s','%s','%s','%s','%s','%s',NULL,%s,%s,%s,'EPSG','9001',%s,%s,%s,%s,%s,%s,%s, %s,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (id_geog, name, scope, method_geog_code, method_geog_name, src[0], src[1], target[0], target[1], area_of_use[0], area_of_use[1], x, y, z, rx, ry, rz, r_uom_auth_name, r_uom_code, s, s_uom_auth_name, s_uom_code, operation_version) + sql = """INSERT INTO "helmert_transformation" VALUES('IGNF','%s','%s',NULL,'EPSG',%s,%s,'%s','%s','%s','%s',NULL,%s,%s,%s,'EPSG','9001',%s,%s,%s,%s,%s,%s,%s, %s,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%s',0);""" % (id_geog, name, method_geog_code, method_geog_name, src[0], src[1], target[0], target[1], x, y, z, rx, ry, rz, r_uom_auth_name, r_uom_code, s, s_uom_auth_name, s_uom_code, operation_version) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','helmert_transformation','IGNF','%s','%s','%s','%s','%s');""" % (id_geog, id_geog, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) if src[1] == 'NTFG': @@ -694,7 +733,9 @@ for node in root.iterfind('.//Transformation'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','concatenated_operation');""" % (id_concat) #all_sql_concat.append(sql) - sql = """INSERT INTO "concatenated_operation" VALUES('IGNF','%s','Nouvelle Triangulation Francaise Paris grades to %s',NULL,'%s','IGNF','%s','%s','%s','%s','%s',NULL,'%s',0);""" % (id_concat, target[1], scope, NTFPalias, target[0], target[1], area_of_use[0], area_of_use[1], operation_version) + sql = """INSERT INTO "concatenated_operation" VALUES('IGNF','%s','Nouvelle Triangulation Francaise Paris grades to %s',NULL,'IGNF','%s','%s','%s',NULL,'%s',0);""" % (id_concat, target[1], NTFPalias, target[0], target[1], operation_version) + all_sql_concat.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','concatenated_operation','IGNF','%s','%s','%s','%s','%s');""" % (id_concat, id_concat, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql_concat.append(sql) sql = """INSERT INTO "concatenated_operation_step" VALUES('IGNF','%s',1,'IGNF','%s');""" % (id_concat, idFirstOp) @@ -767,7 +808,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','1024','Popular Visualisation Pseudo Mercator','EPSG','8801','Latitude of natural origin',0.0,'EPSG','9102','EPSG','8802','Longitude of natural origin',0.0,'EPSG','9102','EPSG','8806','False easting',0.0,'EPSG','9001','EPSG','8807','False northing',0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','1024','Popular Visualisation Pseudo Mercator','EPSG','8801','Latitude of natural origin',0.0,'EPSG','9102','EPSG','8802','Longitude of natural origin',0.0,'EPSG','9102','EPSG','8806','False easting',0.0,'EPSG','9001','EPSG','8807','False northing',0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -785,7 +826,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','1028','Equidistant Cylindrical','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_ts'], d['lon_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','1028','Equidistant Cylindrical','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_ts'], d['lon_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -802,7 +843,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1886','EPSG','9807','Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9807','Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -821,7 +862,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9827','Bonne','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9827','Bonne','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -839,7 +880,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9820','Lambert Azimuthal Equal Area','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9820','Lambert Azimuthal Equal Area','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -857,7 +898,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9802','Lambert Conic Conformal (2SP)','EPSG','8821','Latitude of false origin',%s,'EPSG','9102','EPSG','8822','Longitude of false origin',%s,'EPSG','9102','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','9102','EPSG','8824','Latitude of 2nd standard parallel',%s,'EPSG','9102','EPSG','8826','Easting at false origin',%s,'EPSG','9001','EPSG','8827','Northing at false origin',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['lat_1'], d['lat_2'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9802','Lambert Conic Conformal (2SP)','EPSG','8821','Latitude of false origin',%s,'EPSG','9102','EPSG','8822','Longitude of false origin',%s,'EPSG','9102','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','9102','EPSG','8824','Latitude of 2nd standard parallel',%s,'EPSG','9102','EPSG','8826','Easting at false origin',%s,'EPSG','9001','EPSG','8827','Northing at false origin',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['lat_1'], d['lat_2'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -875,7 +916,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9804','Mercator (variant A)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9804','Mercator (variant A)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -893,7 +934,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -911,7 +952,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9105','EPSG','8802','Longitude of natural origin',%s,'EPSG','9105','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -930,7 +971,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','PROJ','gstm','Gauss Schreiber Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'PROJ','gstm','Gauss Schreiber Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -950,7 +991,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','EPSG','9810','Polar Stereographic (variant A)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'EPSG','9810','Polar Stereographic (variant A)','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -970,7 +1011,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','PROJ','mill','PROJ mill',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id,name) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,'PROJ','mill','PROJ mill',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id,name) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -1003,7 +1044,9 @@ for node in root.iterfind('.//ProjectedCRS'): continue assert definedByConversion in mapConversionId, (id, name, definedByConversion) - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope = node.find('scope').text + scope_auth_and_code = get_scope_auth_name_code(scope) #sql = """INSERT INTO "crs" VALUES('IGNF','%s','projected');""" % (id, ) #all_sql.append(sql) @@ -1014,7 +1057,9 @@ for node in root.iterfind('.//ProjectedCRS'): if usesCartesianCS == 'TYP_CRG34': cs_code = 4530 - sql = """INSERT INTO "projected_crs" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','%s','%s','%s','%s','%s','%s','%s',NULL,0);""" % (id,name,cs_code,mapGeographicId[baseGeographicCRS][0], mapGeographicId[baseGeographicCRS][1],mapConversionId[definedByConversion][0], mapConversionId[definedByConversion][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "projected_crs" VALUES('IGNF','%s','%s',NULL,'EPSG','%s','%s','%s','%s','%s',NULL,0);""" % (id,name,cs_code,mapGeographicId[baseGeographicCRS][0], mapGeographicId[baseGeographicCRS][1],mapConversionId[definedByConversion][0], mapConversionId[definedByConversion][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','projected_crs','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) if len(names) >= 2 and names[1].startswith('http://registre.ign.fr/ign/IGNF/crs/IGNF/'): @@ -1024,7 +1069,9 @@ for node in root.iterfind('.//ProjectedCRS'): #sql = """INSERT INTO "crs" VALUES('IGNF','%s','projected'); -- alias of %s""" % (alias, id) #all_sql.append(sql) - sql = """INSERT INTO "projected_crs" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','%s','%s','%s','%s','%s','%s','%s',NULL,0);""" % (alias,name,cs_code,mapGeographicId[baseGeographicCRS][0], mapGeographicId[baseGeographicCRS][1],mapConversionId[definedByConversion][0], mapConversionId[definedByConversion][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "projected_crs" VALUES('IGNF','%s','%s',NULL,'EPSG','%s','%s','%s','%s','%s',NULL,0);""" % (alias,name,cs_code,mapGeographicId[baseGeographicCRS][0], mapGeographicId[baseGeographicCRS][1],mapConversionId[definedByConversion][0], mapConversionId[definedByConversion][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','projected_crs','IGNF','%s','%s','%s','%s','%s');""" % (alias, alias, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) mapProjectedId[id] = ('IGNF', id) @@ -1050,12 +1097,16 @@ for node in root.iterfind('.//CompoundCRS'): else: horiz = mapGeographicId[singleCRS[0]] - area_of_use = get_area_of_use(node.find('domainOfValidity')) + extent_auth_and_code = get_extent_auth_name_code(node.find('domainOfValidity')) + scope = node.find('scope').text + scope_auth_and_code = get_scope_auth_name_code(scope) #sql = """INSERT INTO "crs" VALUES('IGNF','%s','compound');""" % (id, ) #all_sql.append(sql) - sql = """INSERT INTO "compound_crs" VALUES('IGNF','%s','%s',NULL,NULL,'%s','%s','%s','%s','%s','%s',0);""" % (id,name,horiz[0], horiz[1],mapVerticalCrsId[singleCRS[1]][0], mapVerticalCrsId[singleCRS[1]][1], area_of_use[0], area_of_use[1]) + sql = """INSERT INTO "compound_crs" VALUES('IGNF','%s','%s',NULL,'%s','%s','%s','%s',0);""" % (id,name,horiz[0], horiz[1],mapVerticalCrsId[singleCRS[1]][0], mapVerticalCrsId[singleCRS[1]][1]) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('IGNF', '%s_USAGE','compound_crs','IGNF','%s','%s','%s','%s','%s');""" % (id, id, extent_auth_and_code[0], extent_auth_and_code[1], scope_auth_and_code[0], scope_auth_and_code[1]) all_sql.append(sql) if len(names) >= 2 and names[1].startswith('http://registre.ign.fr/ign/IGNF/crs/IGNF/'): @@ -1112,28 +1163,40 @@ all_sql.append("""--- Null transformations between RRAF and WGS84 adapted from E all_sql.append('') area_of_use_name = 'ANTILLES FRANCAISES' -assert area_of_use_name in areaOfUseMap -area_of_use = areaOfUseMap[area_of_use_name] +assert area_of_use_name in extentMap +extent_auth_and_code = extentMap[area_of_use_name] -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAF_TO_EPSG_4978','RRAF to WGS 84',NULL,NULL,'EPSG','1031','Geocentric translations (geocentric domain)','IGNF','RRAF','EPSG','4978','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAF_TO_EPSG_4978','RRAF to WGS 84',NULL,'EPSG','1031','Geocentric translations (geocentric domain)','IGNF','RRAF','EPSG','4978',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RRAF_TO_EPSG_4978', 'IGNF_RRAF_TO_EPSG_4978', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAFG_TO_EPSG_4326','RRAFG to WGS 84',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RRAFG','EPSG','4326','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAFG_TO_EPSG_4326','RRAFG to WGS 84',NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RRAFG','EPSG','4326',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RRAFG_TO_EPSG_4326', 'IGNF_RRAFG_TO_EPSG_4326', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAFGDD_TO_EPSG_4326','RRAFGDD to WGS 84',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RRAFGDD','EPSG','4326','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RRAFGDD_TO_EPSG_4326','RRAFGDD to WGS 84',NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RRAFGDD','EPSG','4326',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RRAFGDD_TO_EPSG_4326', 'IGNF_RRAFGDD_TO_EPSG_4326', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) all_sql.append('') all_sql.append("""--- Null transformations between RGF93 and WGS84 adapted from EPSG""") all_sql.append('') area_of_use_name = 'FRANCE METROPOLITAINE (CORSE COMPRISE)' -assert area_of_use_name in areaOfUseMap -area_of_use = areaOfUseMap[area_of_use_name] +assert area_of_use_name in extentMap +extent_auth_and_code = extentMap[area_of_use_name] -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93_TO_EPSG_4978','RGF93 to WGS 84',NULL,NULL,'EPSG','1031','Geocentric translations (geocentric domain)','IGNF','RGF93','EPSG','4978','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93_TO_EPSG_4978','RGF93 to WGS 84',NULL,'EPSG','1031','Geocentric translations (geocentric domain)','IGNF','RGF93','EPSG','4978',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RGF93_TO_EPSG_4978', 'IGNF_RGF93_TO_EPSG_4978', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93G_TO_EPSG_4326','RGF93G to WGS 84',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RGF93G','EPSG','4326','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93G_TO_EPSG_4326','RGF93G to WGS 84',NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RGF93G','EPSG','4326',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RGF93G_TO_EPSG_4326', 'IGNF_RGF93G_TO_EPSG_4326', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) -all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93GDD_TO_EPSG_4326','RGF93GDD to WGS 84',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RGF93GDD','EPSG','4326','%s','%s',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (area_of_use[0], area_of_use[1])) +all_sql.append("""INSERT INTO "helmert_transformation" VALUES('PROJ','IGNF_RGF93GDD_TO_EPSG_4326','RGF93GDD to WGS 84',NULL,'EPSG','9603','Geocentric translations (geog2D domain)','IGNF','RGF93GDD','EPSG','4326',1.0,0.0,0.0,0.0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""") +sql = """INSERT INTO "usage" VALUES('PROJ', '%s_USAGE','helmert_transformation','PROJ','%s','%s','%s','%s','%s');""" % ('IGNF_RGF93GDD_TO_EPSG_4326', 'IGNF_RGF93GDD_TO_EPSG_4326', extent_auth_and_code[0], extent_auth_and_code[1], 'EPSG', '1024') +all_sql.append(sql) script_dir_name = os.path.dirname(os.path.realpath(__file__)) sql_dir_name = os.path.join(os.path.dirname(script_dir_name), 'data', 'sql') diff --git a/scripts/build_db_from_esri.py b/scripts/build_db_from_esri.py index 482c968b..ff72bf97 100755 --- a/scripts/build_db_from_esri.py +++ b/scripts/build_db_from_esri.py @@ -92,57 +92,57 @@ def escape_literal(x): ######################## -map_areaname_to_auth_code = {} +map_extentname_to_auth_code = {} esri_area_counter = 1 -def find_area(areaname, slat, nlat, llon, rlon): +def find_extent(extentname, slat, nlat, llon, rlon): global esri_area_counter - if areaname in map_areaname_to_auth_code: - return map_areaname_to_auth_code[areaname] + if extentname in map_extentname_to_auth_code: + return map_extentname_to_auth_code[extentname] deg = b'\xC2\xB0'.decode('utf-8') - cursor.execute("SELECT auth_name, code FROM area WHERE name = ? AND auth_name != 'ESRI'", - (areaname.replace('~', deg),)) + cursor.execute("SELECT auth_name, code FROM extent WHERE name = ? AND auth_name != 'ESRI'", + (extentname.replace('~', deg),)) row = cursor.fetchone() if row is None: cursor.execute( - "SELECT auth_name, code FROM area WHERE auth_name != 'ESRI' AND south_lat = ? AND north_lat = ? AND west_lon = ? AND east_lon = ?", (slat, nlat, llon, rlon)) + "SELECT auth_name, code FROM extent WHERE auth_name != 'ESRI' AND south_lat = ? AND north_lat = ? AND west_lon = ? AND east_lon = ?", (slat, nlat, llon, rlon)) row = cursor.fetchone() if row is None: - #print('unknown area inserted: ' + areaname) + #print('unknown extent inserted: ' + extentname) if float(rlon) > 180: new_rlon = '%s' % (float(rlon) - 360) print('Correcting rlon from %s to %s for %s' % - (rlon, new_rlon, areaname)) + (rlon, new_rlon, extentname)) rlon = new_rlon - assert float(slat) >= -90 and float(slat) <= 90, (areaname, + assert float(slat) >= -90 and float(slat) <= 90, (extentname, slat, nlat, llon, rlon) - assert float(nlat) >= -90 and float(nlat) <= 90, (areaname, + assert float(nlat) >= -90 and float(nlat) <= 90, (extentname, slat, nlat, llon, rlon) - assert float(nlat) > float(slat), (areaname, slat, nlat, llon, rlon) - assert float(llon) >= -180 and float(llon) <= 180, (areaname, + assert float(nlat) > float(slat), (extentname, slat, nlat, llon, rlon) + assert float(llon) >= -180 and float(llon) <= 180, (extentname, slat, nlat, llon, rlon) - assert float(rlon) >= -180 and float(rlon) <= 180, (areaname, + assert float(rlon) >= -180 and float(rlon) <= 180, (extentname, slat, nlat, llon, rlon) - sql = """INSERT INTO "area" VALUES('ESRI','%d','%s','%s',%s,%s,%s,%s,0);""" % ( - esri_area_counter, escape_literal(areaname), escape_literal(areaname), slat, nlat, llon, rlon) + sql = """INSERT INTO "extent" VALUES('ESRI','%d','%s','%s',%s,%s,%s,%s,0);""" % ( + esri_area_counter, escape_literal(extentname), escape_literal(extentname), slat, nlat, llon, rlon) all_sql.append(sql) - map_areaname_to_auth_code[areaname] = [ + map_extentname_to_auth_code[extentname] = [ 'ESRI', '%d' % esri_area_counter] esri_area_counter += 1 else: auth_name = row[0] code = row[1] - map_areaname_to_auth_code[areaname] = [auth_name, code] + map_extentname_to_auth_code[extentname] = [auth_name, code] - return map_areaname_to_auth_code[areaname] + return map_extentname_to_auth_code[extentname] ################# @@ -578,7 +578,7 @@ def import_geogcs(): deprecated = 1 if row[idx_deprecated] == 'yes' else 0 - area_auth_name, area_code = find_area( + extent_auth_name, extent_code = find_extent( row[idx_areaname], row[idx_slat], row[idx_nlat], row[idx_llon], row[idx_rlon]) if datum_auth_name == 'ESRI': @@ -587,8 +587,10 @@ def import_geogcs(): p = map_datum_esri_to_parameters[datum_code] - sql = """INSERT INTO "geodetic_datum" VALUES('ESRI','%s','%s','%s',NULL,'%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - datum_code, p['esri_name'], p['description'], p['ellps_auth_name'], p['ellps_code'], pm_auth_name, pm_code, area_auth_name, area_code, p['deprecated']) + sql = """INSERT INTO "geodetic_datum" VALUES('ESRI','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + datum_code, p['esri_name'], p['description'], p['ellps_auth_name'], p['ellps_code'], pm_auth_name, pm_code, p['deprecated']) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','geodetic_datum','ESRI','%s','%s','%s','%s','%s');""" % (datum_code, datum_code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) p['pm_auth_name'] = pm_auth_name p['pm_code'] = pm_code @@ -616,8 +618,10 @@ def import_geogcs(): 'deprecated': p['deprecated'] } - sql = """INSERT INTO "geodetic_datum" VALUES('ESRI','%s','%s',NULL,'%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - datum_code, p['esri_name'], p['description'], p['ellps_auth_name'], p['ellps_code'], pm_auth_name, pm_code, area_auth_name, area_code, p['deprecated']) + sql = """INSERT INTO "geodetic_datum" VALUES('ESRI','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + datum_code, p['esri_name'], p['description'], p['ellps_auth_name'], p['ellps_code'], pm_auth_name, pm_code, p['deprecated']) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','geodetic_datum','ESRI','%s','%s','%s','%s','%s');""" % (datum_code, datum_code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) p['pm_auth_name'] = pm_auth_name p['pm_code'] = pm_code @@ -628,8 +632,10 @@ def import_geogcs(): if esri_name not in map_geogcs_esri_name_to_auth_code: map_geogcs_esri_name_to_auth_code[esri_name] = ['ESRI', code] - sql = """INSERT INTO "geodetic_crs" VALUES('ESRI','%s','%s',NULL,NULL,'geographic 2D','EPSG','%s','%s','%s','%s','%s',NULL,%d);""" % ( - code, esri_name, cs_code, datum_auth_name, datum_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "geodetic_crs" VALUES('ESRI','%s','%s',NULL,'geographic 2D','EPSG','%s','%s','%s',NULL,%d);""" % ( + code, esri_name, cs_code, datum_auth_name, datum_code, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','geodetic_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) if deprecated and code != latestWkid and code not in ('4305', '4812'): # Voirol 1960 no longer in EPSG @@ -855,7 +861,7 @@ def import_projcs(): geogcs_name, row) geogcs_auth_name, geogcs_code = map_geogcs_esri_name_to_auth_code[geogcs_name] - area_auth_name, area_code = find_area( + extent_auth_name, extent_code = find_extent( row[idx_areaname], row[idx_slat], row[idx_nlat], row[idx_llon], row[idx_rlon]) map_projcs_esri_name_to_auth_code[esri_name] = ['ESRI', code] @@ -901,18 +907,24 @@ def import_projcs(): conv_auth_name = 'ESRI' conv_code = code - sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','EPSG','9807','Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','%s','EPSG','8802','Longitude of natural origin',%s,'EPSG','%s','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - code, conv_name, area_auth_name, area_code, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) + sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,'EPSG','9807','Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','%s','EPSG','8802','Longitude of natural origin',%s,'EPSG','%s','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + code, conv_name, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) - sql_extract = sql[sql.find('NULL,NULL'):] + sql_extract = sql[sql.find('NULL'):] if conv_name != 'unnamed' or sql_extract not in map_conversion_sql_to_code: all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'CONV_%s_USAGE','conversion','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') + all_sql.append(sql) + map_conversion_sql_to_code[sql_extract] = conv_code else: conv_code = map_conversion_sql_to_code[sql_extract] - sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,'%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', 'PCRS_%s_USAGE','projected_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif method == 'Hotine_Oblique_Mercator_Azimuth_Natural_Origin': @@ -930,18 +942,24 @@ def import_projcs(): conv_auth_name = 'ESRI' conv_code = code - sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','EPSG','9812','Hotine Oblique Mercator (variant A)','EPSG','8811','Latitude of projection centre',%s,'EPSG','%s','EPSG','8812','Longitude of projection centre',%s,'EPSG','%s','EPSG','8813','Azimuth of initial line',%s,'EPSG','%s','EPSG','8814','Angle from Rectified to Skew Grid',%s,'EPSG','%s','EPSG','8815','Scale factor on initial line',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',%d);""" % ( - code, conv_name, area_auth_name, area_code, Latitude_Of_Center, ang_uom_code, Longitude_Of_Center, ang_uom_code, Azimuth, ang_uom_code, Azimuth, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) + sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,'EPSG','9812','Hotine Oblique Mercator (variant A)','EPSG','8811','Latitude of projection centre',%s,'EPSG','%s','EPSG','8812','Longitude of projection centre',%s,'EPSG','%s','EPSG','8813','Azimuth of initial line',%s,'EPSG','%s','EPSG','8814','Angle from Rectified to Skew Grid',%s,'EPSG','%s','EPSG','8815','Scale factor on initial line',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',%d);""" % ( + code, conv_name, Latitude_Of_Center, ang_uom_code, Longitude_Of_Center, ang_uom_code, Azimuth, ang_uom_code, Azimuth, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) - sql_extract = sql[sql.find('NULL,NULL'):] + sql_extract = sql[sql.find('NULL'):] if conv_name != 'unnamed' or sql_extract not in map_conversion_sql_to_code: all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'CONV_%s_USAGE','conversion','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') + all_sql.append(sql) + map_conversion_sql_to_code[sql_extract] = conv_code else: conv_code = map_conversion_sql_to_code[sql_extract] - sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,'%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', 'PCRS_%s_USAGE','projected_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif method == 'Lambert_Conformal_Conic' and 'Standard_Parallel_2' in parsed_conv_wkt: @@ -959,18 +977,24 @@ def import_projcs(): conv_auth_name = 'ESRI' conv_code = code - sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','EPSG','9802','Lambert Conic Conformal (2SP)','EPSG','8821','Latitude of false origin',%s,'EPSG','%s','EPSG','8822','Longitude of false origin',%s,'EPSG','%s','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','%s','EPSG','8824','Latitude of 2nd standard parallel',%s,'EPSG','%s','EPSG','8826','Easting at false origin',%s,'EPSG','%s','EPSG','8827','Northing at false origin',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - code, conv_name, area_auth_name, area_code, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Standard_Parallel_1, ang_uom_code, Standard_Parallel_2, ang_uom_code, False_Easting, uom_code, False_Northing, uom_code, deprecated) + sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,'EPSG','9802','Lambert Conic Conformal (2SP)','EPSG','8821','Latitude of false origin',%s,'EPSG','%s','EPSG','8822','Longitude of false origin',%s,'EPSG','%s','EPSG','8823','Latitude of 1st standard parallel',%s,'EPSG','%s','EPSG','8824','Latitude of 2nd standard parallel',%s,'EPSG','%s','EPSG','8826','Easting at false origin',%s,'EPSG','%s','EPSG','8827','Northing at false origin',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + code, conv_name, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Standard_Parallel_1, ang_uom_code, Standard_Parallel_2, ang_uom_code, False_Easting, uom_code, False_Northing, uom_code, deprecated) - sql_extract = sql[sql.find('NULL,NULL'):] + sql_extract = sql[sql.find('NULL'):] if conv_name != 'unnamed' or sql_extract not in map_conversion_sql_to_code: all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'CONV_%s_USAGE','conversion','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') + all_sql.append(sql) + map_conversion_sql_to_code[sql_extract] = conv_code else: conv_code = map_conversion_sql_to_code[sql_extract] - sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,'%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', 'PCRS_%s_USAGE','projected_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif method == 'Lambert_Conformal_Conic' and 'Scale_Factor' in parsed_conv_wkt: @@ -989,24 +1013,34 @@ def import_projcs(): conv_auth_name = 'ESRI' conv_code = code - sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','%s','EPSG','8802','Longitude of natural origin',%s,'EPSG','%s','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - code, conv_name, area_auth_name, area_code, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) + sql = """INSERT INTO "conversion" VALUES('ESRI','%s','%s',NULL,'EPSG','9801','Lambert Conic Conformal (1SP)','EPSG','8801','Latitude of natural origin',%s,'EPSG','%s','EPSG','8802','Longitude of natural origin',%s,'EPSG','%s','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','%s','EPSG','8807','False northing',%s,'EPSG','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + code, conv_name, Latitude_Of_Origin, ang_uom_code, Central_Meridian, ang_uom_code, Scale_Factor, False_Easting, uom_code, False_Northing, uom_code, deprecated) - sql_extract = sql[sql.find('NULL,NULL'):] + sql_extract = sql[sql.find('NULL'):] if conv_name != 'unnamed' or sql_extract not in map_conversion_sql_to_code: all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'CONV_%s_USAGE','conversion','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') + all_sql.append(sql) + map_conversion_sql_to_code[sql_extract] = conv_code else: conv_code = map_conversion_sql_to_code[sql_extract] - sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s','%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( - code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,'%s','%s','%s','%s','%s','%s',NULL,%d);""" % ( + code, esri_name, cs_auth_name, cs_code, geogcs_auth_name, geogcs_code, conv_auth_name, conv_code, deprecated) + all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'PCRS_%s_USAGE','projected_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) else: - sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,NULL,NULL,'%s','%s',NULL,NULL,'%s','%s','%s',%d);""" % ( - code, esri_name, geogcs_auth_name, geogcs_code, area_auth_name, area_code, escape_literal(wkt), deprecated) + sql = """INSERT INTO "projected_crs" VALUES('ESRI','%s','%s',NULL,NULL,NULL,'%s','%s',NULL,NULL,'%s',%d);""" % ( + code, esri_name, geogcs_auth_name, geogcs_code, escape_literal(wkt), deprecated) + all_sql.append(sql) + + sql = """INSERT INTO "usage" VALUES('ESRI', 'PCRS_%s_USAGE','projected_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) if deprecated and code != latestWkid: @@ -1217,7 +1251,7 @@ def import_vertcs(): deprecated = 1 if row[idx_deprecated] == 'yes' else 0 - area_auth_name, area_code = find_area( + extent_auth_name, extent_code = find_extent( row[idx_areaname], row[idx_slat], row[idx_nlat], row[idx_llon], row[idx_rlon]) if datum_auth_name == 'ESRI': @@ -1225,10 +1259,11 @@ def import_vertcs(): datum_written.add(datum_code) p = map_vdatum_esri_to_parameters[datum_code] - sql = """INSERT INTO "vertical_datum" VALUES('ESRI','%s','%s',NULL,NULL,'%s','%s',NULL,%d);""" % ( - datum_code, p['esri_name'], area_auth_name, area_code, p['deprecated']) + sql = """INSERT INTO "vertical_datum" VALUES('ESRI','%s','%s',NULL,NULL,%d);""" % ( + datum_code, p['esri_name'], p['deprecated']) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','vertical_datum','ESRI','%s','%s','%s','%s','%s');""" % (datum_code, datum_code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) - map_vertcs_esri_name_to_auth_code[esri_name] = ['ESRI', code] if 'PARAMETER["Direction",1.0]' in wkt and 'UNIT["Meter"' in wkt: @@ -1241,8 +1276,10 @@ def import_vertcs(): assert False, ('unknown coordinate system for %s' % str(row)) - sql = """INSERT INTO "vertical_crs" VALUES('ESRI','%s','%s',NULL,NULL,'EPSG','%s','%s','%s','%s','%s',%d);""" % ( - code, esri_name, cs_code, datum_auth_name, datum_code, area_auth_name, area_code, deprecated) + sql = """INSERT INTO "vertical_crs" VALUES('ESRI','%s','%s',NULL,'EPSG','%s','%s','%s',%d);""" % ( + code, esri_name, cs_code, datum_auth_name, datum_code, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','vertical_crs','ESRI','%s','%s','%s','%s','%s');""" % (code, code, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) if deprecated and code != latestWkid: @@ -1480,7 +1517,7 @@ def import_geogtran(): 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( + extent_auth_name, extent_code = find_extent( row[idx_areaname], row[idx_slat], row[idx_nlat], row[idx_llon], row[idx_rlon]) accuracy = row[idx_accuracy] @@ -1504,8 +1541,10 @@ def import_geogtran(): method_code = '9606' method_name = 'Position Vector transformation (geog2D domain)' - 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',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,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, deprecated) + sql = """INSERT INTO "helmert_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','%s','%s','%s','%s','%s','%s',%s,%s,%s,%s,'EPSG','9001',%s,%s,%s,'EPSG','9104',%s,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,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, accuracy, x, y, z, rx, ry, rz, s, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','helmert_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_molodensky_badekas: @@ -1526,8 +1565,10 @@ def import_geogtran(): method_code = '9636' method_name = 'Molodensky-Badekas (CF geog2D domain)' - 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',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%s,%s,%s,'EPSG','9001',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, px, py, pz, deprecated) + sql = """INSERT INTO "helmert_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','%s','%s','%s','%s','%s','%s',%s,%s,%s,%s,'EPSG','9001',%s,%s,%s,'EPSG','9104',%s,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%s,%s,%s,'EPSG','9001',NULL,%d);""" % ( + wkid, esri_name, method_code, method_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, accuracy, x, y, z, rx, ry, rz, s, px, py, pz, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','helmert_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_geocentric_translation: @@ -1539,8 +1580,10 @@ def import_geogtran(): method_code = '9603' method_name = 'Geocentric translations (geog2D domain)' - 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',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,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, deprecated) + sql = """INSERT INTO "helmert_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','%s','%s','%s','%s','%s','%s',%s,%s,%s,%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,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, accuracy, x, y, z, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','helmert_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_Time_Based_Helmert_Position_Vector or is_Time_Based_Helmert_Coordinate_Frame: @@ -1569,8 +1612,10 @@ def import_geogtran(): 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) + sql = """INSERT INTO "helmert_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','%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, 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) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','helmert_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_geog2d_offset: @@ -1584,8 +1629,10 @@ def import_geogtran(): lat_offset = get_parameter(wkt, 'Latitude_Offset') assert wkt.count('PARAMETER[') == 2 - sql = """INSERT INTO "other_transformation" VALUES('ESRI','%s','%s',NULL,NULL,'EPSG','9619','Geographic2D offsets','%s','%s','%s','%s','%s','%s',%s,'EPSG','8601','Latitude offset',%s,'EPSG','9104','EPSG','8602','Longitude offset',%s,'EPSG','9104',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code, accuracy, lat_offset, long_offset, deprecated) + sql = """INSERT INTO "other_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','9619','Geographic2D offsets','%s','%s','%s','%s',%s,'EPSG','8601','Latitude offset',%s,'EPSG','9104','EPSG','8602','Longitude offset',%s,'EPSG','9104',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, accuracy, lat_offset, long_offset, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','other_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_null: @@ -1593,8 +1640,10 @@ def import_geogtran(): lat_offset = '0' assert wkt.count('PARAMETER[') == 0 - sql = """INSERT INTO "other_transformation" VALUES('ESRI','%s','%s',NULL,NULL,'EPSG','9619','Geographic2D offsets','%s','%s','%s','%s','%s','%s',%s,'EPSG','8601','Latitude offset',%s,'EPSG','9104','EPSG','8602','Longitude offset',%s,'EPSG','9104',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code, accuracy, lat_offset, long_offset, deprecated) + sql = """INSERT INTO "other_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','9619','Geographic2D offsets','%s','%s','%s','%s',%s,'EPSG','8601','Latitude offset',%s,'EPSG','9104','EPSG','8602','Longitude offset',%s,'EPSG','9104',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, accuracy, lat_offset, long_offset, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','other_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) elif is_unitchange: @@ -1617,14 +1666,16 @@ def import_geogtran(): cursor.execute( - "SELECT name, grid_name FROM grid_transformation WHERE auth_name != 'ESRI' AND source_crs_auth_name = ? AND source_crs_code = ? AND target_crs_auth_name = ? AND target_crs_code = ? AND area_of_use_auth_name = ? AND area_of_use_code = ?", (src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code)) + "SELECT g.name, g.grid_name FROM grid_transformation g JOIN usage u ON u.object_table_name = 'grid_transformation' AND u.object_auth_name = g.auth_name AND u.object_code = g.code JOIN extent e ON u.extent_auth_name = e.auth_name AND u.extent_code = e.code WHERE g.auth_name != 'ESRI' AND g.source_crs_auth_name = ? AND g.source_crs_code = ? AND g.target_crs_auth_name = ? AND g.target_crs_code = ? AND e.auth_name = ? AND e.code = ?", (src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, extent_auth_name, extent_code)) src_row = cursor.fetchone() if src_row: print('A grid_transformation (%s, using grid %s) is already known for the equivalent of %s (%s:%s --> %s:%s) for area %s, which uses grid %s. Skipping it' % (src_row[0], src_row[1], esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, row[idx_areaname], filename)) continue - sql = """INSERT INTO "grid_transformation" VALUES('ESRI','%s','%s',NULL,NULL,'EPSG','9615','NTv2','%s','%s','%s','%s','%s','%s',%s,'EPSG','8656','Latitude and longitude difference file','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( - wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, area_auth_name, area_code, accuracy, filename, deprecated) + sql = """INSERT INTO "grid_transformation" VALUES('ESRI','%s','%s',NULL,'EPSG','9615','NTv2','%s','%s','%s','%s',%s,'EPSG','8656','Latitude and longitude difference file','%s',NULL,NULL,NULL,NULL,NULL,NULL,NULL,%d);""" % ( + wkid, esri_name, src_crs_auth_name, src_crs_code, dst_crs_auth_name, dst_crs_code, accuracy, filename, deprecated) + all_sql.append(sql) + sql = """INSERT INTO "usage" VALUES('ESRI', '%s_USAGE','grid_transformation','ESRI','%s','%s','%s','%s','%s');""" % (wkid, wkid, extent_auth_name, extent_code, 'EPSG', '1024') all_sql.append(sql) global manual_grids |