Mercurial > gemma
changeset 948:5f89868bd75e
Store virtual and physical distance marks separately
Virtual distance marks on waterway axis always have an ISRS location
code, which is their natural primary key, but which other distance
marks do not (always) have. catdis and position_code are constant
for distance marks on fairway axis and do not need to be stored.
Btw. add a previously missing field for the name of the ENC the
information is based on.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Sat, 13 Oct 2018 14:26:40 +0200 |
parents | 1417ae641f7f |
children | d11f1211b4e6 |
files | schema/gemma.sql |
diffstat | 1 files changed, 14 insertions(+), 5 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Oct 12 09:36:46 2018 +0200 +++ b/schema/gemma.sql Sat Oct 13 14:26:40 2018 +0200 @@ -286,11 +286,21 @@ -- 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 -- distance marks along waterway axis (see SUC7). - CREATE TABLE distance_marks ( + CREATE TABLE distance_marks_virtual ( location_code isrs PRIMARY KEY, geom geography(POINT, 4326) NOT NULL, + related_enc varchar(12) NOT NULL + ) + + CREATE TABLE distance_marks ( + country char(2) NOT NULL REFERENCES countries, + hectom int NOT NULL, + 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 + position_code char(2) NOT NULL REFERENCES position_codes, + related_enc varchar(12) NOT NULL ) -- A table to help geoserver serve the distance marks as WFS 1.1.0. @@ -299,10 +309,9 @@ CREATE VIEW waterway.distance_marks_geoserver AS SELECT location_code::VARCHAR, geom::Geometry(POINT, 4326), - catdis, - position_code, + related_enc, (location_code).hectometre - FROM waterway.distance_marks + FROM waterway.distance_marks_virtual CREATE TABLE sections_stretches ( id varchar PRIMARY KEY,