Mercurial > gemma
annotate schema/search_functions.sql @ 3158:f91df0bc4986
client: compare surveys: prevent loading of differences from blocking the whole dialog
The loading process of differences data is now visualized inside of the button to toggle between differences and the survey layer.
The button is now disabled and says 'calculating differences' for the time of the calculation
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Mon, 06 May 2019 12:21:28 +0200 |
parents | 0f1a915344c5 |
children | 194feb1a7e37 |
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 $$; |