Mercurial > gemma
changeset 3969:7015fb18d536
Moved db migrations to 3.0.1 to a new folder under updates.
Replace the fragile symbolic link with a copy.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 17 Jul 2019 10:29:02 +0200 |
| parents | f68529ff47a4 |
| children | 32e9ae5210f9 |
| files | schema/updates/3.0.1/01.dismar-wwname.sql schema/updates/3.0.1/02.search_functions.sql schema/updates_3.0.1/01.dismar-wwname.sql schema/updates_3.0.1/02.search_functions.sql |
| diffstat | 4 files changed, 172 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- /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;
--- /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 <wilde@intevation.de> + +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; +$$;