Mercurial > gemma
changeset 1986:4949e723bf45
Merged.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 23 Jan 2019 17:59:15 +0100 |
parents | 8eeb0b5eb340 (current diff) 48001472e1d8 (diff) |
children | ee274693b8ee |
files | schema/gemma.sql |
diffstat | 1 files changed, 17 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jan 23 17:58:57 2019 +0100 +++ b/schema/gemma.sql Wed Jan 23 17:59:15 2019 +0100 @@ -175,6 +175,7 @@ CREATE TABLE responsibility_areas ( country char(2) PRIMARY KEY REFERENCES countries, area geography(MULTIPOLYGON, 4326) + CHECK(ST_IsValid(CAST(area AS geometry))) ) CREATE TABLE templates ( @@ -230,7 +231,8 @@ CREATE TABLE waterway_area ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - area geography(POLYGON, 4326) NOT NULL, + area geography(POLYGON, 4326) NOT NULL + CHECK(ST_IsValid(CAST(area AS geometry))), catccl smallint REFERENCES catccls, dirimp smallint REFERENCES dirimps ) @@ -294,7 +296,8 @@ CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - wtwaxs geography(LINESTRING, 4326) NOT NULL, + wtwaxs geography(LINESTRING, 4326) NOT NULL + CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), -- TODO: Do we need to check data set quality (DRC 2.1.6)? objnam varchar NOT NULL, nobjnam varchar @@ -351,7 +354,8 @@ id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar NOT NULL, stretch isrsrange NOT NULL, - area geography(MULTIPOLYGON, 4326) NOT NULL, + area geography(MULTIPOLYGON, 4326) NOT NULL + CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -412,7 +416,8 @@ CREATE TABLE fairway_dimensions ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - area geography(POLYGON, 4326) NOT NULL, + area geography(POLYGON, 4326) NOT NULL + CHECK(ST_IsValid(CAST(area AS geometry))), level_of_service smallint NOT NULL REFERENCES levels_of_service, min_width smallint NOT NULL, max_width smallint NOT NULL, @@ -439,7 +444,8 @@ objnam varchar, nobjnm varchar, stretch isrsrange NOT NULL, - area geography(MULTIPOLYGON, 4326) NOT NULL, + area geography(MULTIPOLYGON, 4326) NOT NULL + CHECK(ST_IsValid(CAST(area AS geometry))), rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface responsible_country char(2) NOT NULL REFERENCES countries, @@ -494,11 +500,13 @@ bottleneck_id int NOT NULL REFERENCES bottlenecks(id), date_info date NOT NULL, UNIQUE (bottleneck_id, date_info), - area geography(POLYGON, 4326) NOT NULL, + area geography(POLYGON, 4326) NOT NULL + CHECK(ST_IsValid(CAST(area AS geometry))), surtyp varchar REFERENCES survey_types, coverage varchar REFERENCES coverage_types, depth_reference char(3) NOT NULL REFERENCES depth_references, - point_cloud geography(MULTIPOINTZ, 4326) NOT NULL, + point_cloud geography(MULTIPOINTZ, 4326) NOT NULL + CHECK(ST_IsSimple(CAST(point_cloud AS geometry))), octree_checksum varchar, octree_index bytea, staging_done boolean NOT NULL DEFAULT false @@ -509,6 +517,8 @@ ON DELETE CASCADE, height numeric NOT NULL, lines geography(multilinestring, 4326) NOT NULL, + -- TODO: generate valid simple features and add constraint: + -- CHECK(ST_IsSimple(CAST(lines AS geometry))), PRIMARY KEY (sounding_result_id, height) ) -- A view to help geoserver serve contour lines.