# HG changeset patch # User Sascha L. Teichmann # Date 1563352142 -7200 # Node ID 7015fb18d5365a8b92253c218ddfd025757337e8 # Parent f68529ff47a4bc081146fe98c21b5e93784559a8 Moved db migrations to 3.0.1 to a new folder under updates. Replace the fragile symbolic link with a copy. diff -r f68529ff47a4 -r 7015fb18d536 schema/updates/3.0.1/01.dismar-wwname.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/3.0.1/01.dismar-wwname.sql Wed Jul 17 10:29:02 2019 +0200 @@ -0,0 +1,2 @@ +ALTER TABLE waterway.distance_marks_virtual + ADD COLUMN IF NOT EXISTS wwname varchar; diff -r f68529ff47a4 -r 7015fb18d536 schema/updates/3.0.1/02.search_functions.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/3.0.1/02.search_functions.sql Wed Jul 17 10:29:02 2019 +0200 @@ -0,0 +1,170 @@ +-- 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): +-- * Sascha Wilde + +CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + IF search_string SIMILAR TO '[0-9]+' THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT (location_code).hectometre || ' rhm' AS name, + isrs_asText(location_code) AS locationcode, + wwname AS location, + ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type + FROM waterway.distance_marks_virtual + WHERE (location_code).hectometre = search_string::int) r; + ELSIF is_ISRSstring(search_string) THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT (location_code).hectometre || ' rhm' AS name, + isrs_asText(location_code) AS locationcode, + wwname AS location, + ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type + FROM waterway.distance_marks_virtual + WHERE location_code = isrs_fromText(search_string)) r; + ELSE + _result='[]'; + END IF; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, + 'bottleneck' AS type + FROM waterway.bottlenecks + WHERE objnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT name, + country_code AS location, + ST_AsGeoJSON(location)::json AS geom, + 'city' AS type + FROM waterway.geonames + WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') + AND (name ILIKE '%' || search_string || '%' + OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) + ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], + feature_code::text), + name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + IF is_ISRSstring(search_string) THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM ( + SELECT objname AS name, + ST_AsGeoJSON(geom)::json AS geom, + 'gauge' AS type, + isrs_asText(location) AS locationcode + FROM waterway.gauges + WHERE NOT erased AND location = isrs_fromText(search_string) + ORDER BY name) r; + ELSE + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM ( + SELECT objname AS name, + ST_AsGeoJSON(geom)::json AS geom, + 'gauge' AS type, + isrs_asText(location) AS locationcode + FROM waterway.gauges + WHERE NOT erased AND objname ILIKE '%' || search_string || '%' + ORDER BY name) r; + END IF; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT id, + objnam AS name, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, + 'section' AS type + FROM waterway.sections + WHERE objnam ILIKE '%' || search_string || '%' + OR nobjnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT id, + objnam AS name, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, + 'stretch' AS type + FROM waterway.stretches + WHERE objnam ILIKE '%' || search_string || '%' + OR nobjnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +BEGIN + RETURN search_hectometre(search_string) + || search_bottlenecks(search_string) + || search_gauges(search_string) + || search_sections(search_string) + || search_stretches(search_string) + || search_cities(search_string); +END; +$$; diff -r f68529ff47a4 -r 7015fb18d536 schema/updates_3.0.1/01.dismar-wwname.sql --- a/schema/updates_3.0.1/01.dismar-wwname.sql Tue Jul 16 18:39:24 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,2 +0,0 @@ -ALTER TABLE waterway.distance_marks_virtual - ADD COLUMN IF NOT EXISTS wwname varchar; diff -r f68529ff47a4 -r 7015fb18d536 schema/updates_3.0.1/02.search_functions.sql --- a/schema/updates_3.0.1/02.search_functions.sql Tue Jul 16 18:39:24 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,1 +0,0 @@ -../search_functions.sql \ No newline at end of file