Mercurial > gemma
changeset 35:62e14b4d25fc
First working draft of schema for bottlenecks.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 03 May 2018 18:08:07 +0200 |
parents | |
children | 333c42e341e5 |
files | Dockerfile wamos.sql |
diffstat | 2 files changed, 163 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /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;