Mercurial > gemma

annotate schema/search_functions.sql @ 2549:9bf6b767a56a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: refactored and improved splitscreen for diagrams To make different diagrams possible, the splitscreen view needed to be decoupled from the cross profiles. Also the style has changed to make it more consistent with the rest of the app. The standard box header is now used and there are collapse and expand animations.
author Markus Kottlaender <markus@intevation.de>
date Fri, 08 Mar 2019 08:50:47 +0100
parents 0f1a915344c5
children 194feb1a7e37
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2018 by via donau
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Author(s):
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- * Sascha Wilde <wilde@intevation.de>
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 LANGUAGE plpgsql
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 INTO _result
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 FROM (SELECT objnam AS name,
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ST_AsGeoJSON(ST_Centroid(area))::json AS geom,
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 'bottleneck' AS type
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 FROM waterway.bottlenecks
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 WHERE objnam ILIKE '%' || search_string || '%'
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 ORDER BY name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 LANGUAGE plpgsql
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 INTO _result
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FROM (SELECT name || ' (' || country_code || ')' AS name,
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 ST_AsGeoJSON(location)::json AS geom,
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 'city' AS type
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 FROM waterway.geonames
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 AND (name ILIKE '%' || search_string || '%'
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 feature_code::text),
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
54 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
55 LANGUAGE plpgsql
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
56 AS $$
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
57 DECLARE
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
58 _result jsonb;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
59 BEGIN
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
60 SELECT COALESCE(json_agg(r),'[]')
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
61 INTO _result
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
62 FROM (SELECT objname AS name,
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
63 ST_AsGeoJSON(geom)::json AS geom,
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
64 'gauge' AS type
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
65 FROM waterway.gauges
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
66 WHERE objname ILIKE '%' || search_string || '%'
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
67 ORDER BY name) r;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
68 RETURN _result;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
69 END;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
70 $$;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
71
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 LANGUAGE plpgsql
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 BEGIN
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
76 RETURN search_bottlenecks(search_string)
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
77 || search_gauges(search_string)
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
78 || search_cities(search_string);
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 $$;