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