Mercurial > gemma

comparison schema/search_functions.sql @ 1833:b9c59050014a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make search functions self-replacing and indicate that in filename All other files containing only functions already were named *_functions.sql.
author Tom Gottfried <tom@intevation.de>
date Wed, 16 Jan 2019 17:41:05 +0100
parents
children 0f1a915344c5
comparison
equal deleted inserted replaced
1832:661597546ed9 1833:b9c59050014a
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
2 -- without warranty, see README.md and license for details.
3
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6
7 -- Copyright (C) 2018 by via donau
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
9 -- Software engineering by Intevation GmbH
10
11 -- Author(s):
12 -- * Sascha Wilde <wilde@intevation.de>
13
14 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
15 LANGUAGE plpgsql
16 AS $$
17 DECLARE
18 _result jsonb;
19 BEGIN
20 SELECT COALESCE(json_agg(r),'[]')
21 INTO _result
22 FROM (SELECT objnam AS name,
23 ST_AsGeoJSON(ST_Centroid(area))::json AS geom,
24 'bottleneck' AS type
25 FROM waterway.bottlenecks
26 WHERE objnam ILIKE '%' || search_string || '%'
27 ORDER BY name) r;
28 RETURN _result;
29 END;
30 $$;
31
32 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
33 LANGUAGE plpgsql
34 AS $$
35 DECLARE
36 _result jsonb;
37 BEGIN
38 SELECT COALESCE(json_agg(r),'[]')
39 INTO _result
40 FROM (SELECT name || ' (' || country_code || ')' AS name,
41 ST_AsGeoJSON(location)::json AS geom,
42 'city' AS type
43 FROM waterway.geonames
44 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
45 AND (name ILIKE '%' || search_string || '%'
46 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
47 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
48 feature_code::text),
49 name) r;
50 RETURN _result;
51 END;
52 $$;
53
54 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
55 LANGUAGE plpgsql
56 AS $$
57 BEGIN
58 RETURN search_bottlenecks(search_string) || search_cities(search_string);
59 END;
60 $$;