Mercurial > gemma
changeset 2452:0f1a915344c5
Added the ability to search for gauges.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Fri, 01 Mar 2019 17:06:25 +0100 |
parents | 960550ccca55 |
children | 00cac7890574 |
files | schema/search_functions.sql |
diffstat | 1 files changed, 21 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/search_functions.sql Fri Mar 01 16:28:24 2019 +0100 +++ b/schema/search_functions.sql Fri Mar 01 17:06:25 2019 +0100 @@ -51,10 +51,30 @@ END; $$; +CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objname AS name, + ST_AsGeoJSON(geom)::json AS geom, + 'gauge' AS type + FROM waterway.gauges + WHERE objname ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb LANGUAGE plpgsql AS $$ BEGIN - RETURN search_bottlenecks(search_string) || search_cities(search_string); + RETURN search_bottlenecks(search_string) + || search_gauges(search_string) + || search_cities(search_string); END; $$;