--- a/schema/gemma.sql	Fri Jan 18 16:52:20 2019 +0100
+++ b/schema/gemma.sql	Fri Jan 18 17:10:16 2019 +0100
@@ -234,24 +234,23 @@
         catccl smallint REFERENCES catccls,
         dirimp smallint REFERENCES dirimps
     )
-    CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
 
     CREATE TABLE gauges (
         location isrs PRIMARY KEY CHECK(
             (location).orc SIMILAR TO 'G[[:digit:]]{4}'
             AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
         objname varchar NOT NULL,
-        is_left boolean, -- XXX: Or reference position_codes?
         geom geography(POINT, 4326) NOT NULL,
-        applicability isrsrange,
-        validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy
+        applicability_from_km int8,
+        applicability_to_km int8,
+        validity tstzrange,
         -- pasted text from a more general specification is given
         -- (a gauge is not a berth!)
         -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
         zero_point double precision NOT NULL,
         geodref varchar(4) REFERENCES depth_references,
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL
+        source_organization varchar
     )
     CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
@@ -300,8 +299,6 @@
         objnam varchar NOT NULL,
         nobjnam varchar
     )
-    -- TODO: @tom: Why did you choose this index kind?
-    -- CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
 
     -- This table allows linkage between 1D ISRS location codes and 2D space
     -- e.g. for cutting bottleneck area out of waterway area based on virtual
@@ -313,25 +310,25 @@
     )
 
     CREATE TABLE distance_marks (
-        country char(2) NOT NULL REFERENCES countries,
-        hectom int NOT NULL,
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        country char(2) REFERENCES countries,
+        hectom int,
         geom geography(POINT, 4326) NOT NULL,
         -- include location in primary key, because we have no fairway code:
-        PRIMARY KEY (country, hectom, geom),
-        catdis smallint NOT NULL REFERENCES catdis,
-        position_code char(2) NOT NULL REFERENCES position_codes,
-        related_enc varchar(12) NOT NULL
+        catdis smallint REFERENCES catdis,
+        position_code char(2) REFERENCES position_codes,
+        related_enc varchar(12)
     )
 
     -- A table to help geoserver serve the distance marks as WFS 1.1.0.
     -- At least geoserver-2.13.2 does not serve type geography correctly
     -- and does not serve the location_code as isrs type
-    CREATE VIEW waterway.distance_marks_geoserver AS
+    CREATE VIEW distance_marks_geoserver AS
         SELECT location_code::VARCHAR,
                geom::Geometry(POINT, 4326),
                related_enc,
                (location_code).hectometre
-            FROM waterway.distance_marks_virtual
+            FROM distance_marks_virtual
 
     -- We need to configure primary keys for the views used by
     -- geoserver for wfs, otherwise it will generate ids on the fly,
@@ -350,18 +347,30 @@
         check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
     )
 
-    CREATE TABLE sections_stretches (
-        id varchar PRIMARY KEY,
-        is_section boolean NOT NULL, -- maps 'function' from interface
-        stretch isrsrange,
+    CREATE TABLE stretches (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        name varchar NOT NULL,
+        stretch isrsrange NOT NULL,
+        -- TODO: make it a (MULTI)-LINESTRING.
+        -- POLYGON is chosen for the convinience
+        -- of re-using ISRSrange_area.
+        geom geography(POLYGON, 4326) NOT NULL,
         objnam varchar NOT NULL,
         nobjnam varchar,
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL,
-        staging_done boolean NOT NULL DEFAULT false
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE(name, staging_done)
     )
+
+    CREATE TABLE stretch_countries (
+        stretches_id int NOT NULL REFERENCES stretches(id),
+        country_code char(2) NOT NULL REFERENCES countries(country_code),
+        UNIQUE(stretches_id, country_code)
+    )
+
     CREATE TRIGGER sections_stretches_date_info
-        BEFORE UPDATE ON sections_stretches
+        BEFORE UPDATE ON stretches
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE waterway_profiles (
@@ -396,7 +405,6 @@
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false
     )
-    CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
     CREATE TRIGGER fairway_dimensions_date_info
         BEFORE UPDATE ON fairway_dimensions
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
@@ -468,13 +476,13 @@
     )
     -- A view to help geoserver serve contour lines.
     -- At least geoserver-2.13.2 does not serve type geography correctly
-    CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS
+    CREATE VIEW sounding_results_contour_lines_geoserver AS
         SELECT bottleneck_id,
                 date_info,
                 height,
                 CAST(lines AS geometry(multilinestring, 4326)) AS lines
-            FROM waterway.sounding_results_contour_lines cl
-                JOIN waterway.sounding_results sr
+            FROM sounding_results_contour_lines cl
+                JOIN sounding_results sr
                     ON sr.id = cl.sounding_result_id
 
     --
@@ -536,16 +544,15 @@
             OR value_lifetime IS NOT NULL)
     )
 
-    CREATE VIEW waterway.bottleneck_overview AS
+    CREATE VIEW bottleneck_overview AS
     SELECT
       objnam AS name,
       ST_Centroid(area)::Geometry AS point,
       (lower(stretch)).hectometre AS from,
       (upper(stretch)).hectometre AS to,
       sr.current::text
-    FROM waterway.bottlenecks bn LEFT JOIN (
-      SELECT bottleneck_id, max(date_info) AS current FROM
-      waterway.sounding_results
+    FROM bottlenecks bn LEFT JOIN (
+      SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
       GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
     ORDER BY objnam