Mercurial > gemma
changeset 579:642df1164aca
Ensure gauges are identified by appropriate ISRS location code
The Object Reference Code ist checked according to section
I.3.4 Waterway Gauge of the RIS-Index Encoding Guide.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 06 Sep 2018 16:15:38 +0200 |
parents | 61af85a432bf |
children | e78bdbb6cac8 |
files | schema/auth_tests.sql schema/gemma.sql schema/isrs.sql schema/tap_tests_data.sql |
diffstat | 4 files changed, 8 insertions(+), 6 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth_tests.sql Thu Sep 06 13:19:51 2018 +0200 +++ b/schema/auth_tests.sql Thu Sep 06 16:15:38 2018 +0200 @@ -33,7 +33,7 @@ revisiting_time, limiting, source_organization) VALUES ( $1, - ('AT', 'XXX', '00001', '00000', 1)::isrs, + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), $2, 'AT', 'AT', 'AT',
--- a/schema/gemma.sql Thu Sep 06 13:19:51 2018 +0200 +++ b/schema/gemma.sql Thu Sep 06 16:15:38 2018 +0200 @@ -201,7 +201,9 @@ CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23))) CREATE TABLE gauges ( - location isrs PRIMARY KEY, + location isrs PRIMARY KEY CHECK( + (location).orc SIMILAR TO 'G[[:digit:]]{4}' + AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), function_code varchar(10) NOT NULL, -- XXX: What is this really for? objname varchar NOT NULL, is_left boolean, -- XXX: Or reference position_codes?
--- a/schema/isrs.sql Thu Sep 06 13:19:51 2018 +0200 +++ b/schema/isrs.sql Thu Sep 06 16:15:38 2018 +0200 @@ -16,7 +16,7 @@ locode char(3), -- without the country code: -- http://www.unece.org/cefact/locode/welcome.html fairway_section char(5), - object_reference char(5), + orc char(5), -- Object Reference Code hectometre int -- should be constrained to five digits );
--- a/schema/tap_tests_data.sql Thu Sep 06 13:19:51 2018 +0200 +++ b/schema/tap_tests_data.sql Thu Sep 06 16:15:38 2018 +0200 @@ -22,7 +22,7 @@ INSERT INTO waterway.gauges ( location, function_code, objname, geom, zero_point, source_organization) VALUES ( - ('AT', 'XXX', '00001', '00000', 1)::isrs, + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, 'xxx', 'testgauge', ST_geomfromtext('POINT(0 0)', 4326), @@ -35,7 +35,7 @@ revisiting_time, limiting, source_organization, staging_done) VALUES ( 'testbottleneck1', - ('AT', 'XXX', '00001', '00000', 1)::isrs, + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), @@ -43,7 +43,7 @@ 1, 'depth', 'testorganization', false ), ( 'testbottleneck2', - ('AT', 'XXX', '00001', '00000', 1)::isrs, + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326),