--- a/schema/gemma.sql	Tue Jul 17 19:13:16 2018 +0200
+++ b/schema/gemma.sql	Wed Jul 18 16:48:27 2018 +0200
@@ -19,18 +19,18 @@
 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
 -- See RIS-Index Encoding Guide
 CREATE TYPE isrs AS (
-       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
+    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
-       );
+    subtype = isrs
+);
 
 --
 -- GEMMA data
@@ -47,47 +47,48 @@
 -- Auxiliary tables
 --
 CREATE TABLE gemma.system_config (
-       config_key varchar PRIMARY KEY,
-       config_val varchar
-       );
+    config_key varchar PRIMARY KEY,
+    config_val varchar
+);
 
 CREATE TABLE gemma.countries (
-       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.
-       );
+    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 gemma.responsibility_areas (
-       country char(2) PRIMARY KEY REFERENCES countries,
-       area geometry(MULTIPOLYGON, 4326) --XXX: Should be geography (elsewhere too)
-       );
+    country char(2) PRIMARY KEY REFERENCES countries,
+    area geometry(MULTIPOLYGON, 4326)
+    --XXX: Should be geography (elsewhere too)
+);
 
 CREATE TABLE gemma.language_codes (
-       language_code varchar PRIMARY KEY
-       );
+    language_code varchar PRIMARY KEY
+);
 
 CREATE TABLE gemma.user_profiles (
-       username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
-       country char(2) NOT NULL REFERENCES countries,
-       language_code varchar REFERENCES language_codes,
-       map_extent box2d,
-       email_adress varchar NOT NULL UNIQUE
-       );
+    username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
+    country char(2) NOT NULL REFERENCES countries,
+    language_code varchar REFERENCES language_codes,
+    map_extent box2d,
+    email_adress varchar NOT NULL UNIQUE
+);
 
 CREATE TABLE gemma.templates (
-       template_name varchar PRIMARY KEY,
-       template_data bytea NOT NULL,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
-       );
+    template_name varchar PRIMARY KEY,
+    template_data bytea NOT NULL,
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma.user_templates (
-       username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
-       template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
-       PRIMARY KEY (username, template_name)
-       );
+    username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
+    template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
+    PRIMARY KEY (username, template_name)
+);
 
 --
 -- General river information
@@ -106,305 +107,305 @@
 --        -- factor, this might be an issue.
 --        rwdr double precision NOT NULL,
 --        EXCLUDE USING GIST (stretch WITH &&)
---        );
+--);
 
 CREATE TABLE gemma_waterway.catccls (
-       catccl smallint PRIMARY KEY
-       -- TODO: Do we need name and/or definition from IENC feature catalogue?
-       );
+    catccl smallint PRIMARY KEY
+    -- TODO: Do we need name and/or definition from IENC feature catalogue?
+);
 
 CREATE TABLE gemma_waterway.dirimps (
-       dirimp smallint PRIMARY KEY
-       -- TODO: Do we need name and/or definition from IENC feature catalogue?
-       );
+    dirimp smallint PRIMARY KEY
+    -- TODO: Do we need name and/or definition from IENC feature catalogue?
+);
 
 CREATE TABLE gemma_waterway.waterway_area (
-       area geometry(POLYGON, 4326) PRIMARY KEY,
-       catccl smallint REFERENCES catccls,
-       dirimp smallint REFERENCES dirimps
-       );
+    area geometry(POLYGON, 4326) PRIMARY KEY,
+    catccl smallint REFERENCES catccls,
+    dirimp smallint REFERENCES dirimps
+);
 
 CREATE TABLE gemma_fairway.depth_references (
-       depth_reference varchar(4) PRIMARY KEY
-       -- See col. AB and AI RIS-Index Encoding Guide
-       -- XXX: We need a way to distinguish between geodetic (eg. col. AP
-       -- RIS-Index) and other references (e.g. col. AB and AI):
-       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
-       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
-       -- _ Do not mixup things with different meanings in one table at all
-       --   (which would mean a model differing a bit from RIS-Index ideas)
-       );
+    depth_reference varchar(4) PRIMARY KEY
+    -- See col. AB and AI RIS-Index Encoding Guide
+    -- XXX: We need a way to distinguish between geodetic (eg. col. AP
+    -- RIS-Index) and other references (e.g. col. AB and AI):
+    -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
+    --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
+    -- _ Do not mixup things with different meanings in one table at all
+    --   (which would mean a model differing a bit from RIS-Index ideas)
+);
 
 CREATE TABLE gemma_fairway.reference_water_levels (
-       reference_water_level varchar(20) PRIMARY KEY
-       );
+    reference_water_level varchar(20) PRIMARY KEY
+);
 
 CREATE TABLE gemma_fairway.gauges (
-       location isrs PRIMARY KEY,
-       function_code varchar(10) NOT NULL, -- XXX: What is this really for?
-       objname varchar NOT NULL,
-       is_left boolean, -- XXX: Or reference position_codes?
-       geom geometry(POINT, 4326) NOT NULL,
-       applicability isrsrange,
-       validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
-       -- 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
-       );
+    location isrs PRIMARY KEY,
+    function_code varchar(10) NOT NULL, -- XXX: What is this really for?
+    objname varchar NOT NULL,
+    is_left boolean, -- XXX: Or reference position_codes?
+    geom geometry(POINT, 4326) NOT NULL,
+    applicability isrsrange,
+    validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
+    -- 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
+);
 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_fairway.gauges_reference_water_levels (
-       gauge_id isrs NOT NULL REFERENCES gauges,
-       reference_water_level varchar(20)
-           NOT NULL REFERENCES reference_water_levels,
-       PRIMARY KEY (gauge_id, reference_water_level),
-       value int NOT NULL
-       );
+    gauge_id isrs NOT NULL REFERENCES gauges,
+    reference_water_level varchar(20)
+        NOT NULL REFERENCES reference_water_levels,
+    PRIMARY KEY (gauge_id, reference_water_level),
+    value int NOT NULL
+);
 
 CREATE TABLE gemma_fairway.gauge_measurements (
-       fk_gauge_id isrs NOT NULL REFERENCES gauges,
-       measure_date timestamp with time zone NOT NULL,
-       PRIMARY KEY (fk_gauge_id, measure_date),
-       -- XXX: Is country_code really relevant for GEMMA or just NtS?
-       -- country_code char(2) NOT NULL REFERENCES countries,
-       -- TODO: add relations to stuff provided as enumerations
-       sender varchar NOT NULL, -- "from" attribute from DRC
-       language_code varchar NOT NULL REFERENCES language_codes,
-       date_issue timestamp with time zone NOT NULL,
-       -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
-       -- XXX: Always ZPG?
-       water_level double precision NOT NULL,
-       predicted boolean NOT NULL,
-       is_waterlevel boolean NOT NULL,
-       -- XXX: "measure_code" if really only W or Q
-       -- XXX: Do we need "unit" attribute or can we normalise on import?
-       value_min double precision, -- XXX: NOT NULL if predicted?
-       value_max double precision, -- XXX: NOT NULL if predicted?
-       --- TODO: Add a double range type for checking?
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL -- "originator"
-       );
+    fk_gauge_id isrs NOT NULL REFERENCES gauges,
+    measure_date timestamp with time zone NOT NULL,
+    PRIMARY KEY (fk_gauge_id, measure_date),
+    -- XXX: Is country_code really relevant for GEMMA or just NtS?
+    -- country_code char(2) NOT NULL REFERENCES countries,
+    -- TODO: add relations to stuff provided as enumerations
+    sender varchar NOT NULL, -- "from" attribute from DRC
+    language_code varchar NOT NULL REFERENCES language_codes,
+    date_issue timestamp with time zone NOT NULL,
+    -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
+    -- XXX: Always ZPG?
+    water_level double precision NOT NULL,
+    predicted boolean NOT NULL,
+    is_waterlevel boolean NOT NULL,
+    -- XXX: "measure_code" if really only W or Q
+    -- XXX: Do we need "unit" attribute or can we normalise on import?
+    value_min double precision, -- XXX: NOT NULL if predicted?
+    value_max double precision, -- XXX: NOT NULL if predicted?
+    --- TODO: Add a double range type for checking?
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL -- "originator"
+);
 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_waterway.waterway_axis (
-       wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
-       -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
-       objnam varchar NOT NULL,
-       nobjnam varchar
-       );
+    wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
+    -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
+    objnam varchar NOT NULL,
+    nobjnam varchar
+);
 
 CREATE TABLE gemma_waterway.distance_mark_functions (
-       -- XXX: Redundant information to object code in isrs code of dist. mark
-       distance_mark_function varchar(8) PRIMARY KEY
-       );
+    -- XXX: Redundant information to object code in isrs code of dist. mark
+    distance_mark_function varchar(8) PRIMARY KEY
+);
 
 CREATE TABLE gemma_waterway.position_codes (
-       position_code char(2) PRIMARY KEY
-       -- Use smallint because of fairway availability provided on daily basis?
-       -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
-       -- sheet "Position_code" or RIS-Index encoding guide?
-       -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
-       -- Clarify!
-       -- TODO: Do we need an attribute "meaning" or so?
-       );
+    position_code char(2) PRIMARY KEY
+    -- Use smallint because of fairway availability provided on daily basis?
+    -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
+    -- sheet "Position_code" or RIS-Index encoding guide?
+    -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
+    -- Clarify!
+    -- TODO: Do we need an attribute "meaning" or so?
+);
 
 -- This table allows linkage between the 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 gemma_waterway.distance_marks (
-       location_code isrs PRIMARY KEY,
-       geom geometry(POINT, 4326) NOT NULL,
-       distance_mark_function varchar(8)
-           NOT NULL REFERENCES distance_mark_functions,
-       -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
-       -- to encode the same thing as the object code in ISRS location code.
-       position_code char(2) NOT NULL REFERENCES position_codes
-       );
+    location_code isrs PRIMARY KEY,
+    geom geometry(POINT, 4326) NOT NULL,
+    distance_mark_function varchar(8)
+        NOT NULL REFERENCES distance_mark_functions,
+    -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
+    -- to encode the same thing as the object code in ISRS location code.
+    position_code char(2) NOT NULL REFERENCES position_codes
+);
 
 CREATE TABLE gemma_waterway.sections_stretches (
-       id varchar PRIMARY KEY,
-       is_section boolean NOT NULL, -- maps 'function' from interface
-       stretch isrsrange,
-       objnam varchar NOT NULL,
-       nobjnam varchar,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
+    id varchar PRIMARY KEY,
+    is_section boolean NOT NULL, -- maps 'function' from interface
+    stretch isrsrange,
+    objnam varchar NOT NULL,
+    nobjnam varchar,
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL
+);
 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_waterway.waterway_profiles (
-       location isrs NOT NULL,
-       validity tstzrange,
-       EXCLUDE USING GIST (validity WITH &&),
-       PRIMARY KEY (location, validity),
-       lnwl smallint,
-       mwl smallint,
-       hnwl smallint,
-       fe30 smallint,
-       fe100 smallint,
-       -- XXX: further normalise using reference_water_levels?
-       CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
-             OR validity IS NOT NULL),
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
+    location isrs NOT NULL,
+    validity tstzrange,
+    EXCLUDE USING GIST (validity WITH &&),
+    PRIMARY KEY (location, validity),
+    lnwl smallint,
+    mwl smallint,
+    hnwl smallint,
+    fe30 smallint,
+    fe100 smallint,
+    -- XXX: further normalise using reference_water_levels?
+    CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
+        OR validity IS NOT NULL),
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL
+);
 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_waterway.levels_of_service (
-       level_of_service smallint PRIMARY KEY
-       );
+    level_of_service smallint PRIMARY KEY
+);
 
 CREATE TABLE gemma_waterway.fairway_dimensions (
-       area geometry(POLYGON, 4326) PRIMARY KEY,
-       level_of_service smallint NOT NULL REFERENCES levels_of_service,
-       min_width smallint NOT NULL,
-       max_width smallint NOT NULL,
-       min_depth smallint NOT NULL,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
+    area geometry(POLYGON, 4326) PRIMARY KEY,
+    level_of_service smallint NOT NULL REFERENCES levels_of_service,
+    min_width smallint NOT NULL,
+    max_width smallint NOT NULL,
+    min_depth smallint NOT NULL,
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL
+);
 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 --
 -- Bottlenecks
 --
 CREATE TABLE gemma_fairway.riverbed_materials (
-       material varchar PRIMARY KEY
-       -- XXX: Should this table contain choices from DRC 2.2.3 or
-       -- from IENC Encoding Guide M.4.3, attribute NATSUR?
-       );
+    material varchar PRIMARY KEY
+    -- XXX: Should this table contain choices from DRC 2.2.3 or
+    -- from IENC Encoding Guide M.4.3, attribute NATSUR?
+);
 
 CREATE TABLE gemma_fairway.survey_types (
-       survey_type varchar PRIMARY KEY
-       );
+    survey_type varchar PRIMARY KEY
+);
 
 CREATE TABLE gemma_fairway.coverage_types (
-       coverage_type varchar PRIMARY KEY
-       );
+    coverage_type varchar PRIMARY KEY
+);
 
 CREATE TABLE gemma_fairway.limiting_factors (
-       limiting_factor varchar PRIMARY KEY
-       );
+    limiting_factor varchar PRIMARY KEY
+);
 
 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
 CREATE TABLE gemma_fairway.bottlenecks (
-       bottleneck_id varchar PRIMARY KEY,
-       fk_g_fid isrs NOT NULL REFERENCES gauges,
-       -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
-       objnam varchar,
-       nobjnm varchar,
-       stretch isrsrange NOT NULL,
-       area geometry(POLYGON, 4326) NOT NULL,
-       rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
-       lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
-       responsible_country char(2) NOT NULL REFERENCES countries,
-       revisiting_time smallint NOT NULL,
-       limiting varchar NOT NULL REFERENCES limiting_factors,
-       -- surtyp varchar NOT NULL REFERENCES survey_types,
-       -- XXX: Also an attribut of sounding result?
-       -- coverage varchar REFERENCES coverage_types,
-       -- XXX: Also an attribut of sounding result?
-       -- CHECK allowed combinations of surtyp and coverage or
-       -- different model approach?
-       -- depth_reference char(3) NOT NULL REFERENCES depth_references,
-       -- XXX: Also an attribut of sounding result?
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL,
-       -- additional_data xml -- Currently not relevant for GEMMA
-       staging_done boolean NOT NULL DEFAULT false
-       );
+    bottleneck_id varchar PRIMARY KEY,
+    fk_g_fid isrs NOT NULL REFERENCES gauges,
+    -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
+    objnam varchar,
+    nobjnm varchar,
+    stretch isrsrange NOT NULL,
+    area geometry(POLYGON, 4326) NOT NULL,
+    rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
+    lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
+    responsible_country char(2) NOT NULL REFERENCES countries,
+    revisiting_time smallint NOT NULL,
+    limiting varchar NOT NULL REFERENCES limiting_factors,
+    -- surtyp varchar NOT NULL REFERENCES survey_types,
+    -- XXX: Also an attribut of sounding result?
+    -- coverage varchar REFERENCES coverage_types,
+    -- XXX: Also an attribut of sounding result?
+    -- CHECK allowed combinations of surtyp and coverage or
+    -- different model approach?
+    -- depth_reference char(3) NOT NULL REFERENCES depth_references,
+    -- XXX: Also an attribut of sounding result?
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL,
+    -- additional_data xml -- Currently not relevant for GEMMA
+    staging_done boolean NOT NULL DEFAULT false
+);
 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials (
-       bottleneck_id varchar REFERENCES bottlenecks,
-       riverbed varchar REFERENCES riverbed_materials,
-       -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
-       PRIMARY KEY (bottleneck_id, riverbed)
-       );
+    bottleneck_id varchar REFERENCES bottlenecks,
+    riverbed varchar REFERENCES riverbed_materials,
+    -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
+    PRIMARY KEY (bottleneck_id, riverbed)
+);
 
 CREATE TABLE gemma_fairway.sounding_results (
-       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
-       date_info date NOT NULL,
-       PRIMARY KEY (bottleneck_id, date_info),
-       area geometry(POLYGON, 4326) NOT NULL,
-       surtyp varchar NOT NULL REFERENCES survey_types,
-       coverage varchar REFERENCES coverage_types,
-       depth_reference char(3) NOT NULL REFERENCES depth_references,
-       sounding_data raster NOT NULL,
-       staging_done boolean NOT NULL DEFAULT false
-       );
+    bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+    date_info date NOT NULL,
+    PRIMARY KEY (bottleneck_id, date_info),
+    area geometry(POLYGON, 4326) NOT NULL,
+    surtyp varchar NOT NULL REFERENCES survey_types,
+    coverage varchar REFERENCES coverage_types,
+    depth_reference char(3) NOT NULL REFERENCES depth_references,
+    sounding_data raster NOT NULL,
+    staging_done boolean NOT NULL DEFAULT false
+);
 
 --
 -- Fairway availability
 --
 CREATE TABLE gemma_fairway.measure_types (
-       measure_type varchar PRIMARY KEY
-       );
+    measure_type varchar PRIMARY KEY
+);
 
 CREATE TABLE gemma_fairway.fairway_availability (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       position_code char(2) REFERENCES position_codes,
-       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
-       surdat date NOT NULL,
-       UNIQUE (bottleneck_id, surdat),
-       -- additional_data xml -- Currently not relevant for GEMMA
-       critical boolean,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
+    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+    position_code char(2) REFERENCES position_codes,
+    bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+    surdat date NOT NULL,
+    UNIQUE (bottleneck_id, surdat),
+    -- additional_data xml -- Currently not relevant for GEMMA
+    critical boolean,
+    date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    source_organization varchar NOT NULL
+);
 CREATE TRIGGER fairway_availability_date_info
-       BEFORE UPDATE ON fairway_availability
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+    BEFORE UPDATE ON fairway_availability
+    FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma_fairway.fa_reference_values (
-       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
-       level_of_service smallint NOT NULL REFERENCES levels_of_service,
-       PRIMARY KEY (fairway_availability_id, level_of_service),
-       fairway_depth smallint,
-       fairway_width smallint,
-       fairway_radius int,
-       CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
-       shallowest_spot geometry(POINT, 4326)
-       );
+    fairway_availability_id int NOT NULL REFERENCES fairway_availability,
+    level_of_service smallint NOT NULL REFERENCES levels_of_service,
+    PRIMARY KEY (fairway_availability_id, level_of_service),
+    fairway_depth smallint,
+    fairway_width smallint,
+    fairway_radius int,
+    CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
+    shallowest_spot geometry(POINT, 4326)
+);
 
 CREATE TABLE gemma_fairway.bottleneck_pdfs (
-       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
-       profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
-       profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
-       PRIMARY KEY (fairway_availability_id, profile_pdf_url),
-       pdf_generation_date timestamp with time zone NOT NULL,
-       source_organization varchar NOT NULL
-       );
+    fairway_availability_id int NOT NULL REFERENCES fairway_availability,
+    profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
+    profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
+    PRIMARY KEY (fairway_availability_id, profile_pdf_url),
+    pdf_generation_date timestamp with time zone NOT NULL,
+    source_organization varchar NOT NULL
+);
 
 CREATE TABLE gemma_fairway.effective_fairway_availability (
-       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
-       measure_date timestamp with time zone NOT NULL,
-       level_of_service smallint NOT NULL REFERENCES levels_of_service,
-       PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
-       available_depth_value smallint,
-       available_width_value smallint,
-       water_level_value smallint,
-       CHECK(COALESCE(available_depth_value, available_width_value,
-           water_level_value) IS NOT NULL),
-       measure_type varchar NOT NULL REFERENCES measure_types,
-       source_organization varchar NOT NULL,
-       forecast_generation_time timestamp with time zone,
-       CHECK(measure_type <> 'forecasted'
-             OR forecast_generation_time IS NOT NULL),
-       value_lifetime timestamp with time zone,
-       CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
-       );
+    fairway_availability_id int NOT NULL REFERENCES fairway_availability,
+    measure_date timestamp with time zone NOT NULL,
+    level_of_service smallint NOT NULL REFERENCES levels_of_service,
+    PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
+    available_depth_value smallint,
+    available_width_value smallint,
+    water_level_value smallint,
+    CHECK(COALESCE(available_depth_value, available_width_value,
+        water_level_value) IS NOT NULL),
+    measure_type varchar NOT NULL REFERENCES measure_types,
+    source_organization varchar NOT NULL,
+    forecast_generation_time timestamp with time zone,
+    CHECK(measure_type <> 'forecasted'
+        OR forecast_generation_time IS NOT NULL),
+    value_lifetime timestamp with time zone,
+    CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
+);
 
 COMMIT;