Mercurial > gemma
diff schema/gemma.sql @ 1905:4f58bada50b8 dev-pdf-generation
merging in default branch
author | Bernhard Reiter <bernhard@intevation.de> |
---|---|
date | Fri, 18 Jan 2019 17:10:16 +0100 |
parents | d72a1539ef3c |
children | 32c56e6c089a |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Jan 18 16:52:20 2019 +0100 +++ b/schema/gemma.sql Fri Jan 18 17:10:16 2019 +0100 @@ -234,24 +234,23 @@ catccl smallint REFERENCES catccls, dirimp smallint REFERENCES dirimps ) - CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23))) CREATE TABLE gauges ( location isrs PRIMARY KEY CHECK( (location).orc SIMILAR TO 'G[[:digit:]]{4}' AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), objname varchar NOT NULL, - is_left boolean, -- XXX: Or reference position_codes? geom geography(POINT, 4326) NOT NULL, - applicability isrsrange, - validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy + applicability_from_km int8, + applicability_to_km int8, + validity tstzrange, -- 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 + source_organization varchar ) CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges FOR EACH ROW EXECUTE PROCEDURE update_date_info() @@ -300,8 +299,6 @@ objnam varchar NOT NULL, nobjnam varchar ) - -- TODO: @tom: Why did you choose this index kind? - -- CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23))) -- 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 @@ -313,25 +310,25 @@ ) CREATE TABLE distance_marks ( - country char(2) NOT NULL REFERENCES countries, - hectom int NOT NULL, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + country char(2) REFERENCES countries, + hectom int, 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, - related_enc varchar(12) NOT NULL + catdis smallint REFERENCES catdis, + position_code char(2) REFERENCES position_codes, + related_enc varchar(12) ) -- A table to help geoserver serve the distance marks as WFS 1.1.0. -- At least geoserver-2.13.2 does not serve type geography correctly -- and does not serve the location_code as isrs type - CREATE VIEW waterway.distance_marks_geoserver AS + CREATE VIEW distance_marks_geoserver AS SELECT location_code::VARCHAR, geom::Geometry(POINT, 4326), related_enc, (location_code).hectometre - FROM waterway.distance_marks_virtual + FROM distance_marks_virtual -- We need to configure primary keys for the views used by -- geoserver for wfs, otherwise it will generate ids on the fly, @@ -350,18 +347,30 @@ check (pk_policy in ('sequence', 'assigned', 'autogenerated')) ) - CREATE TABLE sections_stretches ( - id varchar PRIMARY KEY, - is_section boolean NOT NULL, -- maps 'function' from interface - stretch isrsrange, + CREATE TABLE stretches ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name varchar NOT NULL, + stretch isrsrange NOT NULL, + -- TODO: make it a (MULTI)-LINESTRING. + -- POLYGON is chosen for the convinience + -- of re-using ISRSrange_area. + geom geography(POLYGON, 4326) NOT NULL, objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, - staging_done boolean NOT NULL DEFAULT false + staging_done boolean NOT NULL DEFAULT false, + UNIQUE(name, staging_done) ) + + CREATE TABLE stretch_countries ( + stretches_id int NOT NULL REFERENCES stretches(id), + country_code char(2) NOT NULL REFERENCES countries(country_code), + UNIQUE(stretches_id, country_code) + ) + CREATE TRIGGER sections_stretches_date_info - BEFORE UPDATE ON sections_stretches + BEFORE UPDATE ON stretches FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE waterway_profiles ( @@ -396,7 +405,6 @@ source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false ) - CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23))) CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions FOR EACH ROW EXECUTE PROCEDURE update_date_info() @@ -468,13 +476,13 @@ ) -- A view to help geoserver serve contour lines. -- At least geoserver-2.13.2 does not serve type geography correctly - CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS + CREATE VIEW sounding_results_contour_lines_geoserver AS SELECT bottleneck_id, date_info, height, CAST(lines AS geometry(multilinestring, 4326)) AS lines - FROM waterway.sounding_results_contour_lines cl - JOIN waterway.sounding_results sr + FROM sounding_results_contour_lines cl + JOIN sounding_results sr ON sr.id = cl.sounding_result_id -- @@ -536,16 +544,15 @@ OR value_lifetime IS NOT NULL) ) - CREATE VIEW waterway.bottleneck_overview AS + CREATE VIEW bottleneck_overview AS SELECT objnam AS name, ST_Centroid(area)::Geometry AS point, (lower(stretch)).hectometre AS from, (upper(stretch)).hectometre AS to, sr.current::text - FROM waterway.bottlenecks bn LEFT JOIN ( - SELECT bottleneck_id, max(date_info) AS current FROM - waterway.sounding_results + FROM bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current FROM sounding_results GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id ORDER BY objnam