diff options
Diffstat (limited to 'data/sql/commit.sql')
| -rw-r--r-- | data/sql/commit.sql | 23 |
1 files changed, 18 insertions, 5 deletions
diff --git a/data/sql/commit.sql b/data/sql/commit.sql index 3394c756..d9adffef 100644 --- a/data/sql/commit.sql +++ b/data/sql/commit.sql @@ -118,21 +118,33 @@ FOR EACH ROW BEGIN 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''') + SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight or Geog3D to Geog2D+XXX has not its source_crs in geodetic_crs table with type = ''geographic 3D''') WHERE EXISTS (SELECT * FROM grid_transformation g WHERE g.deprecated = 0 AND - g.method_name LIKE 'Geographic3D to GravityRelatedHeight%' AND - NOT (g.auth_name = 'EPSG' AND g.code IN (9658,9660)) AND -- those are wrongly registered as they refer to a Geog2D CRS in EPGS v10.017. Reported to EPSG + (g.method_name LIKE 'Geographic3D to %' OR g.method_name LIKE 'Geog3D to %') AND + NOT (g.auth_name = 'EPSG' AND g.code IN (9658,9659,9660,9661)) AND -- those are wrongly registered as they refer to a Geog2D CRS in EPGS v10.017. 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')); + -- check that grids with 'Vertical Offset by Grid Interpolation' methods are properly registered + SELECT RAISE(ABORT, 'One grid_transformation with Vertical Offset by Grid Interpolation has not its source_crs in vertical_crs table') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_name LIKE 'Vertical Offset by Grid Interpolation%' 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 Vertical Offset by Grid Interpolation has not its target_crs in vertical_crs table') + WHERE EXISTS (SELECT * FROM grid_transformation g WHERE + g.method_name LIKE 'Vertical Offset by Grid Interpolation%' AND + g.target_crs_auth_name || g.target_crs_code NOT IN + (SELECT auth_name || code FROM vertical_crs)); + -- 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, usage vu, extent ve, usage cu, extent ce WHERE v.deprecated = 0 AND - v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND + (v.table_name = 'grid_transformation' OR 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 vu.object_table_name = v.table_name AND @@ -149,7 +161,8 @@ FOR EACH ROW BEGIN 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, usage vu, extent ve, usage cu, extent ce WHERE v.deprecated = 0 AND - v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND + ((v.table_name = 'grid_transformation' AND NOT (v.auth_name = 'IGNF' AND v.code = 'TSG1185')) + OR 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 vu.object_table_name = v.table_name AND |