Mercurial > gemma

changeset 579:642df1164aca

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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),