Mercurial > gemma

changeset 3566:4c585b5d4fe8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce hectometer based order for ranges of ISRS location codes
author Tom Gottfried <tom@intevation.de>
date Fri, 31 May 2019 20:06:27 +0200
parents f6d67697f158
children 2b002b042499
files pkg/imports/bn.go pkg/imports/sec.go pkg/imports/st.go schema/isrs.sql schema/isrs_tests.sql schema/run_tests.sh
diffstat 6 files changed, 182 insertions(+), 52 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/bn.go	Fri May 31 17:43:39 2019 +0200
+++ b/pkg/imports/bn.go	Fri May 31 20:06:27 2019 +0200
@@ -46,6 +46,11 @@
 SELECT true FROM waterway.bottlenecks WHERE bottleneck_id = $1`
 
 	insertBottleneckSQL = `
+WITH
+bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))),
+r AS (SELECT isrsrange(
+    (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
+    (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
 INSERT INTO waterway.bottlenecks (
   bottleneck_id,
   gauge_location,
@@ -70,11 +75,9 @@
     tstzrange(NULL, NULL)),
   $3,
   $4,
-  isrsrange(least(isrs_fromText($5), isrs_fromText($6)),
-            greatest(isrs_fromText($5), isrs_fromText($6))),
+  (SELECT r FROM r),
   ISRSrange_area(
-    ISRSrange_axis(isrsrange(least(isrs_fromText($5), isrs_fromText($6)),
-                             greatest(isrs_fromText($5), isrs_fromText($6))),
+    ISRSrange_axis((SELECT r FROM r),
                    $14),
     (SELECT ST_Collect(CAST(area AS geometry))
         FROM waterway.waterway_area)),
--- a/pkg/imports/sec.go	Fri May 31 17:43:39 2019 +0200
+++ b/pkg/imports/sec.go	Fri May 31 20:06:27 2019 +0200
@@ -74,29 +74,22 @@
         relation = 'waterway.sections'::regclass)`
 
 	secInsertSQL = `
-WITH r AS (
-  SELECT isrsrange(
-    least(($1::char(2),
-           $2::char(3),
-           $3::char(5),
-           $4::char(5),
-           $5::int)::isrs,
-          ($6::char(2),
-           $7::char(3),
-           $8::char(5),
-           $9::char(5),
-           $10::int)::isrs),
-    greatest(($1::char(2),
-              $2::char(3),
-              $3::char(5),
-              $4::char(5),
-              $5::int)::isrs,
-             ($6::char(2),
-              $7::char(3),
-              $8::char(5),
-              $9::char(5),
-              $10::int)::isrs)
-    ) AS r),
+WITH
+bounds (b) AS (VALUES (
+    ($1::char(2),
+     $2::char(3),
+     $3::char(5),
+     $4::char(5),
+     $5::int)::isrs
+  ), (
+    ($6::char(2),
+     $7::char(3),
+     $8::char(5),
+     $9::char(5),
+     $10::int)::isrs)),
+r AS (SELECT isrsrange(
+    (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
+    (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r),
 axs AS (
   SELECT ISRSrange_axis((SELECT r FROM r), $16::double precision) AS axs)
 INSERT INTO waterway.sections (
--- a/pkg/imports/st.go	Fri May 31 17:43:39 2019 +0200
+++ b/pkg/imports/st.go	Fri May 31 20:06:27 2019 +0200
@@ -76,29 +76,22 @@
         relation = 'waterway.stretches'::regclass)`
 
 	stInsertSQL = `
-WITH r AS (
-  SELECT isrsrange(
-    least(($1::char(2),
-           $2::char(3),
-           $3::char(5),
-           $4::char(5),
-           $5::int)::isrs,
-          ($6::char(2),
-           $7::char(3),
-           $8::char(5),
-           $9::char(5),
-           $10::int)::isrs),
-    greatest(($1::char(2),
-              $2::char(3),
-              $3::char(5),
-              $4::char(5),
-              $5::int)::isrs,
-             ($6::char(2),
-              $7::char(3),
-              $8::char(5),
-              $9::char(5),
-              $10::int)::isrs)
-    ) AS r),
+WITH
+bounds (b) AS (VALUES (
+    ($1::char(2),
+     $2::char(3),
+     $3::char(5),
+     $4::char(5),
+     $5::int)::isrs
+  ), (
+    ($6::char(2),
+     $7::char(3),
+     $8::char(5),
+     $9::char(5),
+     $10::int)::isrs)),
+r AS (SELECT isrsrange(
+    (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
+    (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r),
 axs AS (
   SELECT ISRSrange_axis((SELECT r FROM r), $16::double precision) AS axs)
 INSERT INTO waterway.stretches (
--- a/schema/isrs.sql	Fri May 31 17:43:39 2019 +0200
+++ b/schema/isrs.sql	Fri May 31 20:06:27 2019 +0200
@@ -47,10 +47,101 @@
     CHECK (is_country((VALUE).country_code))
     CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
 
+CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int
+AS $$
+    /* TODO: handle non-matching combinations of country_codes and
+       fairway_sections. Otherwise, this will give unexpected results if
+       both hectometre values do not refer to the same river. */
+    SELECT (a).hectometre - (b).hectometre
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
+AS $$
+    SELECT isrs_cmp(a, b) < 0
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
+AS $$
+    SELECT isrs_cmp(a, b) <= 0
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
+AS $$
+    SELECT isrs_cmp(a, b) = 0
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
+AS $$
+    SELECT isrs_cmp(a, b) >= 0
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
+AS $$
+    SELECT isrs_cmp(a, b) > 0
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OPERATOR <~ (
+    leftarg = isrs,
+    rightarg = isrs,
+    function = isrslt
+);
+
+CREATE OPERATOR <~= (
+    leftarg = isrs,
+    rightarg = isrs,
+    function = isrsle
+);
+
+CREATE OPERATOR ~= (
+    leftarg = isrs,
+    rightarg = isrs,
+    function = isrseq,
+    commutator = ~=
+);
+
+CREATE OPERATOR >~= (
+    leftarg = isrs,
+    rightarg = isrs,
+    function = isrsge,
+    commutator = <~=,
+    negator = <~
+);
+
+CREATE OPERATOR >~ (
+    leftarg = isrs,
+    rightarg = isrs,
+    function = isrsgt,
+    commutator = <~,
+    negator = <~=
+);
+
+CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS
+    OPERATOR 1 <~,
+    OPERATOR 2 <~=,
+    OPERATOR 3 ~=,
+    OPERATOR 4 >~=,
+    OPERATOR 5 >~,
+    FUNCTION 1 isrs_cmp;
+
+CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
+AS $$
+    SELECT CAST(isrs_cmp(a, b) AS double precision)
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
 CREATE TYPE isrsrange AS RANGE (
-    subtype = isrs
+    subtype = isrs,
+    subtype_opclass = isrs_ops,
+    subtype_diff = isrs_diff
 );
 
+
 --
 -- Functions
 --
--- a/schema/isrs_tests.sql	Fri May 31 17:43:39 2019 +0200
+++ b/schema/isrs_tests.sql	Fri May 31 20:06:27 2019 +0200
@@ -15,6 +15,9 @@
 -- pgTAP test script for ISRS location code types and functions
 --
 
+--
+-- Conversion from/to text
+--
 SELECT results_eq($$
     SELECT isrs_fromText('DEBON03901G007906548')
     $$,
@@ -34,6 +37,53 @@
     ,
     'isrs_asText() is the inverse of isrs_fromText()');
 
+--
+-- Comparison operators
+--
+SELECT ok(
+    isrs_fromText('DEBON03901G007906548')
+        <> isrs_fromText('DEXXX039010000006548'),
+    'Different codes at equal hectometre do not equal by default');
+
+SELECT ok(
+    isrs_fromText('DEBON03901G007906548')
+        ~= isrs_fromText('DEXXX039010000006548')
+    AND isrs_fromText('DEBON03901G007906548')
+        >~= isrs_fromText('DEXXX039010000006548')
+    AND isrs_fromText('DEBON03901G007906548')
+        <~= isrs_fromText('DEXXX039010000006548'),
+    'isrs_ops: Different codes at equal hectometre compare as equal');
+
+SELECT ok(
+    isrs_fromText('DEBON03901G007906549')
+        >~ isrs_fromText('DEXXX039010000006548')
+    AND isrs_fromText('DEBON03901G007906549')
+        >~= isrs_fromText('DEXXX039010000006548')
+    AND isrs_fromText('DEXXX039010000006547')
+        <~= isrs_fromText('DEBON03901G007906548')
+    AND isrs_fromText('DEXXX039010000006547')
+        <~ isrs_fromText('DEBON03901G007906548'),
+    'isrs_ops: Ordering depends on hectometre');
+
+SELECT ok(
+    isrsrange(isrs_fromText('DEXXX039010000006540'),
+        isrs_fromText('DEXXX039010000006560'))
+    @> isrs_fromText('ATXXX000000000006550')
+    AND isrs_fromText('DEXXX039010000006560')
+    <@ isrsrange(isrs_fromText('ATXXX000000000006550'),
+        isrs_fromText('ATXXX000000000006570')),
+    'isrsrange: ''Contains'' depends on hectometre');
+
+SELECT ok(
+    isrsrange(isrs_fromText('DEXXX039010000006540'),
+        isrs_fromText('DEXXX039010000006560'))
+    && isrsrange(isrs_fromText('ATXXX000000000006550'),
+        isrs_fromText('ATXXX000000000006570')),
+    'isrsrange: Overlap depends on hectometre');
+
+--
+-- Geometry processing
+--
 SELECT throws_ok($$
     SELECT ISRSrange_points(isrsrange(
             ('AT', 'XXX', '00001', '00000', 0)::isrs,
--- a/schema/run_tests.sh	Fri May 31 17:43:39 2019 +0200
+++ b/schema/run_tests.sh	Fri May 31 20:06:27 2019 +0200
@@ -28,7 +28,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c "SELECT plan(65 + (
+    -c "SELECT plan(70 + (
             SELECT count(*)::int
                 FROM information_schema.tables
                 WHERE table_schema = 'waterway'))" \