Mercurial > gemma

annotate schema/search_functions.sql @ 4969:5621586e7a54 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid checking the same condition twice An entry should either be UPDATEd or INSERTed, if it's not already in the database. Thus simply INSERT if there was no UPDATE.
author Tom Gottfried <tom@intevation.de>
date Fri, 28 Feb 2020 17:55:55 +0100
parents 7cbe5d32a614
children a6e5bb85f0a7
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
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
7 -- Copyright (C) 2018,2019 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
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
46 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
47 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 INTO _result
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 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
54 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
55 'bottleneck' AS type,
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
56 bottleneck_id AS location
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 FROM waterway.bottlenecks
4874
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
58 WHERE (objnam ILIKE '%' || search_string || '%'
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents: 4389
diff changeset
59 OR bottleneck_id ILIKE '%' || search_string || '%')
4021
b10f210af325 Fixed latest fix to search-bottlenecks
Sascha Wilde <wilde@intevation.de>
parents: 4019
diff changeset
60 AND validity @> now()
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 ORDER BY name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 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
67 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
68 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 DECLARE
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 _result jsonb;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 BEGIN
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 SELECT COALESCE(json_agg(r),'[]')
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 INTO _result
3779
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
74 FROM (SELECT name,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
75 country_code AS location,
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 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
77 'city' AS type
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 FROM waterway.geonames
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 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
80 AND (name ILIKE '%' || search_string || '%'
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 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
83 feature_code::text),
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 name) r;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 RETURN _result;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 $$;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88
3737
e3f15a163823 Fixed typo in search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3736
diff changeset
89 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
90 LANGUAGE plpgsql STABLE PARALLEL SAFE
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
91 AS $$
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
92 DECLARE
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
93 _result jsonb;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
94 BEGIN
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
95 IF is_ISRSstring(search_string) THEN
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
96 SELECT COALESCE(json_agg(r),'[]')
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
97 INTO _result
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
98 FROM (
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
99 SELECT objname AS name,
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
100 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
101 'gauge' AS type,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
102 isrs_asText(location) AS locationcode
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
103 FROM waterway.gauges
3736
4cc3b28fd87b Ignore erased gauges in search.
Sascha Wilde <wilde@intevation.de>
parents: 3735
diff changeset
104 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
105 ORDER BY name) r;
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
106 ELSE
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
107 SELECT COALESCE(json_agg(r),'[]')
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
108 INTO _result
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
109 FROM (
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
110 SELECT objname AS name,
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
111 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
112 'gauge' AS type,
33180c25bd87 Added extra information to search results (wwname, ISRS code)
Sascha Wilde <wilde@intevation.de>
parents: 3753
diff changeset
113 isrs_asText(location) AS locationcode
3732
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
114 FROM waterway.gauges
3736
4cc3b28fd87b Ignore erased gauges in search.
Sascha Wilde <wilde@intevation.de>
parents: 3735
diff changeset
115 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
116 ORDER BY name) r;
b1fe61b58048 Allow to search gauges per ISRS code.
Sascha Wilde <wilde@intevation.de>
parents: 3383
diff changeset
117 END IF;
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
118 RETURN _result;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
119 END;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
120 $$;
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
121
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
122 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
123 LANGUAGE plpgsql STABLE PARALLEL SAFE
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
124 AS $$
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
125 DECLARE
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
126 _result jsonb;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
127 BEGIN
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
128 SELECT COALESCE(json_agg(r),'[]')
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
129 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
130 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
131 objnam AS name,
3740
6f1d5f310e08 Search delivers now bbox (geometry) for sections and bottlenecks.
Sascha Wilde <wilde@intevation.de>
parents: 3739
diff changeset
132 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
133 'section' AS type
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
134 FROM waterway.sections
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
135 WHERE objnam ILIKE '%' || search_string || '%'
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
136 OR nobjnam ILIKE '%' || search_string || '%'
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
137 ORDER BY name) r;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
138 RETURN _result;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
139 END;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
140 $$;
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
141
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
142 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
143 LANGUAGE plpgsql STABLE PARALLEL SAFE
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
144 AS $$
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
145 DECLARE
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
146 _result jsonb;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
147 BEGIN
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
148 SELECT COALESCE(json_agg(r),'[]')
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
149 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
150 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
151 objnam AS name,
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
152 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
153 'stretch' AS type
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4021
diff changeset
154 FROM users.stretches
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
155 WHERE objnam ILIKE '%' || search_string || '%'
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
156 OR nobjnam ILIKE '%' || search_string || '%'
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
157 ORDER BY name) r;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
158 RETURN _result;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
159 END;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
160 $$;
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
161
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
162 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
3735
96230eadf96e Added some optimizer hints to search functions.
Sascha Wilde <wilde@intevation.de>
parents: 3734
diff changeset
163 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
164 AS $$
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 BEGIN
3734
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
166 RETURN search_hectometre(search_string)
1504856c9378 Moved rhm search to db.
Sascha Wilde <wilde@intevation.de>
parents: 3732
diff changeset
167 || search_bottlenecks(search_string)
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
168 || search_gauges(search_string)
3383
194feb1a7e37 Added sections to searchable objects.
Sascha Wilde <wilde@intevation.de>
parents: 2452
diff changeset
169 || search_sections(search_string)
3741
71de5ce5a402 Added search for stretches.
Sascha Wilde <wilde@intevation.de>
parents: 3740
diff changeset
170 || search_stretches(search_string)
2452
0f1a915344c5 Added the ability to search for gauges.
Sascha Wilde <wilde@intevation.de>
parents: 1833
diff changeset
171 || search_cities(search_string);
1833
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 END;
b9c59050014a Make search functions self-replacing and indicate that in filename
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 $$;