# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1525453306 -7200
# Node ID 6f273a649f080ab4d01742d508faa5bb224d12ae
# Parent  333c42e341e5eec6ddf88b9645bc551d99e747e3
Be more specific with ISRS and update some comments.

diff -r 333c42e341e5 -r 6f273a649f08 wamos.sql
--- a/wamos.sql	Thu May 03 18:15:46 2018 +0200
+++ b/wamos.sql	Fri May 04 19:01:46 2018 +0200
@@ -11,27 +11,31 @@
     END;
 $$;
 
+-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
+-- See RIS-Index Encoding Guide
 CREATE TYPE isrs AS (
-       country_code char(2),
-       locode varchar,
-       fairway_section varchar,
-       object_reference varchar,
-       hectometre int
-); -- Likely a composite type with UN/LOCODE, fairway section,
-   -- object reference, hectometre. See RIS-Index Encoding Guide
+       country_code char(2), -- ISO 3166 country code
+       -- could be validated against countries table.
+       locode char(3), -- without the country code:
+       -- http://www.unece.org/cefact/locode/welcome.html
+       fairway_section char(5),
+       object_reference char(5),
+       hectometre int -- should be constrained to five digits
+);
 
 CREATE TYPE isrsrange AS RANGE (
        subtype = isrs
        );
--- Maybe we should bundle types etc. in an EXTENSION 'postris'?
 
 CREATE TABLE rwdrs (
        stretch isrsrange PRIMARY KEY,
-       -- XXX: https://www.postgresql.org/docs/10/static/sql-createindex.html:
-       -- Only B-tree supports UNIQUE indexes!
+       -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
+       -- Only B-tree supports UNIQUE indexes, but we need the GIST index
+       -- below anyhow.
        -- Is it a good idea to build B-tree indexes on relatively large
-       -- string values or should we use inter PKs? What were our thoughts
-       -- for EUMETSAT? => see section 2.5 in respective report
+       -- composites of string values or should we use inter PKs?
+       -- => In case the index is used and cache space becomes a limiting
+       -- factor, this might be an issue.
        rwdr double precision NOT NULL,
        EXCLUDE USING GIST (stretch WITH &&)
        );
@@ -41,9 +45,10 @@
        );
 
 CREATE TABLE countries (
-       country_code char(2) PRIMARY KEY
-       -- XXX: smallint would require even less disk space i.e. on the FK side
-       -- XXX: char(2) sufficient?
+       country_code char(2) PRIMARY KEY -- ISO 3166 country code
+       -- A smallint PK would require even less disk space i.e. on the FK side.
+       -- This might be an issue in case cache space becomes a limiting
+       -- factor when there are many FKs pointing here.
        );
 
 CREATE TABLE riverbed_materials (