Mercurial > gemma

annotate schema/search_functions.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents a6e5bb85f0a7
children
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
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
7 -- Copyright (C) 2018,2019,2020 by via donau
1833
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
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
14 CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
15 LANGUAGE plpgsql STABLE PARALLEL SAFE
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
16 AS $$
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
17 DECLARE
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
18 _result jsonb;
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
19 BEGIN
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
20 IF search_string SIMILAR TO '[0-9]+' THEN
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
21 SELECT COALESCE(json_agg(r),'[]')
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
22 INTO _result
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
23 FROM (SELECT (location_code).hectometre || ' rhm' AS name,
3739
e1bd5391a4b8 Use correct existing function to convert ISRS code to string.
Sascha Wilde <wilde@intevation.de>
parents: 3738
diff changeset
24 isrs_asText(location_code) AS locationcode,
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
25 wwname AS location,
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
26 ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
27 FROM waterway.distance_marks_virtual
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
28 WHERE (location_code).hectometre = search_string::int) r;
3738
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
29 ELSIF is_ISRSstring(search_string) THEN
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
30 SELECT COALESCE(json_agg(r),'[]')
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
31 INTO _result
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
32 FROM (SELECT (location_code).hectometre || ' rhm' AS name,
3739
e1bd5391a4b8 Use correct existing function to convert ISRS code to string.
Sascha Wilde <wilde@intevation.de>
parents: 3738
diff changeset
33 isrs_asText(location_code) AS locationcode,
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
34 wwname AS location,
3738
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
35 ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
36 FROM waterway.distance_marks_virtual
9f20d2b59d4c Added ISRS code search for rhm.
Sascha Wilde <wilde@intevation.de>
parents: 3737
diff changeset
37 WHERE location_code = isrs_fromText(search_string)) r;
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
38 ELSE
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
39 _result='[]';
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
40 END IF;
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
41 RETURN _result;
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
42 END;
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
43 $$;
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
44
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
45 CREATE OR REPLACE FUNCTION search_bottlenecks(
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
46 search_string text,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
47 search_time timestamp with time zone)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
48 RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
49 LANGUAGE plpgsql STABLE PARALLEL SAFE
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 INTO _result
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 FROM (SELECT objnam AS name,
3740
6f1d5f310e08 Search delivers now bbox (geometry) for sections and bottlenecks.
Sascha Wilde <wilde@intevation.de>
parents: 3739
diff changeset
57 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
4874
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
58 'bottleneck' AS type,
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
59 bottleneck_id AS location
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 FROM waterway.bottlenecks
4874
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
61 WHERE (objnam ILIKE '%' || search_string || '%'
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
62 OR bottleneck_id ILIKE '%' || search_string || '%')
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
63 AND validity @> search_time
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 ORDER BY name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
70 LANGUAGE plpgsql STABLE PARALLEL SAFE
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 INTO _result
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
77 FROM (SELECT name,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
78 country_code AS location,
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 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
80 'city' AS type
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 FROM waterway.geonames
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 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
83 AND (name ILIKE '%' || search_string || '%'
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 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
86 feature_code::text),
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91
3737
e3f15a163823 Fixed typo in search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3736
diff changeset
92 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
93 LANGUAGE plpgsql STABLE PARALLEL SAFE
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
94 AS $$
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
95 DECLARE
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
96 _result jsonb;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
97 BEGIN
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
98 IF is_ISRSstring(search_string) THEN
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
99 SELECT COALESCE(json_agg(r),'[]')
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
100 INTO _result
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
101 FROM (
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
102 SELECT objname AS name,
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
103 ST_AsGeoJSON(geom)::json AS geom,
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
104 'gauge' AS type,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
105 isrs_asText(location) AS locationcode
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
106 FROM waterway.gauges
3736
4cc3b28fd87b Ignore erased gauges in search.
Sascha Wilde <wilde@intevation.de>
parents: 3735
diff changeset
107 WHERE NOT erased AND location = isrs_fromText(search_string)
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
108 ORDER BY name) r;
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
109 ELSE
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
110 SELECT COALESCE(json_agg(r),'[]')
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
111 INTO _result
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
112 FROM (
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
113 SELECT objname AS name,
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
114 ST_AsGeoJSON(geom)::json AS geom,
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
115 'gauge' AS type,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
116 isrs_asText(location) AS locationcode
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
117 FROM waterway.gauges
3736
4cc3b28fd87b Ignore erased gauges in search.
Sascha Wilde <wilde@intevation.de>
parents: 3735
diff changeset
118 WHERE NOT erased AND objname ILIKE '%' || search_string || '%'
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
119 ORDER BY name) r;
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
120 END IF;
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
121 RETURN _result;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
122 END;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
123 $$;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
124
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
125 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
126 LANGUAGE plpgsql STABLE PARALLEL SAFE
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
127 AS $$
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
128 DECLARE
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
129 _result jsonb;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
130 BEGIN
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
131 SELECT COALESCE(json_agg(r),'[]')
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
132 INTO _result
3753
78ba9b2574ff search: include id in search result to mark sections/stretches as selected
Markus Kottlaender <markus@intevation.de>
parents: 3741
diff changeset
133 FROM (SELECT id,
78ba9b2574ff search: include id in search result to mark sections/stretches as selected
Markus Kottlaender <markus@intevation.de>
parents: 3741
diff changeset
134 objnam AS name,
3740
6f1d5f310e08 Search delivers now bbox (geometry) for sections and bottlenecks.
Sascha Wilde <wilde@intevation.de>
parents: 3739
diff changeset
135 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
136 'section' AS type
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
137 FROM waterway.sections
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
138 WHERE objnam ILIKE '%' || search_string || '%'
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
139 OR nobjnam ILIKE '%' || search_string || '%'
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
140 ORDER BY name) r;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
141 RETURN _result;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
142 END;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
143 $$;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
144
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
145 CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
146 LANGUAGE plpgsql STABLE PARALLEL SAFE
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
147 AS $$
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
148 DECLARE
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
149 _result jsonb;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
150 BEGIN
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
151 SELECT COALESCE(json_agg(r),'[]')
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
152 INTO _result
3753
78ba9b2574ff search: include id in search result to mark sections/stretches as selected
Markus Kottlaender <markus@intevation.de>
parents: 3741
diff changeset
153 FROM (SELECT id,
78ba9b2574ff search: include id in search result to mark sections/stretches as selected
Markus Kottlaender <markus@intevation.de>
parents: 3741
diff changeset
154 objnam AS name,
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
155 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
156 'stretch' AS type
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4021
diff changeset
157 FROM users.stretches
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
158 WHERE objnam ILIKE '%' || search_string || '%'
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
159 OR nobjnam ILIKE '%' || search_string || '%'
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
160 ORDER BY name) r;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
161 RETURN _result;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
162 END;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
163 $$;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
164
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
165 CREATE OR REPLACE FUNCTION search_most(
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
166 search_string text,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
167 search_time timestamp with time zone)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
168 RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
169 LANGUAGE plpgsql STABLE PARALLEL SAFE
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
170 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 BEGIN
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
172 RETURN search_hectometre(search_string)
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
173 || search_bottlenecks(search_string,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents: 4874
diff changeset
174 COALESCE(search_time, current_timestamp))
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
175 || search_gauges(search_string)
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
176 || search_sections(search_string)
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
177 || search_stretches(search_string)
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
178 || search_cities(search_string);
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
180 $$;