diff options
| -rw-r--r-- | data/sql/proj_db_table_defs.sql | 223 |
1 files changed, 117 insertions, 106 deletions
diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index d7d5a93b..08f74c34 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -1,5 +1,16 @@ --- Table structures +-- Note on the INTEGER_OR_TEXT data type. This is a "non-standard" type +-- declaration, but this is perfectly legal as SQLite is loosely typed. +-- As this declaration contains the string INT, it is assigned INTEGER affinity. +-- Which means that values provided either as text (that contains integer value) +-- or integer will be stored as integers, whereas text values will be stored as +-- text. See paragraph 3 and 3.1 of https://www.sqlite.org/datatype3.html. +-- The "INTEGER_OR_TEXT" name is a hint for the user, and software like +-- GDAL (>= 3.3) to expose the column as string... +-- The effect of using this rather than TEXT is making the DB size go from +-- 9 MB to 8.4. + CREATE TABLE metadata( key TEXT NOT NULL PRIMARY KEY CHECK (length(key) >= 1), value TEXT NOT NULL @@ -7,7 +18,7 @@ CREATE TABLE metadata( CREATE TABLE unit_of_measure( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), type TEXT NOT NULL CHECK (type IN ('length', 'angle', 'scale', 'time')), conv_factor FLOAT, @@ -18,7 +29,7 @@ CREATE TABLE unit_of_measure( CREATE TABLE celestial_body ( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), semi_major_axis FLOAT NOT NULL CHECK (semi_major_axis > 0), -- approximate (in metre) CONSTRAINT pk_celestial_body PRIMARY KEY (auth_name, code) @@ -28,14 +39,14 @@ INSERT INTO celestial_body VALUES('PROJ', 'EARTH', 'Earth', 6378137.0); CREATE TABLE ellipsoid ( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, celestial_body_auth_name TEXT NOT NULL, - celestial_body_code TEXT NOT NULL, + celestial_body_code INTEGER_OR_TEXT NOT NULL, semi_major_axis FLOAT NOT NULL CHECK (semi_major_axis > 0), uom_auth_name TEXT NOT NULL, - uom_code TEXT NOT NULL, + uom_code INTEGER_OR_TEXT NOT NULL, inv_flattening FLOAT CHECK (inv_flattening = 0 OR inv_flattening >= 1.0), semi_minor_axis FLOAT CHECK (semi_minor_axis > 0 AND semi_minor_axis <= semi_major_axis), deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), @@ -54,7 +65,7 @@ END; CREATE TABLE extent( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT NOT NULL, south_lat FLOAT CHECK (south_lat BETWEEN -90 AND 90), @@ -68,7 +79,7 @@ CREATE TABLE extent( CREATE TABLE scope( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_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) @@ -76,18 +87,18 @@ CREATE TABLE scope( CREATE TABLE usage( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_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, + object_code INTEGER_OR_TEXT NOT NULL, extent_auth_name TEXT NOT NULL, - extent_code TEXT NOT NULL, + extent_code INTEGER_OR_TEXT NOT NULL, scope_auth_name TEXT NOT NULL, - scope_code TEXT NOT NULL, + scope_code INTEGER_OR_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) @@ -115,11 +126,11 @@ END; CREATE TABLE prime_meridian( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), longitude FLOAT NOT NULL CHECK (longitude BETWEEN -180 AND 180), uom_auth_name TEXT NOT NULL, - uom_code TEXT NOT NULL, + uom_code INTEGER_OR_TEXT NOT NULL, deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), CONSTRAINT pk_prime_meridian PRIMARY KEY (auth_name, code), CONSTRAINT fk_prime_meridian_unit_of_measure FOREIGN KEY (uom_auth_name, uom_code) REFERENCES unit_of_measure(auth_name, code) @@ -134,13 +145,13 @@ END; CREATE TABLE geodetic_datum ( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, ellipsoid_auth_name TEXT NOT NULL, - ellipsoid_code TEXT NOT NULL, + ellipsoid_code INTEGER_OR_TEXT NOT NULL, prime_meridian_auth_name TEXT NOT NULL, - prime_meridian_code TEXT NOT NULL, + prime_meridian_code INTEGER_OR_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 @@ -163,9 +174,9 @@ END; CREATE TABLE geodetic_datum_ensemble_member ( ensemble_auth_name TEXT NOT NULL, - ensemble_code TEXT NOT NULL, + ensemble_code INTEGER_OR_TEXT NOT NULL, member_auth_name TEXT NOT NULL, - member_code TEXT NOT NULL, + member_code INTEGER_OR_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), @@ -174,7 +185,7 @@ CREATE TABLE geodetic_datum_ensemble_member ( CREATE TABLE vertical_datum ( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_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 @@ -186,9 +197,9 @@ CREATE TABLE vertical_datum ( CREATE TABLE vertical_datum_ensemble_member ( ensemble_auth_name TEXT NOT NULL, - ensemble_code TEXT NOT NULL, + ensemble_code INTEGER_OR_TEXT NOT NULL, member_auth_name TEXT NOT NULL, - member_code TEXT NOT NULL, + member_code INTEGER_OR_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), @@ -197,7 +208,7 @@ CREATE TABLE vertical_datum_ensemble_member ( CREATE TABLE coordinate_system( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), 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), @@ -208,15 +219,15 @@ CREATE TABLE coordinate_system( CREATE TABLE axis( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), abbrev TEXT NOT NULL, orientation TEXT NOT NULL, coordinate_system_auth_name TEXT NOT NULL, - coordinate_system_code TEXT NOT NULL, + coordinate_system_code INTEGER_OR_TEXT NOT NULL, coordinate_system_order SMALLINT NOT NULL CHECK (coordinate_system_order BETWEEN 1 AND 3), uom_auth_name TEXT, - uom_code TEXT, + uom_code INTEGER_OR_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) @@ -233,14 +244,14 @@ END; CREATE TABLE geodetic_crs( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, type TEXT NOT NULL CHECK (type IN ('geographic 2D', 'geographic 3D', 'geocentric')), coordinate_system_auth_name TEXT, - coordinate_system_code TEXT, + coordinate_system_code INTEGER_OR_TEXT, datum_auth_name TEXT, - datum_code TEXT, + datum_code INTEGER_OR_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), @@ -280,13 +291,13 @@ END; CREATE TABLE vertical_crs( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, coordinate_system_auth_name TEXT NOT NULL, - coordinate_system_code TEXT NOT NULL, + coordinate_system_code INTEGER_OR_TEXT NOT NULL, datum_auth_name TEXT NOT NULL, - datum_code TEXT NOT NULL, + datum_code INTEGER_OR_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), @@ -311,7 +322,7 @@ END; CREATE TABLE conversion_method( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), CONSTRAINT pk_conversion_method PRIMARY KEY (auth_name, code) @@ -319,7 +330,7 @@ CREATE TABLE conversion_method( CREATE TABLE conversion_param( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), CONSTRAINT pk_conversion_param PRIMARY KEY (auth_name, code) @@ -327,63 +338,63 @@ CREATE TABLE conversion_param( CREATE TABLE conversion_table( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, 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), + method_code INTEGER_OR_TEXT CHECK (method_code IS NULL OR length(method_code) >= 1), -- method_name TEXT, param1_auth_name TEXT, - param1_code TEXT, + param1_code INTEGER_OR_TEXT, -- param1_name TEXT, param1_value FLOAT, param1_uom_auth_name TEXT, - param1_uom_code TEXT, + param1_uom_code INTEGER_OR_TEXT, param2_auth_name TEXT, - param2_code TEXT, + param2_code INTEGER_OR_TEXT, --param2_name TEXT, param2_value FLOAT, param2_uom_auth_name TEXT, - param2_uom_code TEXT, + param2_uom_code INTEGER_OR_TEXT, param3_auth_name TEXT, - param3_code TEXT, + param3_code INTEGER_OR_TEXT, --param3_name TEXT, param3_value FLOAT, param3_uom_auth_name TEXT, - param3_uom_code TEXT, + param3_uom_code INTEGER_OR_TEXT, param4_auth_name TEXT, - param4_code TEXT, + param4_code INTEGER_OR_TEXT, --param4_name TEXT, param4_value FLOAT, param4_uom_auth_name TEXT, - param4_uom_code TEXT, + param4_uom_code INTEGER_OR_TEXT, param5_auth_name TEXT, - param5_code TEXT, + param5_code INTEGER_OR_TEXT, --param5_name TEXT, param5_value FLOAT, param5_uom_auth_name TEXT, - param5_uom_code TEXT, + param5_uom_code INTEGER_OR_TEXT, param6_auth_name TEXT, - param6_code TEXT, + param6_code INTEGER_OR_TEXT, --param6_name TEXT, param6_value FLOAT, param6_uom_auth_name TEXT, - param6_uom_code TEXT, + param6_uom_code INTEGER_OR_TEXT, param7_auth_name TEXT, - param7_code TEXT, + param7_code INTEGER_OR_TEXT, --param7_name TEXT, param7_value FLOAT, param7_uom_auth_name TEXT, - param7_uom_code TEXT, + param7_uom_code INTEGER_OR_TEXT, deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)), @@ -675,15 +686,15 @@ END; CREATE TABLE projected_crs( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, coordinate_system_auth_name TEXT, - coordinate_system_code TEXT, + coordinate_system_code INTEGER_OR_TEXT, geodetic_crs_auth_name TEXT, - geodetic_crs_code TEXT, + geodetic_crs_code INTEGER_OR_TEXT, conversion_auth_name TEXT, - conversion_code TEXT, + conversion_code INTEGER_OR_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), @@ -725,13 +736,13 @@ END; CREATE TABLE compound_crs( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, horiz_crs_auth_name TEXT NOT NULL, - horiz_crs_code TEXT NOT NULL, + horiz_crs_code INTEGER_OR_TEXT NOT NULL, vertical_crs_auth_name TEXT NOT NULL, - vertical_crs_code TEXT NOT NULL, + vertical_crs_code INTEGER_OR_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) @@ -762,7 +773,7 @@ END; CREATE TABLE coordinate_operation_method( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), CONSTRAINT pk_coordinate_operation_method PRIMARY KEY (auth_name, code) @@ -770,19 +781,19 @@ CREATE TABLE coordinate_operation_method( CREATE TABLE helmert_transformation_table( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1), - method_code TEXT NOT NULL CHECK (length(method_code) >= 1), + method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1), --method_name TEXT NOT NULL CHECK (length(method_name) >= 2), source_crs_auth_name TEXT NOT NULL, - source_crs_code TEXT NOT NULL, + source_crs_code INTEGER_OR_TEXT NOT NULL, target_crs_auth_name TEXT NOT NULL, - target_crs_code TEXT NOT NULL, + target_crs_code INTEGER_OR_TEXT NOT NULL, accuracy FLOAT CHECK (accuracy >= 0), @@ -790,36 +801,36 @@ CREATE TABLE helmert_transformation_table( ty FLOAT NOT NULL, tz FLOAT NOT NULL, translation_uom_auth_name TEXT NOT NULL, - translation_uom_code TEXT NOT NULL, + translation_uom_code INTEGER_OR_TEXT NOT NULL, rx FLOAT, ry FLOAT, rz FLOAT, rotation_uom_auth_name TEXT, - rotation_uom_code TEXT, + rotation_uom_code INTEGER_OR_TEXT, scale_difference FLOAT, scale_difference_uom_auth_name TEXT, - scale_difference_uom_code TEXT, + scale_difference_uom_code INTEGER_OR_TEXT, rate_tx FLOAT, rate_ty FLOAT, rate_tz FLOAT, rate_translation_uom_auth_name TEXT, - rate_translation_uom_code TEXT, + rate_translation_uom_code INTEGER_OR_TEXT, rate_rx FLOAT, rate_ry FLOAT, rate_rz FLOAT, rate_rotation_uom_auth_name TEXT, - rate_rotation_uom_code TEXT, + rate_rotation_uom_code INTEGER_OR_TEXT, rate_scale_difference FLOAT, rate_scale_difference_uom_auth_name TEXT, - rate_scale_difference_uom_code TEXT, + rate_scale_difference_uom_code INTEGER_OR_TEXT, epoch FLOAT, epoch_uom_auth_name TEXT, - epoch_uom_code TEXT, + epoch_uom_code INTEGER_OR_TEXT, px FLOAT, -- Pivot / evaluation point for Molodensky-Badekas py FLOAT, pz FLOAT, pivot_uom_auth_name TEXT, - pivot_uom_code TEXT, + pivot_uom_code INTEGER_OR_TEXT, operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here @@ -1002,34 +1013,34 @@ END; CREATE TABLE grid_transformation( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1), - method_code TEXT NOT NULL CHECK (length(method_code) >= 1), + method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1), method_name TEXT NOT NULL CHECK (length(method_name) >= 2), source_crs_auth_name TEXT NOT NULL, - source_crs_code TEXT NOT NULL, + source_crs_code INTEGER_OR_TEXT NOT NULL, target_crs_auth_name TEXT NOT NULL, - target_crs_code TEXT NOT NULL, + target_crs_code INTEGER_OR_TEXT NOT NULL, accuracy FLOAT CHECK (accuracy >= 0), grid_param_auth_name TEXT NOT NULL, - grid_param_code TEXT NOT NULL, + grid_param_code INTEGER_OR_TEXT NOT NULL, grid_param_name TEXT NOT NULL, grid_name TEXT NOT NULL, grid2_param_auth_name TEXT, - grid2_param_code TEXT, + grid2_param_code INTEGER_OR_TEXT, grid2_param_name TEXT, grid2_name TEXT, interpolation_crs_auth_name TEXT, - interpolation_crs_code TEXT, + interpolation_crs_code INTEGER_OR_TEXT, operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here @@ -1121,7 +1132,7 @@ END; CREATE TABLE other_transformation( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, @@ -1131,67 +1142,67 @@ CREATE TABLE other_transformation( -- if method_auth_name = 'PROJ', method_code can be 'WKT' for a -- PROJ string and then method_name is a WKT string (CoordinateOperation) method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1), - method_code TEXT NOT NULL CHECK (length(method_code) >= 1), + method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1), method_name TEXT NOT NULL CHECK (length(method_name) >= 2), source_crs_auth_name TEXT NOT NULL, - source_crs_code TEXT NOT NULL, + source_crs_code INTEGER_OR_TEXT NOT NULL, target_crs_auth_name TEXT NOT NULL, - target_crs_code TEXT NOT NULL, + target_crs_code INTEGER_OR_TEXT NOT NULL, accuracy FLOAT CHECK (accuracy >= 0), param1_auth_name TEXT, - param1_code TEXT, + param1_code INTEGER_OR_TEXT, param1_name TEXT, param1_value FLOAT, param1_uom_auth_name TEXT, - param1_uom_code TEXT, + param1_uom_code INTEGER_OR_TEXT, param2_auth_name TEXT, - param2_code TEXT, + param2_code INTEGER_OR_TEXT, param2_name TEXT, param2_value FLOAT, param2_uom_auth_name TEXT, - param2_uom_code TEXT, + param2_uom_code INTEGER_OR_TEXT, param3_auth_name TEXT, - param3_code TEXT, + param3_code INTEGER_OR_TEXT, param3_name TEXT, param3_value FLOAT, param3_uom_auth_name TEXT, - param3_uom_code TEXT, + param3_uom_code INTEGER_OR_TEXT, param4_auth_name TEXT, - param4_code TEXT, + param4_code INTEGER_OR_TEXT, param4_name TEXT, param4_value FLOAT, param4_uom_auth_name TEXT, - param4_uom_code TEXT, + param4_uom_code INTEGER_OR_TEXT, param5_auth_name TEXT, - param5_code TEXT, + param5_code INTEGER_OR_TEXT, param5_name TEXT, param5_value FLOAT, param5_uom_auth_name TEXT, - param5_uom_code TEXT, + param5_uom_code INTEGER_OR_TEXT, param6_auth_name TEXT, - param6_code TEXT, + param6_code INTEGER_OR_TEXT, param6_name TEXT, param6_value FLOAT, param6_uom_auth_name TEXT, - param6_uom_code TEXT, + param6_uom_code INTEGER_OR_TEXT, param7_auth_name TEXT, - param7_code TEXT, + param7_code INTEGER_OR_TEXT, param7_name TEXT, param7_value FLOAT, param7_uom_auth_name TEXT, - param7_uom_code TEXT, + param7_uom_code INTEGER_OR_TEXT, interpolation_crs_auth_name TEXT, - interpolation_crs_code TEXT, + interpolation_crs_code INTEGER_OR_TEXT, operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here @@ -1234,15 +1245,15 @@ END; -- enforce that source_crs_code == step1.source_crs_code etc CREATE TABLE concatenated_operation( auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), name TEXT NOT NULL CHECK (length(name) >= 2), description TEXT, source_crs_auth_name TEXT NOT NULL, - source_crs_code TEXT NOT NULL, + source_crs_code INTEGER_OR_TEXT NOT NULL, target_crs_auth_name TEXT NOT NULL, - target_crs_code TEXT NOT NULL, + target_crs_code INTEGER_OR_TEXT NOT NULL, accuracy FLOAT CHECK (accuracy >= 0), @@ -1277,10 +1288,10 @@ END; CREATE TABLE concatenated_operation_step( operation_auth_name TEXT NOT NULL CHECK (length(operation_auth_name) >= 1), - operation_code TEXT NOT NULL CHECK (length(operation_code) >= 1), + operation_code INTEGER_OR_TEXT NOT NULL CHECK (length(operation_code) >= 1), step_number INTEGER NOT NULL CHECK (step_number >= 1), step_auth_name TEXT NOT NULL CHECK (length(step_auth_name) >= 1), - step_code TEXT NOT NULL CHECK (length(step_code) >= 1), + step_code INTEGER_OR_TEXT NOT NULL CHECK (length(step_code) >= 1), CONSTRAINT pk_concatenated_operation_step PRIMARY KEY (operation_auth_name, operation_code, step_number) --CONSTRAINT fk_concatenated_operation_step_to_operation FOREIGN KEY (step_auth_name, step_code) REFERENCES coordinate_operation(auth_name, code) @@ -1302,7 +1313,7 @@ END; CREATE TABLE geoid_model( name TEXT NOT NULL, operation_auth_name TEXT NOT NULL, - operation_code TEXT NOT NULL, + operation_code INTEGER_OR_TEXT NOT NULL, CONSTRAINT pk_geoid_model PRIMARY KEY (name, operation_auth_name, operation_code) -- CONSTRAINT fk_geoid_model_operation FOREIGN KEY (operation_auth_name, operation_code) REFERENCES coordinate_operation(auth_name, code) ); @@ -1322,7 +1333,7 @@ CREATE TABLE alias_name( '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), - code TEXT NOT NULL CHECK (length(code) >= 1), + code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1), alt_name TEXT NOT NULL CHECK (length(alt_name) >= 2), source TEXT ); @@ -1346,14 +1357,14 @@ CREATE TABLE supersession( '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, + superseded_code INTEGER_OR_TEXT NOT NULL, replacement_table_name TEXT NOT NULL CHECK (replacement_table_name IN ( 'unit_of_measure', 'celestial_body', 'ellipsoid', '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, - replacement_code TEXT NOT NULL, + replacement_code INTEGER_OR_TEXT NOT NULL, source TEXT, same_source_target_crs BOOLEAN NOT NULL CHECK (same_source_target_crs IN (0, 1)) -- for transformations, whether the (source_crs, target_crs) of the replacement transfrm is the same as the superseded one ); @@ -1378,9 +1389,9 @@ CREATE TABLE deprecation( 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', 'helmert_transformation', 'other_transformation', 'concatenated_operation')), deprecated_auth_name TEXT NOT NULL, - deprecated_code TEXT NOT NULL, + deprecated_code INTEGER_OR_TEXT NOT NULL, replacement_auth_name TEXT NOT NULL, - replacement_code TEXT NOT NULL, + replacement_code INTEGER_OR_TEXT NOT NULL, source TEXT ); |