--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Dockerfile	Thu May 03 18:08:07 2018 +0200
@@ -0,0 +1,47 @@
+FROM centos:7
+MAINTAINER tom.gottfried@intevation.de
+
+# Add the PostgreSQL PGP key to verify the official yum repository packages
+RUN rpm --import https://yum.postgresql.org/RPM-GPG-KEY-PGDG-10
+
+# Add PostgreSQL's repository. It contains the most recent release
+# of PostgreSQL, 10:
+RUN yum -q -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
+
+# Install PostgreSQL 10 and PostGIS
+RUN yum -q -y install postgresql10-server
+RUN yum -q -y install epel-release
+RUN yum -q -y install postgis24_10
+
+USER postgres
+ENV PGBIN /usr/pgsql-10/bin/
+
+# initdb PostgreSQL 10:
+ENV PGDATA /var/lib/pgsql/10/data
+RUN $PGBIN/initdb -E UTF8 2>&1 < /dev/null
+
+# Adjust PostgreSQL configuration so that remote connections to the
+# database are possible.
+ENV PGCONF /var/lib/pgsql/10/data/postgresql.conf
+RUN echo "host all  all    0.0.0.0/0  md5" >> /var/lib/pgsql/10/data/pg_hba.conf
+RUN echo "listen_addresses='*'" >> $PGCONF
+
+# Expose the PostgreSQL port
+EXPOSE 5432
+
+# Create WAMOS role and database
+ADD wamos.sql .
+RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \
+    psql -c "CREATE USER wamos PASSWORD 'wamos'" && \
+    createdb wamos && \
+    psql -f wamos.sql -d wamos && \
+    $PGBIN/pg_ctl stop -m smart
+
+# Set the default command to run when starting the container
+CMD ["/usr/pgsql-10/bin/postgres", "-D", "/var/lib/pgsql/10/data"]
+
+# Build with e.g.:
+# docker build -t wamos .
+
+# Get a running instance with e.g.:
+# docker run --name wamos -d -p 54321:5432 wamos

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/wamos.sql	Thu May 03 18:08:07 2018 +0200
@@ -0,0 +1,116 @@
+BEGIN;
+
+CREATE EXTENSION postgis;
+
+CREATE FUNCTION update_date_info() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.date_info = CURRENT_TIMESTAMP;
+        RETURN NEW;
+    END;
+$$;
+
+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
+
+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!
+       -- 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
+       rwdr double precision NOT NULL,
+       EXCLUDE USING GIST (stretch WITH &&)
+       );
+
+CREATE TABLE gauges (
+       location isrs PRIMARY KEY
+       );
+
+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?
+       );
+
+CREATE TABLE riverbed_materials (
+       material varchar PRIMARY KEY
+       );
+
+CREATE TABLE survey_types (
+       survey_type varchar PRIMARY KEY
+       );
+
+CREATE TABLE coverage_types (
+       coverage_type varchar PRIMARY KEY
+       );
+
+CREATE TABLE limiting_factors (
+       limiting_factor varchar PRIMARY KEY
+       );
+
+CREATE TABLE depth_references (
+       depth_reference char(3) PRIMARY KEY
+       -- Possible codes? char(3) ok?
+       );
+
+CREATE TABLE bottlenecks (
+       bottleneck_id varchar PRIMARY KEY,
+       -- XXX: Why encoding three different data in the ID as described in
+       -- DRC?
+       fk_g_fid isrs REFERENCES gauges,
+       -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
+       objnam varchar UNIQUE NOT NULL,
+       nobjnm varchar UNIQUE,
+       stretch isrsrange NOT NULL,
+       rb_lb varchar, -- XXX: Why two data in one attribute? Why not:
+       rb char(2) NOT NULL REFERENCES countries,
+       lb char(2) NOT NULL REFERENCES countries,
+       riverbed varchar NOT NULL REFERENCES riverbed_materials,
+       -- XXX: list of materials differs between IENC Encoding Guide and DRC
+       responsible_country char(2) NOT NULL REFERENCES countries,
+       revisiting_time smallint NOT NULL,
+       surtyp varchar NOT NULL REFERENCES survey_types,
+       -- XXX: Not an attribut of sounding result?
+       coverage varchar NOT NULL REFERENCES coverage_types,
+       -- XXX: Not an attribut of sounding result?
+       -- CHECK allowed combinations of surtyp and coverage or
+       -- different model approach?
+       limiting varchar NOT NULL REFERENCES limiting_factors,
+       depth_reference char(3) NOT NULL REFERENCES depth_references,
+       -- XXX: Not an attribut of sounding result?
+       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL,
+       additional_data xml -- XXX: What is that really for?
+       );
+CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE vertical_references (
+       vertical_reference varchar PRIMARY KEY
+       );
+
+CREATE TABLE sounding_results (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       date_info date NOT NULL,
+       PRIMARY KEY (bottleneck_id, date_info),
+       area int, -- XX: Check SRS/test data what this should be
+       type int, -- XX: Check SRS/test data what this should be
+       vertical_reference varchar NOT NULL REFERENCES vertical_references,
+       sounding_data raster NOT NULL
+       );
+
+COMMIT;