Mercurial > gemma
view schema/isrs.sql @ 2652:a2dd96c305be
client: waterlevel diagram: fixed animated x-axis in mainchart when zooming
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Thu, 14 Mar 2019 14:56:40 +0100 |
parents | 5b3d778db00c |
children | 4c585b5d4fe8 |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- -- Types and functions for ISRS location codes -- BEGIN; CREATE TABLE countries ( country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code ); CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean AS $$ SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code) $$ LANGUAGE sql STABLE PARALLEL SAFE; -- -- Types -- -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. -- See RIS-Index Encoding Guide CREATE TYPE isrs_base AS ( country_code char(2), -- ISO 3166 country code 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 ); CREATE DOMAIN isrs AS isrs_base CHECK (is_country((VALUE).country_code)) CHECK ((VALUE).hectometre BETWEEN 0 AND 99999); 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', DETAIL = 'Failing value: ' || isrs_text; 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; CREATE OR REPLACE FUNCTION isrs_asText( code isrs ) RETURNS text AS $$ SELECT code.country_code || code.locode || code.fairway_section || code.orc || lpad(CAST(code.hectometre AS text), 5, '0'); $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; COMMIT;