# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1539433600 -7200
# Node ID 5f89868bd75e411799b716264b8bd279204c204a
# Parent  1417ae641f7f39c9d4c82ab3b1cf6832e90b9553
Store virtual and physical distance marks separately

Virtual distance marks on waterway axis always have an ISRS location
code, which is their natural primary key, but which other distance
marks do not (always) have. catdis and position_code are constant
for distance marks on fairway axis and do not need to be stored.

Btw. add a previously missing field for the name of the ENC the
information is based on.

diff -r 1417ae641f7f -r 5f89868bd75e schema/gemma.sql
--- a/schema/gemma.sql	Fri Oct 12 09:36:46 2018 +0200
+++ b/schema/gemma.sql	Sat Oct 13 14:26:40 2018 +0200
@@ -286,11 +286,21 @@
     -- 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
     -- distance marks along waterway axis (see SUC7).
-    CREATE TABLE distance_marks (
+    CREATE TABLE distance_marks_virtual (
         location_code isrs PRIMARY KEY,
         geom geography(POINT, 4326) NOT NULL,
+        related_enc varchar(12) NOT NULL
+    )
+
+    CREATE TABLE distance_marks (
+        country char(2) NOT NULL REFERENCES countries,
+        hectom int NOT NULL,
+        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
+        position_code char(2) NOT NULL REFERENCES position_codes,
+        related_enc varchar(12) NOT NULL
     )
 
     -- A table to help geoserver serve the distance marks as WFS 1.1.0.
@@ -299,10 +309,9 @@
     CREATE VIEW waterway.distance_marks_geoserver AS
         SELECT location_code::VARCHAR,
                geom::Geometry(POINT, 4326),
-               catdis,
-               position_code,
+               related_enc,
                (location_code).hectometre
-            FROM waterway.distance_marks
+            FROM waterway.distance_marks_virtual
 
     CREATE TABLE sections_stretches (
         id varchar PRIMARY KEY,