Mercurial > gemma

changeset 1984:48001472e1d8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ensure geometries in database are valid OGC simple features
author Tom Gottfried <tom@intevation.de>
date Wed, 23 Jan 2019 17:46:50 +0100
parents f9f1babe52ae
children 4949e723bf45
files schema/gemma.sql
diffstat 1 files changed, 17 insertions(+), 7 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Jan 23 16:25:43 2019 +0100
+++ b/schema/gemma.sql	Wed Jan 23 17:46:50 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.