From 3a035e99f786b61837efcdf56a86748828e1f155 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Wed, 11 Dec 2019 00:13:57 +0100 Subject: Make EPSG:102100 resolve to ESRI:102100 (fixes #1730) --- data/sql/commit.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'data/sql/commit.sql') diff --git a/data/sql/commit.sql b/data/sql/commit.sql index 0468032d..4b17ea2b 100644 --- a/data/sql/commit.sql +++ b/data/sql/commit.sql @@ -36,6 +36,9 @@ FOR EACH ROW BEGIN lower(g1.grid_name) = lower(g2.grid_name) AND g1.auth_name = 'PROJ' AND g2.auth_name = 'EPSG'); + SELECT RAISE(ABORT, 'Arg! there is now a EPSG:102100 object. Hack in createFromUserInput() will no longer work') + WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name == 'EPSG' AND code = '102100'); + -- check geoid_model table SELECT RAISE(ABORT, 'missing GEOID99 in geoid_model') WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID99'); -- cgit v1.2.3 From e7ef9b626ecabfe6bc5ff3793a9565426470a4a3 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sat, 21 Dec 2019 02:13:07 +0100 Subject: Database: fix registration of Icegeoid_xxx.gtx files, and add corresponding entries in grid_alternatives.sql --- data/sql/commit.sql | 28 +++++++++++++++++++++++++++- 1 file changed, 27 insertions(+), 1 deletion(-) (limited to 'data/sql/commit.sql') diff --git a/data/sql/commit.sql b/data/sql/commit.sql index 4b17ea2b..d7bc7b4a 100644 --- a/data/sql/commit.sql +++ b/data/sql/commit.sql @@ -37,7 +37,33 @@ FOR EACH ROW BEGIN g1.auth_name = 'PROJ' AND g2.auth_name = 'EPSG'); SELECT RAISE(ABORT, 'Arg! there is now a EPSG:102100 object. Hack in createFromUserInput() will no longer work') - WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name == 'EPSG' AND code = '102100'); + WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name = 'EPSG' AND code = '102100'); + + -- check that grids with HEIGHT_TO_GEOGRAPHIC3D method are properly registered + SELECT RAISE(ABORT, 'One grid_transformation with HEIGHT_TO_GEOGRAPHIC3D has not its source_crs in vertical_crs table') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_code = 'HEIGHT_TO_GEOGRAPHIC3D' AND + g.source_crs_auth_name || g.source_crs_code NOT IN + (SELECT auth_name || code FROM vertical_crs)); + SELECT RAISE(ABORT, 'One grid_transformation with HEIGHT_TO_GEOGRAPHIC3D has not its target_crs in geodetic_crs table with type = ''geographic 3D''') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_code = 'HEIGHT_TO_GEOGRAPHIC3D' AND + g.target_crs_auth_name || g.target_crs_code NOT IN + (SELECT auth_name || code FROM geodetic_crs + WHERE type = 'geographic 3D')); + + -- check that grids with Geographic3D to GravityRelatedHeight method are properly registered + SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its target_crs in vertical_crs table') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_code LIKE 'Geographic3D to GravityRelatedHeight%' AND + g.target_crs_auth_name || g.target_crs_code NOT IN + (SELECT auth_name || code FROM vertical_crs)); + SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its source_crs in geodetic_crs table with type = ''geographic 3D''') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_code LIKE 'Geographic3D to GravityRelatedHeight%' AND + g.source_crs_auth_name || g.source_crs_code NOT IN + (SELECT auth_name || code FROM geodetic_crs + WHERE type = 'geographic 3D')); -- check geoid_model table SELECT RAISE(ABORT, 'missing GEOID99 in geoid_model') -- cgit v1.2.3 From 5b1346e95c8d9cf07d42a9700df68ff2646699cb Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sat, 21 Dec 2019 02:41:28 +0100 Subject: Database: add/fix consistency checks --- data/sql/commit.sql | 31 +++++++++++++++++++++++++++++-- 1 file changed, 29 insertions(+), 2 deletions(-) (limited to 'data/sql/commit.sql') diff --git a/data/sql/commit.sql b/data/sql/commit.sql index d7bc7b4a..73e41d64 100644 --- a/data/sql/commit.sql +++ b/data/sql/commit.sql @@ -39,6 +39,30 @@ FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'Arg! there is now a EPSG:102100 object. Hack in createFromUserInput() will no longer work') WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name = 'EPSG' AND code = '102100'); + -- check coordinate_operation_view "foreign keys" + SELECT RAISE(ABORT, 'One coordinate_operation has a broken source_crs link') + WHERE EXISTS (SELECT * FROM coordinate_operation_view cov WHERE + cov.source_crs_auth_name || cov.source_crs_code NOT IN + (SELECT auth_name || code FROM crs_view)); + SELECT RAISE(ABORT, 'One coordinate_operation has a broken target_crs link') + WHERE EXISTS (SELECT * FROM coordinate_operation_view cov WHERE + cov.target_crs_auth_name || cov.target_crs_code NOT IN + (SELECT auth_name || code FROM crs_view)); + + -- check that grids with NTv2 method are properly registered + SELECT RAISE(ABORT, 'One grid_transformation with NTv2 has not its source_crs in geodetic_crs table with type = ''geographic 2D''') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_name = 'NTv2' AND + g.source_crs_auth_name || g.source_crs_code NOT IN + (SELECT auth_name || code FROM geodetic_crs + WHERE type = 'geographic 2D')); + SELECT RAISE(ABORT, 'One grid_transformation with NTv2 has not its target_crs in geodetic_crs table with type = ''geographic 2D''') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_name = 'NTv2' AND + g.target_crs_auth_name || g.target_crs_code NOT IN + (SELECT auth_name || code FROM geodetic_crs + WHERE type = 'geographic 2D')); + -- check that grids with HEIGHT_TO_GEOGRAPHIC3D method are properly registered SELECT RAISE(ABORT, 'One grid_transformation with HEIGHT_TO_GEOGRAPHIC3D has not its source_crs in vertical_crs table') WHERE EXISTS (SELECT * FROM grid_transformation g WHERE @@ -55,12 +79,15 @@ FOR EACH ROW BEGIN -- check that grids with Geographic3D to GravityRelatedHeight method are properly registered SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its target_crs in vertical_crs table') WHERE EXISTS (SELECT * FROM grid_transformation g WHERE - g.method_code LIKE 'Geographic3D to GravityRelatedHeight%' AND + g.deprecated = 0 AND + g.method_name LIKE 'Geographic3D to GravityRelatedHeight%' AND g.target_crs_auth_name || g.target_crs_code NOT IN (SELECT auth_name || code FROM vertical_crs)); SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its source_crs in geodetic_crs table with type = ''geographic 3D''') WHERE EXISTS (SELECT * FROM grid_transformation g WHERE - g.method_code LIKE 'Geographic3D to GravityRelatedHeight%' AND + g.deprecated = 0 AND + g.method_name LIKE 'Geographic3D to GravityRelatedHeight%' AND + NOT (g.auth_name = 'EPSG' AND g.code IN (7648, 7649, 7650)) AND -- those are wrongly registered as they use a geocentric CRS. Reported to EPSG g.source_crs_auth_name || g.source_crs_code NOT IN (SELECT auth_name || code FROM geodetic_crs WHERE type = 'geographic 3D')); -- cgit v1.2.3 From 325ebcafd3111262623c110dd7efb7e515e415fe Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sat, 21 Dec 2019 11:36:20 +0100 Subject: Database: add consistency checks of transformation area of use w.r.t source/target CRS one --- data/sql/commit.sql | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'data/sql/commit.sql') diff --git a/data/sql/commit.sql b/data/sql/commit.sql index 73e41d64..d28f2b9f 100644 --- a/data/sql/commit.sql +++ b/data/sql/commit.sql @@ -92,6 +92,31 @@ FOR EACH ROW BEGIN (SELECT auth_name || code FROM geodetic_crs WHERE type = 'geographic 3D')); + -- check that transformations intersect the area of use of their source/target CRS + -- EPSG, ESRI and IGNF have cases where this does not hold. + SELECT RAISE(ABORT, 'The area of use of at least one coordinate_operation does not intersect the one of its source CRS') + WHERE EXISTS (SELECT * FROM coordinate_operation_view v, crs_view c, area va, area ca WHERE + v.deprecated = 0 AND + v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND + v.source_crs_auth_name = c.auth_name AND + v.source_crs_code = c.code AND + v.area_of_use_auth_name = va.auth_name AND + v.area_of_use_code = va.code AND + c.area_of_use_auth_name = ca.auth_name AND + c.area_of_use_code = ca.code AND + NOT (ca.south_lat < va.north_lat AND va.south_lat < ca.north_lat)); + SELECT RAISE(ABORT, 'The area of use of at least one coordinate_operation does not intersect the one of its target CRS') + WHERE EXISTS (SELECT * FROM coordinate_operation_view v, crs_view c, area va, area ca WHERE + v.deprecated = 0 AND + v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND + v.target_crs_auth_name = c.auth_name AND + v.target_crs_code = c.code AND + v.area_of_use_auth_name = va.auth_name AND + v.area_of_use_code = va.code AND + c.area_of_use_auth_name = ca.auth_name AND + c.area_of_use_code = ca.code AND + NOT (ca.south_lat < va.north_lat AND va.south_lat < ca.north_lat)); + -- check geoid_model table SELECT RAISE(ABORT, 'missing GEOID99 in geoid_model') WHERE NOT EXISTS(SELECT 1 FROM geoid_model WHERE name = 'GEOID99'); -- cgit v1.2.3