Mercurial > gemma
changeset 3741:71de5ce5a402
Added search for stretches.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Mon, 24 Jun 2019 12:47:34 +0200 |
parents | 6f1d5f310e08 |
children | fcac7787ec22 16c3c0150030 |
files | schema/search_functions.sql |
diffstat | 1 files changed, 20 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/search_functions.sql Mon Jun 24 12:43:30 2019 +0200 +++ b/schema/search_functions.sql Mon Jun 24 12:47:34 2019 +0200 @@ -130,6 +130,25 @@ 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 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 $$ @@ -138,6 +157,7 @@ || search_bottlenecks(search_string) || search_gauges(search_string) || search_sections(search_string) + || search_stretches(search_string) || search_cities(search_string); END; $$;