Mercurial > gemma
view schema/isrs.sql @ 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 | 72430f050d90 |
line wrap: on
line source
-- -- Types and functions for ISRS location codes -- BEGIN; -- -- Types -- -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. -- See RIS-Index Encoding Guide CREATE TYPE isrs AS ( country_code char(2), -- ISO 3166 country code -- could be validated against countries table. locode char(3), -- without the country code: -- http://www.unece.org/cefact/locode/welcome.html fairway_section char(5), orc char(5), -- Object Reference Code hectometre int -- should be constrained to five digits ); CREATE TYPE isrsrange AS RANGE ( subtype = isrs ); -- -- Functions -- CREATE OR REPLACE FUNCTION isrs_fromText( isrs_text varchar ) RETURNS isrs AS $$ DECLARE isrs_len CONSTANT int = 20; BEGIN IF char_length(isrs_text) <> isrs_len THEN RAISE 'ISRS location code must be % characters long', isrs_len USING ERRCODE = 'invalid_parameter_value'; ELSE RETURN CAST(( substring(isrs_text from 1 for 2), substring(isrs_text from 3 for 3), substring(isrs_text from 6 for 5), substring(isrs_text from 11 for 5), CAST(substring(isrs_text from 16 for 5) AS int)) AS isrs); END IF; END; $$ LANGUAGE plpgsql; COMMIT;