Mercurial > gemma
changeset 3383:194feb1a7e37
Added sections to searchable objects.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 22 May 2019 15:56:33 +0200 |
parents | d83c738e8627 |
children | 4ed940b0f0ca |
files | schema/search_functions.sql |
diffstat | 1 files changed, 20 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/search_functions.sql Wed May 22 15:38:21 2019 +0200 +++ b/schema/search_functions.sql Wed May 22 15:56:33 2019 +0200 @@ -69,12 +69,32 @@ END; $$; +CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Centroid(area))::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_most(search_string text) RETURNS jsonb LANGUAGE plpgsql AS $$ BEGIN RETURN search_bottlenecks(search_string) || search_gauges(search_string) + || search_sections(search_string) || search_cities(search_string); END; $$;