From a9b6f39494e6dab0ea02af9d82e7b3d570f5422f Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Thu, 24 Sep 2020 22:41:59 +0200 Subject: Database: "minimal" update to EPSG v10.003 Content mostly unchanged since v9.9 This update is "minimal" in that it mostly reflects the removal of the 'area' table, replaced now by 'extent', 'scope' and 'usage' Other new aspects of EPSG v10 are left aside. --- data/sql/proj_db_table_defs.sql | 235 ++++++++++++++++------------------------ 1 file changed, 92 insertions(+), 143 deletions(-) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index 3c70b384..d5e8a25c 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -55,7 +55,7 @@ FOR EACH ROW BEGIN WHERE (SELECT type FROM unit_of_measure WHERE auth_name = NEW.uom_auth_name AND code = NEW.uom_code) != 'length'; END; -CREATE TABLE area( +CREATE TABLE extent( 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), @@ -65,10 +65,57 @@ CREATE TABLE area( west_lon FLOAT CHECK (west_lon BETWEEN -180 AND 180), east_lon FLOAT CHECK (east_lon BETWEEN -180 AND 180), deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), - CONSTRAINT pk_area PRIMARY KEY (auth_name, code), - CONSTRAINT check_area_lat CHECK (south_lat <= north_lat) + CONSTRAINT pk_extent PRIMARY KEY (auth_name, code), + CONSTRAINT check_extent_lat CHECK (south_lat <= north_lat) ); +CREATE TABLE scope( + auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), + code TEXT NOT NULL CHECK (length(code) >= 1), + scope TEXT NOT NULL CHECK (length(scope) >= 1), + deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), + CONSTRAINT pk_scope PRIMARY KEY (auth_name, code) +); + +CREATE TABLE usage( + auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), + code TEXT NOT NULL CHECK (length(code) >= 1), + object_table_name TEXT NOT NULL CHECK (object_table_name IN ( + 'geodetic_datum', 'vertical_datum', + 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs', + 'conversion', 'grid_transformation', + 'helmert_transformation', 'other_transformation', 'concatenated_operation')), + object_auth_name TEXT NOT NULL, + object_code TEXT NOT NULL, + extent_auth_name TEXT NOT NULL, + extent_code TEXT NOT NULL, + scope_auth_name TEXT NOT NULL, + scope_code TEXT NOT NULL, + CONSTRAINT pk_usage PRIMARY KEY (auth_name, code), + CONSTRAINT fk_usage_extent FOREIGN KEY (extent_auth_name, extent_code) REFERENCES extent(auth_name, code), + CONSTRAINT fk_usage_scope FOREIGN KEY (scope_auth_name, scope_code) REFERENCES scope(auth_name, code) +); + +CREATE INDEX idx_usage_object ON usage(object_table_name, object_auth_name, object_code); + +CREATE TRIGGER usage_insert_trigger +BEFORE INSERT ON usage +FOR EACH ROW BEGIN + SELECT RAISE(ABORT, 'insert on usage violates constraint: new entry refers to unexisting code') + WHERE NOT EXISTS (SELECT 1 FROM object_view o WHERE o.table_name = NEW.object_table_name AND o.auth_name = NEW.object_auth_name AND o.code = NEW.object_code); + SELECT RAISE(ABORT, 'insert on usage violates constraint: extent must not be deprecated when object is not deprecated') + WHERE EXISTS ( + SELECT 1 FROM extent JOIN object_view o WHERE + NOT (o.table_name IN ('projected_crs', 'conversion') AND o.auth_name = 'ESRI') AND + o.table_name = NEW.object_table_name AND + o.auth_name = NEW.object_auth_name AND + o.code = NEW.object_code AND + extent.auth_name = NEW.extent_auth_name AND + extent.code = NEW.extent_code AND + extent.deprecated = 1 AND + o.deprecated = 0); +END; + CREATE TABLE prime_meridian( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), code TEXT NOT NULL CHECK (length(code) >= 1), @@ -93,19 +140,15 @@ CREATE TABLE geodetic_datum ( code TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, ellipsoid_auth_name TEXT NOT NULL, ellipsoid_code TEXT NOT NULL, prime_meridian_auth_name TEXT NOT NULL, prime_meridian_code TEXT NOT NULL, - area_of_use_auth_name TEXT NOT NULL, - area_of_use_code TEXT NOT NULL, publication_date TEXT, --- YYYY-MM-DD format deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_geodetic_datum PRIMARY KEY (auth_name, code), CONSTRAINT fk_geodetic_datum_ellipsoid FOREIGN KEY (ellipsoid_auth_name, ellipsoid_code) REFERENCES ellipsoid(auth_name, code), - CONSTRAINT fk_geodetic_datum_prime_meridian FOREIGN KEY (prime_meridian_auth_name, prime_meridian_code) REFERENCES prime_meridian(auth_name, code), - CONSTRAINT fk_geodetic_datum_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) + CONSTRAINT fk_geodetic_datum_prime_meridian FOREIGN KEY (prime_meridian_auth_name, prime_meridian_code) REFERENCES prime_meridian(auth_name, code) ); CREATE TRIGGER geodetic_datum_insert_trigger @@ -115,8 +158,6 @@ FOR EACH ROW BEGIN WHERE EXISTS(SELECT 1 FROM ellipsoid WHERE ellipsoid.auth_name = NEW.ellipsoid_auth_name AND ellipsoid.code = NEW.ellipsoid_code AND ellipsoid.deprecated != 0) AND NEW.deprecated = 0; SELECT RAISE(ABORT, 'insert on geodetic_datum violates constraint: prime_meridian must not be deprecated when geodetic_datum is not deprecated') WHERE EXISTS(SELECT 1 FROM prime_meridian WHERE prime_meridian.auth_name = NEW.prime_meridian_auth_name AND prime_meridian.code = NEW.prime_meridian_code AND prime_meridian.deprecated != 0) AND NEW.deprecated = 0; - SELECT RAISE(ABORT, 'insert on geodetic_datum violates constraint: area_of_use must not be deprecated when geodetic_datum is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; CREATE TABLE vertical_datum ( @@ -124,26 +165,15 @@ CREATE TABLE vertical_datum ( 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, publication_date TEXT CHECK (NULL OR length(publication_date) = 10), --- YYYY-MM-DD format deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), - CONSTRAINT pk_vertical_datum PRIMARY KEY (auth_name, code), - CONSTRAINT fk_vertical_datum_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) + CONSTRAINT pk_vertical_datum PRIMARY KEY (auth_name, code) ); -CREATE TRIGGER vertical_datum_insert_trigger -BEFORE INSERT ON vertical_datum -FOR EACH ROW BEGIN - SELECT RAISE(ABORT, 'insert on vertical_datum violates constraint: area_of_use must not be deprecated when vertical_datum is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; -END; - CREATE TABLE coordinate_system( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), code TEXT NOT NULL CHECK (length(code) >= 1), - type TEXT NOT NULL CHECK (type IN ('Cartesian', 'vertical', 'ellipsoidal', 'spherical')), + type TEXT NOT NULL CHECK (type IN ('Cartesian', 'vertical', 'ellipsoidal', 'spherical', 'ordinal')), dimension SMALLINT NOT NULL CHECK (dimension BETWEEN 1 AND 3), CONSTRAINT pk_coordinate_system PRIMARY KEY (auth_name, code), CONSTRAINT check_cs_vertical CHECK (type != 'vertical' OR dimension = 1), @@ -160,8 +190,8 @@ CREATE TABLE axis( coordinate_system_auth_name TEXT NOT NULL, coordinate_system_code TEXT NOT NULL, coordinate_system_order SMALLINT NOT NULL CHECK (coordinate_system_order BETWEEN 1 AND 3), - uom_auth_name TEXT NOT NULL, - uom_code TEXT NOT NULL, + uom_auth_name TEXT, + uom_code TEXT, CONSTRAINT pk_axis PRIMARY KEY (auth_name, code), CONSTRAINT fk_axis_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code), CONSTRAINT fk_axis_unit_of_measure FOREIGN KEY (uom_auth_name, uom_code) REFERENCES unit_of_measure(auth_name, code) @@ -172,6 +202,8 @@ BEFORE INSERT ON axis FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on axis violates constraint: coordinate_system_order should be <= coordinate_system.dimension') WHERE NEW.coordinate_system_order > (SELECT dimension FROM coordinate_system WHERE auth_name = NEW.coordinate_system_auth_name AND code = NEW.coordinate_system_code); + SELECT RAISE(ABORT, 'insert on axis violates constraint: uom should be defined unless the coordinate system is ordinal') + WHERE EXISTS(SELECT 1 FROM coordinate_system cs WHERE cs.type != 'ordinal' AND (NEW.uom_auth_name IS NULL OR NEW.uom_code IS NULL) AND cs.auth_name = NEW.coordinate_system_auth_name AND cs.code = NEW.coordinate_system_code); END; CREATE TABLE geodetic_crs( @@ -179,25 +211,20 @@ CREATE TABLE geodetic_crs( code TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, type TEXT NOT NULL CHECK (type IN ('geographic 2D', 'geographic 3D', 'geocentric')), coordinate_system_auth_name TEXT, coordinate_system_code TEXT, datum_auth_name TEXT, datum_code TEXT, - area_of_use_auth_name TEXT, - area_of_use_code TEXT, text_definition TEXT, -- PROJ string or WKT string. Use of this is discouraged as prone to definition ambiguities deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_geodetic_crs PRIMARY KEY (auth_name, code), CONSTRAINT fk_geodetic_crs_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code), CONSTRAINT fk_geodetic_crs_datum FOREIGN KEY (datum_auth_name, datum_code) REFERENCES geodetic_datum(auth_name, code), - CONSTRAINT fk_geodetic_crs_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code), CONSTRAINT check_geodetic_crs_cs CHECK (NOT ((coordinate_system_auth_name IS NULL OR coordinate_system_code IS NULL) AND text_definition IS NULL)), CONSTRAINT check_geodetic_crs_cs_bis CHECK (NOT ((NOT(coordinate_system_auth_name IS NULL OR coordinate_system_code IS NULL)) AND text_definition IS NOT NULL)), CONSTRAINT check_geodetic_crs_datum CHECK (NOT ((datum_auth_name IS NULL OR datum_code IS NULL) AND text_definition IS NULL)), - CONSTRAINT check_geodetic_crs_datum_bis CHECK (NOT ((NOT(datum_auth_name IS NULL OR datum_code IS NULL)) AND text_definition IS NOT NULL)), - CONSTRAINT check_geodetic_crs_area CHECK (NOT ((area_of_use_auth_name IS NULL OR area_of_use_code IS NULL) AND text_definition IS NULL)) + CONSTRAINT check_geodetic_crs_datum_bis CHECK (NOT ((NOT(datum_auth_name IS NULL OR datum_code IS NULL)) AND text_definition IS NOT NULL)) ); CREATE TRIGGER geodetic_crs_insert_trigger @@ -210,9 +237,6 @@ FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on geodetic_crs violates constraint: datum must not be deprecated when geodetic_crs is not deprecated') WHERE EXISTS(SELECT 1 FROM geodetic_datum datum WHERE datum.auth_name = NEW.datum_auth_name AND datum.code = NEW.datum_code AND datum.deprecated != 0) AND NEW.deprecated = 0 AND NEW.text_definition IS NOT NULL; - SELECT RAISE(ABORT, 'insert on geodetic_crs violates constraint: area_of_use must not be deprecated when geodetic_crs is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0 AND NEW.text_definition IS NOT NULL; - SELECT RAISE(ABORT, 'insert on geodetic_crs violates constraint: coordinate_system.dimension must be 3 for type = ''geocentric''') WHERE NEW.type = 'geocentric' AND (SELECT dimension FROM coordinate_system WHERE coordinate_system.auth_name = NEW.coordinate_system_auth_name AND coordinate_system.code = NEW.coordinate_system_code) != 3; @@ -234,18 +258,14 @@ CREATE TABLE vertical_crs( code TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, coordinate_system_auth_name TEXT NOT NULL, coordinate_system_code TEXT NOT NULL, datum_auth_name TEXT NOT NULL, datum_code TEXT NOT NULL, - area_of_use_auth_name TEXT NOT NULL, - area_of_use_code TEXT NOT NULL, deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_vertical_crs PRIMARY KEY (auth_name, code), CONSTRAINT fk_vertical_crs_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code), - CONSTRAINT fk_vertical_crs_datum FOREIGN KEY (datum_auth_name, datum_code) REFERENCES vertical_datum(auth_name, code), - CONSTRAINT fk_vertical_crs_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) + CONSTRAINT fk_vertical_crs_datum FOREIGN KEY (datum_auth_name, datum_code) REFERENCES vertical_datum(auth_name, code) ); CREATE TRIGGER vertical_crs_insert_trigger @@ -258,9 +278,6 @@ FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on vertical_crs violates constraint: datum must not be deprecated when vertical_crs is not deprecated') WHERE EXISTS(SELECT 1 FROM vertical_crs datum WHERE datum.auth_name = NEW.datum_auth_name AND datum.code = NEW.datum_code AND datum.deprecated != 0) AND NEW.deprecated = 0; - SELECT RAISE(ABORT, 'insert on vertical_crs violates constraint: area_of_use must not be deprecated when vertical_crs is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; - SELECT RAISE(ABORT, 'insert on vertical_crs violates constraint: coordinate_system.type must be ''vertical''') WHERE (SELECT type FROM coordinate_system WHERE coordinate_system.auth_name = NEW.coordinate_system_auth_name AND coordinate_system.code = NEW.coordinate_system_code) != 'vertical'; SELECT RAISE(ABORT, 'insert on vertical_crs violates constraint: coordinate_system.dimension must be 1') @@ -289,10 +306,6 @@ CREATE TABLE conversion_table( 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), @@ -350,7 +363,6 @@ CREATE TABLE conversion_table( deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_conversion PRIMARY KEY (auth_name, code), - CONSTRAINT fk_conversion_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code), CONSTRAINT fk_conversion_method FOREIGN KEY (method_auth_name, method_code) REFERENCES conversion_method(auth_name, code), --CONSTRAINT fk_conversion_coordinate_operation FOREIGN KEY (auth_name, code) REFERENCES coordinate_operation(auth_name, code), CONSTRAINT fk_conversion_param1_uom FOREIGN KEY (param1_uom_auth_name, param1_uom_code) REFERENCES unit_of_measure(auth_name, code), @@ -368,10 +380,6 @@ CREATE VIEW conversion AS SELECT c.name, c.description, - c.scope, - - c.area_of_use_auth_name, - c.area_of_use_code, c.method_auth_name, c.method_code, @@ -584,10 +592,6 @@ INSERT INTO conversion_table VALUES NEW.name, NEW.description, - NEW.scope, - - NEW.area_of_use_auth_name, - NEW.area_of_use_code, NEW.method_auth_name, NEW.method_code, @@ -651,27 +655,22 @@ CREATE TABLE projected_crs( code TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, coordinate_system_auth_name TEXT, coordinate_system_code TEXT, geodetic_crs_auth_name TEXT, geodetic_crs_code TEXT, conversion_auth_name TEXT, conversion_code TEXT, - area_of_use_auth_name TEXT, - area_of_use_code TEXT, text_definition TEXT, -- PROJ string or WKT string. Use of this is discouraged as prone to definition ambiguities deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_projected_crs PRIMARY KEY (auth_name, code), CONSTRAINT fk_projected_crs_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code), CONSTRAINT fk_projected_crs_geodetic_crs FOREIGN KEY (geodetic_crs_auth_name, geodetic_crs_code) REFERENCES geodetic_crs(auth_name, code), CONSTRAINT fk_projected_crs_conversion FOREIGN KEY (conversion_auth_name, conversion_code) REFERENCES conversion_table(auth_name, code), - CONSTRAINT fk_projected_crs_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code), CONSTRAINT check_projected_crs_cs CHECK (NOT((coordinate_system_auth_name IS NULL OR coordinate_system_code IS NULL) AND text_definition IS NULL)), CONSTRAINT check_projected_crs_cs_bis CHECK (NOT((NOT(coordinate_system_auth_name IS NULL OR coordinate_system_code IS NULL)) AND text_definition IS NOT NULL)), CONSTRAINT check_projected_crs_geodetic_crs CHECK (NOT((geodetic_crs_auth_name IS NULL OR geodetic_crs_code IS NULL) AND text_definition IS NULL)), - CONSTRAINT check_projected_crs_conversion CHECK (NOT((NOT(conversion_auth_name IS NULL OR conversion_code IS NULL)) AND text_definition IS NOT NULL)), - CONSTRAINT check_projected_crs_area CHECK (NOT((area_of_use_auth_name IS NULL OR area_of_use_code IS NULL) AND text_definition IS NULL)) + CONSTRAINT check_projected_crs_conversion CHECK (NOT((NOT(conversion_auth_name IS NULL OR conversion_code IS NULL)) AND text_definition IS NOT NULL)) ); CREATE TRIGGER projected_crs_insert_trigger @@ -693,9 +692,6 @@ FOR EACH ROW BEGIN --SELECT RAISE(ABORT, 'insert on projected_crs violates constraint: geodetic_crs must NOT be defined when text_definition is NOT NULL') -- WHERE (NOT(NEW.geodetic_crs_auth_name IS NULL OR NEW.geodetic_crs_code IS NULL)) AND NEW.text_definition IS NOT NULL; - SELECT RAISE(ABORT, 'insert on projected_crs violates constraint: area_of_use must not be deprecated when projected_crs is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0 AND NEW.text_definition IS NOT NULL; - SELECT RAISE(ABORT, 'insert on projected_crs violates constraint: coordinate_system.type must be ''cartesian''') WHERE (SELECT type FROM coordinate_system WHERE coordinate_system.auth_name = NEW.coordinate_system_auth_name AND coordinate_system.code = NEW.coordinate_system_code) != 'Cartesian'; @@ -709,17 +705,13 @@ CREATE TABLE compound_crs( code TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, horiz_crs_auth_name TEXT NOT NULL, horiz_crs_code TEXT NOT NULL, vertical_crs_auth_name TEXT NOT NULL, vertical_crs_code TEXT NOT NULL, - area_of_use_auth_name TEXT NOT NULL, - area_of_use_code TEXT NOT NULL, deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_compound_crs PRIMARY KEY (auth_name, code), - CONSTRAINT fk_compound_crs_vertical_crs FOREIGN KEY (vertical_crs_auth_name, vertical_crs_code) REFERENCES vertical_crs(auth_name, code), - CONSTRAINT fk_compoundcrs_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) + CONSTRAINT fk_compound_crs_vertical_crs FOREIGN KEY (vertical_crs_auth_name, vertical_crs_code) REFERENCES vertical_crs(auth_name, code) ); CREATE TRIGGER compound_crs_insert_trigger @@ -743,9 +735,6 @@ FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on compound_crs violates constraint: vertical_crs must not be deprecated when compound_crs is not deprecated') WHERE EXISTS (SELECT 1 FROM vertical_crs WHERE vertical_crs.auth_name = NEW.vertical_crs_auth_name AND vertical_crs.code = NEW.vertical_crs_code AND vertical_crs.deprecated != 0) AND NEW.deprecated = 0; - - SELECT RAISE(ABORT, 'insert on compound_crs violates constraint: area_of_use must not be deprecated when compound_crs is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; CREATE TABLE coordinate_operation_method( @@ -762,7 +751,6 @@ CREATE TABLE helmert_transformation_table( 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), @@ -773,9 +761,6 @@ CREATE TABLE helmert_transformation_table( 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, @@ -820,7 +805,6 @@ CREATE TABLE helmert_transformation_table( CONSTRAINT pk_helmert_transformation PRIMARY KEY (auth_name, code), CONSTRAINT fk_helmert_transformation_source_crs FOREIGN KEY (source_crs_auth_name, source_crs_code) REFERENCES geodetic_crs(auth_name, code), CONSTRAINT fk_helmert_transformation_target_crs FOREIGN KEY (target_crs_auth_name, target_crs_code) REFERENCES geodetic_crs(auth_name, code), - CONSTRAINT fk_helmert_transformation_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code), CONSTRAINT fk_helmert_transformation_method FOREIGN KEY (method_auth_name, method_code) REFERENCES coordinate_operation_method(auth_name, code), --CONSTRAINT fk_helmert_transformation_coordinate_operation FOREIGN KEY (auth_name, code) REFERENCES coordinate_operation(auth_name, code), CONSTRAINT fk_helmert_translation_uom FOREIGN KEY (translation_uom_auth_name, translation_uom_code) REFERENCES unit_of_measure(auth_name, code), @@ -839,7 +823,6 @@ CREATE VIEW helmert_transformation AS SELECT h.name, h.description, - h.scope, h.method_auth_name, h.method_code, @@ -850,9 +833,6 @@ CREATE VIEW helmert_transformation AS SELECT h.target_crs_auth_name, h.target_crs_code, - h.area_of_use_auth_name, - h.area_of_use_code, - h.accuracy, h.tx, @@ -916,7 +896,6 @@ INSERT INTO helmert_transformation_table VALUES NEW.name, NEW.description, - NEW.scope, NEW.method_auth_name, NEW.method_code, @@ -927,9 +906,6 @@ INSERT INTO helmert_transformation_table VALUES NEW.target_crs_auth_name, NEW.target_crs_code, - NEW.area_of_use_auth_name, - NEW.area_of_use_code, - NEW.accuracy, NEW.tx, @@ -996,11 +972,9 @@ FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on helmert_transformation violates constraint: pivot_uom.type must be ''length''') WHERE (SELECT type FROM unit_of_measure WHERE unit_of_measure.auth_name = NEW.pivot_uom_auth_name AND unit_of_measure.code = NEW.pivot_uom_code) != 'length'; SELECT RAISE(ABORT, 'insert on helmert_transformation violates constraint: source_crs must not be deprecated when helmert_transformation is not deprecated') - WHERE EXISTS(SELECT 1 FROM geodetic_crs crs WHERE crs.auth_name = NEW.source_crs_auth_name AND crs.code = NEW.source_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); + WHERE EXISTS(SELECT 1 FROM geodetic_crs crs WHERE crs.auth_name = NEW.source_crs_auth_name AND crs.code = NEW.source_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI') AND NOT (NEW.auth_name = 'EPSG' AND NEW.code = '5375'); -- Issue with EPSG:5375 "SIRGAS-Chile to WGS 84 (1)" SELECT RAISE(ABORT, 'insert on helmert_transformation violates constraint: target_crs must not be deprecated when helmert_transformation is not deprecated') WHERE EXISTS(SELECT 1 FROM geodetic_crs crs WHERE crs.auth_name = NEW.target_crs_auth_name AND crs.code = NEW.target_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); - SELECT RAISE(ABORT, 'insert on helmert_transformation violates constraint: area_of_use must not be deprecated when helmert_transformation is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; CREATE TABLE grid_transformation( @@ -1009,7 +983,6 @@ CREATE TABLE grid_transformation( 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), @@ -1020,9 +993,6 @@ CREATE TABLE grid_transformation( 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), grid_param_auth_name TEXT NOT NULL, @@ -1046,8 +1016,7 @@ CREATE TABLE grid_transformation( --CONSTRAINT fk_grid_transformation_coordinate_operation FOREIGN KEY (auth_name, code) REFERENCES coordinate_operation(auth_name, code), --CONSTRAINT fk_grid_transformation_source_crs FOREIGN KEY (source_crs_auth_name, source_crs_code) REFERENCES crs(auth_name, code), --CONSTRAINT fk_grid_transformation_target_crs FOREIGN KEY (target_crs_auth_name, target_crs_code) REFERENCES crs(auth_name, code), - CONSTRAINT fk_grid_transformation_interpolation_crs FOREIGN KEY (interpolation_crs_auth_name, interpolation_crs_code) REFERENCES geodetic_crs(auth_name, code), - CONSTRAINT fk_grid_transformation_transformation_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) + CONSTRAINT fk_grid_transformation_interpolation_crs FOREIGN KEY (interpolation_crs_auth_name, interpolation_crs_code) REFERENCES geodetic_crs(auth_name, code) ); CREATE TRIGGER grid_transformation_insert_trigger @@ -1066,8 +1035,6 @@ FOR EACH ROW BEGIN WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.source_crs_auth_name AND crs.code = NEW.source_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); SELECT RAISE(ABORT, 'insert on grid_transformation violates constraint: target_crs must not be deprecated when grid_transformation is not deprecated') WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.target_crs_auth_name AND crs.code = NEW.target_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); - SELECT RAISE(ABORT, 'insert on grid_transformation violates constraint: area_of_use must not be deprecated when grid_transformation is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; -- Table that describe packages/archives that contain several grids @@ -1128,7 +1095,6 @@ CREATE TABLE other_transformation( name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, -- if method_auth_name = 'PROJ', method_code can be 'PROJString' for a -- PROJ string and then method_name is a PROJ string (typically a pipeline) @@ -1143,9 +1109,6 @@ CREATE TABLE other_transformation( 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), param1_auth_name TEXT, @@ -1205,7 +1168,6 @@ CREATE TABLE other_transformation( --CONSTRAINT fk_other_transformation_coordinate_operation FOREIGN KEY (auth_name, code) REFERENCES coordinate_operation(auth_name, code), --CONSTRAINT fk_other_transformation_source_crs FOREIGN1 KEY (source_crs_auth_name, source_crs_code) REFERENCES crs(auth_name, code), --CONSTRAINT fk_other_transformation_target_crs FOREIGN KEY (target_crs_auth_name, target_crs_code) REFERENCES crs(auth_name, code), - CONSTRAINT fk_other_transformation_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) CONSTRAINT fk_other_transformation_param1_uom FOREIGN KEY (param1_uom_auth_name, param1_uom_code) REFERENCES unit_of_measure(auth_name, code), CONSTRAINT fk_other_transformation_param2_uom FOREIGN KEY (param2_uom_auth_name, param2_uom_code) REFERENCES unit_of_measure(auth_name, code), CONSTRAINT fk_other_transformation_param3_uom FOREIGN KEY (param3_uom_auth_name, param3_uom_code) REFERENCES unit_of_measure(auth_name, code), @@ -1232,8 +1194,6 @@ FOR EACH ROW BEGIN WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.source_crs_auth_name AND crs.code = NEW.source_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); SELECT RAISE(ABORT, 'insert on other_transformation violates constraint: target_crs must not be deprecated when other_transformation is not deprecated') WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.target_crs_auth_name AND crs.code = NEW.target_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); - SELECT RAISE(ABORT, 'insert on other_transformation violates constraint: area_of_use must not be deprecated when other_transformation is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; -- Note: in EPSG, the steps might be to be chained in reverse order, so we cannot @@ -1244,27 +1204,22 @@ CREATE TABLE concatenated_operation( name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, - scope TEXT, 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), operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), - CONSTRAINT pk_concatenated_operation PRIMARY KEY (auth_name, code), + CONSTRAINT pk_concatenated_operation PRIMARY KEY (auth_name, code) --CONSTRAINT fk_concatenated_operation_coordinate_operation FOREIGN KEY (auth_name, code) REFERENCES coordinate_operation(auth_name, code), --CONSTRAINT fk_concatenated_operation_source_crs FOREIGN KEY (source_crs_auth_name, source_crs_code) REFERENCES crs(auth_name, code), --CONSTRAINT fk_concatenated_operation_target_crs FOREIGN KEY (target_crs_auth_name, target_crs_code) REFERENCES crs(auth_name, code), - CONSTRAINT fk_concatenated_operation_transformation_area FOREIGN KEY (area_of_use_auth_name, area_of_use_code) REFERENCES area(auth_name, code) ); CREATE TRIGGER concatenated_operation_insert_trigger @@ -1284,8 +1239,6 @@ FOR EACH ROW BEGIN WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.source_crs_auth_name AND crs.code = NEW.source_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); SELECT RAISE(ABORT, 'insert on concatenated_operation violates constraint: target_crs must not be deprecated when concatenated_operation is not deprecated') WHERE EXISTS(SELECT 1 FROM crs_view crs WHERE crs.auth_name = NEW.target_crs_auth_name AND crs.code = NEW.target_crs_code AND crs.deprecated != 0) AND NEW.deprecated = 0 AND NOT (NEW.auth_name = 'ESRI'); - SELECT RAISE(ABORT, 'insert on concatenated_operation violates constraint: area_of_use must not be deprecated when concatenated_operation is not deprecated') - WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0; END; CREATE TABLE concatenated_operation_step( @@ -1331,7 +1284,7 @@ END; CREATE TABLE alias_name( table_name TEXT NOT NULL CHECK (table_name IN ( 'unit_of_measure', 'celestial_body', 'ellipsoid', - 'area', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', + 'extent', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', 'helmert_transformation', 'other_transformation', 'concatenated_operation')), auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), @@ -1355,14 +1308,14 @@ END; CREATE TABLE supersession( superseded_table_name TEXT NOT NULL CHECK (superseded_table_name IN ( 'unit_of_measure', 'celestial_body', 'ellipsoid', - 'area', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', + 'extent', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', 'helmert_transformation', 'other_transformation', 'concatenated_operation')), superseded_auth_name TEXT NOT NULL, superseded_code TEXT NOT NULL, replacement_table_name TEXT NOT NULL CHECK (replacement_table_name IN ( 'unit_of_measure', 'celestial_body', 'ellipsoid', - 'area', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', + 'extent', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', 'helmert_transformation', 'other_transformation', 'concatenated_operation')), replacement_auth_name TEXT NOT NULL, @@ -1387,7 +1340,7 @@ END; CREATE TABLE deprecation( table_name TEXT NOT NULL CHECK (table_name IN ( 'unit_of_measure', 'celestial_body', 'ellipsoid', - 'area', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', + 'extent', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', 'helmert_transformation', 'other_transformation', 'concatenated_operation')), deprecated_auth_name TEXT NOT NULL, @@ -1411,31 +1364,27 @@ END; CREATE VIEW coordinate_operation_view AS SELECT 'grid_transformation' AS table_name, auth_name, code, name, - description, scope, + description, method_auth_name, method_code, method_name, source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code, - area_of_use_auth_name, area_of_use_code, accuracy, deprecated FROM grid_transformation UNION ALL SELECT 'helmert_transformation' AS table_name, auth_name, code, name, - description, scope, + description, method_auth_name, method_code, method_name, source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code, - area_of_use_auth_name, area_of_use_code, accuracy, deprecated FROM helmert_transformation UNION ALL SELECT 'other_transformation' AS table_name, auth_name, code, name, - description, scope, + description, method_auth_name, method_code, method_name, source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code, - area_of_use_auth_name, area_of_use_code, accuracy, deprecated FROM other_transformation UNION ALL SELECT 'concatenated_operation' AS table_name, auth_name, code, name, - description, scope, + description, NULL, NULL, NULL, source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code, - area_of_use_auth_name, area_of_use_code, accuracy, deprecated FROM concatenated_operation ; @@ -1445,48 +1394,44 @@ CREATE VIEW coordinate_operation_with_conversion_view AS CREATE VIEW crs_view AS SELECT 'geodetic_crs' AS table_name, auth_name, code, name, type, - description, scope, - area_of_use_auth_name, area_of_use_code, + description, deprecated FROM geodetic_crs UNION ALL SELECT 'projected_crs' AS table_name, auth_name, code, name, 'projected', - description, scope, - area_of_use_auth_name, area_of_use_code, + description, deprecated FROM projected_crs UNION ALL SELECT 'vertical_crs' AS table_name, auth_name, code, name, 'vertical', - description, scope, - area_of_use_auth_name, area_of_use_code, + description, deprecated FROM vertical_crs UNION ALL SELECT 'compound_crs' AS table_name, auth_name, code, name, 'compound', - description, scope, - area_of_use_auth_name, area_of_use_code, + description, deprecated FROM compound_crs ; CREATE VIEW object_view AS - SELECT 'unit_of_measure' AS table_name, auth_name, code, name, NULL as type, NULL as area_of_use_auth_name, NULL as area_of_use_code, deprecated FROM unit_of_measure + SELECT 'unit_of_measure' AS table_name, auth_name, code, name, NULL as type, deprecated FROM unit_of_measure UNION ALL - SELECT 'celestial_body', auth_name, code, name, NULL, NULL, NULL, 0 FROM celestial_body + SELECT 'celestial_body', auth_name, code, name, NULL, 0 FROM celestial_body UNION ALL - SELECT 'ellipsoid', auth_name, code, name, NULL, NULL, NULL, deprecated FROM ellipsoid + SELECT 'ellipsoid', auth_name, code, name, NULL, deprecated FROM ellipsoid UNION ALL - SELECT 'area', auth_name, code, name, NULL, NULL, NULL, deprecated FROM area + SELECT 'extent', auth_name, code, name, NULL, deprecated FROM extent UNION ALL - SELECT 'prime_meridian', auth_name, code, name, NULL, NULL, NULL, deprecated FROM prime_meridian + SELECT 'prime_meridian', auth_name, code, name, NULL, deprecated FROM prime_meridian UNION ALL - SELECT 'geodetic_datum', auth_name, code, name, NULL, area_of_use_auth_name, area_of_use_code, deprecated FROM geodetic_datum + SELECT 'geodetic_datum', auth_name, code, name, NULL, deprecated FROM geodetic_datum UNION ALL - SELECT 'vertical_datum', auth_name, code, name, NULL, area_of_use_auth_name, area_of_use_code, deprecated FROM vertical_datum + SELECT 'vertical_datum', auth_name, code, name, NULL, deprecated FROM vertical_datum UNION ALL - SELECT 'axis', auth_name, code, name, NULL, NULL, NULL, 0 as deprecated FROM axis + SELECT 'axis', auth_name, code, name, NULL, 0 as deprecated FROM axis UNION ALL - SELECT table_name, auth_name, code, name, type, area_of_use_auth_name, area_of_use_code, deprecated FROM crs_view + SELECT table_name, auth_name, code, name, type, deprecated FROM crs_view UNION ALL - SELECT 'conversion', auth_name, code, name, NULL, area_of_use_auth_name, area_of_use_code, deprecated FROM conversion_table + SELECT 'conversion', auth_name, code, name, NULL, deprecated FROM conversion_table UNION ALL - SELECT table_name, auth_name, code, name, NULL, area_of_use_auth_name, area_of_use_code, deprecated FROM coordinate_operation_view + SELECT table_name, auth_name, code, name, NULL, deprecated FROM coordinate_operation_view ; CREATE VIEW authority_list AS @@ -1496,7 +1441,11 @@ CREATE VIEW authority_list AS UNION SELECT DISTINCT auth_name FROM ellipsoid UNION - SELECT DISTINCT auth_name FROM area + SELECT DISTINCT auth_name FROM extent + UNION + SELECT DISTINCT auth_name FROM scope + UNION + SELECT DISTINCT auth_name FROM usage UNION SELECT DISTINCT auth_name FROM prime_meridian UNION -- cgit v1.2.3 From 4c992038ea01ead56df12f468f29325f7ca9e43d Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Mon, 5 Oct 2020 14:36:33 +0200 Subject: Database: add a reference_frame_epoch column to the geodetic_datum for dynamic datums, but not yet used --- data/sql/proj_db_table_defs.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index d5e8a25c..7e182e11 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -145,6 +145,7 @@ CREATE TABLE geodetic_datum ( prime_meridian_auth_name TEXT NOT NULL, prime_meridian_code TEXT NOT NULL, publication_date TEXT, --- YYYY-MM-DD format + frame_reference_epoch FLOAT, --- only set for dynamic datum, and should be set when it is a dynamic datum deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_geodetic_datum PRIMARY KEY (auth_name, code), CONSTRAINT fk_geodetic_datum_ellipsoid FOREIGN KEY (ellipsoid_auth_name, ellipsoid_code) REFERENCES ellipsoid(auth_name, code), -- cgit v1.2.3 From ff9a386c72afb277aa79b86c7efb54e9843e810a Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Tue, 6 Oct 2020 17:08:29 +0200 Subject: Database: import datum ensemble accuracy and members (but do not use them) --- data/sql/proj_db_table_defs.sql | 30 ++++++++++++++++++++++++++++-- 1 file changed, 28 insertions(+), 2 deletions(-) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index 7e182e11..1862429a 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -146,6 +146,7 @@ CREATE TABLE geodetic_datum ( prime_meridian_code TEXT NOT NULL, publication_date TEXT, --- YYYY-MM-DD format frame_reference_epoch FLOAT, --- only set for dynamic datum, and should be set when it is a dynamic datum + ensemble_accuracy FLOAT CHECK (ensemble_accuracy IS NULL OR ensemble_accuracy > 0), --- only for a datum ensemble. and should be set when it is a datum ensemble deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_geodetic_datum PRIMARY KEY (auth_name, code), CONSTRAINT fk_geodetic_datum_ellipsoid FOREIGN KEY (ellipsoid_auth_name, ellipsoid_code) REFERENCES ellipsoid(auth_name, code), @@ -159,18 +160,43 @@ FOR EACH ROW BEGIN WHERE EXISTS(SELECT 1 FROM ellipsoid WHERE ellipsoid.auth_name = NEW.ellipsoid_auth_name AND ellipsoid.code = NEW.ellipsoid_code AND ellipsoid.deprecated != 0) AND NEW.deprecated = 0; SELECT RAISE(ABORT, 'insert on geodetic_datum violates constraint: prime_meridian must not be deprecated when geodetic_datum is not deprecated') WHERE EXISTS(SELECT 1 FROM prime_meridian WHERE prime_meridian.auth_name = NEW.prime_meridian_auth_name AND prime_meridian.code = NEW.prime_meridian_code AND prime_meridian.deprecated != 0) AND NEW.deprecated = 0; + SELECT RAISE(ABORT, 'frame_reference_epoch and ensemble_accuracy are mutually exclusive') + WHERE NEW.frame_reference_epoch IS NOT NULL AND NEW.ensemble_accuracy IS NOT NULL; END; +CREATE TABLE geodetic_datum_ensemble_member ( + ensemble_auth_name TEXT NOT NULL, + ensemble_code TEXT NOT NULL, + member_auth_name TEXT NOT NULL, + member_code TEXT NOT NULL, + sequence INTEGER NOT NULL CHECK (sequence >= 1), + CONSTRAINT fk_geodetic_datum_ensemble_member_ensemble FOREIGN KEY (ensemble_auth_name, ensemble_code) REFERENCES geodetic_datum(auth_name, code), + CONSTRAINT fk_geodetic_datum_ensemble_member_ensemble_member FOREIGN KEY (member_auth_name, member_code) REFERENCES geodetic_datum(auth_name, code), + CONSTRAINT unique_geodetic_datum_ensemble_member UNIQUE (ensemble_auth_name, ensemble_code, sequence) +); + CREATE TABLE vertical_datum ( 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, publication_date TEXT CHECK (NULL OR length(publication_date) = 10), --- YYYY-MM-DD format + ensemble_accuracy FLOAT CHECK (ensemble_accuracy IS NULL OR ensemble_accuracy > 0), --- only for a datum ensemble. and should be set when it is a datum ensemble deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_vertical_datum PRIMARY KEY (auth_name, code) ); +CREATE TABLE vertical_datum_ensemble_member ( + ensemble_auth_name TEXT NOT NULL, + ensemble_code TEXT NOT NULL, + member_auth_name TEXT NOT NULL, + member_code TEXT NOT NULL, + sequence INTEGER NOT NULL CHECK (sequence >= 1), + CONSTRAINT fk_vertical_datum_ensemble_member_ensemble FOREIGN KEY (ensemble_auth_name, ensemble_code) REFERENCES vertical_datum(auth_name, code), + CONSTRAINT fk_vertical_datum_ensemble_member_ensemble_member FOREIGN KEY (member_auth_name, member_code) REFERENCES vertical_datum(auth_name, code), + CONSTRAINT unique_vertical_datum_ensemble_member UNIQUE (ensemble_auth_name, ensemble_code, sequence) +); + CREATE TABLE coordinate_system( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), code TEXT NOT NULL CHECK (length(code) >= 1), @@ -1422,9 +1448,9 @@ CREATE VIEW object_view AS UNION ALL SELECT 'prime_meridian', auth_name, code, name, NULL, deprecated FROM prime_meridian UNION ALL - SELECT 'geodetic_datum', auth_name, code, name, NULL, deprecated FROM geodetic_datum + SELECT 'geodetic_datum', auth_name, code, name, CASE WHEN ensemble_accuracy IS NOT NULL THEN "ensemble" ELSE "datum" END, deprecated FROM geodetic_datum UNION ALL - SELECT 'vertical_datum', auth_name, code, name, NULL, deprecated FROM vertical_datum + SELECT 'vertical_datum', auth_name, code, name, CASE WHEN ensemble_accuracy IS NOT NULL THEN "ensemble" ELSE "datum" END, deprecated FROM vertical_datum UNION ALL SELECT 'axis', auth_name, code, name, NULL, 0 as deprecated FROM axis UNION ALL -- cgit v1.2.3 From ad80dc88a70032e1d96812b574b2d4d22e223394 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sun, 11 Oct 2020 19:50:09 +0200 Subject: Database: add a frame_reference_epoch column in vertical_datum to be able to handle dynamic vertical datums, and instanciate them properly from database --- data/sql/proj_db_table_defs.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index 1862429a..60a12209 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -181,6 +181,7 @@ CREATE TABLE vertical_datum ( name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, publication_date TEXT CHECK (NULL OR length(publication_date) = 10), --- YYYY-MM-DD format + frame_reference_epoch FLOAT, --- only set for dynamic datum, and should be set when it is a dynamic datum ensemble_accuracy FLOAT CHECK (ensemble_accuracy IS NULL OR ensemble_accuracy > 0), --- only for a datum ensemble. and should be set when it is a datum ensemble deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_vertical_datum PRIMARY KEY (auth_name, code) -- cgit v1.2.3