# HG changeset patch # User Tom Gottfried # Date 1559325987 -7200 # Node ID 4c585b5d4fe8877c6d3a198917b223ef983e8fd6 # Parent f6d67697f158f680fbfc1167b8bd1e7282bfd637 Introduce hectometer based order for ranges of ISRS location codes diff -r f6d67697f158 -r 4c585b5d4fe8 pkg/imports/bn.go --- 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)), diff -r f6d67697f158 -r 4c585b5d4fe8 pkg/imports/sec.go --- 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 ( diff -r f6d67697f158 -r 4c585b5d4fe8 pkg/imports/st.go --- 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 ( diff -r f6d67697f158 -r 4c585b5d4fe8 schema/isrs.sql --- 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 -- diff -r f6d67697f158 -r 4c585b5d4fe8 schema/isrs_tests.sql --- 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, diff -r f6d67697f158 -r 4c585b5d4fe8 schema/run_tests.sh --- 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'))" \