Merge pull request #1710 from rouault/geoid_model

Add support for GEOIDMODEL

4 files changed, 126 insertions, 12 deletions

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index eb49828a..a708df0f 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -29,6 +29,22 @@ FOR EACH ROW BEGIN
         WHERE (SELECT 1 FROM object_view LIMIT 1) = 0;
     SELECT RAISE(ABORT, 'corrupt definition of authority_list')
         WHERE (SELECT 1 FROM authority_list LIMIT 1) = 0;
+
+    -- check geoid_model table
+    SELECT RAISE(ABORT, 'missing GEOID99 in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID99');
+    SELECT RAISE(ABORT, 'missing GEOID03 in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID03');
+    SELECT RAISE(ABORT, 'missing GEOID06 in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID06');
+    SELECT RAISE(ABORT, 'missing GEOID09 in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID09');
+    SELECT RAISE(ABORT, 'missing GEOID12A in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID12A');
+    SELECT RAISE(ABORT, 'missing GEOID12B in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID12B');
+    SELECT RAISE(ABORT, 'missing GEOID18 in geoid_model')
+        WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID18');
 END;
 INSERT INTO dummy DEFAULT VALUES;
 DROP TRIGGER final_checks;
diff --git a/data/sql/customizations.sql b/data/sql/customizations.sql
index 99891dc1..6301bda9 100644
--- a/data/sql/customizations.sql
+++ b/data/sql/customizations.sql
@@ -83,15 +83,19 @@ INSERT INTO "helmert_transformation" VALUES('PROJ','WGS84_TO_WGS84_G1674','WGS 8
 INSERT INTO "helmert_transformation" VALUES('PROJ','WGS84_TO_WGS84_G1762','WGS 84 to WGS 84 (G1762)','','Accuracy 2m','EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4326','EPSG','9057','EPSG','1262',2.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,'',0);
 INSERT INTO "helmert_transformation" VALUES('PROJ','WGS84_TO_WGS84_TRANSIT','WGS 84 to WGS 84 (Transit)','','Accuracy 2m','EPSG','9603','Geocentric translations (geog2D domain)','EPSG','4326','EPSG','8888','EPSG','1262',2.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,'',0);
 
---- Remove a few supersessions ---
-
--- TODO: remove this once https://github.com/OSGeo/proj-datumgrid/issues/55 is dealt with
--- The following supersessions are for geoid2012, replaced by geoid2018
--- but we don't have geoid2018 yet as PROJ grids, so remove for now those
--- supersessions
--- INSERT INTO "supersession" VALUES('grid_transformation','EPSG','6326','grid_transformation','EPSG','9229','EPSG');
--- INSERT INTO "supersession" VALUES('grid_transformation','EPSG','7646','grid_transformation','EPSG','9230','EPSG');
--- INSERT INTO "supersession" VALUES('grid_transformation','EPSG','7647','grid_transformation','EPSG','9231','EPSG');
-DELETE FROM supersession WHERE superseded_table_name = 'grid_transformation' AND superseded_auth_name = 'EPSG' AND superseded_code = '6326';
-DELETE FROM supersession WHERE superseded_table_name = 'grid_transformation' AND superseded_auth_name = 'EPSG' AND superseded_code = '7646';
-DELETE FROM supersession WHERE superseded_table_name = 'grid_transformation' AND superseded_auth_name = 'EPSG' AND superseded_code = '7647';
+---- Geoid models -----
+
+INSERT INTO "geoid_model" SELECT 'GEOID99', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'g1999%' AND deprecated = 0;
+
+INSERT INTO "geoid_model" SELECT 'GEOID03', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'geoid03%' AND deprecated = 0;
+
+INSERT INTO "geoid_model" SELECT 'GEOID06', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'geoid06%' AND deprecated = 0;
+
+INSERT INTO "geoid_model" SELECT 'GEOID09', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'geoid09%' AND deprecated = 0;
+
+-- Geoid12A and Geoid12B are identical
+INSERT INTO "geoid_model" SELECT 'GEOID12A', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'g2012b%' AND deprecated = 0;
+
+INSERT INTO "geoid_model" SELECT 'GEOID12B', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'g2012b%' AND deprecated = 0;
+
+INSERT INTO "geoid_model" SELECT 'GEOID18', auth_name, code FROM grid_transformation WHERE auth_name = 'EPSG' AND grid_name LIKE 'g2018%' AND deprecated = 0;
diff --git a/data/sql/grid_alternatives.sql b/data/sql/grid_alternatives.sql
index eb752e44..ab81a2fa 100644
--- a/data/sql/grid_alternatives.sql
+++ b/data/sql/grid_alternatives.sql
@@ -361,6 +361,74 @@ INSERT INTO grid_alternatives(original_grid_name,
                               NULL, NULL, NULL, NULL);
 
 ----------------------------
+-- US GEOID99 height models
+----------------------------
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u01.bin', 'g1999u01.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u02.bin', 'g1999u02.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u03.bin', 'g1999u03.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u04.bin', 'g1999u04.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u05.bin', 'g1999u05.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u06.bin', 'g1999u06.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u07.bin', 'g1999u07.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g1999u08.bin', 'g1999u08.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+-- Not mapped:
+-- g1999a01.gtx to g1999a04.gtx : Alaska
+-- g1999h01.gtx : Hawaii
+-- g1999p01.gtx : Puerto Rico
+
+----------------------------
+-- US GEOID03 height models
+----------------------------
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('geoid03_conus.bin', 'geoid03_conus.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+-- Not mapped:
+-- g2003a01.gtx to g2003a04.gtx : Alaska
+-- g2003h01.gtx : Hawaii
+---g2003p01.gtx : Puerto Rico
+
+----------------------------
+-- US GEOID06 height models
+----------------------------
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('geoid06_ak.bin', 'geoid06_ak.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+----------------------------
+-- US GEOID09 height models
+----------------------------
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('geoid09_ak.bin', 'geoid09_ak.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('geoid09_conus.bin', 'geoid09_conus.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+-- Not mapped:
+-- g2009h01.gtx : Hawaii
+-- g2009g01.gtx : Guam and Northern Mariana Islands.
+-- g2009s01.gtx : American Samoa.
+-- g2009p01.gtx : Puerto Rico / U.S. Vigin Islands.
+
+----------------------------
 -- US GEOID12B height models
 ----------------------------
 
@@ -444,6 +512,15 @@ INSERT INTO grid_alternatives(original_grid_name,
                               'proj-datumgrid-north-america',
                               NULL, NULL, NULL, NULL);
 
+----------------------------
+-- US GEOID18 height models
+----------------------------
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g2018u0.bin', 'g2018u0.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name, proj_grid_name, proj_grid_format, proj_method, inverse_direction, package_name, url, direct_download, open_license, directory)
+    VALUES ('g2018p0.bin', 'g2018p0.gtx', 'GTX', 'vgridshift', 1, 'proj-datumgrid-north-america', NULL, NULL, NULL, NULL);
 
 ----------------------------
 -- French vertical grids
diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index 8a02acf4..a97e75a8 100644
--- a/data/sql/proj_db_table_defs.sql
+++ b/data/sql/proj_db_table_defs.sql
@@ -1365,6 +1365,23 @@ FOR EACH ROW BEGIN
 
 END;
 
+
+CREATE TABLE geoid_model(
+    name TEXT NOT NULL,
+    operation_auth_name TEXT NOT NULL,
+    operation_code TEXT NOT NULL,
+    CONSTRAINT pk_geoid_model PRIMARY KEY (name, operation_auth_name, operation_code)
+    -- CONSTRATINT fk_geoid_model_operation FOREIGN KEY (operation_auth_name, operation_code) REFERENCES coordinate_operation(auth_name, code)
+);
+
+CREATE TRIGGER geoid_model_insert_trigger
+BEFORE INSERT ON geoid_model
+FOR EACH ROW BEGIN
+    SELECT RAISE(ABORT, 'insert on geoid_model violates constraint: (operation_auth_name, operation_code) must already exist in coordinate_operation_with_conversion_view')
+        WHERE NOT EXISTS (SELECT 1 FROM coordinate_operation_with_conversion_view covwv WHERE covwv.auth_name = NEW.operation_auth_name AND covwv.code = NEW.operation_code);
+END;
+
+
 CREATE TABLE alias_name(
     table_name TEXT NOT NULL CHECK (table_name IN (
         'unit_of_measure', 'celestial_body', 'ellipsoid',