From 7d8fc7fcfb3690e48876972de014387b47918e11 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Wed, 8 May 2019 22:01:51 +0200 Subject: scripts/build_db.py: update to reflect new proj.db structure --- scripts/build_db.py | 161 +++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 159 insertions(+), 2 deletions(-) diff --git a/scripts/build_db.py b/scripts/build_db.py index d10db663..26839669 100755 --- a/scripts/build_db.py +++ b/scripts/build_db.py @@ -134,7 +134,7 @@ def fill_conversion(proj_db_cursor): trigger_sql = """ CREATE TRIGGER conversion_method_check_insert_trigger BEFORE INSERT ON conversion -FOR EACH ROW BEGIN +BEGIN """ proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, area_of_use_code, coord_op_method_code, coord_op_method_name, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'conversion' AND coord_op_name NOT LIKE '%to DMSH'") @@ -598,8 +598,165 @@ proj_db_cursor = proj_db_conn.cursor() proj_db_cursor.execute('PRAGMA foreign_keys = 1;') ingest_sqlite_dump(proj_db_cursor, os.path.join(sql_dir_name, 'proj_db_table_defs.sql')) + +# A bit messy, but to avoid churn in our existing .sql files, we temporarily +# recreate the original conversion and helmert_transformation tables +# instead of the view in the true database. + +proj_db_cursor.execute("""DROP VIEW conversion;""") +proj_db_cursor.execute("""DROP TABLE conversion_table;""") +proj_db_cursor.execute("""CREATE TABLE conversion( + auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), + code TEXT NOT NULL CHECK (length(code) >= 1), + 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), + method_name TEXT, + + param1_auth_name TEXT, + param1_code TEXT, + param1_name TEXT, + param1_value FLOAT, + param1_uom_auth_name TEXT, + param1_uom_code TEXT, + + param2_auth_name TEXT, + param2_code TEXT, + param2_name TEXT, + param2_value FLOAT, + param2_uom_auth_name TEXT, + param2_uom_code TEXT, + + param3_auth_name TEXT, + param3_code TEXT, + param3_name TEXT, + param3_value FLOAT, + param3_uom_auth_name TEXT, + param3_uom_code TEXT, + + param4_auth_name TEXT, + param4_code TEXT, + param4_name TEXT, + param4_value FLOAT, + param4_uom_auth_name TEXT, + param4_uom_code TEXT, + + param5_auth_name TEXT, + param5_code TEXT, + param5_name TEXT, + param5_value FLOAT, + param5_uom_auth_name TEXT, + param5_uom_code TEXT, + + param6_auth_name TEXT, + param6_code TEXT, + param6_name TEXT, + param6_value FLOAT, + param6_uom_auth_name TEXT, + param6_uom_code TEXT, + + param7_auth_name TEXT, + param7_code TEXT, + param7_name TEXT, + param7_value FLOAT, + param7_uom_auth_name TEXT, + param7_uom_code TEXT, + + deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), + + CONSTRAINT pk_conversion PRIMARY KEY (auth_name, code) +);""") + +proj_db_cursor.execute("""DROP VIEW helmert_transformation;""") +proj_db_cursor.execute("""DROP TABLE helmert_transformation_table;""") +proj_db_cursor.execute("""CREATE TABLE helmert_transformation( + auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), + code TEXT NOT NULL CHECK (length(code) >= 1), + 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), + method_name NOT NULL CHECK (length(method_name) >= 2), + + source_crs_auth_name TEXT NOT NULL, + source_crs_code TEXT NOT NULL, + 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, + ty FLOAT NOT NULL, + tz FLOAT NOT NULL, + translation_uom_auth_name TEXT NOT NULL, + translation_uom_code TEXT NOT NULL, + rx FLOAT, + ry FLOAT, + rz FLOAT, + rotation_uom_auth_name TEXT, + rotation_uom_code TEXT, + scale_difference FLOAT, + scale_difference_uom_auth_name TEXT, + scale_difference_uom_code TEXT, + rate_tx FLOAT, + rate_ty FLOAT, + rate_tz FLOAT, + rate_translation_uom_auth_name TEXT, + rate_translation_uom_code TEXT, + rate_rx FLOAT, + rate_ry FLOAT, + rate_rz FLOAT, + rate_rotation_uom_auth_name TEXT, + rate_rotation_uom_code TEXT, + rate_scale_difference FLOAT, + rate_scale_difference_uom_auth_name TEXT, + rate_scale_difference_uom_code TEXT, + epoch FLOAT, + epoch_uom_auth_name TEXT, + epoch_uom_code TEXT, + px FLOAT, -- Pivot / evaluation point for Molodensky-Badekas + py FLOAT, + pz FLOAT, + pivot_uom_auth_name TEXT, + pivot_uom_code TEXT, + + operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here + + deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), + + CONSTRAINT pk_helmert_transformation PRIMARY KEY (auth_name, code) +);""") + +proj_db_cursor.execute("SELECT name, sql FROM sqlite_master WHERE type = 'table' AND name = 'projected_crs'") +for (name, sql) in proj_db_cursor.fetchall(): + proj_db_cursor.execute("DROP TABLE " + name) + proj_db_cursor.execute(sql.replace('conversion_table', 'conversion')) + +proj_db_cursor.execute("SELECT name, sql FROM sqlite_master WHERE type = 'view'") +for (name, sql) in proj_db_cursor.fetchall(): + if 'conversion_table' in sql: + proj_db_cursor.execute("DROP VIEW " + name) + proj_db_cursor.execute(sql.replace('conversion_table', 'conversion')) + elif 'helmert_transformation_table' in sql: + proj_db_cursor.execute("DROP VIEW " + name) + proj_db_cursor.execute(sql.replace('helmert_transformation_table', 'helmert_transformation')) + 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) @@ -647,7 +804,7 @@ for line in proj_db_conn.iterdump(): f = open(os.path.join(sql_dir_name, table_name) + '.sql', 'wb') f.write("--- This file has been generated by scripts/build_db.py. DO NOT EDIT !\n\n".encode('UTF-8')) files[table_name] = f - f.write((line + '\n').encode('UTF-8')) + f.write((line + '\n').replace('BEFORE INSERT ON conversion', 'INSTEAD OF INSERT ON conversion').encode('UTF-8')) #f = files['coordinate_operation'] #for row in non_imported_operations: # f.write(("--- Non imported: " + str(row) + '\n').encode('UTF-8')) -- cgit v1.2.3 From 482482380c4f5562eb8a6e1b8c42ca0bff64eeda Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Wed, 8 May 2019 22:02:00 +0200 Subject: Database: update to EPSG 9.6.2 --- data/sql/grid_transformation.sql | 4 ++-- data/sql/helmert_transformation.sql | 6 ++++-- data/sql/metadata.sql | 4 ++-- 3 files changed, 8 insertions(+), 6 deletions(-) diff --git a/data/sql/grid_transformation.sql b/data/sql/grid_transformation.sql index 87279545..dd75c490 100644 --- a/data/sql/grid_transformation.sql +++ b/data/sql/grid_transformation.sql @@ -256,8 +256,8 @@ INSERT INTO "grid_transformation" VALUES('EPSG','8364','S-JTSK [JTSK03] to S-JTS INSERT INTO "grid_transformation" VALUES('EPSG','8369','BD72 to ETRS89 (3)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4313','EPSG','4258','EPSG','1347',0.01,'EPSG','8656','Latitude and longitude difference file','bd72lb72_etrs89lb08.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Bel 0.01m',0); INSERT INTO "grid_transformation" VALUES('EPSG','8371','RGF93 to NGF IGN69 height (2)',NULL,NULL,'EPSG','1073','Geographic3D to GravityRelatedHeight (IGN2009)','EPSG','4965','EPSG','5720','EPSG','1326',0.02,'EPSG','8666','Geoid (height correction) model file','RAF09.mnt',NULL,NULL,NULL,NULL,NULL,NULL,'IGN Fra 09',0); INSERT INTO "grid_transformation" VALUES('EPSG','8372','RGF93 to IGN78 Corsica height (2)',NULL,NULL,'EPSG','1073','Geographic3D to GravityRelatedHeight (IGN2009)','EPSG','4965','EPSG','5721','EPSG','1327',0.05,'EPSG','8666','Geoid (height correction) model file','RAC09.mnt',NULL,NULL,NULL,NULL,NULL,NULL,'IGN Fra Cor 09',0); -INSERT INTO "grid_transformation" VALUES('EPSG','8444','GDA94 to GDA2020 (4)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','4169',0.05,'EPSG','8656','Latitude and longitude difference file','XMAS_C_V1.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cxr CI Conf',0); -INSERT INTO "grid_transformation" VALUES('EPSG','8445','GDA94 to GDA2020 (5)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','1069',0.05,'EPSG','8656','Latitude and longitude difference file','COCOS_C_V1.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cki Conf',0); +INSERT INTO "grid_transformation" VALUES('EPSG','8444','GDA94 to GDA2020 (4)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','4169',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_christmas_island.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cxr Conf',0); +INSERT INTO "grid_transformation" VALUES('EPSG','8445','GDA94 to GDA2020 (5)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','1069',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_cocos_island.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Cck Conf',0); INSERT INTO "grid_transformation" VALUES('EPSG','8446','GDA94 to GDA2020 (3)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','2575',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Aus NTv2 Conf',0); INSERT INTO "grid_transformation" VALUES('EPSG','8447','GDA94 to GDA2020 (2)',NULL,NULL,'EPSG','9615','NTv2','EPSG','4283','EPSG','7844','EPSG','2575',0.05,'EPSG','8656','Latitude and longitude difference file','GDA94_GDA2020_conformal_and_distortion.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'ICSM-Aus Conf and Dist',0); INSERT INTO "grid_transformation" VALUES('EPSG','8451','GDA2020 to AHD height (1)',NULL,NULL,'EPSG','1048','Geographic3D to GravityRelatedHeight (Ausgeoid v2)','EPSG','7843','EPSG','5711','EPSG','4493',0.03,'EPSG','8666','Geoid (height correction) model file','AUSGeoid2020_windows_binary.gsb',NULL,NULL,NULL,NULL,NULL,NULL,'GA-Aus 2020',0); diff --git a/data/sql/helmert_transformation.sql b/data/sql/helmert_transformation.sql index a2aaeda1..86f759e9 100644 --- a/data/sql/helmert_transformation.sql +++ b/data/sql/helmert_transformation.sql @@ -1089,8 +1089,10 @@ INSERT INTO "helmert_transformation" VALUES('EPSG','8892','LKS94 to ETRS89 (1)', INSERT INTO "helmert_transformation" VALUES('EPSG','8893','SRB_ETRS89 to ETRS89 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','8685','EPSG','4258','EPSG','3534',0.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,'IOGP-Sbr',0); INSERT INTO "helmert_transformation" VALUES('EPSG','8894','SRB_ETRS89 to WGS 84 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','8685','EPSG','4326','EPSG','3534',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,'IOGP-Srb',0); INSERT INTO "helmert_transformation" VALUES('EPSG','8895','CHTRF95 to ETRS89 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4151','EPSG','4258','EPSG','1286',0.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,'IOGP-Che',0); -INSERT INTO "helmert_transformation" VALUES('EPSG','8913','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','8906','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0); -INSERT INTO "helmert_transformation" VALUES('EPSG','8914','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0); +INSERT INTO "helmert_transformation" VALUES('EPSG','8913','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','8906','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',1); +INSERT INTO "helmert_transformation" VALUES('EPSG','8914','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5364','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',1); +INSERT INTO "helmert_transformation" VALUES('EPSG','8968','CR05 to CR-SIRGAS (1)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5365','EPSG','8907','EPSG','1074',0.5,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0); +INSERT INTO "helmert_transformation" VALUES('EPSG','8969','CR05 to WGS 84 (2)',NULL,NULL,'EPSG','9606','Position Vector transformation (geog2D domain)','EPSG','5365','EPSG','4326','EPSG','1074',1.0,-0.16959,0.35312,0.51846,'EPSG','9001',-0.03385,0.16325,-0.03446,'EPSG','9104',0.03693,'EPSG','9202',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'IGN-Cri 2014',0); INSERT INTO "helmert_transformation" VALUES('EPSG','10085','Trinidad 1903 to WGS 84 (2)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4302','EPSG','4326','EPSG','1339',3.0,-61.0,285.2,471.6,'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,'EOG-Tto Trin',0); INSERT INTO "helmert_transformation" VALUES('EPSG','10086','JAD69 to WGS 72 (1)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4242','EPSG','4322','EPSG','3342',15.0,48.0,208.0,382.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,'SD-Jam',0); INSERT INTO "helmert_transformation" VALUES('EPSG','10089','Aratu to WGS 84 (5)',NULL,NULL,'EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4208','EPSG','4326','EPSG','2962',7.0,-163.466,317.396,-147.538,'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,'EXM-Bra Santos',0); diff --git a/data/sql/metadata.sql b/data/sql/metadata.sql index 2d0151de..39250290 100644 --- a/data/sql/metadata.sql +++ b/data/sql/metadata.sql @@ -1,2 +1,2 @@ -INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v9.6.1'); -INSERT INTO "metadata" VALUES('EPSG.DATE', '2019-03-25'); +INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v9.6.2'); +INSERT INTO "metadata" VALUES('EPSG.DATE', '2019-05-08'); -- cgit v1.2.3 From 1868f3b445f83b771ed4044697f5b275b49b3633 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Wed, 8 May 2019 22:09:17 +0200 Subject: NEWS: update for EPSG v9.6.2 mention --- NEWS | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/NEWS b/NEWS index 8368b22a..3c82f8dc 100644 --- a/NEWS +++ b/NEWS @@ -14,7 +14,7 @@ o Update internal version of googletest to v1.8.1 (#1361) - o Database update: EPSG v9.6.1, IGNF v3.0.3, ESRI 10.7.0 + o Database update: EPSG v9.6.2 (#1462), IGNF v3.0.3, ESRI 10.7.0 and add operation_version column (#1368) o Add proj_normalize_for_visualization() that attempts to apply axis -- cgit v1.2.3